CREATE DATABASE QLGiaoVien
GO
USE QLGiaoVien
GO

CREATE TABLE GIAOVIEN(
    MAGV CHAR(5),
    HOTEN NVARCHAR(20),
    LUONG FLOAT,
    PHAI NCHAR(3),
    DIACHI NCHAR(50),
    NGAYSINH DATETIME,
    MANQL CHAR(5),
    MABM CHAR(5),
    PRIMARY KEY (MAGV)
)

CREATE TABLE BOMON(
    TENBM NCHAR(50),
    MABM CHAR(5),
    PHONG NCHAR(20),
    DIENTHOAI CHAR(20),
    MAKHOA CHAR(5),
    TRBOMON CHAR(5),
    NGAY_NC DATETIME,
    PRIMARY KEY (MABM)
)

CREATE TABLE KHOA(
    MAKHOA CHAR(5),
    TENKHOA NCHAR(20),
    NAMTL INT,
    DIENTHOAI CHAR(20),
    PHONG CHAR(5),
    TRKHOA CHAR(5),
    NGAY_NC DATETIME,
    PRIMARY KEY(MAKHOA)
)

CREATE TABLE GV_DT
(
    MAGV CHAR(5),
    DIENTHOAI CHAR(12),
    PRIMARY KEY (MAGV, DIENTHOAI)
)

CREATE TABLE NGUOITHAN (
    TEN NCHAR(20) NOT NULL,
    MAGV CHAR(5) NOT NULL,
    NGAYSINH DATETIME NULL,
    PHAI NCHAR(3)  NULL
    PRIMARY KEY (MAGV, TEN)
);

CREATE TABLE CHUDE (
    MACD CHAR(5),
    TENCD NCHAR(50),
    PRIMARY KEY (MACD)
)

CREATE TABLE DETAI (
    MADT CHAR(5),
    TENDT NCHAR(50),
    CAPQL NCHAR(50),
    KINHPHI FLOAT,
    NGAYBD DATETIME,
    NGAYKT DATETIME,
    MACD CHAR(5),
    GVCNCD CHAR(5),
    PRIMARY KEY(MADT)
)

CREATE TABLE CONGVIEC(
    MADT CHAR(5),
    SOTT INT,
    TENCV NCHAR(20),
    NGAYBD DATETIME,
    NGAYKT DATETIME,
    PRIMARY KEY(MADT, SOTT)
)

CREATE TABLE THAMGIADT(
    MAGV CHAR(5),
    MADT CHAR(5),
    STT INT,
    PHUCAP FLOAT,
    KETQUA NCHAR(20),
)

ALTER TABLE KHOA
ADD CONSTRAINT FK_KHOA_GIAOVIEN
FOREIGN KEY (TRKHOA)
REFERENCES GIAOVIEN(MAGV)

ALTER TABLE BOMON
ADD CONSTRAINT FK_BOMON_KHOA
FOREIGN KEY(MAKHOA)
REFERENCES KHOA(MAKHOA)

ALTER TABLE BOMON
ADD CONSTRAINT FK_BOMON_GIAOVIEN
FOREIGN KEY(TRUONGBM)
REFERENCES GIAOVIEN(MAGV)

ALTER TABLE GIAOVIEN
ADD CONSTRAINT FK_GIAOVIEN_BOMON
FOREIGN KEY (MABM)
REFERENCES BOMON(MABM)

ALTER TABLE GIAOVIEN
ADD CONSTRAINT FK_GIAOVIEN_GIAOVIEN
FOREIGN KEY (GVQLCM)
REFERENCES GIAOVIEN(MAGV)

ALTER TABLE GV_DT
ADD CONSTRAINT FK_GV_DT_GIAOVIEN
FOREIGN KEY (MAGV)
REFERENCES GIAOVIEN(MAGV)

ALTER TABLE NGUOITHAN
ADD CONSTRAINT FK_NGUOITHAN_GIAOVIEN
FOREIGN KEY (MAGV)
REFERENCES GIAOVIEN(MAGV)

ALTER TABLE DETAI
ADD CONSTRAINT FK_DETAI_GIAOVIEN
FOREIGN KEY (GVCNDT)
REFERENCES GIAOVIEN(MAGV)

ALTER TABLE DETAI
ADD CONSTRAINT FK_DETAI_CHUDE
FOREIGN KEY (MACD)
REFERENCES CHUDE(MACD)

ALTER TABLE CONGVIEC
ADD CONSTRAINT FK_CONGVIEC_DETAI
FOREIGN KEY (MADT)
REFERENCES DETAI(MADT)

ALTER TABLE THAMGIADT
ADD CONSTRAINT FK_THAMGIADETAI_CONGVIEC
FOREIGN KEY (MADT)
REFERENCES CONGVIEC(MADT)

ALTER TABLE THAMGIADT
ADD CONSTRAINT FK_THAMGIADETAI_GIAOVIEN
FOREIGN KEY (MAGV)
REFERENCES GIAOVIEN(MAGV)