CREATE TABLE GIAOVIEN
(
	MAGV CHAR(5),
	HOTEN NVARCHAR(40),
	LUONG FLOAT,
	PHAI NVARCHAR(6)
		CONSTRAINT CK_PHAI_NHANVIEN CHECK (PHAI IN('NAM',N'NỮ')),
	NGSINH DATETIME,
	DIACHI NVARCHAR(100),
	GVQLCM CHAR(5),
	MABM NVARCHAR(5),
	-- khai báo khoá chính
	CONSTRAINT PK_GV -- đặt tên khoá
	PRIMARY KEY (MAGV)
)
CREATE TABLE GV_DT
(
	MAGV CHAR(5),
	DIENTHOAI CHAR(12)

	CONSTRAINT PK_GVDT
	PRIMARY KEY (MAGV,DIENTHOAI)
)
CREATE TABLE BOMON
(
	MABM NVARCHAR(5),
	TENBM NVARCHAR(40),
	PHONG CHAR(5),
	DIENTHOAI CHAR(12),
	TRUONGBM CHAR(5),
	MAKHOA CHAR(4),
	NGAYNHANCHUC DATETIME,

	CONSTRAINT PK_BM
	PRIMARY KEY (MABM)
)
CREATE TABLE KHOA
(
	MAKHOA CHAR(4),
	TENKHOA NVARCHAR(40),
	NAMTL INT,
	PHONG CHAR(5),
	DIENTHOAI CHAR(12),
	TRUONGKHOA CHAR(5),
	NGAYNHANCHUC DATETIME,

	CONSTRAINT PK_K
	PRIMARY KEY (MAKHOA)
)
CREATE TABLE DETAI
(
	MADT CHAR(3),
	TENDT NVARCHAR(100),
	CAPQL NVARCHAR(40),
	KINHPHI FLOAT,
	NGAYBD DATETIME,
	NGAYKT DATETIME,
	MACD NVARCHAR(4),
	GVCNDT CHAR(5),

	CONSTRAINT PK_DT
	PRIMARY KEY (MADT)
)
CREATE TABLE CHUDE
(
	MACD NVARCHAR(4),
	TENCD NVARCHAR(50),

	CONSTRAINT PK_CD
	PRIMARY KEY (MACD)
)
CREATE TABLE CONGVIEC
(
	MADT CHAR(3),
	SOTT INT,
	TENCV NVARCHAR(40),
	NGAYBD DATETIME,
	NGAYKT DATETIME,

	CONSTRAINT PK_CV
	PRIMARY KEY (MADT,SOTT)

)
CREATE TABLE THAMGIADT
(
	MAGV CHAR(5),
	MADT CHAR(3),
	STT INT,
	PHUCAP FLOAT,
	KETQUA NVARCHAR(40),

	CONSTRAINT PK_TG
	PRIMARY KEY (MADT,MAGV,STT)
)
CREATE TABLE NGUOITHAN
(
	MAGV CHAR(5),
	TEN NVARCHAR(20),
	NGSINH DATETIME,
	PHAI NVARCHAR(6)
		CONSTRAINT CK_PHAI_NGUOITHAN CHECK (PHAI IN('NAM',N'NỮ')),
	CONSTRAINT PK_NT
	PRIMARY KEY (MAGV,TEN)

)