--Create database Lab1 use Lab1 CREATE TABLE Faculty FacPK int PRIMARY

Anonymous
SQL
20 Oct 2019
--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'
← newer dump
older dump →