/* ghi chu doan */
-- ghi chu dong
-- b1 tao csdl
USE master
GO
IF DB_ID('QLTTTH') is not null -- kiem tra ten CSDL 'QLSV' co ton tai khong
	DROP DATABASE QLTTTH -- xoa DB QLSV da ton tai
GO -- dam bao khoi lenh truoc ket thuc
CREATE DATABASE QLTTTH -- tao CSDL QLSV
GO
USE QLTTTH -- dung CSDL
-- tao bang

CREATE TABLE NGUOIDANGKY
(
	MANGUOIDANGKY CHAR(5),
	HOTEN NVARCHAR(40),
	PHAI NVARCHAR(3)
		CONSTRAINT CK_PHAI CHECK (PHAI IN('NAM',N'NỮ')),
	NGAYSINH DATE,
	DIACHI NVARCHAR(100),
	SDT CHAR(10),
	-- khai báo khoá chính
	CONSTRAINT PK_NGUOIDANGKY -- đặt tên khoá
	PRIMARY KEY (MANGUOIDANGKY)
)

CREATE TABLE HOCVIEN
(
	MANGUOIDANGKY CHAR(5),
	LOAIHOCVIEN NVARCHAR(20),
	FOREIGN KEY (MANGUOIDANGKY) REFERENCES NGUOIDANGKY (MANGUOIDANGKY)
)

CREATE TABLE PHIEUDUTHI
(
	MAPHIEUDUTHI CHAR(5),
	CONSTRAINT PK_PHIEUDUTHI
	PRIMARY KEY (MAPHIEUDUTHI)
)

CREATE TABLE CHUNGCHI
(
	MACC CHAR(4),
	TENCC NVARCHAR(40),
	KETQUA NVARCHAR(12),
	CONSTRAINT PK_K
	PRIMARY KEY (MACC)
)

CREATE TABLE LICHTHI
(
	MALICHTHI CHAR(5),
	NGAYTHI DATE,
	GIOTHI TIME,
	CONSTRAINT PK_LICHTHI
	PRIMARY KEY (MALICHTHI)
)

CREATE TABLE HOCPHAN
(
	MAHOCPHAN CHAR(5),
	MALOP CHAR(5),
	HOCPHI INT, 
	PHONGHOC INT,
	SOTINCHI INT, 
	TENHOCPHAN NVARCHAR(20),
	TINHTRANG NVARCHAR(20),
	CONSTRAINT PK_HOCPHAN
	PRIMARY KEY (MAHOCPHAN)
)

CREATE TABLE KHOAHOC
(
	MAKHOAHOC CHAR(5),
	TENKHOAHOC NVARCHAR(40),
	CONSTRAINT PK_KHOAHOC
	PRIMARY KEY (MAKHOAHOC)
)

CREATE TABLE HOADON
(
	MAHOADON CHAR(5),
	NHANVIEN CHAR(5),
	SOTIEN MONEY,
	CONSTRAINT PK_HOADON
	PRIMARY KEY (MAHOADON)
)

CREATE TABLE HINHTHUCTHANHTOAN
(
	MAHTTT CHAR(5),
	TENHTTT CHAR(5),
	CONSTRAINT PK_HINHTHUCTHANHTOAN
	PRIMARY KEY (MAHTTT)
)

CREATE TABLE GIAOVIEN
(
	MAGV CHAR(5),
	HOTEN NVARCHAR(40),
	PHAI NVARCHAR(3)
		CONSTRAINT CK_PHAI_GIAOVIEN CHECK (PHAI IN('NAM',N'NỮ')),
	NGAYSINH DATE,
	DIACHI NVARCHAR(100),
	BANGCAP VARCHAR(20),
	SDT CHAR(10),
	CONSTRAINT PK_GIAOVIEN
	PRIMARY KEY (MAGV)
)
-- b3 sau khi tao het cac bang
-- tao khoá ngoại
ALTER TABLE NGUOIDANGKY
ADD 
	CONSTRAINT FK_NGUOIDANGKY_PHIEUDUTHI 
	FOREIGN KEY(MANGUOIDANGKY) 
    REFERENCES PHIEUDUTHI(MAPHIEUDUTHI);