CREATE TABLE KHACHHANG (
MaKH VARCHAR(20) PRIMARY KEY,
TenKH VARCHAR(50),
GioiTinh VARCHAR(10),
DiaChi VARCHAR(100),
SDT VARCHAR(20),
NgaySinh DATETIME
);
CREATE TABLE NHANVIEN (
MaNV VARCHAR(20) PRIMARY KEY,
HoTen VARCHAR(50),
GioiTinh VARCHAR(10),
NgaySinh DATETIME,
DiaChi VARCHAR(100),
Email VARCHAR(50),
ChucVu VARCHAR(50)
);
CREATE TABLE LOAIHANG (
MaLoaiHang VARCHAR(20) PRIMARY KEY,
TenLoaiHang VARCHAR(50)
);
CREATE TABLE NHACUNGCAP (
MaNCC VARCHAR(20) PRIMARY KEY,
TenNCC VARCHAR(50),
DiaChi VARCHAR(100),
SDT VARCHAR(20),
Email VARCHAR(50)
);
CREATE TABLE NCC_LOAIHANG (
MaNCC VARCHAR(20),
MaLoaiHang VARCHAR(20),
PRIMARY KEY (MaNCC, MaLoaiHang),
FOREIGN KEY (MaNCC) REFERENCES NHACUNGCAP(MaNCC),
FOREIGN KEY (MaLoaiHang) REFERENCES LOAIHANG(MaLoaiHang)
);
CREATE TABLE MATHANG (
MaMatHang VARCHAR(20) PRIMARY KEY,
TenMatHang VARCHAR(50),
DVT VARCHAR(20),
SLTon INT,
DGNhap FLOAT,
DGBan FLOAT,
MaLoaiHang VARCHAR(20),
FOREIGN KEY (MaLoaiHang) REFERENCES LOAIHANG(MaLoaiHang)
);
CREATE TABLE KHO (
MaKho VARCHAR(20) PRIMARY KEY,
TenKho VARCHAR(50),
MaNV VARCHAR(20),
FOREIGN KEY (MaNV) REFERENCES NHANVIEN(MaNV)
);
CREATE TABLE CHITIETKHO (
MaKho VARCHAR(20),
MaMatHang VARCHAR(20),
SoLuong INT,
PRIMARY KEY (MaKho, MaMatHang),
FOREIGN KEY (MaKho) REFERENCES KHO(MaKho),
FOREIGN KEY (MaMatHang) REFERENCES MATHANG(MaMatHang)
);
CREATE TABLE DONMUAHANG (
MaDonMua VARCHAR(20) PRIMARY KEY,
NgayLap DATETIME,
GhiChu VARCHAR(100),
MaKH VARCHAR(20),
MaNV VARCHAR(20),
FOREIGN KEY (MaKH) REFERENCES KHACHHANG(MaKH),
FOREIGN KEY (MaNV) REFERENCES NHANVIEN(MaNV)
);
CREATE TABLE CHITIETDONMUHANG (
MaDonMua VARCHAR(20),
MaMatHang VARCHAR(20),
SoLuong INT,
PRIMARY KEY (MaDonMua, MaMatHang),
FOREIGN KEY (MaDonMua) REFERENCES
DONMUAHANG(MaDonMua),
FOREIGN KEY (MaMatHang) REFERENCES MATHANG(MaMatHang)
);
CREATE TABLE HOADON (
MaHD VARCHAR(20) PRIMARY KEY,
NgayLap DATETIME,
MaDonMua VARCHAR(20),
MaKH VARCHAR(20),
FOREIGN KEY (MaDonMua) REFERENCES
DONMUAHANG(MaDonMua),
FOREIGN KEY (MaKH) REFERENCES KHACHHANG(MaKH)
);
CREATE TABLE CHITIETHOADON (
MaHD VARCHAR(20),
MaMatHang VARCHAR(20),
DGBan FLOAT,
SoLuong INT,
ThanhTien FLOAT,
HinhThucThanhToan VARCHAR(50),
PRIMARY KEY (MaHD, MaMatHang),
FOREIGN KEY (MaHD) REFERENCES HOADON(MaHD),
FOREIGN KEY (MaMatHang) REFERENCES MATHANG(MaMatHang)
);
CREATE TABLE DONDATHANG (
MaDatHang VARCHAR(20) PRIMARY KEY,
NgayLap DATETIME,
GhiChu VARCHAR(100),
MaNCC VARCHAR(20),
MaNV VARCHAR(20),
FOREIGN KEY (MaNCC) REFERENCES NHACUNGCAP(MaNCC),
FOREIGN KEY (MaNV) REFERENCES NHANVIEN(MaNV)
);
CREATE TABLE CHITIETDONDATHANG (
MaDatHang VARCHAR(20),
MaMatHang VARCHAR(20),
SoLuong INT,
PRIMARY KEY (MaDatHang, MaMatHang),
FOREIGN KEY (MaDatHang) REFERENCES
DONDATHANG(MaDatHang),
FOREIGN KEY (MaMatHang) REFERENCES MATHANG(MaMatHang)
);
CREATE TABLE PHIEUNHAP (
SoPN VARCHAR(20) PRIMARY KEY,
TriGiaNhap FLOAT,
NgayNhap DATETIME,
MaDatHang VARCHAR(20),
MaNV VARCHAR(20),
MaKho VARCHAR(20),
FOREIGN KEY (MaDatHang) REFERENCES
DONDATHANG(MaDatHang),
FOREIGN KEY (MaNV) REFERENCES NHANVIEN(MaNV),
FOREIGN KEY (MaKho) REFERENCES KHO(MaKho)
);
CREATE TABLE CHITIETPHIEUNHAP (
SoPN VARCHAR(20),
MaMatHang VARCHAR(20),
SL INT,
DonGia FLOAT,
PRIMARY KEY (SoPN, MaMatHang),
FOREIGN KEY (SoPN) REFERENCES PHIEUNHAP(SoPN),
FOREIGN KEY (MaMatHang) REFERENCES MATHANG(MaMatHang)
);
CREATE TABLE PHIEUXUAT (
SoPX VARCHAR(20) PRIMARY KEY,
NgayXuat DATETIME,
MaNV VARCHAR(20),
MaKho VARCHAR(20),
FOREIGN KEY (MaNV) REFERENCES NHANVIEN(MaNV),
FOREIGN KEY (MaKho) REFERENCES KHO(MaKho)
);
CREATE TABLE CHITIETPHIEUXUAT (
SoPX VARCHAR(20),
MaMatHang VARCHAR(20),
SoLuong INT,
DonGia FLOAT,
PRIMARY KEY (SoPX, MaMatHang),
FOREIGN KEY (SoPX) REFERENCES PHIEUXUAT(SoPX),
FOREIGN KEY (MaMatHang) REFERENCES MATHANG(MaMatHang)
);