r9mugh2R

1create table if not exists study_week
2(
3	week_id integer not null
4		constraint "Week_pkey"
5			primary key,
6	start_date date not null,
7	end_date date not null
8);
9
10alter table study_week owner to thpvmwfwxmnadt;
11
12create table if not exists day
13(
14	day_id integer not null
15		constraint "Day_pkey"
16			primary key,
17	name varchar(15)
18);
19
20alter table day owner to thpvmwfwxmnadt;
21
22create table if not exists student
23(
24	student_id integer not null
25		constraint "Student_pkey"
26			primary key,
27	name varchar(50) not null,
28	year smallint not null
29);
30
31alter table student owner to thpvmwfwxmnadt;
32
33create table if not exists faculty
34(
35	fac_id serial not null
36		constraint faculty_pk
37			primary key,
38	name varchar(30)
39);
40
41alter table faculty owner to thpvmwfwxmnadt;
42
43create table if not exists speciality
44(
45	spec_id serial not null
46		constraint speciality_pk
47			primary key,
48	name varchar(40) not null,
49	fac_id integer not null
50		constraint fac_id
51			references faculty
52);
53
54alter table speciality owner to thpvmwfwxmnadt;
55
56create unique index if not exists speciality_spec_id_uindex
57	on speciality (spec_id);
58
59create table if not exists subject
60(
61	sub_id integer not null
62		constraint subject_pk
63			primary key,
64	name varchar(50) not null,
65	fac_id integer
66		constraint fac_id
67			references faculty
68);
69
70alter table subject owner to thpvmwfwxmnadt;
71
72create unique index if not exists subject_sub_id_uindex
73	on subject (sub_id);
74
75create table if not exists teacher
76(
77	teacher_id serial not null
78		constraint teacher_pk
79			primary key,
80	name varchar(50) not null
81);
82
83alter table teacher owner to thpvmwfwxmnadt;
84
85create unique index if not exists teacher_name_uindex
86	on teacher (name);
87
88create unique index if not exists teacher_teacher_id_uindex
89	on teacher (teacher_id);
90
91create table if not exists time
92(
93	time_number smallint not null
94		constraint time_pk
95			primary key,
96	time varchar(20) not null
97);
98
99alter table time owner to thpvmwfwxmnadt;
100
101create unique index if not exists time_time_number_uindex
102	on time (time_number);
103
104create table if not exists "group"
105(
106	group_id serial not null
107		constraint group_pk
108			primary key,
109	sub_id integer not null
110		constraint sub_id
111			references subject,
112	group_name varchar(4) not null
113);
114
115alter table "group" owner to thpvmwfwxmnadt;
116
117create unique index if not exists group_group_id_uindex
118	on "group" (group_id);
119
120create table if not exists lesson
121(
122	lesson_id serial not null
123		constraint lesson_pk
124			primary key,
125	teacher_id integer not null
126		constraint teacher_id
127			references teacher,
128	group_id integer not null
129		constraint group_id
130			references "group",
131	day_id integer not null
132		constraint day_id
133			references day,
134	week_id integer
135		constraint week_d
136			references study_week,
137	time_number smallint not null
138		constraint time_number
139			references time,
140	room varchar(10)
141);
142
143alter table lesson owner to thpvmwfwxmnadt;
144
145create unique index if not exists lesson_lesson_id_uindex
146	on lesson (lesson_id);