CREATE DATABASE NML03A;
GO
USE NML03A;

CREATE TABLE MATHANG (
    MSMH char(10) NOT NULL,
    TENMH nvarchar(50) NULL,
    DONGIA float NULL,
    DVT nchar(10) NULL,
	constraint pk_mathang Primary key(MSMH)
);

CREATE TABLE KHACHHANG (
    MSKH char(10) NOT NULL,
    TENKH nvarchar(50) NULL,
	DIACHI nvarchar(50) NULL,
    DIENTHOAI nvarchar(50) NULL,
	constraint pk_khachhang Primary key(MSKH)
);

CREATE TABLE NHANVIEN (
    MSNV char(10) NOT NULL,
    TENNV nvarchar(50) NULL,
	DIACHI nvarchar(50) NULL,
	CMND char(15) NOT NULL,
    DIENTHOAI char(12) NULL,
	NGAYSINH datetime NULL,
	constraint pk_nhanvien Primary key(MSNV)
);

CREATE TABLE HOADON (
    MSHD char(10) NOT NULL,
	NGAYHD datetime NULL,
	MSKH char(10) NOT NULL,
	MSNV char(10) NOT NULL,
	constraint pk_hoadon Primary key(MSHD)
);

CREATE TABLE CTHOADON (
    MSHD char(10) NOT NULL,
	MSMH char(10) NOT NULL,
	SOLUONG int NULL,
	constraint pk_cthoadon Primary key(MSHD, MSMH)
);

ALTER TABLE HOADON
	ADD CONSTRAINT fk_hoadon_khachhang FOREIGN KEY (MSKH) REFERENCES KHACHHANG(MSKH),
		CONSTRAINT fk_hoadon_nhanvien FOREIGN KEY (MSNV) REFERENCES NHANVIEN(MSNV);

ALTER TABLE CTHOADON
	ADD CONSTRAINT fk_cthoadon_hoadon FOREIGN KEY (MSHD) REFERENCES HOADON(MSHD),
		CONSTRAINT fk_cthoadon_mathang FOREIGN KEY (MSMH) REFERENCES MATHANG(MSMH);