Du5XEzak

1CREATE TABLE IF NOT EXISTS teachers
2(
3	teacher_id NUMERIC(3) primary key,
4	teacher_io VARCHAR(40) NOT NULL,
5	teacher_surname VARCHAR(20) NOT NULL
6);
7
8CREATE TABLE IF NOT EXISTS classes
9(
10	class_id NUMERIC(3) primary key,
11	number_of_students NUMERIC(3) NOT NULL,
12	class_name VARCHAR(4) NOT NULL
13);
14
15CREATE TABLE IF NOT EXISTS teacher_profiles
16(
17	teacher_id NUMERIC(3) references teachers,
18	teacher_profile VARCHAR(20) primary key
19);
20
21CREATE TABLE IF NOT EXISTS class_profiles
22(
23	class_id NUMERIC(3) references classes,
24	class_profile VARCHAR(20) primary key
25);
26
27CREATE TABLE IF NOT EXISTS subjects
28(
29	subject_id NUMERIC(3) primary key,
30	subject_name VARCHAR(20) NOT NULL,
31	teacher_profile VARCHAR(20) references teacher_profiles,
32	class_profile VARCHAR(20) references class_profiles
33);
34
35CREATE TABLE IF NOT EXISTS schedule
36(
37	lesson_id NUMERIC(5) primary key,
38	lesson_date date NOT NULL,
39	hometask VARCHAR(30)
40);
41
42CREATE TABLE IF NOT EXISTS subj_lessons
43(
44	subject_id NUMERIC(3) references subjects,
45	lesson_id NUMERIC(5) references schedule
46);
47
48CREATE TABLE IF NOT EXISTS teach_lessons
49(
50	teacher_id NUMERIC(3) references teachers,
51	lesson_id NUMERIC(5) references schedule
52);
53
54CREATE TABLE IF NOT EXISTS class_lessons
55(
56	class_id NUMERIC(3) references classes,
57	lesson_id NUMERIC(5) references schedule
58);
59
60CREATE TABLE IF NOT EXISTS rooms
61(
62	room_number NUMERIC(5) primary key,
63	room_profile VARCHAR(20) NOT NULL,
64	max_students NUMERIC(3) NOT NULL
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
73CREATE TABLE IF NOT EXISTS lesson_time
74(
75	lesson_id NUMERIC(5) references schedule,
76	lesson_time time NOT NULL,
77	lesson_num NUMERIC(2) NOT NULL
78);
79
80CREATE TABLE IF NOT EXISTS class_teachers
81(
82	class_id NUMERIC(3) references classes,
83	Subj VARCHAR(20) NOT NULL,
84	teacher_id NUMERIC(3) references teachers
85);