DatabaseCreation: ddl-script.sql

File ddl-script.sql, 12.2 KB (added by 231070, 3 weeks ago)
Line 
1CREATE TABLE APP_USER (
2 id BIGSERIAL NOT NULL,
3 first_name VARCHAR(50) NOT NULL,
4 last_name VARCHAR(50) NOT NULL,
5 email VARCHAR(50) NOT NULL UNIQUE,
6 phone VARCHAR(20) NOT NULL,
7 created_at DATE NOT NULL,
8 user_password VARCHAR(255) NOT NULL,
9 PRIMARY KEY (id),
10 CONSTRAINT CHK_email_format
11 CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
12 CONSTRAINT CHK_phone_number_format
13 CHECK (phone ~ '^[0-9]{7,15}$')
14);
15
16CREATE TABLE ROLES (
17 id BIGSERIAL NOT NULL,
18 role_name VARCHAR(20) NOT null unique,
19 PRIMARY KEY (id)
20);
21
22CREATE TABLE USER_ROLES (
23 APP_USERid BIGINT NOT NULL,
24 ROLESid BIGINT NOT NULL,
25 PRIMARY KEY (APP_USERid, ROLESid),
26 CONSTRAINT FK_user_roles_user
27 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
28 ON UPDATE CASCADE ON DELETE RESTRICT,
29 CONSTRAINT FK_user_roles_role
30 FOREIGN KEY (ROLESid) REFERENCES ROLES (id)
31 ON UPDATE CASCADE ON DELETE RESTRICT
32);
33
34CREATE TABLE CATEGORIZATION (
35 id BIGSERIAL NOT NULL,
36 category_name VARCHAR(20) NOT NULL,
37 PRIMARY KEY (id)
38);
39
40CREATE TABLE SUBCATEGORY (
41 id BIGSERIAL NOT NULL,
42 subcategory_name VARCHAR(50) NOT NULL,
43 CATEGORIZATIONid BIGINT NOT NULL,
44 PRIMARY KEY (id),
45 CONSTRAINT FK_subcategory_categorization
46 FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id)
47 ON UPDATE CASCADE ON DELETE RESTRICT
48);
49
50CREATE TABLE USER_CATEGORY_SUBSCRIPTION (
51 APP_USERid BIGINT NOT NULL DEFAULT 1,
52 CATEGORIZATIONid BIGINT NOT NULL DEFAULT 1,
53 PRIMARY KEY (APP_USERid, CATEGORIZATIONid),
54 CONSTRAINT FK_user_category_sub_user
55 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
56 ON UPDATE CASCADE ON DELETE SET DEFAULT,
57 CONSTRAINT FK_user_category_sub_categorization
58 FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id)
59 ON UPDATE CASCADE ON DELETE SET DEFAULT
60);
61
62CREATE TABLE USER_SUBCATEGORY_SUBSCRIPTION (
63 APP_USERid BIGINT NOT NULL DEFAULT 1,
64 SUBCATEGORYid BIGINT NOT NULL DEFAULT 1,
65 PRIMARY KEY (APP_USERid, SUBCATEGORYid),
66 CONSTRAINT FK_user_subcategory_sub_user
67 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
68 ON UPDATE CASCADE ON DELETE SET DEFAULT,
69 CONSTRAINT FK_user_subcategory_sub_subcategory
70 FOREIGN KEY (SUBCATEGORYid) REFERENCES SUBCATEGORY (id)
71 ON UPDATE CASCADE ON DELETE SET DEFAULT
72);
73
74CREATE TABLE EVENT (
75 id BIGSERIAL NOT NULL,
76 title VARCHAR(100) NOT NULL,
77 description VARCHAR(255),
78 start_date DATE NOT NULL,
79 end_date DATE NOT NULL,
80 CATEGORIZATIONid BIGINT NOT NULL DEFAULT 1,
81 SUBCATEGORYid BIGINT ,
82 PRIMARY KEY (id),
83 CONSTRAINT CHK_event_dates
84 CHECK (end_date >= start_date),
85 CONSTRAINT FK_event_categorization
86 FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id)
87 ON UPDATE CASCADE ON DELETE SET DEFAULT,
88 CONSTRAINT FK_event_subcategory
89 FOREIGN KEY (SUBCATEGORYid) REFERENCES SUBCATEGORY (id)
90 ON UPDATE CASCADE ON DELETE SET NULL
91);
92
93CREATE TABLE EVENT_IMAGE (
94 id BIGSERIAL NOT NULL,
95 image_url VARCHAR(255) NOT NULL,
96 EVENTid BIGINT NOT NULL,
97 PRIMARY KEY (id),
98 CONSTRAINT FK_event_image_event
99 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
100 ON UPDATE CASCADE ON DELETE CASCADE
101);
102
103CREATE TABLE EVENT_ROLE (
104 id BIGSERIAL NOT NULL,
105 role_name VARCHAR(20) NOT null unique,
106 PRIMARY KEY (id)
107);
108
109CREATE TABLE USER_EVENT (
110 APP_USERid BIGINT NOT NULL,
111 EVENTid BIGINT NOT NULL,
112 EVENT_ROLEid BIGINT NOT NULL,
113 PRIMARY KEY (APP_USERid, EVENTid),
114 CONSTRAINT FK_user_event_user
115 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
116 ON UPDATE CASCADE ON DELETE RESTRICT,
117 CONSTRAINT FK_user_event_event
118 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
119 ON UPDATE CASCADE ON DELETE RESTRICT,
120 CONSTRAINT FK_user_event_role
121 FOREIGN KEY (EVENT_ROLEid) REFERENCES EVENT_ROLE (id)
122 ON UPDATE CASCADE ON DELETE RESTRICT
123);
124
125CREATE TABLE VENUE (
126 id BIGSERIAL NOT NULL,
127 venue_name VARCHAR(100) NOT NULL,
128 city VARCHAR(50),
129 address VARCHAR(100) NOT NULL,
130 PRIMARY KEY (id)
131);
132
133CREATE TABLE HALL (
134 id BIGSERIAL NOT NULL,
135 hall_name VARCHAR(50) NOT NULL,
136 capacity INT NOT NULL,
137 VENUEid BIGINT NOT NULL,
138 PRIMARY KEY (id),
139 CONSTRAINT CHK_capacity_positive_number
140 CHECK (capacity > 0),
141 CONSTRAINT FK_hall_venue
142 FOREIGN KEY (VENUEid) REFERENCES VENUE (id)
143 ON UPDATE CASCADE ON DELETE RESTRICT
144);
145
146CREATE TABLE EVENT_HALL (
147 EVENTid BIGINT NOT NULL,
148 HALLid BIGINT NOT NULL,
149 allowed_access BOOLEAN NOT NULL,
150 PRIMARY KEY (EVENTid, HALLid),
151 CONSTRAINT FK_event_hall_event
152 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
153 ON UPDATE CASCADE ON DELETE RESTRICT,
154 CONSTRAINT FK_event_hall_hall
155 FOREIGN KEY (HALLid) REFERENCES HALL (id)
156 ON UPDATE CASCADE ON DELETE RESTRICT
157);
158
159CREATE TABLE TICKET_TYPE (
160 id BIGSERIAL NOT NULL,
161 type_name VARCHAR(50) NOT NULL,
162 PRIMARY KEY (id)
163);
164
165CREATE TABLE SEAT (
166 id BIGSERIAL NOT NULL,
167 seat_number INT NOT NULL,
168 HALLid BIGINT NOT NULL,
169 TICKET_TYPEid BIGINT NOT NULL,
170 PRIMARY KEY (id),
171 CONSTRAINT FK_seat_hall
172 FOREIGN KEY (HALLid) REFERENCES HALL (id)
173 ON UPDATE CASCADE ON DELETE RESTRICT,
174 CONSTRAINT FK_seat_ticket_type
175 FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
176 ON UPDATE CASCADE ON DELETE RESTRICT
177);
178
179CREATE TABLE EVENT_TICKET_TYPE (
180 EVENTid BIGINT NOT NULL,
181 TICKET_TYPEid BIGINT NOT NULL,
182 price INT NOT NULL,
183 quantity_available INT NOT NULL,
184 PRIMARY KEY (EVENTid, TICKET_TYPEid),
185 CONSTRAINT CHK_ticket_price_positive_num
186 CHECK (price >= 0),
187 CONSTRAINT CHK_ticket_quantity_positive_num
188 CHECK (quantity_available >= 0),
189 CONSTRAINT FK_event_ticket_type_event
190 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
191 ON UPDATE CASCADE ON DELETE RESTRICT,
192 CONSTRAINT FK_event_ticket_type_ticket_type
193 FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
194 ON UPDATE CASCADE ON DELETE RESTRICT
195);
196
197CREATE TABLE STATUS (
198 id BIGSERIAL NOT NULL,
199 status_name VARCHAR(20) NOT NULL,
200 description VARCHAR(255),
201 PRIMARY KEY (id)
202);
203
204CREATE TABLE PROMO_CODE (
205 id BIGSERIAL NOT NULL,
206 code VARCHAR(50) NOT NULL UNIQUE,
207 discount_percent INT NOT NULL,
208 expiration_date DATE NOT NULL,
209 APP_USERid BIGINT DEFAULT 1,
210 PRIMARY KEY (id),
211 CONSTRAINT CHK_discount_percent
212 CHECK (discount_percent > 0 AND discount_percent <= 100),
213 CONSTRAINT FK_promo_code_user
214 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
215 ON UPDATE CASCADE ON DELETE SET DEFAULT
216);
217
218CREATE TABLE USER_ORDER (
219 id BIGSERIAL NOT NULL,
220 order_date DATE NOT NULL,
221 total_amount INT NOT NULL,
222 APP_USERid BIGINT NOT NULL,
223 STATUSid BIGINT NOT NULL,
224 PROMO_CODEid BIGINT,
225 PRIMARY KEY (id),
226 CONSTRAINT CHK_order_amount_positive_num
227 CHECK (total_amount >= 0),
228 CONSTRAINT FK_user_order_user
229 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
230 ON UPDATE CASCADE ON DELETE RESTRICT,
231 CONSTRAINT FK_user_order_status
232 FOREIGN KEY (STATUSid) REFERENCES STATUS (id)
233 ON UPDATE CASCADE ON DELETE RESTRICT,
234 CONSTRAINT FK_user_order_promo_code
235 FOREIGN KEY (PROMO_CODEid) REFERENCES PROMO_CODE (id)
236 ON UPDATE CASCADE ON DELETE SET NULL
237);
238
239CREATE TABLE TICKET (
240 id BIGSERIAL NOT NULL,
241 code VARCHAR(50) NOT NULL UNIQUE,
242 status VARCHAR(20) NOT NULL,
243 TICKET_TYPEid BIGINT NOT NULL,
244 USER_ORDERid BIGINT NOT NULL,
245 SEATid BIGINT,
246 APP_USERid BIGINT NOT NULL,
247 EVENTid BIGINT NOT NULL,
248 HALLid BIGINT NOT NULL,
249 PRIMARY KEY (id),
250 CONSTRAINT FK_ticket_ticket_type
251 FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
252 ON UPDATE CASCADE ON DELETE RESTRICT,
253 CONSTRAINT FK_ticket_user_order
254 FOREIGN KEY (USER_ORDERid) REFERENCES USER_ORDER (id)
255 ON UPDATE CASCADE ON DELETE RESTRICT,
256 CONSTRAINT FK_ticket_seat
257 FOREIGN KEY (SEATid) REFERENCES SEAT (id)
258 ON UPDATE CASCADE ON DELETE RESTRICT,
259 CONSTRAINT FK_ticket_user
260 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
261 ON UPDATE CASCADE ON DELETE RESTRICT,
262 CONSTRAINT FK_ticket_event
263 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
264 ON UPDATE CASCADE ON DELETE RESTRICT,
265 CONSTRAINT FK_ticket_hall
266 FOREIGN KEY (HALLid) REFERENCES HALL (id)
267 ON UPDATE CASCADE ON DELETE RESTRICT
268);
269
270CREATE TABLE PAYMENT_METHOD (
271 id BIGSERIAL NOT NULL,
272 method_name VARCHAR(20) NOT NULL,
273 PRIMARY KEY (id)
274);
275
276CREATE TABLE PAYMENT (
277 id BIGSERIAL NOT NULL,
278 amount INT NOT NULL,
279 status VARCHAR(20) NOT NULL,
280 payment_date DATE NOT NULL,
281 USER_ORDERid BIGINT NOT NULL,
282 PAYMENT_METHODid BIGINT NOT NULL,
283 PRIMARY KEY (id),
284 CONSTRAINT CHK_amount_positive_number
285 CHECK (amount >= 0),
286 CONSTRAINT FK_payment_user_order
287 FOREIGN KEY (USER_ORDERid) REFERENCES USER_ORDER (id)
288 ON UPDATE CASCADE ON DELETE RESTRICT,
289 CONSTRAINT FK_payment_payment_method
290 FOREIGN KEY (PAYMENT_METHODid) REFERENCES PAYMENT_METHOD (id)
291 ON UPDATE CASCADE ON DELETE RESTRICT
292);
293
294CREATE TABLE REFUND (
295 id BIGSERIAL NOT NULL,
296 amount INT NOT NULL,
297 reason VARCHAR(255) NOT NULL,
298 status VARCHAR(20) NOT NULL,
299 PAYMENTid BIGINT NOT NULL,
300 PRIMARY KEY (id),
301 CONSTRAINT CHK_refund_amount_positive_num
302 CHECK (amount >= 0),
303 CONSTRAINT FK_refund_payment
304 FOREIGN KEY (PAYMENTid) REFERENCES PAYMENT (id)
305 ON UPDATE CASCADE ON DELETE RESTRICT
306);
307
308CREATE TABLE REVIEW (
309 id BIGSERIAL NOT NULL,
310 rating INT NOT NULL,
311 review_comment VARCHAR(255),
312 APP_USERid BIGINT DEFAULT 1,
313 EVENTid BIGINT NOT NULL,
314 PRIMARY KEY (id),
315 CONSTRAINT CHK_rating_range
316 CHECK (rating BETWEEN 1 AND 5),
317 CONSTRAINT FK_review_user
318 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
319 ON UPDATE CASCADE ON DELETE SET DEFAULT,
320 CONSTRAINT FK_review_event
321 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
322 ON UPDATE CASCADE ON DELETE CASCADE
323);
324
325CREATE TABLE NOTIFICATION (
326 id BIGSERIAL NOT NULL,
327 message VARCHAR(150) NOT NULL,
328 created_at DATE NOT NULL,
329 APP_USERid BIGINT NOT NULL DEFAULT 1,
330 PRIMARY KEY (id),
331 CONSTRAINT FK_notification_user
332 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
333 ON UPDATE CASCADE ON DELETE SET DEFAULT
334);
335
336CREATE TABLE WAITLIST (
337 id BIGSERIAL NOT NULL,
338 created_at DATE NOT NULL,
339 status VARCHAR(20) NOT NULL,
340 EVENTid BIGINT NOT NULL,
341 PRIMARY KEY (id),
342 CONSTRAINT FK_waitlist_event
343 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
344 ON UPDATE CASCADE ON DELETE CASCADE
345);
346
347CREATE TABLE USER_WAITLIST (
348 APP_USERid BIGINT NOT NULL,
349 WAITLISTid BIGINT NOT NULL,
350 joined_at DATE NOT NULL,
351 PRIMARY KEY (APP_USERid, WAITLISTid),
352 CONSTRAINT FK_user_waitlist_user
353 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
354 ON UPDATE CASCADE ON DELETE RESTRICT,
355 CONSTRAINT FK_user_waitlist_waitlist
356 FOREIGN KEY (WAITLISTid) REFERENCES WAITLIST (id)
357 ON UPDATE CASCADE ON DELETE RESTRICT
358);