RelationalDesign: ddl.2.sql

File ddl.2.sql, 6.1 KB (added by 233149, 2 weeks ago)
Line 
1-- ============================================================
2-- IKnow / FINKI - DDL script (PostgreSQL)
3-- Drops and creates all tables of the database
4-- ============================================================
5
6-- ---------- DROP TABLES (reverse dependency order) ----------
7DROP TABLE IF EXISTS professour_subjects CASCADE;
8DROP TABLE IF EXISTS dependency_subject CASCADE;
9DROP TABLE IF EXISTS major_subjects CASCADE;
10DROP TABLE IF EXISTS payment CASCADE;
11DROP TABLE IF EXISTS token CASCADE;
12DROP TABLE IF EXISTS user_documents CASCADE;
13DROP TABLE IF EXISTS documents CASCADE;
14DROP TABLE IF EXISTS passed_subjects CASCADE;
15DROP TABLE IF EXISTS semesters_subjects CASCADE;
16DROP TABLE IF EXISTS enrolled_semesters CASCADE;
17DROP TABLE IF EXISTS subjects CASCADE;
18DROP TABLE IF EXISTS active_semesters CASCADE;
19DROP TABLE IF EXISTS major CASCADE;
20DROP TABLE IF EXISTS contact CASCADE;
21DROP TABLE IF EXISTS high_school CASCADE;
22DROP TABLE IF EXISTS users CASCADE;
23
24-- ---------- DROP ENUM TYPES ----------
25DROP TYPE IF EXISTS user_role CASCADE;
26DROP TYPE IF EXISTS hs_type CASCADE;
27DROP TYPE IF EXISTS quota_type CASCADE;
28DROP TYPE IF EXISTS semester_type CASCADE;
29DROP TYPE IF EXISTS grade_type CASCADE;
30
31-- ---------- ENUM TYPES ----------
32CREATE TYPE user_role AS ENUM ('student', 'prof', 'admin');
33CREATE TYPE hs_type AS ENUM ('strucen', 'gimnazija');
34CREATE TYPE quota_type AS ENUM ('privatna', 'drzavna');
35CREATE TYPE semester_type AS ENUM ('summer', 'winter');
36CREATE TYPE grade_type AS ENUM ('6', '7', '8', '9', '10');
37
38-- ============================================================
39-- TABLES
40-- ============================================================
41
42CREATE TABLE users (
43 id INTEGER PRIMARY KEY,
44 name VARCHAR(100) NOT NULL,
45 embg VARCHAR(13),
46 surname VARCHAR(100) NOT NULL,
47 "index" VARCHAR(20),
48 bday TIMESTAMP,
49 email VARCHAR(150) NOT NULL UNIQUE,
50 password VARCHAR(255) NOT NULL,
51 role user_role NOT NULL,
52 quota quota_type,
53 enrollment_year INTEGER,
54 created_at TIMESTAMP NOT NULL DEFAULT now()
55);
56
57CREATE TABLE high_school (
58 id INTEGER PRIMARY KEY,
59 user_id INTEGER NOT NULL UNIQUE REFERENCES users (id),
60 gpa REAL NOT NULL,
61 type hs_type NOT NULL
62);
63
64CREATE TABLE contact (
65 id INTEGER PRIMARY KEY,
66 user_id INTEGER NOT NULL UNIQUE REFERENCES users (id),
67 city VARCHAR(100) NOT NULL,
68 municipality VARCHAR(100) NOT NULL,
69 address VARCHAR(150) NOT NULL,
70 number VARCHAR(20) NOT NULL,
71 microsoft_email VARCHAR(150)
72);
73
74CREATE TABLE major (
75 id INTEGER PRIMARY KEY,
76 name VARCHAR(150) NOT NULL
77);
78
79CREATE TABLE active_semesters (
80 id INTEGER PRIMARY KEY,
81 year INTEGER NOT NULL,
82 type semester_type NOT NULL,
83 UNIQUE (year, type)
84);
85
86CREATE TABLE subjects (
87 id INTEGER PRIMARY KEY,
88 name VARCHAR(150) NOT NULL UNIQUE,
89 code VARCHAR(30) NOT NULL UNIQUE,
90 awarded_credits INTEGER NOT NULL,
91 dependency_credit INTEGER
92);
93
94CREATE TABLE enrolled_semesters (
95 id INTEGER PRIMARY KEY,
96 user_id INTEGER NOT NULL REFERENCES users (id),
97 quota quota_type NOT NULL,
98 major_id INTEGER NOT NULL REFERENCES major (id),
99 note VARCHAR(255),
100 student_comment VARCHAR(255),
101 created_at TIMESTAMP NOT NULL DEFAULT now(),
102 last_change TIMESTAMP,
103 completed TIMESTAMP,
104 semester_id INTEGER NOT NULL REFERENCES active_semesters (id),
105 UNIQUE (user_id, semester_id)
106);
107
108CREATE TABLE semesters_subjects (
109 id INTEGER PRIMARY KEY,
110 enrolled_semesters_id INTEGER NOT NULL REFERENCES enrolled_semesters (id),
111 subjects_id INTEGER NOT NULL REFERENCES subjects (id),
112 professor_id INTEGER NOT NULL REFERENCES users (id),
113 signature BOOLEAN NOT NULL DEFAULT FALSE,
114 UNIQUE (enrolled_semesters_id, subjects_id)
115);
116
117CREATE TABLE passed_subjects (
118 id INTEGER PRIMARY KEY,
119 enrolled_id INTEGER NOT NULL UNIQUE REFERENCES semesters_subjects (id),
120 grade grade_type NOT NULL,
121 date_passed TIMESTAMP NOT NULL
122);
123
124CREATE TABLE documents (
125 id INTEGER PRIMARY KEY,
126 type VARCHAR(100) NOT NULL,
127 body TEXT NOT NULL,
128 cost INTEGER NOT NULL
129);
130
131CREATE TABLE user_documents (
132 user_id INTEGER NOT NULL REFERENCES users (id),
133 document_id INTEGER NOT NULL REFERENCES documents (id),
134 PRIMARY KEY (user_id, document_id)
135);
136
137CREATE TABLE token (
138 id INTEGER PRIMARY KEY,
139 user_id INTEGER NOT NULL REFERENCES users (id),
140 token VARCHAR(255) NOT NULL,
141 expires_at TIMESTAMP NOT NULL,
142 is_valid BOOLEAN NOT NULL DEFAULT TRUE
143);
144
145CREATE TABLE payment (
146 id INTEGER PRIMARY KEY,
147 user_id INTEGER NOT NULL REFERENCES users (id),
148 enrollment_id INTEGER NOT NULL REFERENCES enrolled_semesters (id),
149 amount INTEGER NOT NULL
150);
151
152CREATE TABLE major_subjects (
153 major_id INTEGER NOT NULL REFERENCES major (id),
154 subject_id INTEGER NOT NULL REFERENCES subjects (id),
155 manditory_semester INTEGER NOT NULL,
156 PRIMARY KEY (major_id, subject_id)
157);
158
159CREATE TABLE dependency_subject (
160 subject_id INTEGER NOT NULL REFERENCES subjects (id),
161 dependency_id INTEGER NOT NULL REFERENCES subjects (id),
162 PRIMARY KEY (subject_id, dependency_id),
163 CHECK (subject_id <> dependency_id)
164);
165
166CREATE TABLE professour_subjects (
167 prof_id INTEGER NOT NULL REFERENCES users (id),
168 active_semester_id INTEGER NOT NULL REFERENCES active_semesters (id),
169 subject_id INTEGER NOT NULL REFERENCES subjects (id),
170 PRIMARY KEY (prof_id, active_semester_id, subject_id)
171);