Nama : Alfi Perdiansyah Putra (245150701111028)
: Rakapaksi Satrya Putra (245150701111034)
Mata Kuliah : Pemograman SQL
Kelas : TI - A
Dosen Pengampu : Welly Purnomo, ST., [Link].
Query
CREATE SCHEMA OYI;
USE OYI;
-- TABLE: Customer
CREATE TABLE Customer (
ID_Customer INT AUTO_INCREMENT PRIMARY KEY,
Nama VARCHAR(100) NOT NULL,
Kontak VARCHAR(20)
);
-- TABLE: Pegawai
CREATE TABLE Pegawai (
ID_Pegawai INT AUTO_INCREMENT PRIMARY KEY,
Nama VARCHAR(100) NOT NULL,
Jabatan VARCHAR(50),
Shift ENUM('Pagi','Siang','Malam'),
Gaji DECIMAL(12,2),
Status_Pegawai VARCHAR(20)
);
-- TABLE: Menu
CREATE TABLE Menu (
ID_Menu INT AUTO_INCREMENT PRIMARY KEY,
Nama VARCHAR(100) NOT NULL,
Harga DECIMAL(10,2) NOT NULL,
Kategori VARCHAR(50),
Ketersediaan BOOLEAN DEFAULT TRUE,
Estimasi_Waktu INT
);
-- TABLE: Meja
CREATE TABLE Meja (
ID_Meja INT AUTO_INCREMENT PRIMARY KEY,
Nomor_Meja VARCHAR(10) NOT NULL,
Kapasitas INT,
Kategori VARCHAR(50),
Status VARCHAR(20)
);
-- TABLE: Reservasi
CREATE TABLE Reservasi (
ID_Reservasi INT AUTO_INCREMENT PRIMARY KEY,
Tanggal_Reservasi DATETIME NOT NULL,
Status_Reservasi VARCHAR(20) DEFAULT 'Pending',
ID_Customer INT,
ID_Meja INT,
FOREIGN KEY (ID_Customer) REFERENCES Customer(ID_Customer),
FOREIGN KEY (ID_Meja) REFERENCES Meja(ID_Meja)
);
-- TABLE: Pesanan
CREATE TABLE Pesanan (
ID_Pesanan INT AUTO_INCREMENT PRIMARY KEY,
Tanggal DATETIME DEFAULT CURRENT_TIMESTAMP,
Jenis_Pesanan ENUM('Dine-in','Take-away','Delivery'),
Total DECIMAL(12,2) DEFAULT 0,
ID_Pegawai INT,
ID_Meja INT,
FOREIGN KEY (ID_Pegawai) REFERENCES Pegawai(ID_Pegawai),
FOREIGN KEY (ID_Meja) REFERENCES Meja(ID_Meja)
);
-- TABLE: Detail_Pesanan
CREATE TABLE Detail_Pesanan (
ID_Detail INT AUTO_INCREMENT PRIMARY KEY,
ID_Pesanan INT,
ID_Menu INT,
Quantity INT NOT NULL,
Harga_Satuan DECIMAL(10,2) NOT NULL,
Subtotal DECIMAL(12,2) NOT NULL,
FOREIGN KEY (ID_Pesanan) REFERENCES Pesanan(ID_Pesanan),
FOREIGN KEY (ID_Menu) REFERENCES Menu(ID_Menu)
);
-- TABLE: Pembayaran
CREATE TABLE Pembayaran (
ID_Pembayaran INT AUTO_INCREMENT PRIMARY KEY,
Metode ENUM('Cash','QRIS','Kartu') NOT NULL,
Jumlah_Bayar DECIMAL(12,2) NOT NULL,
Tanggal_Bayar DATETIME DEFAULT CURRENT_TIMESTAMP,
ID_Pesanan INT,
FOREIGN KEY (ID_Pesanan) REFERENCES Pesanan(ID_Pesanan)
);
Screenshot