source-tạo-bảng-và-ràng-buộc.sql

USE master
GO
IF DB_ID('QLBTN') IS NOT NULL
	DROP DATABASE QLBTN
GO 
CREATE DATABASE QLBTN
GO

USE QLBTN 

CREATE TABLE NHABAN
(
	MANHA CHAR(5),
	MALOAI CHAR(5),
	DIACHI NVARCHAR(50),
	SOLUONGPHONG INT,
	MACHINHANH CHAR(5),
	MANVPHUTRACH CHAR(5),
	MACHUNHA CHAR(5),
	NGAYDANG DATETIME,
	NGAYHETHAN DATETIME,
	TINHTRANG NVARCHAR(30),
	SOLUOTXEM INT,
	GIABAN INT,
	DIEUKIENBAN NVARCHAR(30)
	--KHAI BAO KHOA CHINH
	CONSTRAINT PK_NB 
	PRIMARY KEY (MANHA)
)
CREATE TABLE NHACHOTHUE
(
	MANHA CHAR(5),
	MALOAI CHAR(5),
	DIACHI NVARCHAR(50),
	SOLUONGPHONG INT,
	MACHINHANH CHAR(5),
	MANVPHUTRACH CHAR(5),
	MACHUNHA CHAR(5),
	TIENTHUE1THANG INT,
	NGAYDANG DATETIME,
	NGAYHETHAN DATETIME,
	TINHTRANG NVARCHAR(30),
	SOLUOTXEM INT
	--KHAI BAO KHOA CHINH
	CONSTRAINT PK_NCT
	PRIMARY KEY (MANHA)
)

CREATE TABLE LOAINHA
(
	MALOAINHA CHAR(5),
	TENLOAINHA NVARCHAR(20)
	--KHOA CHINH
	CONSTRAINT PK_LN
	PRIMARY KEY (MALOAINHA)
)

CREATE TABLE CHUNHA
(
	MACHUNHA CHAR(5),
	HOTEN NVARCHAR(50),
	DIACHI NVARCHAR(50),
	SODIENTHOAI CHAR(12) UNIQUE,
	--KHOA CHINH
	CONSTRAINT PK_CN
	PRIMARY KEY (MACHUNHA)
	--RANG BUOC SDT LA DUY NHAT 

)

CREATE TABLE CHINHANH 
(
	MACHINHANH CHAR(5),
	DIACHI NVARCHAR(50),
	SOFAX CHAR(6) UNIQUE,
	SODIENTHOAI CHAR(12) UNIQUE,
	CONSTRAINT PK_CHINHANH
	PRIMARY KEY (MACHINHANH)
)

CREATE TABLE NHANVIEN 
(
	MANV CHAR(5),
	HOTEN NVARCHAR(30),
	DIACHI NVARCHAR(50),
	DIENTHOAI CHAR(12),
	GIOITINH NCHAR(5),
	NGAYSINH DATETIME,
	LUONG INT,
	MACHINHANH CHAR(5)
	--
	CONSTRAINT PK_NV
	PRIMARY KEY (MANV, MACHINHANH)
)

CREATE TABLE KHACHHANG 
(
	MANGUOITHUE CHAR(5),
	MALOAI CHAR(5),
	HOTEN NVARCHAR(30),
	DIACHI NVARCHAR(50),
	SODIENTHOAI CHAR(12) UNIQUE,
	MACHINHANH CHAR(5)
	CONSTRAINT PK_KH
	PRIMARY KEY (MANGUOITHUE)
)

CREATE TABLE THONGBAOXEMNHA 
(	
	MAKHACHHANG CHAR(5),
	MANV CHAR(5),
	MANHATHUE CHAR(5),
	MANHABAN CHAR(5),
	MACHINHANH CHAR(5),
	NGAYXEM DATETIME, 
	NHANXET NVARCHAR(30),
	CONSTRAINT PK_TBXM
	PRIMARY KEY (MAKHACHHANG, MANV, NGAYXEM)
)

CREATE TABLE HOPDONGCHOTHUE
(
	MAHOPDONG CHAR(5),
	MANHACHOTHUE CHAR(5),
	MAKHACHHANG CHAR(5),
	MACHUNHA CHAR(5)
	CONSTRAINT PK_HDCT
	PRIMARY KEY (MAHOPDONG)
)
CREATE TABLE HOPDONGBAN
(
	MAHOPDONG CHAR(5),
	MANHABAN CHAR(5),
	MAKHACHHANG CHAR(5),
	MACHUNHA CHAR(5)
	CONSTRAINT PK_HDB
	PRIMARY KEY (MAHOPDONG)
)

--TẠO CÁC RÀNG BUỘC KHÓA NGOẠI

--KHOA NGOAI NHABAN VA LOAI NHA 
ALTER TABLE NHABAN ADD CONSTRAINT FK_NB_LN FOREIGN KEY(MALOAI) REFERENCES LOAINHA 
--KHOA NGOAI NHACHOTHUE VA LOAI NHA 
ALTER TABLE NHACHOTHUE ADD CONSTRAINT FK_NCT_LN FOREIGN KEY(MALOAI) REFERENCES LOAINHA 

--KHOA NGOAI CHU NHA VOI NHABAN
ALTER TABLE NHABAN ADD CONSTRAINT FK_NB_CN FOREIGN KEY (MACHUNHA) REFERENCES CHUNHA
--KHOA NGOAI CHU NHA VOI NHACHOTHUE
ALTER TABLE NHACHOTHUE ADD CONSTRAINT FK_NCT_CN FOREIGN KEY (MACHUNHA) REFERENCES CHUNHA
--KHOA NGOAI VOI NHABAN
ALTER TABLE NHABAN ADD CONSTRAINT FK_NB_CHINHANH FOREIGN KEY (MACHINHANH) REFERENCES CHINHANH
--KHOA NGOAI VOI NHACHOTHUE
ALTER TABLE NHACHOTHUE ADD CONSTRAINT FK_NCT_CHINHANH FOREIGN KEY (MACHINHANH) REFERENCES CHINHANH
--CHECK GIOITINH 
ALTER TABLE NHANVIEN
ADD
	CONSTRAINT C_GIOITINH
	CHECK(GIOITINH IN('Nam', N'Nữ'))
--RANG BUOC KHOA NGOAI NHAN VIEN VOI NHABAN 
ALTER TABLE NHABAN ADD CONSTRAINT FK_NB_NV FOREIGN KEY (MANVPHUTRACH, MACHINHANH) REFERENCES NHANVIEN
--RANG BUOC KHOA NGOAI NHAN VIEN VOI NHACHOTHUE
ALTER TABLE NHACHOTHUE ADD CONSTRAINT FK_NCT_NV FOREIGN KEY (MANVPHUTRACH, MACHINHANH) REFERENCES NHANVIEN
--RANG BUOC KHOA NGOAI NHAN VIEN VOI CHI NHANH 
ALTER TABLE NHANVIEN ADD CONSTRAINT FK_NV_CHINHANH FOREIGN KEY (MACHINHANH) REFERENCES CHINHANH
--RANG BUOC KHOA NGOAI KHACH HANG VOI CHI NHANH
ALTER TABLE KHACHHANG ADD CONSTRAINT PK_KH_CHINHANH FOREIGN KEY (MACHINHANH) REFERENCES CHINHANH
--RANG BUOC KHACH HANG VOI LOAI NHA 
ALTER TABLE KHACHHANG ADD CONSTRAINT PK_KH_LN FOREIGN KEY (MALOAI) REFERENCES LOAINHA

--RANG BUOC THONGBAOXEMNHA VOI KHACHHANG
ALTER TABLE THONGBAOXEMNHA ADD CONSTRAINT PK_TBXN_KH FOREIGN KEY (MAKHACHHANG) REFERENCES KHACHHANG
--RANG BUOC THONGBAOXEMNHA VOI NHANVIEN
ALTER TABLE THONGBAOXEMNHA ADD CONSTRAINT PK_TBXN_NV FOREIGN KEY (MANV, MACHINHANH) REFERENCES NHANVIEN
--RANG BUOC THONG BAO XEM NHA VOI NHABAN
ALTER TABLE THONGBAOXEMNHA ADD CONSTRAINT PK_TBXN_NB FOREIGN KEY (MANHABAN) REFERENCES NHABAN
--RANG BUOC THONG BAO XEM NHA VOI NHACHOTHUE
ALTER TABLE THONGBAOXEMNHA ADD CONSTRAINT PK_TBXN_NCT FOREIGN KEY (MANHATHUE) REFERENCES NHACHOTHUE

--RANG BUOC HOPDONGBAN VOI CHU NHA
ALTER TABLE HOPDONGBAN ADD CONSTRAINT PK_HDB_CN FOREIGN KEY (MACHUNHA) REFERENCES CHUNHA
--RANG BUOC HOPDONGBAN VOI KHACHHANG 
ALTER TABLE HOPDONGBAN ADD CONSTRAINT PK_HDB_KH FOREIGN KEY (MAKHACHHANG) REFERENCES KHACHHANG
--RANG BUOC HOPDONGBAN VOI NHA BAN
ALTER TABLE HOPDONGBAN ADD CONSTRAINT PK_HDB_NB FOREIGN KEY (MANHABAN) REFERENCES NHABAN

--RANG BUOC HOPDONGCHOTHUE VOI CHU NHA
ALTER TABLE HOPDONGCHOTHUE ADD CONSTRAINT PK_HDCT_CN FOREIGN KEY (MACHUNHA) REFERENCES CHUNHA
--RANG BUOC HOPDONGCHOTHUE VOI KHACHHANG 
ALTER TABLE HOPDONGCHOTHUE ADD CONSTRAINT PK_HDCT_KH FOREIGN KEY (MAKHACHHANG) REFERENCES KHACHHANG
--RANG BUOC HOPDONGCHOTHUE VOI NHACHOTHUE
ALTER TABLE HOPDONGCHOTHUE ADD CONSTRAINT PK_HDCT_NCT FOREIGN KEY (MANHACHOTHUE) REFERENCES NHACHOTHUE