4fHT3gdc

1CREATE TABLE IF NOT EXISTS subjects
2(
3    subject_id NUMERIC(3) PRIMARY KEY,
4    subject_name VARCHAR(50) NOT NULL
5);
6-- +
7CREATE TABLE IF NOT EXISTS teachers
8(
9    teacher_id NUMERIC(3) PRIMARY KEY,
10    teacher_io VARCHAR(40) NOT NULL,
11    teacher_surname VARCHAR(20) NOT NULL,
12	subject_id NUMERIC(3) REFERENCES subjects
13);
14-- +
15CREATE TABLE IF NOT EXISTS classes
16(
17    class_id NUMERIC(3) PRIMARY KEY,
18    number_of_students NUMERIC(3) NOT NULL,
19    class_name VARCHAR(4) NOT NULL,
20	prof_subid_1 numeric(4) REFERENCES subjects,
21	prof_subid_2 numeric(4) REFERENCES subjects,
22	prof_subid_3 numeric(4) REFERENCES subjects
23);
24-- +
25 
26CREATE TABLE IF NOT EXISTS schedule
27(
28    lesson_id NUMERIC(5) PRIMARY KEY,
29    lesson_date DATE NOT NULL,
30	lesson_num NUMERIC(2) REFERENCES lesson_time,
31    hometask VARCHAR(30)
32);
33-- +
34
35CREATE TABLE IF NOT EXISTS subj_lessons
36(
37    subject_id NUMERIC(3) REFERENCES subjects,
38    lesson_id NUMERIC(5) REFERENCES schedule
39);
40
41-- +
42
43CREATE TABLE IF NOT EXISTS teach_lessons
44(
45    teacher_id NUMERIC(3) REFERENCES teachers,
46    lesson_id NUMERIC(5) REFERENCES schedule
47);
48
49-- +
50
51CREATE TABLE IF NOT EXISTS class_lessons
52(
53    class_id NUMERIC(3) REFERENCES classes,
54    lesson_id NUMERIC(5) REFERENCES schedule
55);
56
57-- +
58
59CREATE TABLE IF NOT EXISTS rooms
60(
61    room_number NUMERIC(5) PRIMARY KEY,
62    max_students NUMERIC(3) NOT NULL,
63	is_sport NUMERIC(1) NOT NULL
64);
65-- +
66
67CREATE TABLE IF NOT EXISTS room_lessons
68(
69    room_number NUMERIC(3) REFERENCES rooms,
70    lesson_id NUMERIC(5) REFERENCES schedule
71);
72
73-- +
74 
75CREATE TABLE IF NOT EXISTS lesson_time
76(
77    lesson_start_time TIME NOT NULL,
78	lesson_end_time TIME NOT NULL,
79    lesson_num NUMERIC(2) PRIMARY KEY
80);
81
82
83CREATE TABLE IF NOT EXISTS class_teachers
84(
85    class_id NUMERIC(3) REFERENCES classes,
86    subject_id NUMERIC(3) REFERENCES subjects,
87    teacher_id NUMERIC(3) REFERENCES teachers
88);
89-- +
90
91insert into subjects values
92	(1,'Математика профиль'),
93	(2,'Физика профиль'),
94	(3,'История профиль'),
95	(4,'Обществознание профиль'),
96	(5,'Биология профиль'),
97	(6,'Химия профиль'),
98	(7,'География'),
99	(8,'ИЗО'),
100	(9,'Физкультура'),
101	(10,'ОБЖ'),
102	(11,'Музыка'),
103	(12,'Физика'),
104	(13,'Биология'),
105	(14,'Химия'),
106	(15,'Математика'),
107	(16,'Русский язык'),
108	(17,'Обществознание'),
109	(18,'История ');
110
111 
112insert into teachers values
113	(1,'Тамара Сергеевна','Милонас',1),
114	(2,'Людмила Владимировна','Аракелян',2),
115	(3,'Елена Александровна','Морковина',3),
116	(4,'Елена Генадьевна','Суботина',4),
117	(5,'Игорь Эдуардович','Воснецов',5),
118	(6,'Даниил Сергеевич','Колобов',6),
119	(7,'Рауф Гасанович','Иванов',7),
120	(8,'Никита Львович','Петров',8),
121	(9,'Сакура Дмитриевна','Чан',9),
122	(10,'Наруто Минатович','Узумаки',10),
123	(11,'Татьяна Сергеевна','Доровская',11),
124	(12,'Евгения Владимировна','Кумаровна',12),
125	(13,'Дарима Александровна','Кириллова',13),
126	(14,'Алина Генадьевна','Цырендоржиева',14),
127	(15,'Джамал Эдуардович','Джалилов',15),
128	(16,'Камал Эдуардович','Джалилов',16),
129	(17,'Иван Иванович','Иванов',17),
130	(18,'Михаил Владимирович','Орехов',18);
131 
132insert into classes values
133	(1,25,'6А',1,2, NULL),
134	(2,18,'7А',1,2, NULL),
135	(3,26,'8Г',1,5,6),
136	(4,27,'8В',3,4, NULL),
137	(5,20,'8Б',5,6, NULL),
138	(6,29,'8А',1,2, NULL),
139	(7,25,'9В',1,3,4),
140	(8,26,'9Б',5,6, NULL),
141	(9,27,'9А',1,2, NULL),
142	(10,28,'10В',3,4, NULL),
143	(11,24,'10Б',1,5,6),
144	(12,23,'10А',1,2, NULL),
145	(13,29,'11В',3,4, NULL),
146	(14,22,'11Б',5,6, NULL),
147	(15,26,'11А',1,2, NULL);
148 
149insert into rooms values
150	(1,30,0),
151	(2,30,0),
152	(3,30,0),
153	(4,30,0),
154	(5,30,0),
155	(6,30,0),
156	(7,30,0),
157	(8,30,0),
158	(9,30,0),
159	(10,30,0),
160	(11,30,0),
161	(12,30,0),
162	(13,45,0),
163	(14,45,0),
164	(15,45,0),
165	(16,60,1),
166	(17,30,0),
167	(18,30,0);
168	
169insert into class_teachers values
170	(1,1,1),(1,2,2),(1,7,7),(1,8,8),(1,9,9),(1,10,10),(1,11,11),(1,12,12),(1,13,13),(1,14,14),(1,15,15),(1,16,16),(1,17,17),(1,18,18),
171	(2,1,1),(2,2,2),(2,7,7),(2,8,8),(2,9,9),(2,10,10),(2,11,11),(2,12,12),(2,13,13),(2,14,14),(2,15,15),(2,16,16),(2,17,17),(2,18,18),
172	(3,1,1),(3,5,5),(3,6,6),(3,7,7),(3,8,8),(3,9,9),(3,10,10),(3,11,11),(3,12,12),(3,13,13),(3,14,14),(3,15,15),(3,16,16),(3,17,17),(3,18,18),
173	(4,3,3),(4,4,4),(4,7,7),(4,8,8),(4,9,9),(4,10,10),(4,11,11),(4,12,12),(4,13,13),(4,14,14),(4,15,15),(4,16,16),(4,17,17),(4,18,18),
174	(5,5,5),(5,6,6),(5,7,7),(5,8,8),(5,9,9),(5,10,10),(5,11,11),(5,12,12),(5,13,13),(5,14,14),(5,15,15),(5,16,16),(5,17,17),(5,18,18),
175	(6,1,1),(6,2,2),(6,7,7),(6,8,8),(6,9,9),(6,10,10),(6,11,11),(6,12,12),(6,13,13),(6,14,14),(6,15,15),(6,16,16),(6,17,17),(6,18,18),
176	(7,1,1),(7,3,3),(7,4,4),(7,7,7),(7,8,8),(7,9,9),(7,10,10),(7,11,11),(7,12,12),(7,13,13),(7,14,14),(7,15,15),(7,16,16),(7,17,17),(7,18,18),
177	(8,5,5),(8,6,6),(8,7,7),(8,8,8),(8,9,9),(8,10,10),(8,11,11),(8,12,12),(8,13,13),(8,14,14),(8,15,15),(8,16,16),(8,17,17),(8,18,18),
178	(9,1,1),(9,2,2),(9,7,7),(9,8,8),(9,9,9),(9,10,10),(9,11,11),(9,12,12),(9,13,13),(9,14,14),(9,15,15),(9,16,16),(9,17,17),(9,18,18),
179	(10,3,3),(10,4,4),(10,7,7),(10,8,8),(10,9,9),(10,10,10),(10,11,11),(10,12,12),(10,13,13),(10,14,14),(10,15,15),(10,16,16),(10,17,17),(10,18,18),
180	(11,1,1),(11,5,5),(11,6,6),(11,7,7),(11,8,8),(11,9,9),(11,10,10),(11,11,11),(11,12,12),(11,13,13),(11,14,14),(11,15,15),(11,16,16),(11,17,17),(11,18,18),
181	(12,1,1),(12,2,2),(12,7,7),(12,8,8),(12,9,9),(12,10,10),(12,11,11),(12,12,12),(12,13,13),(12,14,14),(12,15,15),(12,16,16),(12,17,17),(12,18,18),
182	(13,3,3),(13,4,4),(13,7,7),(13,8,8),(13,9,9),(13,10,10),(13,11,11),(13,12,12),(13,13,13),(13,14,14),(13,15,15),(13,16,16),(13,17,17),(13,18,18),
183	(14,5,5),(14,6,6),(14,7,7),(14,8,8),(14,9,9),(14,10,10),(14,11,11),(14,12,12),(14,13,13),(14,14,14),(14,15,15),(14,16,16),(14,17,17),(14,18,18),
184	(15,1,1),(15,2,2),(15,7,7),(15,8,8),(15,9,9),(15,10,10),(15,11,11),(15,12,12),(15,13,13),(15,14,14),(15,15,15),(15,16,16),(15,17,17),(15,18,18);
185	
186insert into schedule values
187	(1,'2020-04-12',1,NULL),(2,'2020-04-12',2,NULL),(3,'2020-04-12',3,NULL),(4,'2020-04-12',4,NULL),(5,'2020-04-12',5,NULL),(6,'2020-04-12',6,NULL),(7,'2020-04-12',7,NULL),
188	(8,'2020-04-12',8,NULL),(9,'2020-04-12',1,NULL),(10,'2020-04-12',2,NULL),(11,'2020-04-12',3,NULL),(12,'2020-04-12',4,NULL),(13,'2020-04-12',5,NULL),(14,'2020-04-12',6,NULL),
189	(15,'2020-04-12',7,NULL),(16,'2020-04-12',1,NULL),(17,'2020-04-12',2,NULL),(18,'2020-04-12',3,NULL),(19,'2020-04-12',4,NULL),(20,'2020-04-12',5,NULL),(21,'2020-04-12',6,NULL),
190	(22,'2020-04-13',1,NULL),(23,'2020-04-13',2,NULL),(24,'2020-04-13',3,NULL),(25,'2020-04-13',4,NULL),(26,'2020-04-13',5,NULL),(27,'2020-04-13',6,NULL),(28,'2020-04-13',7,NULL),
191	(29,'2020-04-13',8,NULL),(30,'2020-04-13',1,NULL),(31,'2020-04-13',2,NULL),(32,'2020-04-13',3,NULL),(33,'2020-04-13',4,NULL),(34,'2020-04-13',5,NULL),(35,'2020-04-13',6,NULL),
192	(36,'2020-04-13',7,NULL),(37,'2020-04-13',1,NULL),(38,'2020-04-13',2,NULL),(39,'2020-04-13',3,NULL),(40,'2020-04-13',4,NULL),(41,'2020-04-13',5,NULL),(42,'2020-04-13',6,NULL),
193	(43,'2020-04-14',1,NULL),(44,'2020-04-14',2,NULL),(45,'2020-04-14',3,NULL),(46,'2020-04-14',4,NULL),(47,'2020-04-14',5,NULL),(48,'2020-04-14',6,NULL),(49,'2020-04-14',7,NULL),
194	(50,'2020-04-14',1,NULL),(51,'2020-04-14',2,NULL),(52,'2020-04-14',3,NULL),(53,'2020-04-14',4,NULL),(54,'2020-04-14',5,NULL),(55,'2020-04-14',6,NULL),(56,'2020-04-14',7,NULL),
195	(57,'2020-04-14',1,NULL),(58,'2020-04-14',2,NULL),(59,'2020-04-14',3,NULL),(60,'2020-04-14',4,NULL),(61,'2020-04-14',5,NULL),(62,'2020-04-14',6,NULL),
196	(63,'2020-04-15',1,NULL),(64,'2020-04-15',2,NULL),(65,'2020-04-15',3,NULL),(66,'2020-04-15',4,NULL),(67,'2020-04-15',5,NULL),(68,'2020-04-15',6,NULL),(69,'2020-04-15',7,NULL),
197	(70,'2020-04-15',8,NULL),(71,'2020-04-15',1,NULL),(72,'2020-04-15',2,NULL),(73,'2020-04-15',3,NULL),(74,'2020-04-15',4,NULL),(75,'2020-04-15',5,NULL),(76,'2020-04-15',6,NULL),
198	(77,'2020-04-15',7,NULL),(78,'2020-04-15',1,NULL),(79,'2020-04-15',2,NULL),(80,'2020-04-15',3,NULL),(81,'2020-04-15',4,NULL),(82,'2020-04-15',5,NULL),(83,'2020-04-15',6,NULL);
199	
200insert into subj_lessons values
201	(1,1),(1,2),(2,3),(2,4),(7,5),(16,6),(16,7),
202	(9,8),(1,9),(1,10),(12,11),(12,12),(5,13),(5,14),
203	(9,15),(1,16),(1,17),(3,18),(4,19),(7,20),(9,21),
204	(14,22),(14,23),(13,24),(13,25),(11,26),(10,27),(8,28),
205	(9,29),(6,30),(6,31),(13,32),(12,33),(16,34),(16,35),
206	(12,36),(4,37),(4,38),(12,39),(12,40),(16,41),(10,42),
207	(18,43),(18,44),(17,45),(17,46),(1,47),(1,48),(16,49),
208	(5,50),(5,51),(11,52),(10,53),(7,54),(8,55),(16,56),
209	(3,57),(3,58),(17,59),(17,60),(8,61),(15,62),
210	(2,63),(2,64),(15,65),(15,66),(13,67),(14,68),(11,69),
211	(8,70),(1,71),(1,72),(5,73),(5,74),(12,75),(12,76),
212	(9,77),(16,78),(16,79),(15,80),(14,81),(3,82),(1,83);
213	
214
215insert into teach_lessons values
216	(1,1),(1,2),(2,3),(2,4),(7,5),(16,6),(16,7),
217	(9,8),(1,9),(1,10),(12,11),(12,12),(5,13),(5,14),
218	(9,15),(1,16),(1,17),(3,18),(4,19),(7,20),(9,21),
219	(14,22),(14,23),(13,24),(13,25),(11,26),(10,27),(8,28),
220	(9,29),(6,30),(6,31),(13,32),(12,33),(16,34),(16,35),
221	(12,36),(4,37),(4,38),(12,39),(12,40),(16,41),(10,42),
222	(18,43),(18,44),(17,45),(17,46),(1,47),(1,48),(16,49),
223	(5,50),(5,51),(11,52),(10,53),(7,54),(8,55),(16,56),
224	(3,57),(3,58),(17,59),(17,60),(8,61),(15,62),
225	(2,63),(2,64),(15,65),(15,66),(13,67),(14,68),(11,69),
226	(8,70),(1,71),(1,72),(5,73),(5,74),(12,75),(12,76),
227	(9,77),(16,78),(16,79),(15,80),(14,81),(3,82),(1,83);
228
229
230	
231	
232insert into class_lessons values
233	(15,1),(15,2),(15,3),(15,4),(15,5),(15,6),(15,7),
234	(15,8),(11,9),(11,10),(11,11),(11,12),(11,13),(11,14),
235	(11,15),(7,16),(7,17),(7,18),(7,19),(7,20),(7,21),
236	(15,22),(15,23),(15,24),(15,25),(15,26),(15,27),(15,28),
237	(15,29),(11,30),(11,31),(11,32),(11,33),(11,34),(11,35),
238	(11,36),(7,37),(7,38),(7,39),(7,40),(7,41),(7,42),
239	(15,43),(15,44),(15,45),(15,46),(15,47),(15,48),(15,49),
240	(11,50),(11,51),(11,52),(11,53),(11,54),(11,55),(11,56),
241	(7,57),(7,58),(7,59),(7,60),(7,61),(7,62),
242	(15,63),(15,64),(15,65),(15,66),(15,67),(15,68),(15,69),
243	(15,70),(11,71),(11,72),(11,73),(11,74),(11,75),(11,76),
244	(11,77),(7,78),(7,79),(7,80),(7,81),(7,82),(7,83);
245	
246	
247	
248insert into room_lessons values
249	(1,1),(1,2),(12,3),(12,4),(14,5),(15,6),(15,7),
250	(16,8),(2,9),(2,10),(3,11),(3,12),(4,13),(4,14),
251	(16,15),(5,16),(5,17),(6,18),(7,19),(8,20),(16,21),
252	(1,22),(1,23),(2,24),(2,25),(3,26),(4,27),(5,28),
253	(16,29),(6,30),(6,31),(7,32),(8,33),(9,34),(9,35),
254	(10,36),(11,37),(11,38),(12,39),(12,40),(13,41),(14,42),
255	(1,43),(1,44),(2,45),(2,46),(3,47),(3,48),(4,49),
256	(5,50),(5,51),(6,52),(7,53),(8,54),(9,55),(10,56),
257	(11,57),(11,58),(12,59),(12,60),(13,61),(14,62),
258	(1,63),(1,64),(2,65),(2,66),(3,67),(4,68),(5,69),
259	(6,70),(7,71),(7,72),(8,73),(8,74),(9,75),(9,76),
260	(10,77),(11,78),(11,79),(12,80),(13,81),(14,82),(15,83);		
261	
262	
263insert into lesson_time values
264	('9:00','9:45',1),
265	('10:00','10:45',2),
266	('11:00','11:45',3),
267	('12:00','12:45',4),
268	('13:30','14:15',5),
269	('14:30','15:15',6),
270	('16:00','16:45',7),
271	('17:00','17:45',8),
272	('18:15','19:00',9),
273	('19:15','20:00',10),
274