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
DIENTHOAI CHAR(12)
CONSTRAINT PK_GVDT
PRIMARY KEY (MAGV,DIENTHOAI)
CREATE TABLE BOMON
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
TENKHOA NVARCHAR(40),
NAMTL INT,
TRUONGKHOA CHAR(5),
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
TENCD NVARCHAR(50),
CONSTRAINT PK_CD
PRIMARY KEY (MACD)
CREATE TABLE CONGVIEC
SOTT INT,
TENCV NVARCHAR(40),
CONSTRAINT PK_CV
PRIMARY KEY (MADT,SOTT)
CREATE TABLE THAMGIADT
STT INT,
PHUCAP FLOAT,
KETQUA NVARCHAR(40),
CONSTRAINT PK_TG
PRIMARY KEY (MADT,MAGV,STT)
CREATE TABLE NGUOITHAN
TEN NVARCHAR(20),
CONSTRAINT CK_PHAI_NGUOITHAN CHECK (PHAI IN('NAM',N'NỮ')),
CONSTRAINT PK_NT
PRIMARY KEY (MAGV,TEN)