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)
)