nYrKAJiW

1-- Лабораторная работа №3, дисциплина "Базы данных", СПбГУАП, весна-лето 2018
2
3-- Включение форсирования ограничений ссылочной целостности
4PRAGMA foreign_keys = ON;
5
6-- Очистка схемы данных
7
8DROP TABLE IF EXISTS Дисциплина_у_специальности;
9DROP TABLE IF EXISTS Занятие_у_группы;
10DROP TABLE IF EXISTS Занятие;
11DROP TABLE IF EXISTS Группа;
12DROP TABLE IF EXISTS Преподаватель;
13DROP TABLE IF EXISTS Предмет;
14DROP TABLE IF EXISTS Аудитория;
15DROP TABLE IF EXISTS Кафедра;
16DROP TABLE IF EXISTS Специальность;
17
18-- Создание схемы данных
19
20CREATE TABLE Аудитория (
21Номер_аудитории VARCHAR(8) PRIMARY KEY
22);
23  
24CREATE TABLE Предмет (
25Название TEXT PRIMARY KEY
26);
27  
28CREATE TABLE Кафедра (
29Название TEXT,
30Номер INT PRIMARY KEY
31);
32
33CREATE TABLE Преподаватель (
34Номер_паспорта VARCHAR(11)PRIMARY KEY,
35Номер_кафедры INT REFERENCES Кафедра(Номер),
36ФИО TEXT ,
37Должность TEXT
38);
39  
40CREATE TABLE Специальность (
41Название TEXT,
42Номер_специальности VARCHAR(10) PRIMARY KEY
43);
44 
45CREATE TABLE Дисциплина_у_специальности (
46Объем_лабораторных_занятий INT,
47Объем_практических_занятий INT,
48Объем_лекционных_занятий INT CHECK (Объем_лекционных_занятий<60),
49Курсовой_проект VARCHAR(4),
50Номер_семестра INT,
51Номер_специальности VARCHAR(10) REFERENCES Специальность(Номер_специальности),
52Название_предмета TEXT,
53PRIMARY KEY (Номер_специальности,Название_предмета),
54FOREIGN KEY(Название_предмета) REFERENCES Предмет(Название)
55);
56 
57CREATE TABLE Группа (
58Номер_группы VARCHAR(5),
59Номер_специальности VARCHAR(10) REFERENCES Специальность(Номер_специальности) ,
60Номер_семестра INT,
61PRIMARY KEY (Номер_группы)
62 
63);
64CREATE TABLE Занятие (
65Тип TEXT,
66Номер_занятия INTEGER CHECK (([День_недели]="Понедельник" And [Номер_занятия]<=8) Or ([День_недели]="Вторник" And [Номер_занятия]<=8) Or ([День_недели]="Среда" And [Номер_занятия]<=8) Or ([День_недели]="Четверг" And [Номер_занятия]<=8) Or ([День_недели]="Пятница" And [Номер_занятия]<=8) Or ([День_недели]="Суббота" And [Номер_занятия]<=6)),
67День_недели TEXT ,
68Номер_аудитории VARCHAR(8) REFERENCES Аудитория(Номер_аудитории),
69Номер_паспорта_преподавателя VARCHAR(11) REFERENCES Преподаватель(Номер_паспорта),
70Предмет TEXT references Предмет(Название),
71PRIMARY KEY (Номер_занятия, День_недели, Номер_аудитории)
72);
73 
74CREATE TABLE Занятие_у_группы (
75Номер_группы VARCHAR(5),  
76Номер_занятия INTEGER, 
77День_недели TEXT,
78Номер_аудитории VARCHAR(8),
79PRIMARY KEY (Номер_группы, Номер_занятия, День_недели, Номер_аудитории),
80UNIQUE (Номер_группы,Номер_занятия,День_недели),
81FOREIGN KEY(Номер_занятия, День_недели, Номер_аудитории) REFERENCES Занятие (Номер_занятия, День_недели, Номер_аудитории),
82FOREIGN KEY(Номер_группы) REFERENCES Группа(Номер_группы)
83);
84
85-- Заполнение данными
86
87INSERT INTO Аудитория VALUES ('32-02'), ('32-04'),('24-03'),('12-03'), ('12-15'),('13-15'), ('52-08'), ('32-09');
88INSERT INTO Кафедра VALUES ('Кафедра вычислительных систем и сетей',44), ('Кафедра проблемно-ориентированных вычислительных комплексов',41), ('Кафедра компьютерных технологий и программной инженерии',43);
89DROP VIEW IF EXISTS View1;
90DROP VIEW IF EXISTS View2;
91DROP VIEW IF EXISTS View3;
92-- ваш код здесь!
93
94-- Создание представлений
95-- 1) Группы специальности "Программная инженерия", у которых не назначено каких-то пар из тех, которые должны быть в этом семестре.
96--CREATE VIEW View1 AS
97--Select Предмет from Select Название_предмета from Дисциплина_у_специальности where Номер_специальности=(Select Номер_специальности from Специальность where Название='Программная инженерия') Group by Номер_семестра,Название_предмета;
98
99
100INSERT INTO Предмет VALUES ('Теория алгоритмов'), ('Технология программирования'), ('Моделирование'),('Микропроцессорные системы'),('Интерактивная компьютерная графика'), ('Системное программное обеспечение'),('Операционные системы'), ('Схемотехника'), ('Базы данных'), ('Корпоративные сети со службой каталога');
101INSERT INTO Преподаватель VALUES ('2345 435432', 44, 'Петров Игорь Юрьевич', 'Старший преподаватель'), ('5756 214324', 43, 'Пинцкер Василий Анатольевич', 'Профессор'), ('7654 315543', 44, 'Архипов Евгений Алексеевич', 'Профессор'), ('8765 254674', 41, 'Беляков Дмитрий Андреевич', 'Ассистент');
102INSERT INTO Занятие VALUES --('Практика', 3, 'Четверг', '24-03','5756 214324','Операционные системы'),
103('Лекция', 4, 'Четверг', '24-03','8765 254674','Операционные системы'),
104                           ('Практика', 3, 'Вторник', '24-03','5756 214324','Моделирование'),
105                           ('Лекция', 3, 'Понедельник', '32-02','2345 435432', 'Корпоративные сети со службой каталога'),
106                           ('Практика', 2, 'Вторник', '24-03','5756 214324','Моделирование'), 
107                           ('Практика', 4, 'Пятница', '12-03','5756 214324','Технология программирования'),
108                           ('Лабораторная работа', 4, 'Пятница', '12-15','7654 315543','Операционные системы'),
109                           ('Лабораторная работа', 5, 'Суббота', '13-15','7654 315543','Моделирование'),
110                           ('Практика', 3, 'Четверг', '12-03','7654 315543','Базы данных'),
111                           ('Лекция', 4, 'Четверг', '52-08','8765 254674','Операционные системы'),
112                           ('Практика', 4, 'Среда', '32-02','7654 315543','Микропроцессорные системы'),
113                           ('Практика', 6, 'Вторник', '32-09','7654 315543','Схемотехника'),
114                           ('Лекция', 5, 'Вторник', '32-09','7654 315543','Технология программирования');
115INSERT INTO Специальность VALUES ('Информатика и вычислительная техника','09.03.01'), ('Программная инженерия','09.03.04'), ('Прикладная информатика','09.03.03'),('Электроника и наноэлектроника','11.03.04'),('Математическое обеспечение','02.03.03'), ('Прикладная математика и информатика','01.03.02');
116INSERT INTO Группа VALUES ('4732', '09.03.04', 2),('4542', '09.03.01', 6), ('4541','09.03.01', 6), ('4731','09.03.04', 2), ('4512', '09.03.03', 6),('4431', '11.03.04', 8),('4543','09.03.01', 6),('4735', '01.03.02', 2),('4735М', '02.03.03', 2),('4531', '11.03.04', 6),('4515', '09.03.03', 6);
117INSERT INTO Занятие_у_группы VALUES --('4731', 3, 'Четверг', '24-03'), 
118('4732', 4, 'Четверг', '24-03'),
119('4732', 3, 'Вторник', '24-03'),('4542', 3, 'Понедельник', '32-02'), ('4541', 3, 'Понедельник', '32-02'),('4731', 2, 'Вторник', '24-03'), ('4512', 4, 'Пятница', '12-03'),('4735', 4, 'Пятница', '12-15'),('4431', 5, 'Суббота', '13-15'),('4543', 3, 'Четверг', '12-03'),('4735', 4, 'Четверг', '52-08'),('4735М', 4, 'Среда', '32-02'),('4531', 6, 'Вторник', '32-09'),('4515', 5, 'Вторник', '32-09');
120
121
122CREATE VIEW View1 AS
123SELECT DISTINCT X.Номер_аудитории,COUNT( DISTINCT X.Номер_занятия)*100/COUNT( DISTINCT Y.Номер_занятия)--*100/COUNT( DISTINCT Y.Номер_занятия)
124FROM Занятие X, Занятие Y 
125where X.Номер_паспорта_преподавателя in (Select Номер_паспорта From Преподаватель where Номер_кафедры='44') AND X.Тип='Лекция' AND Y.Номер_паспорта_преподавателя IN (Select Номер_паспорта From Преподаватель where Номер_кафедры='44')AND Y.Номер_аудитории = X.Номер_аудитории
126group by Y.Номер_аудитории,X.Номер_аудитории;
127
128
129
130
131CREATE VIEW View2 AS
132Select Номер_группы from 
133Группа where Номер_группы not in( SELECT Номер_группы  FROM 
134Занятие_у_группы  natural join Занятие where  
135Предмет<>(Select Название_предмета from 
136Дисциплина_у_специальности where 
137Номер_специальности=(Select Номер_специальности from
138Специальность where Название='Программная инженерия') Group by Номер_семестра,Название_предмета)) 
139and Номер_специальности=(Select Номер_специальности from 
140Специальность where Название='Программная инженерия');
141CREATE VIEW View3 AS
142select distinct Название from (
143select Кафедра.Название, П.Номер_паспорта, Название_предмета from Кафедра
144join Преподаватель П on Кафедра.Номер = П.Номер_кафедры
145join Занятие З on П.Номер_паспорта = З.Номер_паспорта_преподавателя
146join Занятие_у_группы З2 on З.Номер_занятия = З2.Номер_занятия and З.День_недели = З2.День_недели and З.Номер_аудитории = З2.Номер_аудитории
147join Группа Г on З2.Номер_группы = Г.Номер_группы
148join Специальность С on Г.Номер_специальности = С.Номер_специальности
149join Дисциплина_у_специальности с2 on С.Номер_специальности = с2.Номер_специальности
150group by Кафедра.Название, П.Номер_паспорта, Название_предмета
151having count(distinct С.Номер_специальности) > 1) as t group by Номер_паспорта having count(Название_предмета) = 1;
152INSERT INTO Дисциплина_у_специальности VALUES (33, 53, 58, 'нет',6,'09.03.01','Корпоративные сети со службой каталога'), (25, 23, 27, 'нет',2,'01.03.02','Операционные системы'), (43, 22, 31, 'нет',6,'09.03.01','Интерактивная компьютерная графика'),(15, 44, 24, 'нет',8,'11.03.04','Моделирование'),(0, 50, 0, 'есть',2,'02.03.03','Системное программное обеспечение'),(23, 25, 14, 'нет',4,'01.03.02','Технология программирования'),(37, 73, 15, 'нет',6,'09.03.01','Базы данных'),(47, 34, 32, 'есть',2,'11.03.04','Схемотехника'),(15, 24, 42, 'нет',2,'09.03.04','Операционные системы'),(33, 43, 21, 'нет',6,'09.03.03','Технология программирования'),(25, 56, 42, 'нет',2,'09.03.04','Моделирование'),(25, 56, 42, 'нет',2,'02.03.03','Микропроцессорные системы');