Ph2: ddl.sql

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