Script DB.sql

CREATE DATABASE QLGBD
GO
CREATE TABLE Khoa (
MaKhoa				CHAR (3) PRIMARY KEY, 
TenKhoa				NVARCHAR (50) UNIQUE  NOT NULL
)
CREATE TABLE DoiBong (
MaDoi				CHAR (3) PRIMARY KEY, 
TenDoi				NVARCHAR (30) UNIQUE NOT NULL,
Bang				CHAR(1) NOT NULL check (Bang in ('A','B')),
MSSVDoiTruong		CHAR(11) NOT NULL,
MaKhoa				CHAR(3) NOT NULl,
) 
CREATE TABLE CauThu (
MSSV				CHAR(11) PRIMARY KEY, 
HoTenSV     		NVARCHAR(45) NOT NULL,
Khoa				INT NOT NULL CHECK (Khoa in ('45','46', '47', '48','49')),
SoAo				INT NOT NULL,
SDTCT				CHAR(10),
MaDoi				CHAR(3) NOT NULL,
MaKhoa				CHAR(3) NOT NULL,
) 

CREATE TABLE San(
MaSan				CHAR(3) PRIMARY KEY,
TenSan				NVARCHAR(45) NOT NULL,
DiaDiem				NVARCHAR(100) NOT NULL,
)
CREATE TABLE TrongTai(
MaTT				CHAR(4) PRIMARY KEY, 
HoTenTT				NVARCHAR (50) NOT NULL,
SDTTT				CHAR(10) ,
)
CREATE TABLE TranDau 
(
MaTD				CHAR(4)     PRIMARY KEY ,
VongDau				VARCHAR(2)	CHECK(VongDau IN ('GS', 'SF','TP','GF')),	
NgayToChuc			DATE		NOT NULL,
GioThiDau			TIME		NOT NULL,
MaSan				CHAR(3)		NOT NULL,
MaTT 				CHAR(4)		NOT NULL,
)

CREATE TABLE ChiTietTranDau (
MaTD            	CHAR(4) ,
MaDoi           	CHAR(3)  ,
KetQuaDoi     		VARCHAR(1) CHECK (KetQuaDoi IN ('W','L','D')),
XacDinhKQ     		VARCHAR(2) CHECK (XacDinhKQ IN ('NT', 'ET', 'PS')),
PRIMARY KEY(MaTD, MaDoi),
)
CREATE TABLE BanThang (
MaBT            	CHAR(4) PRIMARY KEY,
LoaiBT				CHAR(1)  NOT NULL CHECK (LoaiBT in ('N','O','P')),
MaTD				CHAR(4)  NOT NULL,
MSSV				CHAR(11) NOT NULL
)
CREATE TABLE ThePhat(

MSSV			 	CHAR(11) NOT NULL,
MaTD			 	CHAR(4)  NOT NULL,       
SoTheVang		 	INT NOT NULL CHECK(SoTheVang in (0,1,2)),
SoTheDo				INT NOT NULL CHECK(SoTheDo in (0,1)),
PRIMARY KEY (MSSV, MaTD),
)
CREATE TABLE LuanLuu(
MaLL				CHAR (4) PRIMARY KEY,
MSSV				CHAR(11) NOT NULL, 
MaTD				CHAR(4)  NOT NULL,
TinhTrang			VARCHAR(1)   CHECK(TinhTrang in ('S','F')),
) 
-- TẠO KHÓA NGOẠI 
ALTER TABLE DoiBong  ADD CONSTRAINT FK_DB_K FOREIGN KEY (MaKhoa) REFERENCES Khoa(MaKhoa)
ALTER TABLE CauThu  ADD CONSTRAINT FK_CT_K FOREIGN KEY (MaKhoa) REFERENCES Khoa(MaKhoa)
ALTER TABLE CauThu ADD CONSTRAINT FK_CT_DB FOREIGN KEY (MaDoi) REFERENCES DoiBong(MaDoi)
ALTER TABLE TranDau  ADD CONSTRAINT FK_TD_S FOREIGN KEY (MaSan) REFERENCES San(MaSan)
ALTER TABLE TranDau  ADD CONSTRAINT FK_TD_TT FOREIGN KEY (MaTT) REFERENCES TrongTai(MaTT)
ALTER TABLE ChiTietTranDau  ADD CONSTRAINT FK_CTTD_TD FOREIGN KEY (MaTD) REFERENCES TranDau(MaTD)
ALTER TABLE ChiTietTranDau  ADD CONSTRAINT FK_CTTD_DB FOREIGN KEY (MaDoi) REFERENCES DoiBong(MaDoi)
ALTER TABLE ThePhat  ADD CONSTRAINT FK_TP_TD FOREIGN KEY (MaTD) REFERENCES TranDau(MaTD)
ALTER TABLE ThePhat  ADD CONSTRAINT FK_TP_CT FOREIGN KEY (MSSV) REFERENCES CauThu(MSSV)
ALTER TABLE LuanLuu  ADD CONSTRAINT FK_LL_TD FOREIGN KEY (MaTD) REFERENCES TranDau(MaTD)
ALTER TABLE LuanLuu ADD CONSTRAINT FK_LL_CT FOREIGN KEY (MSSV) REFERENCES CauThu(MSSV)
ALTER TABLE BanThang ADD CONSTRAINT FK_BT_TD FOREIGN KEY (MaTD) REFERENCES TranDau(MaTD)
ALTER TABLE BanThang ADD CONSTRAINT FK_BT_CT FOREIGN KEY (MSSV) REFERENCES CauThu(MSSV)