Hasil Normalisasi Pertemuan 1 (23 Februari 2023)

drop table if exists kategoriProduk;
create table kategoriProduk (
id BIGINT(20) not null auto_increment primary key,
nama varchar(255)
);
INSERT INTO `kategoriproduk` (`id`, `nama`) VALUES
(1, 'Ponsel'),
(2, 'Komputer'),
(3, 'Memory');

drop table if exists produk;
create table produk (
id BIGINT(20) not null auto_increment primary key,
idKategori BIGINT(20) null,
nama varchar(255) not null,
harga double(10, 2) not null,
stok int(10) not null,
diskon double(10, 2) null,
foreign key (IDKategori) references kategoriProduk(ID) on delete cascade on update cascade
);
INSERT INTO `produk` (`id`, `idKategori`, `nama`, `harga`, `stok`, `diskon`) VALUES
(1, 1, 'Redmi 9A', 1500000.00, 10, NULL),
(2, 1, 'Oppo A53', 2000000.00, 10, NULL),
(3, NULL, 'HP Pavilion 15', 9000000.00, 5, NULL);

drop table if exists supplier;
create table supplier (
id BIGINT(20) not null auto_increment primary key,
nama varchar(255) not null,
alamat varchar(255) not null,
kota varchar(32) not null,
no_telepon varchar(16) not null
);
INSERT INTO `supplier` (`id`, `nama`, `alamat`, `kota`, `no_telepon`) VALUES
(1, 'PT ABC', 'Jl. ABC', 'Bengkulu', '08123'),
(2, 'PT XYZ', 'Jl. XYZ', 'Palembang', '081234');

drop table if exists pembelian;
create table pembelian (
id BIGINT(20) not null auto_increment primary key,
idSupplier bigint(20) null,
no_nota varchar(16) not null unique,
tanggal date not null,
status_pembayaran enum('1', '2') default 1 comment '1=lunas, 2=belum lunas',
metode_pembayaran varchar(32) not null,
diskon double(10, 2) null,
status_pengiriman enum('1', '2', '3') default 1 comment '1=belum dikirim, 2=dalam pengiriman, 3=sudah sampai',
biaya_pengiriman double(10, 2) not null,
total_harga double(10, 2) not null,
keterangan text null,
foreign key (idSupplier) references supplier (id) on delete set null on update cascade);
INSERT INTO `pembelian` (`id`, `idSupplier`, `no_nota`, `tanggal`, `status_pembayaran`, `metode_pembayaran`, `diskon`, `status_pengiriman`, `biaya_pengiriman`, `total_harga`, `keterangan`) VALUES
(1, 1, 'INV-123', '2023-08-03', '1', 'Transfer', NULL, '1', 300000.00, 14000000.00, NULL);

drop table if exists detailPembelian;
create table detailPembelian (
id BIGINT(20) not null auto_increment primary key,
idPembelian BIGINT(20) null,
idProduk BIGINT(20) null,
jumlah_pembelian int(10) not null,
harga double(10, 2) not null,
foreign key (idPembelian) references pembelian (id) on delete cascade on update cascade,
foreign key (idProduk) references produk (id) on delete cascade on update cascade);
INSERT INTO `detailpembelian` (`id`, `idPembelian`, `idProduk`, `jumlah_pembelian`, `harga`) VALUES
(1, 1, 1, 5, 1400000.00),
(2, 1, 2, 5, 1900000.00);