System Restoran Cepat Saji Pizza Hut - UAS - Tabel Fisik

TABEL FISIK

1. TABEL BEKERJA_PADA

SQL pembuat tabel :

create table BEKERJA_PADA
(
IDSHIFT char(6) not null,
IDSTAFF char(6) not null,
constraint PK_BEKERJA_PADA primary key (IDSHIFT, IDSTAFF)
);

SQL pengisi tabel :

INSERT INTO "GESTY"."BEKERJA_PADA" ("IDSHIFT", "IDSTAFF") VALUES ('sh0001', 'st0002');
INSERT INTO "GESTY"."BEKERJA_PADA" ("IDSHIFT", "IDSTAFF") VALUES ('sh0002', 'st0003');
INSERT INTO "GESTY"."BEKERJA_PADA" ("IDSHIFT", "IDSTAFF") VALUES ('sh0002', 'st0004');
INSERT INTO "GESTY"."BEKERJA_PADA" ("IDSHIFT", "IDSTAFF") VALUES ('sh0003', 'st0007');
INSERT INTO "GESTY"."BEKERJA_PADA" ("IDSHIFT", "IDSTAFF") VALUES ('sh0004', 'st0008');
INSERT INTO "GESTY"."BEKERJA_PADA" ("IDSHIFT", "IDSTAFF") VALUES ('sh0001', 'st0009');
INSERT INTO "GESTY"."BEKERJA_PADA" ("IDSHIFT", "IDSTAFF") VALUES ('sh0003', 'st0002');
INSERT INTO "GESTY"."BEKERJA_PADA" ("IDSHIFT", "IDSTAFF") VALUES ('sh0004', 'st0010');

2.TABEL CUSTOMER


SQL pembuat tabel :

create table CUSTOMER
(
IDCUSTOMER char(6) not null,
NAMACUST varchar(30),
ALAMATCUST varchar(30),
NOTELPCUST char(15),
YGPERNAHDIPSN varchar(30),
constraint PK_CUSTOMER primary key (IDCUSTOMER)
);

SQL pengisi tabel :

INSERT INTO "GESTY"."CUSTOMER" ("IDCUSTOMER", "NAMACUST", "ALAMATCUST", "NOTELPCUST", "YGPERNAHDIPSN") VALUES ('cu0001', 'gesty', 'gubeng kertajaya 9 raya', '031234534', 'Stuffed Crust Pizza, Meat Lover Pan Pizza');
INSERT INTO "GESTY"."CUSTOMER" ("IDCUSTOMER", "NAMACUST", "ALAMATCUST", "NOTELPCUST", "YGPERNAHDIPSN") VALUES ('cu0005', 'adi', 'mulyosari', '085746397782', 'stuffed crush pizza');
INSERT INTO "GESTY"."CUSTOMER" ("IDCUSTOMER", "NAMACUST", "ALAMATCUST", "NOTELPCUST", "YGPERNAHDIPSN") VALUES ('cu0002', 'laila', 'kertajaya', '0318299945', 'garlic bread,meat lovers pizza');
INSERT INTO "GESTY"."CUSTOMER" ("IDCUSTOMER", "NAMACUST", "ALAMATCUST", "NOTELPCUST", "YGPERNAHDIPSN") VALUES ('cu0004', 'santosa', 'keputih', '082748388392', 'meat lovers pizza,stuffed crush pizza');
INSERT INTO "GESTY"."CUSTOMER" ("IDCUSTOMER", "NAMACUST", "ALAMATCUST", "NOTELPCUST", "YGPERNAHDIPSN") VALUES ('cu0003', 'dian', 'gebang wetan', '081239948687', 'pasta, vanilla milkshake')

3.TABEL MELAYANI


SQL pembuat tabel :

create table MELAYANI
(
IDSTAFF char(6) not null,
IDCUSTOMER char(6) not null,
constraint PK_MELAYANI primary key (IDSTAFF, IDCUSTOMER)
);

SQL pengisi tabel :

INSERT INTO "GESTY"."MELAYANI" ("IDSTAFF", "IDCUSTOMER") VALUES ("st0004", "cu0002")
INSERT INTO "GESTY"."MELAYANI" ("IDSTAFF", "IDCUSTOMER") VALUES ("st0003", "cu0001")

4.TABEL MEMESAN


SQL pembuat tabel :

create table MEMESAN
(
IDMENU char(6) not null,
IDCUSTOMER char(6) not null,
constraint PK_MEMESAN primary key (IDMENU, IDCUSTOMER)
);

SQL pengisi tabel :

INSERT INTO "GESTY"."MEMESAN" ("IDMENU", "IDCUSTOMER") VALUES ('me0001', 'cu0001');
INSERT INTO "GESTY"."MEMESAN" ("IDMENU", "IDCUSTOMER") VALUES ('me0005', 'cu0001');
INSERT INTO "GESTY"."MEMESAN" ("IDMENU", "IDCUSTOMER") VALUES ('me0008', 'cu0001');
INSERT INTO "GESTY"."MEMESAN" ("IDMENU", "IDCUSTOMER") VALUES ('me0014', 'cu0001');
INSERT INTO "GESTY"."MEMESAN" ("IDMENU", "IDCUSTOMER") VALUES ('me0005', 'cu0002');
INSERT INTO "GESTY"."MEMESAN" ("IDMENU", "IDCUSTOMER") VALUES ('me0010', 'cu0002');
INSERT INTO "GESTY"."MEMESAN" ("IDMENU", "IDCUSTOMER") VALUES ('me0011', 'cu0002');


5.TABEL MENU


SQL pembuat tabel :

create table MENU
(
IDMENU char(6) not null,
NAMAMENU varchar(30),
UKURAN char(6),
HARGA numeric(8,2),
constraint PK_MENU primary key (IDMENU)
);

SQL pengisi tabel :

INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0003', 'Meat Lover', 'medium', '30000');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0001', 'Meat Lover', 'small', '20000');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0002', 'Meat Lover', 'large', '40000');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0005', 'spaghetti', '', '15500');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0006', 'Pan Pizza', 'large', '50000');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0007', 'Fruit Punch', '', '11000');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0008', 'Fruit Salad', '', '10000');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0009', 'Sensasi Delight', '', '15000');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0010', 'Strawberry Juice', '', '13000');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0011', 'Stuffed Crush Pizza', 'medium', '21000');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0012', 'Stuffed Crush Pizza', 'large', '45000');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0013', 'Mini Pizza', '', '18000');
INSERT INTO "GESTY"."MENU" ("IDMENU", "NAMAMENU", "UKURAN", "HARGA") VALUES ('me0014', 'Chocolate Milkshake', '', '12500');

6.TABEL MENYIAPKAN


SQL pembuat tabel :

create table MENYIAPKAN
(
IDMENU char(6) NOT NULL,
IDSTAFF char(6) NOT NULL,
constraint PK_MENYIPKAN primary key (IDMENU, IDSTAFF)
);

SQL pengisi tabel :

INSERT INTO "GESTY"."MENYIAPKAN" ("IDMENU", "IDSTAFF") VALUES ('me0001', 'st0002');
INSERT INTO "GESTY"."MENYIAPKAN" ("IDMENU", "IDSTAFF") VALUES ('me0005', 'st0002');
INSERT INTO "GESTY"."MENYIAPKAN" ("IDMENU", "IDSTAFF") VALUES ('me0011', 'st0002');
INSERT INTO "GESTY"."MENYIAPKAN" ("IDMENU", "IDSTAFF") VALUES ('me0010', 'st0002');
INSERT INTO "GESTY"."MENYIAPKAN" ("IDMENU", "IDSTAFF") VALUES ('me0008', 'st0002');
INSERT INTO "GESTY"."MENYIAPKAN" ("IDMENU", "IDSTAFF") VALUES ('me0014', 'st0014');
INSERT INTO "GESTY"."MENYIAPKAN" ("IDMENU", "IDSTAFF") VALUES ('me0002', 'st0014');
INSERT INTO "GESTY"."MENYIAPKAN" ("IDMENU", "IDSTAFF") VALUES ('me0004', 'st0014');

7.TABEL RESTORAN


SQL pembuat tabel :

create table RESTORAN
(
IDRESTORAN char(6) not null,
IDSTAFF char(6),
NAMAREST varchar(30),
NOTELPREST char(15),
KOTA varchar(30),
PROVINSI varchar(30),
WILAYAHPELAYANAN varchar(50),
constraint PK_RESTORAN primary key (IDRESTORAN)
);

SQL pengisi tabel :

INSERT INTO "GESTY"."RESTORAN" ("IDRESTORAN", "IDSTAFF", "NAMAREST", "NOTELPREST", "KOTA", "PROVINSI", "WILAYAHPELAYANAN") VALUES ('re0003', 'st0003', 'Pizza Hut Mulyosari', '03189343663', 'Surabaya', 'Jawa Timur','Mulyosari,Keputih');
INSERT INTO "GESTY"."RESTORAN" ("IDRESTORAN", "IDSTAFF", "NAMAREST", "NOTELPREST", "KOTA", "PROVINSI", "WILAYAHPELAYANAN") VALUES ('re0001', 'st0004', 'Pizza Hut AR Hakim ', '0319399302', 'Surabaya', 'Jawa Timur','Gebang,Keputih');
INSERT INTO "GESTY"."RESTORAN" ("IDRESTORAN", "IDSTAFF", "NAMAREST", "NOTELPREST", "KOTA", "PROVINSI", "WILAYAHPELAYANAN") VALUES ('re0002', 'st0012', 'Pizza Hut Delta', '0312112394', 'Surabaya', 'Jawa Timur','Gubeng,Karangmenjangan');

8.TABEL SHIFT


SQL pembuat tabel :

create table SHIFT
(
IDSHIFT char(6) not null,
HARI char(7),
JAM time,
constraint PK_SHIFT primary key (IDSHIFT)
);

SQL pengisi tabel :

INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0001', 'senin', '10.00-16.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0002', 'senin', '16.00-22.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0003', 'selasa', '10.00-16.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0004', 'selasa', '16.00-22.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0005', 'rabu', '10.00-16.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0006', 'rabu', '16.00-22.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0007', 'kamis', '10.00-16.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0008', 'kamis', '16.00-22.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0009', 'jumat', '10.00-16.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0010', 'jumat', '16.00-22.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0011', 'sabtu', '10.00-16.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0012', 'sabtu', '16.00-22.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0013', 'minggu', '10.00-16.00');
INSERT INTO "GESTY"."SHIFT" ("IDSHIFT", "HARI", "JAM") VALUES ('sh0014', 'minggu', '16.00-22.00');

9.TABEL STAFF


SQL pembuat tabel :

create table STAFF
(
IDSTAFF char(6) not null,
NAMASTAFF varchar(30),
ALAMATSTAFF varchar(30),
NOTELPSTAFF char(15),
constraint PK_STAFF primary key (IDSTAFF)
);

SQL pengisi tabel :

INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0005', 'Ryan', 'Pucang,Surabaya', '0857645765334');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0001', 'Adit', 'Pucang,Surabaya', '0857647838948');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0002', 'Susi', 'Kalianom', '0817289384750');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0003', 'Tito', 'Gubeng', '0856189938874');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0004', 'Rena', 'Kertajaya', '03100093884');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0006', 'Dita', 'Pucang', '03188389282');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0007', 'Susan', 'Keputih', '0892837463892');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0008', 'Tio', 'Jemur Sari', '0857182930665');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0009', 'Lisa', 'Karangmenjangan', '088374000111');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0010', 'Rendra', 'Jemur Sari', '0856389898899');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0011', 'Angga', 'Pucang Anom', '0857110987789');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0012', 'Anggi', 'Kalianom', '081750392201');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0013', 'Micky', 'Gubeng', '0319928374');
INSERT INTO "GESTY"."STAFF" ("IDSTAFF", "NAMASTAFF", "ALAMATSTAFF", "NOTELPSTAFF") VALUES ('st0014', 'Anton', 'Kertajaya', '031008848493')

10.TABEL STOCK


SQL pembuat tabel :

create table STOCK
(
IDSTOCK char(6) not null,
IDSTAFF char(6),
NAMASTOCK varchar(30),
JUMLAHSTOCK integer,
TGLBELI date,
constraint PK_STOCK primary key (IDSTOCK)
);

SQL pengisi tabel :

INSERT INTO "GESTY"."STOCK" ("IDSTOCK", "IDSTAFF", "NAMASTOCK", "JUMLAHSTOCK", "TGLBELI") VALUES ('sto001', 'st0001', 'daging', '50', TO_DATE('01-01-2009', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "GESTY"."STOCK" ("IDSTOCK", "IDSTAFF", "NAMASTOCK", "JUMLAHSTOCK", "TGLBELI") VALUES ('sto002', 'st0001', 'susu', '20', TO_DATE('01-01-2009', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "GESTY"."STOCK" ("IDSTOCK", "IDSTAFF", "NAMASTOCK", "JUMLAHSTOCK", "TGLBELI") VALUES ('sto003', 'st0001', 'selada', '20', TO_DATE('01-01-2009', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "GESTY"."STOCK" ("IDSTOCK", "IDSTAFF", "NAMASTOCK", "JUMLAHSTOCK", "TGLBELI") VALUES ('sto004', 'st0001', 'keju', '30', TO_DATE('01-01-2009', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "GESTY"."STOCK" ("IDSTOCK", "IDSTAFF", "NAMASTOCK", "JUMLAHSTOCK", "TGLBELI") VALUES ('sto005', 'st0001', 'tomat', '15', TO_DATE('01-01-2009', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "GESTY"."STOCK" ("IDSTOCK", "IDSTAFF", "NAMASTOCK", "JUMLAHSTOCK", "TGLBELI") VALUES ('sto006', 'st0001', 'kentang', '15', TO_DATE('01-01-2009', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "GESTY"."STOCK" ("IDSTOCK", "IDSTAFF", "NAMASTOCK", "JUMLAHSTOCK", "TGLBELI") VALUES ('sto007', 'st0001', 'sosis', '10', TO_DATE('01-01-2009', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "GESTY"."STOCK" ("IDSTOCK", "IDSTAFF", "NAMASTOCK", "JUMLAHSTOCK", "TGLBELI") VALUES ('sto008', 'st0001', 'telur', '25', TO_DATE('01-01-2009', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "GESTY"."STOCK" ("IDSTOCK", "IDSTAFF", "NAMASTOCK", "JUMLAHSTOCK", "TGLBELI") VALUES ('sto009', 'st0001', 'jeruk', '10', TO_DATE('01-01-2009', 'YYYY-MM-DD HH24:MI:SS'));

11.TABEL TRANSAKSI


SQL pembuat tabel :

create table TRANSAKSI
(
IDTRANSAKSI char(6) not null,
IDCUSTOMER char(6),
IDSTAFF char(6),
TGLTRANSAKSI timestamp,
NAMAPESANAN varchar(30),
JUMLAHPESANAN integer,
TOTALHARGA numeric(8,2),
constraint PK_TRANSAKSI primary key (IDTRANSAKSI)
);

SQL pengisi tabel :

INSERT INTO "GESTY"."TRANSAKSI" ("IDTRANSAKSI", "IDCUSTOMER", "IDSTAFF", "TGLTRANSAKSI", "NAMAPESANAN", "JUMLAHPESANAN", "TOTALHARGA") VALUES ('tr0001', 'cu0001', 'st0003', TO_DATE('02-01-2009', 'YYYY-MM-DD HH24:MI:SS'), 'meat lover pizza small', '2', '40.000');
INSERT INTO "GESTY"."TRANSAKSI" ("IDTRANSAKSI", "IDCUSTOMER", "IDSTAFF", "TGLTRANSAKSI", "NAMAPESANAN", "JUMLAHPESANAN", "TOTALHARGA") VALUES ('tr0002', 'cu0001', 'st0003', TO_DATE('02-01-2009', 'YYYY-MM-DD HH24:MI:SS'), 'fruit salad', '1', '10.000');
INSERT INTO "GESTY"."TRANSAKSI" ("IDTRANSAKSI", "IDCUSTOMER", "IDSTAFF", "TGLTRANSAKSI", "NAMAPESANAN", "JUMLAHPESANAN", "TOTALHARGA") VALUES ('tr0003', 'cu0001', 'st0003', TO_DATE('02-01-2009', 'YYYY-MM-DD HH24:MI:SS'), 'spaghetti', '1', '15.500');
INSERT INTO "GESTY"."TRANSAKSI" ("IDTRANSAKSI", "IDCUSTOMER", "IDSTAFF", "TGLTRANSAKSI", "NAMAPESANAN", "JUMLAHPESANAN", "TOTALHARGA") VALUES ('tr0004', 'cu0001', 'st0003', TO_DATE('02-01-2009', 'YYYY-MM-DD HH24:MI:SS'), 'chocolate milkshake', '2', '25.000');
INSERT INTO "GESTY"."TRANSAKSI" ("IDTRANSAKSI", "IDCUSTOMER", "IDSTAFF", "TGLTRANSAKSI", "NAMAPESANAN", "JUMLAHPESANAN", "TOTALHARGA") VALUES ('tr0005', 'cu0002', 'st0004', TO_DATE('03-01-2009', 'YYYY-MM-DD HH24:MI:SS'), 'stuffed crush pizza medium', '1', '21.000');
INSERT INTO "GESTY"."TRANSAKSI" ("IDTRANSAKSI", "IDCUSTOMER", "IDSTAFF", "TGLTRANSAKSI", "NAMAPESANAN", "JUMLAHPESANAN", "TOTALHARGA") VALUES ('tr0006', 'cu0002', 'st0004', TO_DATE('03-01-2009', 'YYYY-MM-DD HH24:MI:SS'), 'spaghetti', '1', '15.500');
INSERT INTO "GESTY"."TRANSAKSI" ("IDTRANSAKSI", "IDCUSTOMER", "IDSTAFF", "TGLTRANSAKSI", "NAMAPESANAN", "JUMLAHPESANAN", "TOTALHARGA") VALUES ('tr0007', 'cu0002', 'st0004', TO_DATE('03-01-2009', 'YYYY-MM-DD HH24:MI:SS'), 'strawberry juice', '1', '13.000');