7UM1eusc

1DROP SCHEMA IF EXISTS  school CASCADE;
2CREATE SCHEMA school;
3
4CREATE TABLE school.teachers (
5  teachers_id INT PRIMARY KEY,
6  birth_dt DATE NOT NULL,
7  name_nm VARCHAR(100) NOT NULL,
8  phone_no VARCHAR(50) NOT NULL,
9  category VARCHAR(20) NOT NULL,
10  lesson_nm VARCHAR(20) NOT NULL
11);
12
13CREATE TABLE school.students (
14  students_id INT PRIMARY KEY,
15  birth_dt DATE NOT NULL,
16  name_nm VARCHAR(100) NOT NULL,
17  class_no INT NOT NULL CHECK (class_no > 0),
18  phone_number VARCHAR(50) NOT NULL
19);
20
21CREATE TABLE school.lessons (
22  lessons_id INT PRIMARY KEY,
23  class_no  INT NOT NULL CHECK(class_no > 0),
24  lesson_nm VARCHAR(100) NOT NULL
25);
26
27CREATE TABLE school.audiences (
28  audiences_id INT PRIMARY KEY,
29  type VARCHAR(100) NOT NULL,
30  predestination VARCHAR(100) NOT NULL
31);
32
33CREATE TABLE school.additional (
34  additional_id INT PRIMARY KEY,
35  lesson_nm VARCHAR(100) NOT NULL,
36  class_no  INT NOT NULL CHECK(class_no > 0),
37  duration INT NOT NULL CHECK(duration > 0)
38);
39
40CREATE TABLE school.additional_students (
41  students_id INT REFERENCES school.students(students_id),
42  teachers_id INT REFERENCES school.teachers(teachers_id),
43  frequency INT NOT NULL CHECK(frequency > 0)
44);
45
46CREATE TABLE school.teachers_students(
47  teachers_id INT REFERENCES school.teachers(teachers_id),
48  students_id INT REFERENCES school.students(students_id)
49);
50
51CREATE TABLE school.audiences_additionals(
52  audiences_id INT REFERENCES school.audiences(audiences_id),
53  additional_id INT REFERENCES school.additional(additional_id),
54  frequency_additional INT NOT NULL, CHECK(frequency_additional > 0)
55);
56
57CREATE TABLE school.audiences_lessons (
58lessons_id INT REFERENCES school.lessons(lessons_id),
59audiences_id INT REFERENCES school.audiences(audiences_id)
60);
61
62CREATE TABLE school.lessons_students(
63teachers_id INT REFERENCES school.teachers(teachers_id),
64lessons_id INT REFERENCES school.lessons(lessons_id)
65);
66
67
68-----------------------------------------
69INSERT INTO school.teachers (teachers_id, birth_dt, name_nm, phone_no, category, lesson_nm) VALUES
70  (1, '1978-02-25','Иванова Лариса Ивановна', '8-919-670-36-53', 1, 'математика'),
71  (2, '1966-02-02', 'Никаноров Василий Иванович', '8-916-348-33-80', 2, 'физика'),
72  (3, '1980-01-23', 'Васильев Николай Георгиевич', '8-916-456-78-23', 1, 'биология'),
73  (4, '1969-12-12', 'Горбунова Мария Алексеевна','8-919-345-34-54', 1, 'литература' ),
74  (5, '1978-05-13', 'Петухова Надежда Викторовна','8-919-654-26-31', 1, 'география'),
75  (6, '1982-11-27', 'Литвинова Любовь Васильевна','8-923-563-32-12', 1, 'русский язык'),
76  (7, '1983-04-16', 'Николаев Виктор Геннадьевич', '8-917-456-23-23', 1, 'история'),
77  (8, '1980-07-13', 'Пяскорская Наталия Вадимовна', '8-947-789-32-32', 2, 'литература'),
78  (9, '1978-11-16', 'Носова Надежда Игоревна','8-967-345-24-44', 2,'русский язык'),
79  (10, '1978-09-19', 'Иванов Петр Васильевич', '8-934-234-33-56', 2,'информатика');
80
81INSERT INTO school.students (students_id, birth_dt, name_nm, class_no, phone_number) VALUES
82  (1, '2000-12-12','Михров Василий Георгиевич', 11, '8-916-345-72-22'),
83  (2, '2000-09-08', ' Свотина Наталия Петровна', 11, '8-919-539-25-19'),
84  (3, '2002-08-23',  'Арифова Анастасия Николаевна', 9, '8-956-673-71-89'),
85  (4, '2001-05-18', 'Чикунова Мария Викторовна', 10, '8-922-546-72-77'),
86  (5, '2001-05-18', 'Чикунов Илья Викторович', 10, '8-928-123-65-11'),
87  (6, '2001-06-06', 'Петров Григорий Иванович', 10, '8-917-678-25-43'),
88  (7, '2000-12-07', 'Дурова Любовь Михайловна', 11, '8-946-249-63-91' ),
89  (8, '2000-10-11', 'Петрова Мария Ивановна', 11, '8-915-361-67-91'),
90  (9, '2000-08-17', 'Николаева Анжелика Леонидовна', 11, '8-910-401-62-42'),
91  (10, '2001-10-19', 'Терентьев Юрий Михайлович', 10, '8-917-462-55-31'),
92  (11, '2000-12-15', 'Григорьева Елена Александровна', 11, '8-963-646-33-99'),
93  (12, '2000-08-20', 'Васильев Иван Петрович', 11, '8-935-369-63-22');
94
95INSERT INTO school.lessons (lessons_id, class_no,  lesson_nm) VALUES
96  (1, 11, 'математика'),
97  (2, 10, 'русский язык'),
98  (3, 11, 'литература'),
99  (4, 11, 'история'),
100  (5, 11, 'биология');
101
102INSERT INTO school.audiences (audiences_id, type, predestination) VALUES
103  (1, 'класс', 'занятия'),
104  (2, 'класс', 'занятия'),
105  (3, 'актовый зал', 'мероприятия'),
106  (4, 'столовая', 'прием пищи'),
107  (5, 'класс', ' занятия'),
108  (6, 'класс','занятия'),
109  (7, 'спортивный зал', 'занятие спортом'),
110  (8, 'кладовая', 'хранение шкоьного инвентаря'),
111  (9, 'класс','занятия'),
112  (10,'учительска', 'проведение собраний');
113
114INSERT INTO school.additional (additional_id, class_no, lesson_nm, duration) VALUES
115  (1, 11, 'математика', 90),
116  (2, 10, 'литература', 90),
117  (3, 11,'информатика', 60);
118
119
120INSERT INTO school.additional_students (students_id, teachers_id, frequency) VALUES
121  (1, 1, 1),
122  (2, 2, 2),
123  (3, 3, 1);
124
125INSERT INTO school.teachers_students(teachers_id, students_id) VALUES
126  (1, 2),
127  (1, 1),
128  (1, 3),
129  (1, 4),
130  (1, 5),
131  (2, 1),
132  (2, 2),
133  (2, 5);
134
135  INSERT INTO school.audiences_additionals(audiences_id, additional_id, frequency_additional) VALUES
136  (1, 1, 90),
137  (1, 2, 90),
138  (2, 1, 90),
139  (3, 2, 60);
140
141  INSERT INTO school.audiences_lessons(lessons_id, audiences_id) VALUES
142  (1, 2),
143  (1, 1),
144  (1, 3),
145  (2, 1),
146  (2, 2),
147  (2, 3);
148
149  INSERT INTO school.lessons_students(teachers_id, lessons_id) VALUES
150    (1, 1),
151    (1, 2),
152    (2, 1),
153    (2, 2),
154    (2, 3),
155    (3, 1),
156    (3, 3);
157SELECT * FROM school.teachers;
158SELECT * FROM school.students;
159SELECT * FROM school.lessons;
160SELECT * FROM school.audiences;
161SELECT * FROM school.additional_students;
162SELECT * FROM school.teachers_students;
163SELECT * FROM school.audiences_additionals;
164SELECT * FROM school.audiences_lessons;
165SELECT * FROM school.lessons_students;