DatabaseCreation: DDL.sql

File DDL.sql, 12.8 KB (added by 231076, 5 days ago)
Line 
1CREATE TYPE user_status AS ENUM ('active', 'inactive', 'blocked');
2CREATE TYPE membership_status AS ENUM ('active', 'inactive', 'suspended', 'expired');
3CREATE TYPE copy_status AS ENUM ('available', 'reserved', 'borrowed', 'lost', 'damaged');
4CREATE TYPE payment_method AS ENUM ('cash', 'card', 'online');
5CREATE TYPE payment_status AS ENUM ('pending', 'completed', 'failed', 'refunded');
6CREATE TYPE reservation_status AS ENUM ('pending', 'active', 'cancelled', 'expired');
7CREATE TYPE loan_status AS ENUM ('borrowed', 'returned', 'overdue', 'lost');
8CREATE TYPE fine_status AS ENUM ('unpaid', 'paid', 'waived', 'overdue');
9CREATE TYPE notification_status AS ENUM ('pending', 'sent', 'failed', 'read');
10CREATE TYPE notification_type AS ENUM ('payment', 'fine', 'reservation', 'event', 'membership', 'general');
11CREATE TYPE event_reservation_status AS ENUM ('reserved', 'cancelled', 'attended');
12CREATE TYPE plan_type AS ENUM ('monthly', 'semiannual', 'annual');
13
14
15CREATE TABLE app_user (
16 user_id SERIAL PRIMARY KEY,
17 username VARCHAR(20) NOT NULL UNIQUE,
18 password VARCHAR(20) NOT NULL,
19 email VARCHAR(30) NOT NULL UNIQUE,
20 first_name VARCHAR(20) NOT NULL,
21 last_name VARCHAR(50) NOT NULL,
22 phone VARCHAR(15),
23 status user_status NOT NULL DEFAULT 'active',
24 address VARCHAR(50),
25 street VARCHAR(50),
26 city VARCHAR(20),
27 country VARCHAR(50),
28 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
29 last_login TIMESTAMP,
30 CONSTRAINT chk_app_user_email CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
31 CONSTRAINT chk_app_user_phone CHECK (phone ~ '^\+389[0-9]{8}$')
32);
33
34CREATE TABLE admin (
35 user_id INT PRIMARY KEY,
36 FOREIGN KEY (user_id) REFERENCES app_user(user_id)
37 ON DELETE CASCADE
38 ON UPDATE CASCADE
39);
40
41CREATE TABLE member (
42 user_id INT PRIMARY KEY,
43 member_number INT NOT NULL UNIQUE,
44 join_date DATE NOT NULL DEFAULT CURRENT_DATE,
45 status membership_status NOT NULL DEFAULT 'inactive',
46 FOREIGN KEY (user_id) REFERENCES app_user(user_id)
47 ON DELETE CASCADE
48 ON UPDATE CASCADE
49);
50
51CREATE TABLE librarian (
52 user_id INT PRIMARY KEY,
53 employee_code INT NOT NULL UNIQUE,
54 hire_date DATE NOT NULL,
55 FOREIGN KEY (user_id) REFERENCES app_user(user_id)
56 ON DELETE CASCADE
57 ON UPDATE CASCADE
58);
59
60CREATE TABLE publisher (
61 publisher_id SERIAL PRIMARY KEY,
62 name VARCHAR(30) NOT NULL UNIQUE,
63 city VARCHAR(30),
64 country VARCHAR(50)
65);
66
67CREATE TABLE category (
68 category_id SERIAL PRIMARY KEY,
69 name VARCHAR(30) NOT NULL UNIQUE,
70 description TEXT,
71 parent_category_id INT,
72 FOREIGN KEY (parent_category_id) REFERENCES category(category_id)
73 ON DELETE SET NULL
74 ON UPDATE CASCADE
75);
76
77CREATE TABLE genre (
78 genre_id SERIAL PRIMARY KEY,
79 name VARCHAR(30) NOT NULL UNIQUE,
80 description TEXT
81);
82
83CREATE TABLE author (
84 author_id SERIAL PRIMARY KEY,
85 first_name VARCHAR(50) NOT NULL,
86 last_name VARCHAR(50) NOT NULL
87);
88
89CREATE TABLE book (
90 barcode VARCHAR(13) PRIMARY KEY,
91 title VARCHAR(50) NOT NULL,
92 publication_year INT,
93 language VARCHAR(20),
94 pages INT,
95 description TEXT,
96 publisher_id INT,
97 shelf_location VARCHAR(10),
98 FOREIGN KEY (publisher_id) REFERENCES publisher(publisher_id)
99 ON DELETE SET NULL
100 ON UPDATE CASCADE,
101 CHECK (barcode ~ '^[0-9]{13}$')
102);
103
104CREATE TABLE book_copy (
105 copy_id SERIAL PRIMARY KEY,
106 barcode VARCHAR(13) NOT NULL,
107 status copy_status NOT NULL DEFAULT 'available',
108 FOREIGN KEY (barcode) REFERENCES book(barcode)
109 ON DELETE RESTRICT
110 ON UPDATE CASCADE
111);
112
113CREATE TABLE book_author (
114 barcode VARCHAR(13) NOT NULL,
115 author_id INT NOT NULL,
116 PRIMARY KEY (barcode, author_id),
117 FOREIGN KEY (barcode) REFERENCES book(barcode)
118 ON DELETE CASCADE
119 ON UPDATE CASCADE,
120 FOREIGN KEY (author_id) REFERENCES author(author_id)
121 ON DELETE CASCADE
122 ON UPDATE CASCADE
123);
124
125CREATE TABLE book_genre (
126 barcode VARCHAR(13) NOT NULL,
127 genre_id INT NOT NULL,
128 PRIMARY KEY (barcode, genre_id),
129 FOREIGN KEY (barcode) REFERENCES book(barcode)
130 ON DELETE CASCADE
131 ON UPDATE CASCADE,
132 FOREIGN KEY (genre_id) REFERENCES genre(genre_id)
133 ON DELETE CASCADE
134 ON UPDATE CASCADE
135);
136
137CREATE TABLE category_book (
138 category_id INT NOT NULL,
139 barcode VARCHAR(13) NOT NULL,
140 PRIMARY KEY (category_id, barcode),
141 FOREIGN KEY (category_id) REFERENCES category(category_id)
142 ON DELETE CASCADE
143 ON UPDATE CASCADE,
144 FOREIGN KEY (barcode) REFERENCES book(barcode)
145 ON DELETE CASCADE
146 ON UPDATE CASCADE
147);
148
149CREATE TABLE membership_plan (
150 plan_id SERIAL PRIMARY KEY,
151 plan_name plan_type NOT NULL,
152 duration_days INT NOT NULL,
153 price INT NOT NULL,
154 CONSTRAINT chk_duration_match CHECK (
155 (plan_name = 'monthly' AND duration_days = 30) OR
156 (plan_name = 'semiannual' AND duration_days = 180) OR
157 (plan_name = 'annual' AND duration_days = 365)
158 ),
159
160 CONSTRAINT chk_price_match CHECK (
161 (plan_name = 'monthly' AND price = 300) OR
162 (plan_name = 'semiannual' AND price = 1500) OR
163 (plan_name = 'annual' AND price = 2500)
164 )
165);
166
167CREATE TABLE membership (
168 membership_id SERIAL PRIMARY KEY,
169 member_user_id INT NOT NULL,
170 plan_id INT NOT NULL,
171 start_date DATE NOT NULL,
172 end_date DATE NOT NULL,
173 status membership_status NOT NULL DEFAULT 'active',
174 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
175 ON DELETE SET DEFAULT --tuka treba da napraveme INSERT za DEFAULT vo tabelata
176 ON UPDATE CASCADE,
177 FOREIGN KEY (plan_id) REFERENCES membership_plan(plan_id)
178 ON DELETE RESTRICT
179 ON UPDATE CASCADE,
180 CHECK (end_date >= start_date)
181);
182
183CREATE TABLE payment (
184 payment_id SERIAL PRIMARY KEY,
185 amount INT NOT NULL,
186 payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
187 method payment_method NOT NULL,
188 status payment_status NOT NULL DEFAULT 'pending',
189 membership_id INT NOT NULL,
190 member_user_id INT NOT NULL,
191 FOREIGN KEY (membership_id) REFERENCES membership(membership_id)
192 ON DELETE RESTRICT
193 ON UPDATE CASCADE,
194 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
195 ON DELETE RESTRICT
196 ON UPDATE CASCADE
197);
198
199CREATE TABLE event (
200 event_id SERIAL PRIMARY KEY,
201 title VARCHAR(50) NOT NULL,
202 description TEXT,
203 event_date DATE NOT NULL,
204 start_time TIME NOT NULL,
205 end_time TIME NOT NULL,
206 location VARCHAR(30) NOT NULL,
207 max_seats INT NOT NULL,
208 available_seats INT NOT NULL,
209
210 created_by INT,
211
212 CHECK (end_time > start_time),
213 CHECK (max_seats > 0 AND available_seats >= 0 AND available_seats <= max_seats),
214
215 FOREIGN KEY (created_by) REFERENCES librarian(user_id)
216 ON DELETE SET NULL
217 ON UPDATE CASCADE
218);
219
220CREATE TABLE event_book (
221 event_id INT NOT NULL,
222 barcode VARCHAR(13) NOT NULL,
223 PRIMARY KEY (event_id, barcode),
224 FOREIGN KEY (event_id) REFERENCES event(event_id)
225 ON DELETE CASCADE
226 ON UPDATE CASCADE,
227 FOREIGN KEY (barcode) REFERENCES book(barcode)
228 ON DELETE CASCADE
229 ON UPDATE CASCADE
230);
231
232CREATE TABLE event_reservation (
233 event_reservation_id SERIAL PRIMARY KEY,
234 event_id INT NOT NULL,
235 member_user_id INT NOT NULL,
236 reservation_date DATE NOT NULL DEFAULT CURRENT_DATE,
237 seats_reserved INT NOT NULL DEFAULT 1,
238 status event_reservation_status NOT NULL DEFAULT 'reserved',
239 FOREIGN KEY (event_id) REFERENCES event(event_id)
240 ON DELETE CASCADE
241 ON UPDATE CASCADE,
242 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
243 ON DELETE CASCADE
244);
245
246CREATE TABLE reservation (
247 reservation_id SERIAL PRIMARY KEY,
248 member_user_id INT NOT NULL,
249 barcode VARCHAR(13) NOT NULL,
250 reservation_date DATE NOT NULL DEFAULT CURRENT_DATE,
251 reservation_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
252 expiration_date DATE,
253 notified_at TIMESTAMP,
254 status reservation_status NOT NULL DEFAULT 'pending',
255 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
256 ON DELETE CASCADE
257 ON UPDATE CASCADE,
258 FOREIGN KEY (barcode) REFERENCES book(barcode)
259 ON DELETE RESTRICT
260 ON UPDATE CASCADE,
261 CHECK (expiration_date IS NULL OR expiration_date >= reservation_date)
262);
263
264CREATE TABLE loan_history (
265 loan_id SERIAL PRIMARY KEY,
266 copy_id INT NOT NULL,
267 librarian_user_id INT NOT NULL,
268 member_user_id INT NOT NULL,
269 reservation_id INT,
270 borrow_date DATE NOT NULL,
271 due_date DATE NOT NULL,
272 return_date DATE,
273 status loan_status NOT NULL DEFAULT 'borrowed',
274 FOREIGN KEY (copy_id) REFERENCES book_copy(copy_id)
275 ON DELETE RESTRICT
276 ON UPDATE CASCADE,
277 FOREIGN KEY (librarian_user_id) REFERENCES librarian(user_id)
278 ON DELETE RESTRICT
279 ON UPDATE CASCADE,
280 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
281 ON DELETE RESTRICT
282 ON UPDATE CASCADE,
283 FOREIGN KEY (reservation_id) REFERENCES reservation(reservation_id)
284 ON DELETE SET NULL
285 ON UPDATE CASCADE,
286 CHECK (
287 due_date >= borrow_date AND
288 (return_date IS NULL OR return_date >= borrow_date)
289 )
290);
291
292CREATE TABLE fines (
293 fine_id SERIAL PRIMARY KEY,
294 loan_id INT NOT NULL,
295 amount INT NOT NULL,
296 payment_due_date DATE NOT NULL,
297 status fine_status NOT NULL DEFAULT 'unpaid',
298 FOREIGN KEY (loan_id) REFERENCES loan_history(loan_id)
299 ON DELETE RESTRICT
300 ON UPDATE CASCADE
301);
302
303CREATE TABLE book_view_log (
304 view_id SERIAL PRIMARY KEY,
305 view_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
306 barcode VARCHAR(13),
307 member_user_id INT,
308 FOREIGN KEY (barcode) REFERENCES book(barcode)
309 ON DELETE SET NULL
310 ON UPDATE CASCADE,
311 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
312 ON DELETE SET NULL
313 ON UPDATE CASCADE
314);
315
316CREATE TABLE search_log (
317 search_id SERIAL PRIMARY KEY,
318 query_text TEXT NOT NULL,
319 search_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
320 member_user_id INT,
321 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
322 ON DELETE SET NULL
323 ON UPDATE CASCADE
324);
325
326CREATE TABLE author_log (
327 authorlog_id SERIAL PRIMARY KEY,
328 member_user_id INT,
329 query_text TEXT NOT NULL,
330 log_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
331 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
332 ON DELETE SET NULL
333 ON UPDATE CASCADE
334);
335
336CREATE TABLE authorlog_author (
337 authorlog_id INT NOT NULL,
338 author_id INT NOT NULL,
339 PRIMARY KEY (authorlog_id, author_id),
340 FOREIGN KEY (authorlog_id) REFERENCES author_log(authorlog_id)
341 ON DELETE SET NULL
342 ON UPDATE CASCADE,
343 FOREIGN KEY (author_id) REFERENCES author(author_id)
344 ON DELETE SET NULL
345 ON UPDATE CASCADE
346);
347
348CREATE TABLE genre_log (
349 genrelog_id SERIAL PRIMARY KEY,
350 member_user_id INT,
351 query_text TEXT NOT NULL,
352 log_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
353 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
354 ON DELETE SET NULL
355 ON UPDATE CASCADE
356);
357
358CREATE TABLE genrelog_genre (
359 genrelog_id INT NOT NULL,
360 genre_id INT NOT NULL,
361 PRIMARY KEY (genrelog_id, genre_id),
362 FOREIGN KEY (genrelog_id) REFERENCES genre_log(genrelog_id)
363 ON DELETE SET NULL
364 ON UPDATE CASCADE,
365 FOREIGN KEY (genre_id) REFERENCES genre(genre_id)
366 ON DELETE SET NULL
367 ON UPDATE CASCADE
368);
369
370CREATE TABLE notification (
371 notification_id SERIAL PRIMARY KEY,
372 member_user_id INT NOT NULL,
373 payment_id INT,
374 notification_type notification_type NOT NULL,
375 title VARCHAR(50) NOT NULL,
376 message TEXT NOT NULL,
377 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
378 sent_at TIMESTAMP,
379 status notification_status NOT NULL DEFAULT 'pending',
380 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
381 ON DELETE CASCADE
382 ON UPDATE CASCADE,
383 FOREIGN KEY (payment_id) REFERENCES payment(payment_id)
384 ON DELETE SET NULL
385 ON UPDATE CASCADE,
386 CHECK (sent_at IS NULL OR sent_at >= created_at)
387);
388
389CREATE TABLE fines_notification (
390 fine_id INT NOT NULL,
391 notification_id INT NOT NULL,
392 PRIMARY KEY (fine_id, notification_id),
393 FOREIGN KEY (fine_id) REFERENCES fines(fine_id)
394 ON DELETE CASCADE
395 ON UPDATE CASCADE,
396 FOREIGN KEY (notification_id) REFERENCES notification(notification_id)
397 ON DELETE CASCADE
398 ON UPDATE CASCADE
399);