use Lab1 CREATE TABLE Faculty FacPK int PRIMARY KEY Name varchar 50 UN

use [Lab1.2]

CREATE TABLE Faculty (
	FacPK int PRIMARY KEY,
	[Name] varchar(50) UNIQUE NOT NULL,
	DekanFK int,	-- Если удаляется преподаватель, являющийся деканом, то ссылка на него устанавливается в NULL.
	Building char(2),
	Fund decimal(9, 2),

	CONSTRAINT CK_Faculty_Building_Number_Legit CHECK(Building BETWEEN '1' AND '10'),
	CONSTRAINT CK_Fund_Sufficient CHECK(Fund > 10000.00) 
)

CREATE TABLE Department (
	DepPK int PRIMARY KEY,
	FacFK int NOT NULL, -- Нельзя удалить факультет, если на нем имеется хотя бы одна кафедра.
	[Name] varchar(50) NOT NULL,
	HeadFK int,	-- Если удаляется преподаватель, являющийся зав. кафедрой, то ссылка на него устанавливается в NULL.
	Building char(3),
	Fund decimal(8, 2),

	CONSTRAINT FK_FacFK 
		FOREIGN KEY(FacFK) 
		REFERENCES Faculty(FacPK),
	CONSTRAINT CK_Department_Building_Number_Legit CHECK(Building BETWEEN '1' AND '10'),
	CONSTRAINT CK_Fund_Within_Values CHECK(Fund >= 20000.00 AND Fund <= 100000.00),
	CONSTRAINT UQ_FacFK_Name UNIQUE (FacFK, [Name])
)

CREATE TABLE Teacher (
	TchPK int PRIMARY KEY,
	DepFK int,	--Если удаляется кафедра, то автоматически устанавливаются в NULL все ссылки на нее.
	[Name] varchar(50) NOT NULL,
	Post varchar(15),
	Tel char(7),
	Hiredate date NOT NULL,
	Salary decimal(6, 2) NOT NULL,
	Commision decimal(6, 2) DEFAULT(0),
	ChiefFK int,

	CONSTRAINT FK_DepFK
		FOREIGN KEY(DepFK) 
		REFERENCES Department(DepPK) 
		ON DELETE SET NULL,
	CONSTRAINT CK_Predefined_Post 
		CHECK(Post IN('ассистент', 'преподаватель', 'доцент', 'профессор')),
	CONSTRAINT CK_Hiredate_Legit CHECK(Hiredate > '19500101'),
	CONSTRAINT CK_Salary_Suffisient CHECK(Salary > 1000),
	CONSTRAINT CK_Commision_IsPositive CHECK(Commision >= 0),
	CONSTRAINT CK_Commision_Suffisient 
		CHECK(Commision >= Salary * 0.5 AND (Commision + Salary >= 1000 AND Commision + Salary <= 3000)),
	CONSTRAINT CK_ChiefFK_NotSelforinted CHECK(ChiefFK <> TchPK),
	CONSTRAINT FK_ChiefFK FOREIGN KEY (ChiefFK) 
		REFERENCES Teacher(TchPK) 
		ON DELETE SET NULL
)

ALTER TABLE Faculty
ADD FOREIGN KEY(DekanFK) REFERENCES Teacher(TchPK) ON DELETE SET NULL

ALTER TABLE Department
ADD FOREIGN KEY(HeadFK) REFERENCES Teacher(TchPK) ON DELETE SET NULL

CREATE TABLE Sgroup (
	GrpPK int PRIMARY KEY,
	DepFK int,
	Course decimal(1),
	Num decimal(3),
	Quantity decimal(2),
	Curator int,
	Rating decimal(3),

	CONSTRAINT FK_DepFK 
		FOREIGN KEY(DepFK)
		REFERENCES Department(DepPK)
		ON DELETE SET NULL,
	CONSTRAINT CK_Course_Legit CHECK(Course BETWEEN 1 AND 6),
	CONSTRAINT CK_Num_Legit CHECK(Num > 0 AND Num < 700),
	CONSTRAINT CK_Quantity_Within CHECK(Quantity BETWEEN 1 AND 50),
	CONSTRAINT FK_Curator
		FOREIGN KEY(Curator)
		REFERENCES Teacher(TchPK)
		ON DELETE SET NULL,
	CONSTRAINT CK_Rating_Within CHECK(Rating BETWEEN 1 AND 100),		
	CONSTRAINT UQ_DepFK_Num UNIQUE(DepFK, Num),
	CONSTRAINT UQ_DepFK_Curator UNIQUE(DepFK, Curator)
)

CREATE TABLE [Subject] (
	SbjPK int PRIMARY KEY,
	[Name] varchar(50) UNIQUE NOT NULL
)

CREATE TABLE Room (
	RoomPK int PRIMARY KEY,
	Num int NOT NULL,
	Seats decimal(3),
	[Floor] decimal(2),
	Building char(5),

	CONSTRAINT CK_Seats_WIthin CHECK(Seats BETWEEN 1 AND 300),
	CONSTRAINT CK_Floor_Within CHECK([Floor] BETWEEN 1 AND 16),
	CONSTRAINT CK_Building_Legit CHECK(Building BETWEEN '1' AND '10'),
	CONSTRAINT UQ_Num_Building UNIQUE(Num, Building)
)

CREATE TABLE Lecture (
	TchFK int,
	GrpFK int,
	SbjFK int,
	RoomFK int,
	[Type] varchar(15) NOT NULL,
	[Day] char(3) NOT NULL,
	[Week] decimal(1) NOT NULL,
	Lesson decimal(1) NOT NULL,

	CONSTRAINT FK_TchFK 
		FOREIGN KEY(TchFK) 
		REFERENCES Teacher(TchPK) 
		ON DELETE SET NULL,
		
	CONSTRAINT FK_GrpFK
		FOREIGN KEY(GrpFK)
		REFERENCES Sgroup(GrpPK)
		ON DELETE CASCADE,

	CONSTRAINT FK_SbjFK
		FOREIGN KEY(SbjFK)
		REFERENCES [Subject](SbjPK),

	CONSTRAINT FK_RoomFK
		FOREIGN KEY(RoomFK)
		REFERENCES Room(RoomPK),

	CONSTRAINT CK_Type_Correct 
		CHECK([Type] IN('лекция', 'лабораторная', 'семинар', 'пркатика')),
	CONSTRAINT CK_Day_Correct
		CHECK([Day] IN('пон', 'втр', 'срд', 'чет', 'пят' , 'суб', 'вск')),
	CONSTRAINT CK_Week_Correct CHECK([Week] >= 1 AND [Week] <= 2),
	CONSTRAINT CK_Lesson_Correct CHECK(Lesson BETWEEN 1 AND 8),
	CONSTRAINT UQ_Lesson_For_Group UNIQUE(GrpFK, [Day], [Week], Lesson),
	CONSTRAINT UQ_Lesson_For_Teacher UNIQUE(TchFK, [Day], [Week], Lesson)
)