eLZPhA0L

1DROP SCHEMA IF EXISTS  school CASCADE;
2CREATE SCHEMA school;
3 
4CREATE TABLE school.teacher (
5  teacher_id INT PRIMARY KEY,
6  birth_dt DATE NOT NULL,
7  teacher_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.student (
14  student_id INT PRIMARY KEY,
15  birth_dt DATE NOT NULL,
16  student_nm VARCHAR(100) NOT NULL,
17  class_no INT NOT NULL CHECK (class_no > 0),
18  phone_no VARCHAR(50) NOT NULL
19);
20 
21CREATE TABLE school.lesson (
22  lesson_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.audience (
28  audience_id INT PRIMARY KEY,
29  view VARCHAR(100) NOT NULL,
30  predestination VARCHAR(100) NOT NULL
31);
32 
33CREATE TABLE school.addition (
34  addition_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.addition_x_student (
41  student_id INT REFERENCES school.student(student_id),
42  teacher_id INT REFERENCES school.teacher(teacher_id),
43  frequency INT NOT NULL CHECK(frequency > 0)
44);
45 
46CREATE TABLE school.teachers_x_student(
47  teacher_id INT REFERENCES school.teacher(teacher_id),
48  student_id INT REFERENCES school.student(student_id)
49);
50 
51CREATE TABLE school.audience_x_addition(
52  audience_id INT REFERENCES school.audience(audience_id),
53  addition_id INT REFERENCES school.addition(addition_id),
54  frequency_x_addition INT NOT NULL, CHECK(frequency_x_addition > 0)
55);
56 
57CREATE TABLE school.audience_x_lesson (
58lesson_id INT REFERENCES school.lesson(lesson_id),
59audience_id INT REFERENCES school.audience(audience_id)
60);
61 
62CREATE TABLE school.lesson_x_student(
63teacher_id INT REFERENCES school.teacher(teacher_id),
64lesson_id INT REFERENCES school.lesson(lesson_id)
65);
66 
67 
68-----------------------------------------
69INSERT INTO school.teacher (teacher_id, birth_dt, teacher_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.student (student_id, birth_dt, student_nm, class_no, phone_no) 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.lesson (lesson_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.audience (audience_id, view, 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.addition (addition_id, class_no, lesson_nm, duration) VALUES
115  (1, 11, 'математика', 90),
116  (2, 10, 'литература', 90),
117  (3, 11,'информатика', 60);
118 
119 
120INSERT INTO school.addition_x_student (student_id, teacher_id, frequency) VALUES
121  (1, 1, 1),
122  (2, 2, 2),
123  (3, 3, 1);
124 
125INSERT INTO school.teacher_x_student(teacher_id, student_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.audience_x_addition(audience_id, addition_id, frequency_x_addition) VALUES
136  (1, 1, 90),
137  (1, 2, 90),
138  (2, 1, 90),
139  (3, 2, 60);
140 
141  INSERT INTO school.audience_x_lesson(lesson_id, audience_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.lesson_x_student(teacher_id, lesson_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.teacher;
158SELECT * FROM school.student;
159SELECT * FROM school.lesson;
160SELECT * FROM school.audience;
161SELECT * FROM school.addition_x_student;
162SELECT * FROM school.teacher_x_student;
163SELECT * FROM school.audience_x_addition;
164SELECT * FROM school.audience_x_lesson;
165SELECT * FROM school.lesson_x_student;