DatabaseCreation: ddl_sreden.txt

File ddl_sreden.txt, 12.8 KB (added by 231076, 37 hours 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 CASCADE
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 CASCADE
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
199
200CREATE TABLE event (
201 event_id serial4 NOT NULL,
202 title varchar(50) NOT NULL,
203 description text NULL,
204 event_date date NOT NULL,
205 start_time time NOT NULL,
206 end_time time NOT NULL,
207 "location" varchar(30) NOT NULL,
208 max_seats int4 NOT NULL,
209 available_seats int4 NOT NULL,
210 created_by int4 NULL,
211 CONSTRAINT event_check CHECK ((end_time > start_time)),
212 CONSTRAINT event_check1 CHECK (((max_seats > 0) AND (available_seats >= 0) AND (available_seats <= max_seats))),
213 CONSTRAINT event_pkey PRIMARY KEY (event_id),
214 CONSTRAINT event_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.librarian(user_id) ON DELETE SET NULL ON UPDATE CASCADE
215);
216
217CREATE TABLE event_book (
218 event_id INT NOT NULL,
219 barcode VARCHAR(13) NOT NULL,
220 PRIMARY KEY (event_id, barcode),
221 FOREIGN KEY (event_id) REFERENCES event(event_id)
222 ON DELETE CASCADE
223 ON UPDATE CASCADE,
224 FOREIGN KEY (barcode) REFERENCES book(barcode)
225 ON DELETE CASCADE
226 ON UPDATE CASCADE
227);
228
229CREATE TABLE event_reservation (
230 event_reservation_id SERIAL PRIMARY KEY,
231 event_id INT NOT NULL,
232 member_user_id INT NOT NULL,
233 reservation_date DATE NOT NULL DEFAULT CURRENT_DATE,
234 seats_reserved INT NOT NULL DEFAULT 1,
235 status event_reservation_status NOT NULL DEFAULT 'reserved',
236 FOREIGN KEY (event_id) REFERENCES event(event_id)
237 ON DELETE CASCADE
238 ON UPDATE CASCADE,
239 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
240 ON DELETE CASCADE
241);
242
243CREATE TABLE reservation (
244 reservation_id SERIAL PRIMARY KEY,
245 member_user_id INT NOT NULL,
246 barcode VARCHAR(13) NOT NULL,
247 reservation_date DATE NOT NULL DEFAULT CURRENT_DATE,
248 reservation_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
249 expiration_date DATE,
250 notified_at TIMESTAMP,
251 status reservation_status NOT NULL DEFAULT 'pending',
252 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
253 ON DELETE CASCADE
254 ON UPDATE CASCADE,
255 FOREIGN KEY (barcode) REFERENCES book(barcode)
256 ON DELETE CASCADE
257 ON UPDATE CASCADE,
258 CHECK (expiration_date IS NULL OR expiration_date >= reservation_date)
259);
260
261CREATE TABLE loan_history (
262 loan_id SERIAL PRIMARY KEY,
263 copy_id INT NOT NULL,
264 librarian_user_id INT NOT NULL,
265 member_user_id INT NOT NULL,
266 reservation_id INT,
267 borrow_date DATE NOT NULL,
268 due_date DATE NOT NULL,
269 return_date DATE,
270 status loan_status NOT NULL DEFAULT 'borrowed',
271 FOREIGN KEY (copy_id) REFERENCES book_copy(copy_id)
272 ON DELETE RESTRICT
273 ON UPDATE CASCADE,
274 FOREIGN KEY (librarian_user_id) REFERENCES librarian(user_id)
275 ON DELETE RESTRICT
276 ON UPDATE CASCADE,
277 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
278 ON DELETE RESTRICT
279 ON UPDATE CASCADE,
280 FOREIGN KEY (reservation_id) REFERENCES reservation(reservation_id)
281 ON DELETE SET NULL
282 ON UPDATE CASCADE,
283 CHECK (
284 due_date >= borrow_date AND
285 (return_date IS NULL OR return_date >= borrow_date)
286 )
287);
288
289CREATE TABLE fines (
290 fine_id SERIAL PRIMARY KEY,
291 loan_id INT NOT NULL,
292 amount INT NOT NULL,
293 payment_due_date DATE NOT NULL,
294 status fine_status NOT NULL DEFAULT 'unpaid',
295 FOREIGN KEY (loan_id) REFERENCES loan_history(loan_id)
296 ON DELETE RESTRICT
297 ON UPDATE CASCADE
298);
299
300CREATE TABLE book_view_log (
301 view_id SERIAL PRIMARY KEY,
302 view_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
303 barcode VARCHAR(13),
304 member_user_id INT,
305 FOREIGN KEY (barcode) REFERENCES book(barcode)
306 ON DELETE SET NULL
307 ON UPDATE CASCADE,
308 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
309 ON DELETE SET NULL
310 ON UPDATE CASCADE
311);
312
313CREATE TABLE search_log (
314 search_id SERIAL PRIMARY KEY,
315 query_text TEXT NOT NULL,
316 search_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
317 member_user_id INT,
318 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
319 ON DELETE SET NULL
320 ON UPDATE CASCADE
321);
322
323CREATE TABLE author_log (
324 authorlog_id SERIAL PRIMARY KEY,
325 member_user_id INT,
326 query_text TEXT NOT NULL,
327 log_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
328 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
329 ON DELETE SET NULL
330 ON UPDATE CASCADE
331);
332
333CREATE TABLE authorlog_author (
334 authorlog_id INT NOT NULL,
335 author_id INT NOT NULL,
336 PRIMARY KEY (authorlog_id, author_id),
337 FOREIGN KEY (authorlog_id) REFERENCES author_log(authorlog_id)
338 ON DELETE CASCADE
339 ON UPDATE CASCADE,
340 FOREIGN KEY (author_id) REFERENCES author(author_id)
341 ON DELETE CASCADE
342 ON UPDATE CASCADE
343);
344
345CREATE TABLE genre_log (
346 genrelog_id SERIAL PRIMARY KEY,
347 member_user_id INT,
348 query_text TEXT NOT NULL,
349 log_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
350 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
351 ON DELETE SET NULL
352 ON UPDATE CASCADE
353);
354
355CREATE TABLE genrelog_genre (
356 genrelog_id INT NOT NULL,
357 genre_id INT NOT NULL,
358 PRIMARY KEY (genrelog_id, genre_id),
359 FOREIGN KEY (genrelog_id) REFERENCES genre_log(genrelog_id)
360 ON DELETE CASCADE
361 ON UPDATE CASCADE,
362 FOREIGN KEY (genre_id) REFERENCES genre(genre_id)
363 ON DELETE CASCADE
364 ON UPDATE CASCADE
365);
366
367CREATE TABLE notification (
368 notification_id SERIAL PRIMARY KEY,
369 member_user_id INT NOT NULL,
370 payment_id INT,
371 notification_type notification_type NOT NULL,
372 title VARCHAR(50) NOT NULL,
373 message TEXT NOT NULL,
374 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
375 sent_at TIMESTAMP,
376 status notification_status NOT NULL DEFAULT 'pending',
377 FOREIGN KEY (member_user_id) REFERENCES member(user_id)
378 ON DELETE CASCADE
379 ON UPDATE CASCADE,
380 FOREIGN KEY (payment_id) REFERENCES payment(payment_id)
381 ON DELETE SET NULL
382 ON UPDATE CASCADE,
383 CHECK (sent_at IS NULL OR sent_at >= created_at)
384);
385
386CREATE TABLE fines_notification (
387 fine_id INT NOT NULL,
388 notification_id INT NOT NULL,
389 PRIMARY KEY (fine_id, notification_id),
390 FOREIGN KEY (fine_id) REFERENCES fines(fine_id)
391 ON DELETE CASCADE
392 ON UPDATE CASCADE,
393 FOREIGN KEY (notification_id) REFERENCES notification(notification_id)
394 ON DELETE CASCADE
395 ON UPDATE CASCADE
396);
397
398