dGcRpSTx

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;
91-- ваш код здесь!
92
93-- Создание представлений
94-- 1) Группы специальности "Программная инженерия", у которых не назначено каких-то пар из тех, которые должны быть в этом семестре.
95--CREATE VIEW View1 AS
96--Select Предмет from Select Название_предмета from Дисциплина_у_специальности where Номер_специальности=(Select Номер_специальности from Специальность where Название='Программная инженерия') Group by Номер_семестра,Название_предмета;
97
98
99INSERT INTO Предмет VALUES ('Теория алгоритмов'), ('Технология программирования'), ('Моделирование'),('Микропроцессорные системы'),('Интерактивная компьютерная графика'), ('Системное программное обеспечение'),('Операционные системы'), ('Схемотехника'), ('Базы данных'), ('Корпоративные сети со службой каталога');
100INSERT INTO Преподаватель VALUES ('2345 435432', 44, 'Петров Игорь Юрьевич', 'Старший преподаватель'), ('5756 214324', 43, 'Пинцкер Василий Анатольевич', 'Профессор'), ('7654 315543', 44, 'Архипов Евгений Алексеевич', 'Профессор'), ('8765 254674', 41, 'Беляков Дмитрий Андреевич', 'Ассистент');
101INSERT INTO Занятие VALUES --('Практика', 3, 'Четверг', '24-03','5756 214324','Операционные системы'),
102('Лекция', 4, 'Четверг', '24-03','8765 254674','Операционные системы'),
103                           ('Практика', 3, 'Вторник', '24-03','5756 214324','Моделирование'),
104                           ('Лекция', 3, 'Понедельник', '32-02','2345 435432', 'Корпоративные сети со службой каталога'),
105                           ('Практика', 2, 'Вторник', '24-03','5756 214324','Моделирование'), 
106                           ('Практика', 4, 'Пятница', '12-03','5756 214324','Технология программирования'),
107                           ('Лабораторная работа', 4, 'Пятница', '12-15','7654 315543','Операционные системы'),
108                           ('Лабораторная работа', 5, 'Суббота', '13-15','7654 315543','Моделирование'),
109                           ('Практика', 3, 'Четверг', '12-03','7654 315543','Базы данных'),
110                           ('Лекция', 4, 'Четверг', '52-08','8765 254674','Операционные системы'),
111                           ('Практика', 4, 'Среда', '32-04','8765 254674','Микропроцессорные системы'),
112                           ('Практика', 6, 'Вторник', '32-09','8765 254674','Схемотехника'),
113                           ('Лекция', 5, 'Вторник', '32-09','7654 315543','Технология программирования');
114INSERT INTO Специальность VALUES ('Информатика и вычислительная техника','09.03.01'), ('Программная инженерия','09.03.04'), ('Прикладная информатика','09.03.03'),('Электроника и наноэлектроника','11.03.04'),('Математическое обеспечение','02.03.03'), ('Прикладная математика и информатика','01.03.02');
115INSERT 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);
116INSERT INTO Занятие_у_группы VALUES --('4731', 3, 'Четверг', '24-03'), 
117('4732', 4, 'Четверг', '24-03'),
118('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-04'),('4531', 6, 'Вторник', '32-09'),('4515', 5, 'Вторник', '32-09');
119CREATE VIEW View1 AS
120     SELECT X.Тип,COUNT( DISTINCT X.Тип)*100/COUNT (DISTINCT Y.Тип) Процент
121      FROM   Занятие X, Занятие Y where X.Тип='Лекция'; --and Номер_паспорта_преподавателя=(Select Номер_паспорта From Преподаватель where Номер_кафедры=(Select Номер From Кафедра ));
122      
123CREATE VIEW View2 AS
124Select Номер_группы from 
125Группа where Номер_группы not in( SELECT Номер_группы  FROM 
126Занятие_у_группы  natural join Занятие where  
127Предмет<>(Select Название_предмета from 
128Дисциплина_у_специальности where 
129Номер_специальности=(Select Номер_специальности from
130Специальность where Название='Программная инженерия') Group by Номер_семестра,Название_предмета)) 
131and Номер_специальности=(Select Номер_специальности from 
132Специальность where Название='Программная инженерия');
133
134INSERT 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','Микропроцессорные системы');