CREATE TABLE TRUONGDH(
MATRUONG CHAR(6) NOT NULL PRIMARY KEY, CHECK (MATRUONG LIKE 'DH[0-9][0-9][0-9][0-9]'),
TENTRUONG NVARCHAR2(30) NOT NULL UNIQUE,
DIACHI NVARCHAR2(30),
MUCTIEU NVARCHAR2(30)
);

CREATE TABLE NGANH(
MANGANH CHAR(5) NOT NULL, CHECK (MANGANH LIKE 'NG[0-9][0-9][0-9]'),
MATRUONG CHAR(6) NOT NULL,
TENNGANH NVARCHAR2(30) NOT NULL,
THONGTINDAURA clob,
DECUONGDAOTAO clob,
CONSTRAINT NGANH_PK PRIMARY KEY (MANGANH, MATRUONG) ENABLE
);

ALTER TABLE NGANH ADD CONSTRAINT FG_NGANH_MATRUONG FOREIGN KEY (MATRUONG) REFERENCES TRUONGDH(MATRUONG)

CREATE TABLE KHOI(
TENKHOI CHAR(3) NOT NULL PRIMARY KEY, CHECK (TENKHOI LIKE '[A-Z][0-9][0-9]')
);

CREATE TABLE THONGTINTUYENSINH(
MATRUONG CHAR(6) NOT NULL,
MANGANH CHAR(5) NOT NULL,
TENKHOI CHAR(3) NOT NULL,
TYLECHOI NUMBER(*, 2),
CHITIEU INT, CHECK (CHITIEU>0),
DIEMCHUANNAMNGOAI NUMBER(*, 2), CHECK (DIEMCHUANNAMNGOAI>0),
DIEMCHUANNV1 NUMBER(*,2), CHECK (DIEMCHUANNV1>0),
DIEMCHUANNV2 NUMBER(*,2), CHECK (DIEMCHUANNV2>0),
DIEMCHUANNV3 NUMBER(*,2), CHECK (DIEMCHUANNV3>0),
CONSTRAINT THONGTINTUYENSINH_PK PRIMARY KEY (MATRUONG, MANGANH, TENKHOI) ENABLE
);

ALTER TABLE THONGTINTUYENSINH ADD CONSTRAINT FG_TTTS_MATRUONG_MANGANH FOREIGN KEY (MATRUONG, MANGANH) REFERENCES NGANH(MATRUONG, MANGANH)
ALTER TABLE THONGTINTUYENSINH ADD CONSTRAINT FG_TTTS_TENKHOI FOREIGN KEY (TENKHOI) REFERENCES KHOI(TENKHOI)

CREATE TABLE MONTHI(
TENKHOI CHAR(3) NOT NULL,
STT CHAR(4) NOT NULL, CHECK (STT LIKE 'MT[0-9][0-9]'),
TENMON NVARCHAR2(20) NOT NULL,
THOIGIANTHI INT, CHECK (THOIGIANTHI<=180),
GIOBATDAU CHAR(5), CHECK (GIOBATDAU LIKE '[0-9][0-9]:[0-9][0-9]'),
MABUOITHI CHAR(3),
CONSTRAINT MONTHI_PK PRIMARY KEY (TENKHOI, STT) ENABLE
);

ALTER TABLE MONTHI ADD CONSTRAINT FG_MONTHI_TENKHOI FOREIGN KEY (TENKHOI) REFERENCES KHOI(TENKHOI)

CREATE TABLE BUOITHI(
MABUOITHI CHAR(3) NOT NULL PRIMARY KEY,
NGAY DATE,
SANGHAYCHIEU INT, CHECK (SANGHAYCHIEU BETWEEN 0 AND 1)
);

ALTER TABLE MONTHI ADD CONSTRAINT FG_MONTHI_MABUOITHI FOREIGN KEY (MABUOITHI) REFERENCES BUOITHI(MABUOITHI)

CREATE TABLE TRUONGTHPT(
MATRUONGTHPT CHAR(8) NOT NULL PRIMARY KEY, CHECK (MATRUONGTHPT LIKE 'THPT[0-9][0-9][0-9][0-9]'),
TENTRUONGTHPT NVARCHAR2(40) NOT NULL
);

CREATE TABLE NHANSU(
ID CHAR(7) NOT NULL PRIMARY KEY, CHECK (ID LIKE '[TS|GT|TT|CB][0-9][0-9][0-9][0-9][0-9]'),
CMND CHAR (10) NOT NULL UNIQUE,
HOTEN NVARCHAR2(50)
);

CREATE TABLE THISINH(
ID CHAR(7) NOT NULL PRIMARY KEY, CHECK (ID LIKE 'TS[0-9][0-9][0-9][0-9][0-9]'),
DIACHI NVARCHAR2(30),
QUEQUAN NVARCHAR2(20),
DIENTHOAI CHAR(10),
MATRUONGTHPT CHAR(8)
);

ALTER TABLE THISINH ADD CONSTRAINT FG_THISINH_ID FOREIGN KEY (ID) REFERENCES NHANSU(ID)
ALTER TABLE THISINH ADD CONSTRAINT FG_THISINH_MATRUONGTHPT FOREIGN KEY (MATRUONGTHPT) REFERENCES TRUONGTHPT(MATRUONGTHPT)

CREATE TABLE CANBO(
ID CHAR(7) NOT NULL PRIMARY KEY,
CONGVIEC NVARCHAR2(20),
CHECK ((CONGVIEC = 'GIAM THI' AND ID LIKE 'GT[0-9][0-9][0-9][0-9][0-9]') OR (CONGVIEC = 'TRAT TU VIEN' AND ID LIKE 'TT[0-9][0-9][0-9][0-9][0-9]') OR (CONGVIEC <> 'GIAM THI' AND CONGVIEC <> 'TRAT TU VIEN' AND ID LIKE 'CB[0-9][0-9][0-9][0-9][0-9]'))
);

ALTER TABLE CANBO ADD CONSTRAINT FG_CANBO_ID FOREIGN KEY (ID) REFERENCES NHANSU(ID)

CREATE TABLE HOSOTHISINHTHI(
ID CHAR(7) NOT NULL,
MATRUONG CHAR(6) NOT NULL,
MANGANH CHAR(5) NOT NULL,
TENKHOI CHAR(3) NOT NULL,
SBD CHAR(5) NOT NULL, CHECK (SBD LIKE '[0-9][0-9][0-9][0-9][0-9]'),
MADIADIEM CHAR(5),
SOPHONG INT,
TONGDIEM NUMBER(*,2) DEFAULT -1.00,
CONSTRAINT HOSOTHISINHTHI_PK PRIMARY KEY (ID, MATRUONG, MANGANH, TENKHOI, SBD) ENABLE
);

ALTER TABLE HOSOTHISINHTHI ADD CONSTRAINT FG_HSTST_ID FOREIGN KEY (ID) REFERENCES THISINH(ID)
ALTER TABLE HOSOTHISINHTHI ADD CONSTRAINT FG_HSTST_MATRUONG_MANGANH FOREIGN KEY (MATRUONG, MANGANH) REFERENCES NGANH(MATRUONG, MANGANH)
ALTER TABLE HOSOTHISINHTHI ADD CONSTRAINT FG_HSTST_TENKHOI FOREIGN KEY (TENKHOI) REFERENCES KHOI(TENKHOI)

CREATE TABLE THISINHDANGKYNGUYENVONG(
ID CHAR(7) NOT NULL,
MATRUONG CHAR(6) NOT NULL,
MANGANH CHAR(5) NOT NULL,
TENKHOI CHAR(3) NOT NULL,
SBD CHAR(5) NOT NULL,
MATRUONG2 CHAR(6) NOT NULL,
MANGANH2 CHAR(5) NOT NULL,
NV2HAYNV3 INT NOT NULL, CHECK (NV2HAYNV3 BETWEEN 1 AND 2),
CONSTRAINT THISINHDANGKYNGUYENVONG_PK PRIMARY KEY (ID, MATRUONG, MANGANH, TENKHOI, SBD, MATRUONG2, MANGANH2) ENABLE
);

ALTER TABLE THISINHDANGKYNGUYENVONG ADD CONSTRAINT FG_TSDKNV_THONGTIN FOREIGN KEY (ID, MATRUONG, MANGANH, TENKHOI, SBD) REFERENCES HOSOTHISINHTHI(ID, MATRUONG, MANGANH, TENKHOI, SBD)
ALTER TABLE THISINHDANGKYNGUYENVONG ADD CONSTRAINT FG_TSDKNV_MATRUONG2_MANGANH2 FOREIGN KEY (MATRUONG2, MANGANH2) REFERENCES NGANH(MATRUONG, MANGANH)

CREATE TABLE DIADIEMTHI(
MADIADIEM CHAR(5) NOT NULL PRIMARY KEY, CHECK (MADIADIEM LIKE 'DD[0-9][0-9][0-9]'),
TENDIADIEM NVARCHAR2(30),
DIACHI NVARCHAR2(30)
);

CREATE TABLE PHONGTHI(
MADIADIEM CHAR(5) NOT NULL,
SOPHONG INT NOT NULL,
TONGSOTHISINH INT,
MAHANHLANG CHAR(6),
CONSTRAINT PHONGTHI_PK PRIMARY KEY (MADIADIEM, SOPHONG) ENABLE
);

ALTER TABLE HOSOTHISINHTHI ADD CONSTRAINT FG_HSTST_MADIADIEM_SOPHONG FOREIGN KEY (MADIADIEM, SOPHONG) REFERENCES PHONGTHI(MADIADIEM, SOPHONG)
ALTER TABLE PHONGTHI ADD CONSTRAINT FG_PHONGTHI_MADIADIEM FOREIGN KEY (MADIADIEM) REFERENCES DIADIEMTHI(MADIADIEM)

CREATE TABLE THISINHVANG(
ID CHAR(7) NOT NULL,
MATRUONG CHAR(6) NOT NULL,
MANGANH CHAR(5) NOT NULL,
TENKHOI CHAR(3) NOT NULL,
SBD CHAR(5) NOT NULL,
MABUOITHI CHAR(3) NOT NULL,
CONSTRAINT THISINHVANG_PK PRIMARY KEY (ID, MATRUONG, MANGANH, TENKHOI, SBD, MABUOITHI) ENABLE
);

ALTER TABLE THISINHVANG ADD CONSTRAINT FG_TSV_THONGTIN FOREIGN KEY (ID, MATRUONG, MANGANH, TENKHOI, SBD) REFERENCES HOSOTHISINHTHI(ID, MATRUONG, MANGANH, TENKHOI, SBD)
ALTER TABLE THISINHVANG ADD CONSTRAINT FG_TSV_MABUOITHI FOREIGN KEY (MABUOITHI) REFERENCES BUOITHI(MABUOITHI)

CREATE TABLE SOTHISINHCOMATTRONGPHONGTHI(
MADIADIEM CHAR(5) NOT NULL,
SOPHONG INT NOT NULL,
MABUOITHI CHAR(3) NOT NULL,
SOTHISINHCOMAT INT default -1,
CONSTRAINT SOTHISINHCOMATTRONGPHONGTHI_PK PRIMARY KEY (MADIADIEM, SOPHONG, MABUOITHI) ENABLE
);

ALTER TABLE SOTHISINHCOMATTRONGPHONGTHI ADD CONSTRAINT FG_STSCMTPT_MADIADIEM_SOPHONG FOREIGN KEY (MADIADIEM, SOPHONG) REFERENCES PHONGTHI(MADIADIEM, SOPHONG)
ALTER TABLE SOTHISINHCOMATTRONGPHONGTHI ADD CONSTRAINT FG_STSCMTPT_MABUOITHI FOREIGN KEY (MABUOITHI) REFERENCES BUOITHI(MABUOITHI)

CREATE TABLE HANHLANG(
MAHANHLANG CHAR(6) NOT NULL PRIMARY KEY, CHECK (MAHANHLANG LIKE 'HL[0-9][0-9][0-9][0-9]')
);

ALTER TABLE PHONGTHI ADD CONSTRAINT FG_PHONGTHI_MAHANHLANG FOREIGN KEY (MAHANHLANG) REFERENCES HANHLANG(MAHANHLANG)

CREATE TABLE GIAMTHIGACTHI(
MAGT1 CHAR(7),
MAGT2 CHAR(7),
MABUOITHI CHAR(3) NOT NULL,
MADIADIEM CHAR(5) NOT NULL,
SOPHONG INT NOT NULL,
CONSTRAINT GIAMTHIGACTHI_PK PRIMARY KEY (MABUOITHI, MADIADIEM, SOPHONG) ENABLE
);

ALTER TABLE GIAMTHIGACTHI ADD CONSTRAINT FG_GTGT_MAGT1 FOREIGN KEY (MAGT1) REFERENCES CANBO(ID) ADD CONSTRAINT GT1_CK CHECK (MAGT1 LIKE 'GT[0-9][0-9][0-9][0-9][0-9]')
ALTER TABLE GIAMTHIGACTHI ADD CONSTRAINT FG_GTGT_MAGT2 FOREIGN KEY (MAGT2) REFERENCES CANBO(ID) ADD CONSTRAINT GT2_CK CHECK (MAGT2 LIKE 'GT[0-9][0-9][0-9][0-9][0-9]') ADD CONSTRAINT CK_GT2 CHECK (MAGT2 <> MAGT1)
ALTER TABLE GIAMTHIGACTHI ADD CONSTRAINT FG_GTGT_MABUOITHI FOREIGN KEY (MABUOITHI) REFERENCES BUOITHI(MABUOITHI)
ALTER TABLE GIAMTHIGACTHI ADD CONSTRAINT FG_GTGT_MADIADIEM_SOPHONG FOREIGN KEY (MADIADIEM, SOPHONG) REFERENCES PHONGTHI(MADIADIEM, SOPHONG)

CREATE TABLE TRATTUVIENGIAMSAT(
MABUOITHI CHAR(3) NOT NULL,
MAHANHLANG CHAR(6) NOT NULL,
MATTV1 CHAR(7),
MATTV2 CHAR(7),
CONSTRAINT TRATTUVIENGIAMSAT_PK PRIMARY KEY (MABUOITHI, MAHANHLANG) ENABLE
);

ALTER TABLE TRATTUVIENGIAMSAT ADD CONSTRAINT FG_TTVGS_MABUOITHI FOREIGN KEY (MABUOITHI) REFERENCES BUOITHI(MABUOITHI)
ALTER TABLE TRATTUVIENGIAMSAT ADD CONSTRAINT FG_TTVGS_MATTV1 FOREIGN KEY (MATTV1) REFERENCES CANBO(ID) ADD CONSTRAINT TTV1_CK CHECK (MATTV1 LIKE 'TT[0-9][0-9][0-9][0-9][0-9]')
ALTER TABLE TRATTUVIENGIAMSAT ADD CONSTRAINT FG_TTVGS_MATTV2 FOREIGN KEY (MATTV2) REFERENCES CANBO(ID) ADD CONSTRAINT TTV2_CK CHECK (MATTV2 LIKE 'TT[0-9][0-9][0-9][0-9][0-9]') ADD CONSTRAINT CK_TTV2 CHECK (MATTV2 <> MATTV1)
ALTER TABLE TRATTUVIENGIAMSAT ADD CONSTRAINT FG_TTVGS_MAHANHLANG FOREIGN KEY (MAHANHLANG) REFERENCES HANHLANG(MAHANHLANG)

CREATE TABLE QUYCHE(
MAQUYCHE CHAR(5) NOT NULL PRIMARY KEY, CHECK (MAQUYCHE LIKE 'QC[0-9][0-9][0-9]'),
MOTA NVARCHAR2(40),
XULY NVARCHAR2(40)
);

CREATE TABLE VIPHAMQUYCHE(
ID CHAR(7)NOT NULL,
MAQUYCHE CHAR(5) NOT NULL,
MABUOITHI CHAR(3) NOT NULL,
CONSTRAINT VIPHAMQUYCHE_PK PRIMARY KEY(ID, MAQUYCHE, MABUOITHI)ENABLE
);

ALTER TABLE VIPHAMQUYCHE ADD CONSTRAINT FG_VPQC_ID FOREIGN KEY (ID) REFERENCES NHANSU(ID)
ALTER TABLE VIPHAMQUYCHE ADD CONSTRAINT FG_VPQC_MAQUYCHE FOREIGN KEY (MAQUYCHE) REFERENCES QUYCHE(MAQUYCHE)
ALTER TABLE VIPHAMQUYCHE ADD CONSTRAINT FG_VPQC_MABUOITHI FOREIGN KEY (MABUOITHI) REFERENCES BUOITHI(MABUOITHI)

CREATE TABLE KETQUATHI(
ID CHAR(7) NOT NULL,
MATRUONG CHAR(6) NOT NULL,
MANGANH CHAR(5) NOT NULL,
TENKHOI CHAR(3) NOT NULL,
SBD CHAR(5) NOT NULL,
STT CHAR(4) NOT NULL,
DIEMTHI NUMBER(*,2), CHECK (DIEMTHI < 10.00),
DIEMPHUCTRA NUMBER(*,2) DEFAULT -1.00, CHECK (DIEMPHUCTRA < 10.00),
CONSTRAINT KETQUATHI_PK PRIMARY KEY (ID, MATRUONG, MANGANH, TENKHOI, SBD, STT) ENABLE
);

ALTER TABLE KETQUATHI ADD CONSTRAINT FG_KQT_THONGTIN FOREIGN KEY (ID, MATRUONG, MANGANH, TENKHOI, SBD) REFERENCES HOSOTHISINHTHI(ID, MATRUONG, MANGANH, TENKHOI, SBD)
ALTER TABLE KETQUATHI ADD CONSTRAINT FG_KQT_TENKHOI_STT FOREIGN KEY (TENKHOI, STT) REFERENCES MONTHI(TENKHOI, STT)