CREATE TABLE LOAIMATHANG(
MaLoaiMatHang CHAR(5) PRIMARY KEY,
TenLoaiMatHang CHAR(40) NOT NULL
)

INSERT INTO LOAIMATHANG(MaLoaiMatHang,TenLoaiMatHang)
VALUES
('MH001','Quan ao'),
('MH002','My pham'),
('MH003','Do gia dung'),
('MH004','Do dien tu')

CREATE TABLE KHUVUC(
MaKhuVuc CHAR(5) PRIMARY KEY,
TenKhuVuc CHAR(40) NOT NULL
)

INSERT INTO KHUVUC(MaKhuVuc, TenKhuVuc)
VALUES
('KV001','Son Tra'),
('KV002','Lien Chieu'),
('KV003','Ngu Hanh Son'),
('KV004','Hai Chau'),
('KV005','Thanh Khe')

CREATE TABLE KHOANGTHOIGIAN(
MaKhoangThoiGianGiaoHang CHAR(5) PRIMARY KEY,
MoTa CHAR(40) NOT NULL
)

INSERT INTO KHOANGTHOIGIAN(MaKhoangThoiGianGiaoHang, MoTa)
VALUES
('TG001','7h - 9h AM'),
('TG002','9h - 11h AM'),
('TG003','1h - 3h PM'),
('TG004','3h - 5h PM'),
('TG005','7h - 9h30 PM')

CREATE TABLE DICHVU(
MaDichVu CHAR(5) PRIMARY KEY,
TenDichVu CHAR(40) NOT NULL
)

INSERT INTO DICHVU(MaDichVu, TenDichVu)
VALUES
('DV001','Giao hang nguoi nhan tra tien phi'),
('DV002','Giao hang nguoi gui tra tien phi'),
('DV003','Giao hang cong ich (Khong tinh phi)')

CREATE TABLE THANHVIENGIAOHANG(
MaThanhVienGiaoHang CHAR(6) PRIMARY KEY,
TenThanhVienGiaoHang CHAR(40) NOT NULL,
NgaySinh DATE NOT NULL,
GioiTinh CHAR(3),
SoDTThanhVien CHAR(10) NOT NULL,
DiaChiThanhVien CHAR(40) NOT NULL
)

INSERT INTO THANHVIENGIAOHANG(MaThanhVienGiaoHang, TenThanhVienGiaoHang, NgaySinh, GioiTinh, SoDTThanhVien, DiaChiThanhVien)
VALUES
('TV0001', 'Nguyen Van A', '11/20/1995', 'Nam', '0905111111', '23 Ong Ich Khiem'),
('TV0002', 'Nguyen Van B', '11/26/1992', 'Nu', '0905111112', '234 Ton Duc Thang'),
('TV0003', 'Nguyen Van C', '11/30/1990', 'Nu', '0905111113', '45 Hoang Dieu'),
('TV0004', 'Nguyen Van D', '7/8/1995', 'Nam', '0905111114', '23/33 Ngu Hang Son'),
('TV0005', 'Nguyen Van E', '2/4/1991', 'Nam', '0905111115', '56 Dinh Thi Dieu')

CREATE TABLE DANGKYGIAOHANG(
MaThanhVienGiaoHang CHAR(6) FOREIGN KEY(MaThanhVienGiaoHang) REFERENCES THANHVIENGIAOHANG,
MaKhoangThoiGianGiaoHang CHAR(5) FOREIGN KEY(MaKhoangThoiGianGiaoHang) REFERENCES KHOANGTHOIGIAN
)

INSERT INTO DANGKYGIAOHANG(MaThanhVienGiaoHang, MaKhoangThoiGianGiaoHang)
VALUES
('TV0001','TG004'),
('TV0002','TG005'),
('TV0003','TG001'),
('TV0003','TG002'),
('TV0003','TG003')

CREATE TABLE KHACHHANG(
MaKhachHang CHAR(5) PRIMARY KEY,
MaKhuVuc CHAR(5) FOREIGN KEY(MaKhuVuc) REFERENCES KHUVUC,
TenKhachHang CHAR(40),
TenCuaHang CHAR(40),
SoDTKhachHang CHAR(40),
DiaChiEmail CHAR(40),
DiaChiNhanHang CHAR(40),
)

INSERT INTO KHACHHANG(MaKhachHang, MaKhuVuc, TenKhachHang, TenCuaHang, SoDTKhachHang, DiaChiEmail, DiaChiNhanHang)
VALUES
('KH001','KV001','Le Thi A','Cua hang 1','0905111111','alethi@gmail.com','80 Pham Phu Thai'),
('KH002','KV001','Nguyen Van B','Cua hang 1','0905111112','bnguyenvan@gmail.com','100 Phan Tu'),
('KH003','KV002','Le Thi B','Cua hang 1','0905111113','blethi@gmail.com','23 An Thuong 18'),
('KH004','KV002','Nguyen Van C','Cua hang 1','0905111114','cnguyenvan@gmail.com','67 Ngo The Thai'),
('KH005','KV001','Le Thi D','Cua hang 1','0905111115','dlethi@gmail.com','100 Chau Thi Vinh Te')

CREATE TABLE DONHANG_GIAOHANG(
MaDonHangGiaoHang CHAR(6) PRIMARY KEY,
MaKhachHang CHAR(5) FOREIGN KEY(MaKhachHang) REFERENCES KHACHHANG,
MaThanhVienGiaoHang CHAR(5),
MaDichVu CHAR(5) FOREIGN KEY(MaDichVu) REFERENCES DICHVU,
MaKhuVucGiaoHang CHAR(5),
TenNguoiNhan CHAR(40),
DiaChiGiaoHang CHAR(40),
SoDTNguoiNhan CHAR(10),
MaKhoangThoiGianGiaoHang CHAR(5) FOREIGN KEY(MaKhoangThoiGianGiaoHang) REFERENCES KHOANGTHOIGIAN,
NgayGiaoHang DATE,
PhuongThucThanhToan CHAR(20),
TrangThaiPheDuyet CHAR(40),
TrangThaiGiaoHang CHAR(40)
)

INSERT INTO DONHANG_GIAOHANG(MaDonHangGiaoHang,MaKhachHang,MaThanhVienGiaoHang,MaDichVu,MaKhuVucGiaoHang,TenNguoiNhan,DiaChiGiaoHang,SoDTNguoiNhan,MaKhoangThoiGianGiaoHang,NgayGiaoHang,PhuongThucThanhToan,TrangThaiPheDuyet,TrangThaiGiaoHang)
VALUES
('DH0001','KH001','TV001','DV001','KV001','Pham Van A','30 Hoang Van Thu','0905111111','TG004','10/10/2016','Tien mat','Da phe duyet','Da giao hang'),
('DH0002','KH001','TV002','DV001','KV005','Pham Van B','15 Le Dinh Ly','0905111112','TG005','12/23/2016','Tien mat','Da phe duyet','Chua giao hang'),
('DH0003','KH002','TV003','DV001','KV005','Pham Van C','23 Le Dinh Duong','0905111113','TG001','4/8/2017','Tien mat','Da phe duyet','Da giao hang'),
('DH0004','KH003','TV001','DV003','KV002','Pham Van D','45 Pham Phu Thai','0905111114','TG002','10/11/2015','Chuyen khoang','Da phe duyet','Da giao hang'),
('DH0005','KH004','TV005','DV003','KV003','Pham Van E','78 Hoang Dieu','0905111115','TG003','4/4/2017','Chuyen khoang','Chua phe duyet','')

CREATE TABLE CHITIET_DONHANG(
MaDonHangGiaoHang CHAR(6) FOREIGN KEY(MaDonHangGiaoHang) REFERENCES DONHANG_GIAOHANG,
TenSanPhamDuocGiao CHAR(50) PRIMARY KEY,
SoLuong tinyint,
TrongLuong FLOAT,
MaLoaiMatHang CHAR(5) FOREIGN KEY(MaLoaiMatHang) REFERENCES LOAIMATHANG,
[TienThuHo(VND)] INT
)

INSERT INTO CHITIET_DONHANG(MaDonHangGiaoHang,TenSanPhamDuocGiao,SoLuong,TrongLuong,MaLoaiMatHang,[TienThuHo(VND)])
VALUES
('DH0001','Ao len',2,0.5,'MH001',200000),
('DH0001','Quan au',1,0.25,'MH001',350000),
('DH0002','Ao thun',1,0.25,'MH001',1000000),
('DH0002','Ao khoac',3,0.25,'MH001',2000000),
('DH0003','Sua duong the',2,0.25,'MH002',780000),
('DH0003','Kem tay da chet',3,0.5,'MH002',150000),
('DH0003','Kem duong ban dem',4,0.25,'MH002',900000)