CREATE DATABASE TUBES_APSI_Kel11
USE TUBES_APSI_Kel11;
-- TABEL PELANGGAN
CREATE TABLE pelanggan (
email_pelanggan VARCHAR(100) PRIMARY KEY,
password_pelanggan VARCHAR(255) NOT NULL,
nama_pelanggan VARCHAR(100) NOT NULL,
gender_pelanggan ENUM('Pria', 'Wanita'),
no_telepon_pelanggan VARCHAR(20),
alamat_pelanggan TEXT
);
-- TABEL STAFF
CREATE TABLE staff (
email_staff VARCHAR(100) PRIMARY KEY,
password_staff VARCHAR(255) NOT NULL,
nama_staff VARCHAR(100) NOT NULL,
gender_staff ENUM('Pria', 'Wanita'),
posisi_staff ENUM('Admin', 'Tim Manajemen'),
no_telepon VARCHAR(20)
);
-- TABEL SUPPLIER
CREATE TABLE supplier (
id_supplier INT AUTO_INCREMENT PRIMARY KEY,
nama_supplier VARCHAR(100) NOT NULL,
telepon_supplier VARCHAR(100),
alamat_supplier TEXT
);
-- TABEL PRODUK
CREATE TABLE produk (
id_produk INT AUTO_INCREMENT PRIMARY KEY,
kode_item VARCHAR(50) UNIQUE NOT NULL,
nama_produk VARCHAR(100) NOT NULL,
merek_produk ENUM('Nike', 'Yonex', 'Speedo','Mikasa'),
ukuran_produk VARCHAR(100) NOT NULL,
gender ENUM('Pria', 'Wanita', 'Unisex'),
stok INT DEFAULT 0,
stok_minimum INT DEFAULT 0,
harga_produk DECIMAL(10,2) NOT NULL,
id_supplier INT,
FOREIGN KEY (id_supplier) REFERENCES supplier(id_supplier)
);
-- TABEL KERANJANG
CREATE TABLE keranjang (
id_keranjang INT AUTO_INCREMENT PRIMARY KEY,
id_transaksi INT NOT NULL,
id_produk INT NOT NULL,
kuantitas INT NOT NULL DEFAULT 1,
personalisasi VARCHAR(100),
FOREIGN KEY (id_transaksi) REFERENCES transaksi (id_transaksi)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (id_produk) REFERENCES produk (id_produk) ON DELETE
RESTRICT ON UPDATE CASCADE
);
-- TABEL TRANSAKSI
CREATE TABLE Transaksi (
id_transaksi INT AUTO_INCREMENT PRIMARY KEY,
email_pelanggan VARCHAR(100) NOT NULL,
email_staff VARCHAR(100),
Date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
id_pengiriman INT,
id_pembayaran INT,
FOREIGN KEY (email_pelanggan) REFERENCES
pelanggan(email_pelanggan)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (email_staff) REFERENCES staff(email_staff)
ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY (id_pengiriman ) REFERENCES pengiriman(id_pengiriman
)
ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY (id_pembayaran) REFERENCES pembayaran(id_pembayaran)
ON UPDATE CASCADE ON DELETE SET NULL
);
-- TABEL PENGIRIMAN
CREATE TABLE pengiriman (
id_pengiriman INT AUTO_INCREMENT PRIMARY KEY,
id_transaksi INT,
metode_pengiriman ENUM ('Gojek', 'JNT', 'Shopee'),
alamat_pengiriman TEXT,
status ENUM('diproses', 'dikirim', 'selesai') DEFAULT
'diproses',
harga_pengiriman DECIMAL(10, 2) NOT NULL,
tanggal_kirim TIMESTAMP NULL
);
-- TABEL PEMBAYARAN
CREATE TABLE pembayaran (
id_pembayaran INT AUTO_INCREMENT PRIMARY KEY,
id_transaksi INT,
metode_pembayaran ENUM('Bank Transfer', 'Dana', 'Debit Card'),
pemilik_akun_pembayaran VARCHAR(50),
tanggal_bayar TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status ENUM('pending', 'lunas', 'gagal') DEFAULT 'pending'
);
-- TABEL LAPORAN
CREATE TABLE laporan (
id_laporan INT AUTO_INCREMENT PRIMARY KEY,
email_staff VARCHAR(100),
jenis_laporan ENUM('Penjualan', 'Stok', 'Musiman', 'Trend') NOT
NULL,
tanggal_dibuat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
periode_awal DATE,
periode_akhir DATE,
deskripsi TEXT,
FOREIGN KEY (email_staff) REFERENCES staff(email_staff)
ON UPDATE CASCADE ON DELETE SET NULL
);