db_pesanan.sql

DROP TABLE IF EXISTS `tbl_barang`;

CREATE TABLE `tbl_barang` (
  `kode_barang` char(9) NOT NULL,
  `nama_barang` varchar(50) NOT NULL,
  `harga` double NOT NULL,
  `id_kategori` mediumint(9) NOT NULL,
  PRIMARY KEY (`kode_barang`),
  KEY `id_kategori` (`id_kategori`),
  CONSTRAINT `tbl_barang_ibfk_1` FOREIGN KEY (`id_kategori`) REFERENCES `tbl_kategori` (`id_kategori`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tbl_barang` VALUES ('BR000001','Eldon Base for stackable storage',10000,1),('BR000002','1.7 Cubic Foot Compact \"Cube\" Office Refrigerator',20000,2),('BR000003','Cardinal Slant-D Ring Binder ',25000,3),('BR000004','R380',15000,4),('BR000005','Holmes HEPA Air Purifier',200000,2);

DROP TABLE IF EXISTS `tbl_kabupaten`;

CREATE TABLE `tbl_kabupaten` (
  `id_kabupaten` char(50) NOT NULL,
  `nama_kabupaten` varchar(50) NOT NULL,
  PRIMARY KEY (`id_kabupaten`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `tbl_kabupaten` VALUES ('18013','Lampung Selatan'),('18021','Lampung Tengah'),('18036','Lampung Utara'),('18044','Lampung Barat'),('18045','Pesisir Barat'),('18052','Tanggamus'),('18067','Tulang Bawang'),('18075','Lampung Timur'),('18077','Pesawaran'),('18078','Tulang Bawang Barat'),('18079','Mesuji'),('18083','Way Kanan'),('18097','Pringsewu'),('18721','Metro'),('18723','Bandar Lampung');


DROP TABLE IF EXISTS `tbl_kategori`;

CREATE TABLE `tbl_kategori` (
  `id_kategori` mediumint(9) NOT NULL AUTO_INCREMENT,
  `kategori_barang` varchar(50) NOT NULL,
  PRIMARY KEY (`id_kategori`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;


INSERT INTO `tbl_kategori` VALUES (1,'Storage & Organization'),(2,'Appliances'),(3,'Binders and Binder Accessories'),(4,'Telephones and Communication'),(5,'Office Furnishing'),(6,'Paper');


DROP TABLE IF EXISTS `tbl_kecamatan`;

CREATE TABLE `tbl_kecamatan` (
  `id_kecamatan` char(8) NOT NULL,
  `nama_kecamatan` varchar(50) NOT NULL,
  `id_kabupaten` char(50) NOT NULL,
  PRIMARY KEY (`id_kecamatan`),
  KEY `id_kabupaten` (`id_kabupaten`),
  CONSTRAINT `tbl_kecamatan_ibfk_1` FOREIGN KEY (`id_kabupaten`) REFERENCES `tbl_kabupaten` (`id_kabupaten`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `tbl_kecamatan` VALUES ('1801301','Bakauheni','18013'),('1801302','Candipuro','18013'),('1801303','Jati Agung','18013'),('1801304','Kalianda','18013'),('1801305','Katibung','18013'),('1801306','Ketapang','18013'),('1801307','Merbau Mataram','18013'),('1801308','Natar','18013'),('1801309','Palas','18013'),('1801310','Penengahan','18013');

DROP TABLE IF EXISTS `tbl_pelanggan`;

CREATE TABLE `tbl_pelanggan` (
  `kode_pelanggan` char(10) NOT NULL,
  `nama_pelanggan` varchar(50) NOT NULL,
  `alamat` mediumtext,
  `id_kecamatan` char(8) NOT NULL,
  PRIMARY KEY (`kode_pelanggan`),
  KEY `id_kecamatan` (`id_kecamatan`),
  CONSTRAINT `tbl_pelanggan_ibfk_1` FOREIGN KEY (`id_kecamatan`) REFERENCES `tbl_kecamatan` (`id_kecamatan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `tbl_pelanggan` VALUES ('701021001','Agustina Regar','RT/RW 007/004 Gang I Kelurahan Way Mili','1801301'),('701042002','Fatimah','Jln. P. Tirtayasa','1801302'),('701141003','Mega Hengky','Jln. abd mutholib Kelurahan Segala Mider','1801303'),('701284004','Anwar','Jln. Teluk Pandan','1801304'),('702053005','Dipo Abilianto','DSN NEGARA RATU II Kelurahan Negara Ratu II','1801305'),('702072006','Fajar Agviola Putra','Jl. Saatria 1 no.7/99, ratulangi, kelurahan penengahan','1801306'),('702112007','Mardiana Zein','Kelurahan Korpri Jaya','1801307'),('702171008','Dwi Anggun Ariani','Jl. Perintis Kemerdekaan Gg. Madlias','1801308'),('702183009','Kiki Rizkia Zahara','Jln. Raya Serdang','1801309'),('702272011','Rinaita Sihotang','Jl. Soekarno Hatta','1801310');


DROP TABLE IF EXISTS `tbl_pesanan`;

CREATE TABLE `tbl_pesanan` (
  `no_pesanan` char(10) NOT NULL,
  `tgl_pesan` date DEFAULT NULL,
  `kode_pelanggan` char(10) NOT NULL,
  `kode_barang` char(9) NOT NULL,
  `jumlah_pesan` double NOT NULL,
  PRIMARY KEY (`no_pesanan`),
  KEY `kode_pelanggan` (`kode_pelanggan`),
  KEY `kode_barang` (`kode_barang`),
  CONSTRAINT `tbl_pesanan_ibfk_1` FOREIGN KEY (`kode_pelanggan`) REFERENCES `tbl_pelanggan` (`kode_pelanggan`),
  CONSTRAINT `tbl_pesanan_ibfk_2` FOREIGN KEY (`kode_barang`) REFERENCES `tbl_barang` (`kode_barang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `tbl_pesanan` VALUES ('PS.001','2017-02-02','701021001','BR000001',2),('PS.002','2017-02-17','701042002','BR000002',3),('PS.003','2017-09-09','701141003','BR000003',4),('PS.004','2017-10-10','701284004','BR000004',2),('PS.005','2017-12-12','702053005','BR000005',4),('PS.006','2018-01-02','702072006','BR000002',2),('PS.007','2018-03-06','702112007','BR000001',5),('PS.008','2018-03-08','702171008','BR000002',6),('PS.009','2018-04-05','702183009','BR000003',8),('PS.010','2018-05-01','701021001','BR000004',6);