--Create database [Lab1.2]
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 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '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 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '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_Teacher
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 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '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)
)
INSERT INTO FACULTY
VALUES (1, 'informatics', NULL, '1', 57398.00)
INSERT INTO FACULTY
VALUES (2, 'economy', NULL, '3' , NULL)
INSERT INTO FACULTY
VALUES (3, 'linguistics', NULL, '4', NULL)
INSERT INTO DEPARTMENT VALUES
(1,1,'SE',NULL,'5',20378.00),
(2,1,'CAD',NULL,'5',21000.00),
(3,1,'DBMS',NULL,'4',22000.00),
(4,2,'Accounts',NULL,'3',NULL)
INSERT INTO TEACHER VALUES
(1,1,'Andrew','ассистент','2281319','20010201',1250,80,NULL),
(2,1,'John','профессор','2281550 ','20010701',1400,150,1),
(3,2,'Bill','преподаватель',NULL,'20021117',1240,80,1),
(4,2,'Albert','ассистент',NULL,'20011111',1260,100,3)
INSERT INTO SGROUP VALUES
(1,1,1,101,33,4,20),
(2,1,1,102,35,3,22),
(3,3,2,205,20,1,15),
(4,3,3,305,25,NULL,40),
(5,3,4,405,25,2,37)
INSERT INTO [SUBJECT] VALUES
(1,'pascal'),
(2,'C'),
(3,'OS'),
(4,'inernet'),
(5,'dbms')
INSERT INTO ROOM VALUES
(1,101,20,'1',5),
(2,316,150,'3',5),
(3,201,150,'2',2),
(4,202,30,'2',5)
INSERT INTO LECTURE VALUES
(1,1,1,1,'лекция','пон',1,1),
(1,2,2,1,'лабораторная','пон',1,2),
(2,3,3,1,'лекция','втр',1,3),
(3,4,4,2,'практика','срд',1,3),
(4,4,5,2,'практика','чет',2,4),
(4,4,5,3,'семинар','пят',2,1)
/*ABOUT NATIONAL AVIATION UNIVERSITY*/
INSERT INTO FACULTY VALUES
(4,'ФККПІ', NULL,'6', 1000000.00)
INSERT INTO DEPARTMENT VALUES
(5,4,'Кафедра інженеріїї програмного забезпечення',NULL,'6',21000.00)
INSERT INTO TEACHER VALUES
(5,5,'Азаренко Олена Василівна','профессор','8800055','01.02.2010',2000,80,NULL),
(6,5,'Писарчук Олексій Олександрович','доцент','123456','01.02.2009',1900,50,5),
(7,5,'Безкоровайна Юлія Миколаївна','преподаватель','1234567','01.02.2001',1900,50,6),
(8,5,'Белозерова Яна Андріївна','преподаватель','1234567','01.02.2010',1800,80,7)
UPDATE FACULTY
SET DekanFK = 4
WHERE FacPK = 4
UPDATE DEPARTMENT
SET HeadFK = 2
WHERE DepPK = 3
INSERT INTO SGROUP VALUES
(6,5,3,320,28,NULL,80)
INSERT INTO [SUBJECT] VALUES
(6,'Documents'),
(7,'SQL'),
(8,'Visualization'),
(9,'Artificial Intelligence'),
(10,'Software Design')
INSERT INTO ROOM VALUES
(5,6200,180,'2',6),
(6,6201,150,'2',6),
(7,6313,30,'3',6),
(8,6311,30,'3',6)
INSERT INTO LECTURE VALUES
(7,6,6,5,'лекция','пон',1,1),
(8,6,6,8,'лабораторная','втр',1,2)
INSERT INTO TEACHER VALUES
(9,1,'Bob','профессор','2281319','01.02.2001',1250,80,NULL)
UPDATE FACULTY
SET DekanFK=9,Fund=346700.00
WHERE [Name] = 'economy'
INSERT INTO TEACHER VALUES
(10,1,'Frank','профессор','2281319','01.02.2001',1100,80,9)
UPDATE DEPARTMENT
SET HeadFK=10, Building=3
WHERE DepPK =3
UPDATE TEACHER
SET Commision = Salary*0.25
WHERE Post = 'ассистент'
UPDATE SGROUP
SET Rating = 1
WHERE Course = 1
UPDATE SUBJECT
SET [Name] = 'html'
WHERE [Name] = 'internet'