· 9 years ago · Oct 22, 2016, 08:04 PM
1CREATE TABLE IF NOT EXISTS public.specialty
2(
3 id SERIAL NOT NULL,
4 name text NOT NULL,
5 CONSTRAINT specialty_pkey PRIMARY KEY (id),
6 CONSTRAINT specialty_name_key UNIQUE (name)
7);
8
9CREATE TABLE IF NOT EXISTS public.subject
10(
11 id SERIAL NOT NULL,
12 name text NOT NULL,
13 CONSTRAINT subject_pkey PRIMARY KEY (id)
14);
15
16CREATE TABLE IF NOT EXISTS public.person
17(
18 id SERIAL NOT NULL,
19 name text NOT NULL,
20 surname text NOT NULL,
21 middlename text,
22 CONSTRAINT person_pkey PRIMARY KEY (id)
23);
24
25CREATE TABLE IF NOT EXISTS public.studentsgroup
26(
27 id SERIAL NOT NULL,
28 name text NOT NULL,
29 date_start date NOT NULL,
30 date_finish date NOT NULL,
31 specialty integer NOT NULL,
32 course integer NOT NULL,
33 CONSTRAINT studentsgroup_pkey PRIMARY KEY (id),
34 CONSTRAINT fk_studentsgroup__specialty FOREIGN KEY (specialty)
35 REFERENCES public.specialty (id)
36 ON UPDATE NO ACTION ON DELETE NO ACTION,
37 CONSTRAINT studentsgroup_name_key UNIQUE (name)
38);
39
40CREATE TABLE IF NOT EXISTS public.student
41(
42 id SERIAL NOT NULL,
43 person integer NOT NULL,
44 students_group integer NOT NULL,
45 CONSTRAINT student_pkey PRIMARY KEY (id),
46 CONSTRAINT fk_student__person FOREIGN KEY (person)
47 REFERENCES public.person (id)
48 ON UPDATE NO ACTION ON DELETE NO ACTION,
49 CONSTRAINT fk_student__students_group FOREIGN KEY (students_group)
50 REFERENCES public.studentsgroup (id)
51 ON UPDATE NO ACTION ON DELETE NO ACTION
52);
53
54CREATE TABLE IF NOT EXISTS public.department
55(
56 id SERIAL NOT NULL,
57 name text NOT NULL,
58 CONSTRAINT department_pkey PRIMARY KEY (id)
59);
60
61CREATE TABLE IF NOT EXISTS public.professor
62(
63 id SERIAL NOT NULL,
64 person integer NOT NULL,
65 department integer NOT NULL,
66 CONSTRAINT professor_pkey PRIMARY KEY (id),
67 CONSTRAINT fk_professor__department FOREIGN KEY (department)
68 REFERENCES public.department (id)
69 ON UPDATE NO ACTION ON DELETE NO ACTION,
70 CONSTRAINT fk_professor__person FOREIGN KEY (person)
71 REFERENCES public.person (id)
72 ON UPDATE NO ACTION ON DELETE NO ACTION
73);
74
75CREATE TABLE IF NOT EXISTS public.curriculum
76(
77 id SERIAL NOT NULL,
78 subject integer NOT NULL,
79 semester integer NOT NULL,
80 course integer NOT NULL,
81 specialty integer NOT NULL,
82 hours_amount integer NOT NULL,
83 professor_id INTEGER,
84 CONSTRAINT curriculum_pkey PRIMARY KEY (id),
85 CONSTRAINT fk_curriculum__specialty FOREIGN KEY (specialty)
86 REFERENCES public.specialty (id)
87 ON UPDATE NO ACTION ON DELETE NO ACTION,
88 CONSTRAINT fk_curriculum__subject FOREIGN KEY (subject)
89 REFERENCES public.subject (id)
90 ON UPDATE NO ACTION ON DELETE NO ACTION,
91 CONSTRAINT fk_professor__curriculum FOREIGN KEY (professor_id)
92 REFERENCES public.professor (id)
93 ON UPDATE NO ACTION ON DELETE NO ACTION
94);
95
96CREATE TABLE IF NOT EXISTS public.sheet
97(
98 id SERIAL NOT NULL,
99 student integer NOT NULL,
100 curriculum integer NOT NULL,
101 date date NOT NULL,
102 CONSTRAINT sheet_pkey PRIMARY KEY (id),
103 CONSTRAINT fk_sheet__curriculum FOREIGN KEY (curriculum)
104 REFERENCES public.curriculum (id)
105 ON UPDATE NO ACTION ON DELETE NO ACTION,
106 CONSTRAINT fk_sheet__student FOREIGN KEY (student)
107 REFERENCES public.student (id)
108 ON UPDATE NO ACTION ON DELETE NO ACTION
109);
110
111CREATE TABLE IF NOT EXISTS public.academicrecord
112(
113 id SERIAL NOT NULL,
114 sheet integer NOT NULL,
115 result boolean NOT NULL,
116 CONSTRAINT academicrecord_pkey PRIMARY KEY (id),
117 CONSTRAINT fk_academicrecord__sheet FOREIGN KEY (sheet)
118 REFERENCES public.sheet (id)
119 ON UPDATE NO ACTION ON DELETE NO ACTION
120);
121
122CREATE TABLE IF NOT EXISTS public.examinationlist
123(
124 id SERIAL NOT NULL,
125 sheet integer NOT NULL,
126 result integer NOT NULL,
127 CONSTRAINT examinationlist_pkey PRIMARY KEY (id),
128 CONSTRAINT fk_examinationlist__sheet FOREIGN KEY (sheet)
129 REFERENCES public.sheet (id)
130 ON UPDATE NO ACTION ON DELETE NO ACTION
131);
132
133CREATE TABLE IF NOT EXISTS public.professorsubject
134(
135 id SERIAL NOT NULL,
136 professor integer NOT NULL,
137 subject integer NOT NULL,
138 CONSTRAINT professorsubject_pkey PRIMARY KEY (id),
139 CONSTRAINT fk_professorsubject__professor FOREIGN KEY (professor)
140 REFERENCES public.professor (id)
141 ON UPDATE NO ACTION ON DELETE NO ACTION,
142 CONSTRAINT fk_professorsubject__subject FOREIGN KEY (subject)
143 REFERENCES public.subject (id)
144 ON UPDATE NO ACTION ON DELETE NO ACTION
145);