DatabaseCreation: ddl.sql

File ddl.sql, 40.1 KB (added by 231151, 17 hours ago)
Line 
1CREATE TABLE AGENCY (
2 AGENCY_ID SERIAL NOT NULL,
3 AGENCY_NAME VARCHAR(100) NOT NULL DEFAULT 'Unknown Agency',
4 AGENCY_URL VARCHAR(255),
5 AGENCY_TIMEZONE VARCHAR(50) NOT NULL DEFAULT 'UTC',
6 AGENCY_LANG VARCHAR(10) DEFAULT 'en',
7 AGENCY_PHONE VARCHAR(30),
8 AGENCY_EMAIL VARCHAR(100),
9 AGENCY_FARE_URL VARCHAR(255),
10
11 CONSTRAINT PK_AGENCY PRIMARY KEY (AGENCY_ID),
12
13 CONSTRAINT CHK_AGENCY_EMAIL CHECK (
14 AGENCY_EMAIL IS NULL OR
15 AGENCY_EMAIL ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'
16 ),
17
18 CONSTRAINT CHK_AGENCY_PHONE CHECK (
19 AGENCY_PHONE IS NULL OR
20 AGENCY_PHONE ~ '^\+?[0-9\s\(\)\-]{7,20}$'
21 )
22);
23
24CREATE TABLE DISCOUNT (
25 DISCOUNT_ID SERIAL NOT NULL,
26 DISCOUNT_NAME VARCHAR(100) NOT NULL DEFAULT 'General Discount',
27 DISCOUNT_PERCENTAGE DECIMAL(5,2) NOT NULL DEFAULT 0.00,
28 DESCRIPTION TEXT,
29 ELIGIBILITY_CRITERIA TEXT,
30 VALID_FROM DATE DEFAULT CURRENT_DATE,
31 VALID_TO DATE,
32
33 CONSTRAINT PK_DISCOUNT PRIMARY KEY (DISCOUNT_ID),
34
35 CONSTRAINT CHK_DISCOUNT_PERCENTAGE CHECK (
36 DISCOUNT_PERCENTAGE BETWEEN 0 AND 100
37 ),
38
39 CONSTRAINT CHK_DISCOUNT_DATES CHECK (
40 VALID_FROM IS NULL OR VALID_TO IS NULL OR VALID_FROM <= VALID_TO
41 )
42);
43
44CREATE TABLE ZONE (
45 ZONE_ID VARCHAR(50) NOT NULL,
46 ZONE_NAME VARCHAR(100) NOT NULL DEFAULT 'Unnamed Zone',
47 ZONE_NUMBER INTEGER,
48 DESCRIPTION TEXT,
49
50 CONSTRAINT PK_ZONE PRIMARY KEY (ZONE_ID),
51
52 CONSTRAINT CHK_ZONE_NUMBER CHECK (
53 ZONE_NUMBER IS NULL OR ZONE_NUMBER >= 0
54 )
55);
56
57CREATE TABLE VEHICLE_TYPE (
58 VEHICLE_TYPE_ID SERIAL NOT NULL,
59 TYPE_NAME VARCHAR(50) NOT NULL DEFAULT 'Unknown Type',
60 DESCRIPTION TEXT,
61
62 CONSTRAINT PK_VEHICLE_TYPE PRIMARY KEY (VEHICLE_TYPE_ID)
63);
64
65CREATE TABLE STOPS (
66 STOP_ID VARCHAR(50) NOT NULL,
67 STOP_NAME VARCHAR(100) NOT NULL DEFAULT 'Unnamed Stop',
68 LATITUDE DECIMAL(10,7) NOT NULL,
69 LONGITUDE DECIMAL(10,7) NOT NULL,
70 ZONE_ID VARCHAR(50),
71 VEHICLE_TYPE_ID INTEGER NOT NULL,
72
73 CONSTRAINT PK_STOPS PRIMARY KEY (STOP_ID, VEHICLE_TYPE_ID),
74
75 -- ако се избрише зоната, постојката останува со NULL зона наместо да се брише
76 CONSTRAINT FK_STOPS_ZONE FOREIGN KEY (ZONE_ID) REFERENCES ZONE(ZONE_ID)
77 ON DELETE SET NULL ON UPDATE CASCADE,
78
79 CONSTRAINT FK_STOPS_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
80 ON DELETE RESTRICT ON UPDATE CASCADE
81);
82
83CREATE TABLE ROUTE (
84 ROUTE_ID VARCHAR(50) NOT NULL,
85 AGENCY_ID INTEGER NOT NULL,
86 START_STOP_ID VARCHAR(50),
87 START_STOP_VT INTEGER,
88 END_STOP_ID VARCHAR(50),
89 END_STOP_VT INTEGER,
90 ROUTE_NAME VARCHAR(100) NOT NULL DEFAULT 'Unnamed Route',
91 DISTANCE_KM DECIMAL(8,2),
92 ESTIMATED_DURATION INTEGER,
93 VEHICLE_TYPE_ID INTEGER,
94
95 CONSTRAINT PK_ROUTE PRIMARY KEY (ROUTE_ID),
96
97 -- не може да се избрише агенција додека има активни линии под неа
98 CONSTRAINT FK_ROUTE_AGENCY FOREIGN KEY (AGENCY_ID) REFERENCES AGENCY(AGENCY_ID)
99 ON DELETE RESTRICT ON UPDATE CASCADE,
100
101 -- ако се избрише почетната постојка, линијата останува со NULL почеток
102 CONSTRAINT FK_ROUTE_STARTSTOP FOREIGN KEY (START_STOP_ID, START_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
103 ON DELETE SET NULL ON UPDATE CASCADE,
104
105 -- ако се избрише крајната постојка, линијата останува со NULL крај
106 CONSTRAINT FK_ROUTE_ENDSTOP FOREIGN KEY (END_STOP_ID, END_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
107 ON DELETE SET NULL ON UPDATE CASCADE,
108
109 -- ако се избрише типот на возило, линијата останува без тип (NULL)
110 CONSTRAINT FK_ROUTE_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
111 ON DELETE SET NULL ON UPDATE CASCADE
112);
113
114CREATE TABLE ROUTE_STOP (
115 ROUTE_STOP_ID SERIAL NOT NULL,
116 ROUTE_ID VARCHAR(50) NOT NULL,
117 STOP_ID VARCHAR(50) NOT NULL,
118 STOP_VT INTEGER NOT NULL,
119 STOP_SEQUENCE INTEGER NOT NULL,
120 DISTANCE_TRAVELED DECIMAL(8,2),
121 TIMEPOINT INTEGER DEFAULT 1,
122
123 CONSTRAINT PK_ROUTE_STOP PRIMARY KEY (ROUTE_STOP_ID),
124
125 -- ако се избрише линијата, сите нејзини постојки во редоследот се бришат автоматски
126 CONSTRAINT FK_ROUTESTOP_ROUTE FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE(ROUTE_ID)
127 ON DELETE CASCADE ON UPDATE CASCADE,
128
129 -- не може да се избрише постојка која е дел од некоја линија
130 CONSTRAINT FK_ROUTESTOP_STOP FOREIGN KEY (STOP_ID, STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
131 ON DELETE RESTRICT ON UPDATE CASCADE,
132
133 CONSTRAINT CHK_ROUTESTOP_SEQ CHECK (STOP_SEQUENCE >= 0),
134 CONSTRAINT CHK_ROUTESTOP_TIMEPOINT CHECK (TIMEPOINT IN (0, 1)),
135 CONSTRAINT CHK_ROUTESTOP_DIST CHECK (DISTANCE_TRAVELED IS NULL OR DISTANCE_TRAVELED >= 0)
136);
137
138CREATE TABLE SHAPE (
139 SHAPE_ID VARCHAR(50) NOT NULL,
140 DESCRIPTION VARCHAR(100),
141 VEHICLE_TYPE_ID INTEGER,
142
143 CONSTRAINT PK_SHAPE PRIMARY KEY (SHAPE_ID),
144
145 -- ако се избрише типот на возило, shape останува без тип (NULL)
146 CONSTRAINT FK_SHAPE_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
147 ON DELETE SET NULL ON UPDATE CASCADE
148);
149
150CREATE TABLE SHAPE_POINT (
151 SHAPE_ID VARCHAR(50) NOT NULL,
152 SHAPE_PT_SEQUENCE INTEGER NOT NULL,
153 SHAPE_PT_LAT DECIMAL(10,7) NOT NULL,
154 SHAPE_PT_LON DECIMAL(10,7) NOT NULL,
155 SHAPE_DIST_TRAVELED DECIMAL(8,2),
156 VEHICLE_TYPE_ID INTEGER,
157
158 CONSTRAINT PK_SHAPE_POINT PRIMARY KEY (SHAPE_ID, SHAPE_PT_SEQUENCE),
159
160 -- ако се избрише shape-от, сите негови точки се бришат автоматски
161 CONSTRAINT FK_SHAPEPOINT_SHAPE FOREIGN KEY (SHAPE_ID) REFERENCES SHAPE(SHAPE_ID)
162 ON DELETE CASCADE ON UPDATE CASCADE,
163
164 -- ако се избрише типот на возило, точката останува без тип (NULL)
165 CONSTRAINT FK_SHAPEPOINT_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
166 ON DELETE SET NULL ON UPDATE CASCADE
167);
168
169CREATE TABLE VEHICLE (
170 VEHICLE_ID VARCHAR(50) NOT NULL,
171 VEHICLE_TYPE_ID INTEGER NOT NULL,
172 REGISTRATION_NUMBER VARCHAR(30) NOT NULL,
173 MODEL VARCHAR(100) DEFAULT 'Unknown Model',
174 MANUFACTURER VARCHAR(100) DEFAULT 'Unknown Manufacturer',
175 CAPACITY INTEGER,
176 YEAR_OF_MANUFACTURE INTEGER,
177 STATUS VARCHAR(20) DEFAULT 'ACTIVE',
178
179 CONSTRAINT PK_VEHICLE PRIMARY KEY (VEHICLE_ID),
180
181 -- не може да се избрише тип на возило додека постојат возила од тој тип
182 CONSTRAINT FK_VEHICLE_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
183 ON DELETE RESTRICT ON UPDATE CASCADE,
184
185 CONSTRAINT CHK_VEHICLE_STATUS CHECK (
186 STATUS IS NULL OR STATUS IN ('ACTIVE', 'INACTIVE', 'MAINTENANCE', 'DECOMMISSIONED')
187 ),
188
189 CONSTRAINT CHK_VEHICLE_CAPACITY CHECK (CAPACITY IS NULL OR CAPACITY > 0),
190
191 CONSTRAINT CHK_VEHICLE_REGNO CHECK (
192 REGISTRATION_NUMBER ~ '^[A-Z0-9\-\s]{2,20}$'
193 )
194);
195
196CREATE TABLE EMPLOYEE (
197 EMPLOYEE_ID VARCHAR(50) NOT NULL,
198 FIRST_NAME VARCHAR(50) NOT NULL DEFAULT 'Unknown',
199 LAST_NAME VARCHAR(50) NOT NULL DEFAULT 'Unknown',
200 PHONE VARCHAR(30),
201 EMAIL VARCHAR(100),
202 HIRE_DATE DATE DEFAULT CURRENT_DATE,
203 POSITION VARCHAR(50),
204 STATUS VARCHAR(20) DEFAULT 'ACTIVE',
205 PASSWORD_HASH VARCHAR(255),
206
207 CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID),
208
209 CONSTRAINT CHK_EMP_EMAIL CHECK (
210 EMAIL IS NULL OR
211 EMAIL ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'
212 ),
213
214 CONSTRAINT CHK_EMP_PHONE CHECK (
215 PHONE IS NULL OR
216 PHONE ~ '^\+?[0-9\s\(\)\-]{7,20}$'
217 ),
218
219 CONSTRAINT CHK_EMP_STATUS CHECK (
220 STATUS IS NULL OR STATUS IN ('ACTIVE', 'INACTIVE', 'ON_LEAVE', 'TERMINATED')
221 ),
222
223 CONSTRAINT CHK_EMP_HIREDATE CHECK (
224 HIRE_DATE IS NULL OR HIRE_DATE <= CURRENT_DATE
225 )
226);
227
228CREATE TABLE DISCOUNT_OFFICER (
229 OFFICER_ID VARCHAR(50) NOT NULL,
230 EMPLOYEE_ID VARCHAR(50) NOT NULL,
231 ASSIGNED_REGION VARCHAR(100) DEFAULT 'General',
232 CERTIFICATION_CODE VARCHAR(50),
233 AUTHORIZED_FROM DATE DEFAULT CURRENT_DATE,
234 AUTHORIZED_TO DATE,
235
236 CONSTRAINT PK_DISCOUNT_OFFICER PRIMARY KEY (OFFICER_ID),
237
238 -- не може да се избрише вработен кој е активен службеник за попусти
239 CONSTRAINT FK_OFFICER_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
240 ON DELETE RESTRICT ON UPDATE CASCADE,
241
242 -- датумот на почеток на овластување мора да биде пред датумот на истекување
243 CONSTRAINT CHK_OFFICER_DATES CHECK (
244 AUTHORIZED_FROM IS NULL OR AUTHORIZED_TO IS NULL OR AUTHORIZED_FROM <= AUTHORIZED_TO
245 )
246);
247
248CREATE TABLE DISCOUNT_OFFICER_DISCOUNT (
249 ID SERIAL NOT NULL,
250 OFFICER_ID VARCHAR(50) NOT NULL,
251 DISCOUNT_ID INTEGER NOT NULL,
252 ASSIGNED_DATE DATE DEFAULT CURRENT_DATE,
253 NOTES TEXT,
254
255 CONSTRAINT PK_OFFICER_DISCOUNT PRIMARY KEY (ID),
256
257 -- ако се избрише службеникот, неговите доделувања на попусти се бришат автоматски
258 CONSTRAINT FK_OD_OFFICER FOREIGN KEY (OFFICER_ID) REFERENCES DISCOUNT_OFFICER(OFFICER_ID)
259 ON DELETE CASCADE ON UPDATE CASCADE,
260
261 -- не може да се избрише попуст кој сеуште е доделен на службеници
262 CONSTRAINT FK_OD_DISCOUNT FOREIGN KEY (DISCOUNT_ID) REFERENCES DISCOUNT(DISCOUNT_ID)
263 ON DELETE RESTRICT ON UPDATE CASCADE
264);
265
266CREATE TABLE DRIVER (
267 DRIVER_ID VARCHAR(50) NOT NULL,
268 EMPLOYEE_ID VARCHAR(50) NOT NULL,
269 LICENSE_NUMBER VARCHAR(50) NOT NULL,
270 LICENSE_CATEGORY VARCHAR(10) DEFAULT 'D',
271 LICENSE_EXPIRY_DATE DATE,
272 YEARS_EXPERIENCE INTEGER DEFAULT 0,
273
274 CONSTRAINT PK_DRIVER PRIMARY KEY (DRIVER_ID),
275
276 CONSTRAINT FK_DRIVER_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
277 ON DELETE RESTRICT ON UPDATE CASCADE, -- не може да се избрише вработен кој е регистриран возач
278
279 CONSTRAINT CHK_DRIVER_EXPERIENCE CHECK (
280 YEARS_EXPERIENCE IS NULL OR YEARS_EXPERIENCE >= 0
281 ),
282
283 CONSTRAINT CHK_DRIVER_LICENSE CHECK (
284 LICENSE_NUMBER ~ '^[A-Z0-9\-]{3,30}$'
285 )
286);
287
288CREATE TABLE CONDUCTOR (
289 CONDUCTOR_ID VARCHAR(50) NOT NULL,
290 EMPLOYEE_ID VARCHAR(50) NOT NULL,
291 CERTIFICATION_NUMBER VARCHAR(50),
292 HIRE_ROLE_DATE DATE DEFAULT CURRENT_DATE,
293
294 CONSTRAINT PK_CONDUCTOR PRIMARY KEY (CONDUCTOR_ID),
295
296 -- не може да се избрише вработен кој е регистриран кондуктер
297 CONSTRAINT FK_CONDUCTOR_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
298 ON DELETE RESTRICT ON UPDATE CASCADE
299);
300
301CREATE TABLE SHIFT (
302 SHIFT_ID SERIAL NOT NULL,
303 SHIFT_NAME VARCHAR(50) NOT NULL DEFAULT 'Regular Shift',
304 START_TIME TIME NOT NULL,
305 END_TIME TIME NOT NULL,
306 DESCRIPTION TEXT,
307
308 CONSTRAINT PK_SHIFT PRIMARY KEY (SHIFT_ID)
309);
310
311CREATE TABLE TRIP (
312 TRIP_ID VARCHAR(50) NOT NULL,
313 ROUTE_ID VARCHAR(50) NOT NULL,
314 VEHICLE_ID VARCHAR(50) NOT NULL,
315 DRIVER_ID VARCHAR(50) NOT NULL,
316 SHAPE_ID VARCHAR(50),
317 TRIP_HEADSIGN VARCHAR(100),
318 START_TIME TIME,
319 END_TIME TIME,
320 DIRECTION_ID INTEGER,
321 WHEELCHAIR_ACCESSIBLE INTEGER,
322 TRIP_DATE DATE DEFAULT CURRENT_DATE,
323 STATUS VARCHAR(20) DEFAULT 'SCHEDULED',
324 VEHICLE_TYPE_ID INTEGER,
325
326 CONSTRAINT PK_TRIP PRIMARY KEY (TRIP_ID),
327
328 -- не може да се избрише линија додека има возења на неа
329 CONSTRAINT FK_TRIP_ROUTE FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE(ROUTE_ID)
330 ON DELETE RESTRICT ON UPDATE CASCADE,
331
332 -- не може да се избрише возило кое е користено во возење
333 CONSTRAINT FK_TRIP_VEHICLE FOREIGN KEY (VEHICLE_ID) REFERENCES VEHICLE(VEHICLE_ID)
334 ON DELETE RESTRICT ON UPDATE CASCADE,
335
336 -- не може да се избрише возач кој е доделен на возење
337 CONSTRAINT FK_TRIP_DRIVER FOREIGN KEY (DRIVER_ID) REFERENCES DRIVER(DRIVER_ID)
338 ON DELETE RESTRICT ON UPDATE CASCADE,
339
340 -- ако се избрише shape-от, возењето останува без геометрија (NULL)
341 CONSTRAINT FK_TRIP_SHAPE FOREIGN KEY (SHAPE_ID) REFERENCES SHAPE(SHAPE_ID)
342 ON DELETE SET NULL ON UPDATE CASCADE,
343
344 CONSTRAINT CHK_TRIP_STATUS CHECK (
345 STATUS IS NULL OR
346 STATUS IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED', 'DELAYED')
347 ),
348
349 -- ако се избрише типот на возило, возењето останува без тип (NULL)
350 CONSTRAINT FK_TRIP_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
351 ON DELETE SET NULL ON UPDATE CASCADE
352);
353
354CREATE TABLE STOP_TIME (
355 STOP_TIME_ID VARCHAR(50) NOT NULL,
356 TRIP_ID VARCHAR(50) NOT NULL,
357 STOP_ID VARCHAR(50) NOT NULL,
358 STOP_VT INTEGER,
359 ROUTE_STOP_ID INTEGER,
360 ARRIVAL_TIME TIME,
361 DEPARTURE_TIME TIME,
362 STOP_SEQUENCE INTEGER,
363 SHAPE_DIST_TRAVELED DECIMAL(8,2),
364 VEHICLE_TYPE_ID INTEGER,
365
366 CONSTRAINT PK_STOP_TIME PRIMARY KEY (STOP_TIME_ID),
367
368 -- ако се избрише возењето, сите времиња по постојки се бришат автоматски
369 CONSTRAINT FK_STOPTIME_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
370 ON DELETE CASCADE ON UPDATE CASCADE,
371
372 -- не може да се избрише постојка која е дел од активно возење
373 CONSTRAINT FK_STOPTIME_STOP FOREIGN KEY (STOP_ID, STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
374 ON DELETE RESTRICT ON UPDATE CASCADE,
375
376 -- ако route_stop се избрише, записот останува со NULL (не се губи историјата)
377 CONSTRAINT FK_STOPTIME_ROUTESTOP FOREIGN KEY (ROUTE_STOP_ID) REFERENCES ROUTE_STOP(ROUTE_STOP_ID)
378 ON DELETE SET NULL ON UPDATE CASCADE,
379
380 CONSTRAINT CHK_STOPTIME_SEQ CHECK (STOP_SEQUENCE IS NULL OR STOP_SEQUENCE >= 0),
381
382 -- ако се избрише типот на возило, записот останува без тип (NULL)
383 CONSTRAINT FK_STOPTIME_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
384 ON DELETE SET NULL ON UPDATE CASCADE
385);
386
387CREATE TABLE TRANSFER (
388 TRANSFER_ID SERIAL NOT NULL,
389 FROM_STOP_ID VARCHAR(50) NOT NULL,
390 FROM_STOP_VT INTEGER NOT NULL,
391 TO_STOP_ID VARCHAR(50) NOT NULL,
392 TO_STOP_VT INTEGER NOT NULL,
393 FROM_ROUTE_ID VARCHAR(50),
394 TO_ROUTE_ID VARCHAR(50),
395 VEHICLE_TYPE_ID INTEGER,
396
397 CONSTRAINT PK_TRANSFER PRIMARY KEY (TRANSFER_ID),
398
399 -- не може да се избрише почетна постојка на трансфер
400 CONSTRAINT FK_TRANSFER_FROMSTOP FOREIGN KEY (FROM_STOP_ID, FROM_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
401 ON DELETE RESTRICT ON UPDATE CASCADE,
402
403 -- не може да се избрише крајна постојка на трансфер
404 CONSTRAINT FK_TRANSFER_TOSTOP FOREIGN KEY (TO_STOP_ID, TO_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
405 ON DELETE RESTRICT ON UPDATE CASCADE,
406
407 -- ако се избрише линијата, трансферот станува генерален (без конкретна линија)
408 CONSTRAINT FK_TRANSFER_FROMROUTE FOREIGN KEY (FROM_ROUTE_ID) REFERENCES ROUTE(ROUTE_ID)
409 ON DELETE SET NULL ON UPDATE CASCADE,
410
411 -- ако се избрише линијата, трансферот станува генерален (без конкретна линија)
412 CONSTRAINT FK_TRANSFER_TOROUTE FOREIGN KEY (TO_ROUTE_ID) REFERENCES ROUTE(ROUTE_ID)
413 ON DELETE SET NULL ON UPDATE CASCADE,
414
415 -- ако се избрише типот на возило, трансферот останува без тип (NULL)
416 CONSTRAINT FK_TRANSFER_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
417 ON DELETE SET NULL ON UPDATE CASCADE
418);
419
420CREATE TABLE DELAY_LOG (
421 DELAY_ID SERIAL NOT NULL,
422 TRIP_ID VARCHAR(50) NOT NULL,
423 STOP_ID VARCHAR(50),
424 STOP_VT INTEGER,
425 DELAY_MINUTES INTEGER,
426 DELAY_REASON TEXT,
427 REPORTED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
428
429 CONSTRAINT PK_DELAY_LOG PRIMARY KEY (DELAY_ID),
430
431 -- ако се избрише возењето, сите записи за доцнење се бришат автоматски
432 CONSTRAINT FK_DELAYLOG_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
433 ON DELETE CASCADE ON UPDATE CASCADE,
434
435 -- ако се избрише постојката, записот за доцнење останува без референца на постојка
436 CONSTRAINT FK_DELAYLOG_STOP FOREIGN KEY (STOP_ID, STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
437 ON DELETE SET NULL ON UPDATE CASCADE,
438
439 CONSTRAINT CHK_DELAY_MINUTES CHECK (
440 DELAY_MINUTES IS NULL OR DELAY_MINUTES >= 0
441 )
442);
443
444CREATE TABLE CAPACITY_LOG (
445 CAPACITY_LOG_ID SERIAL NOT NULL,
446 TRIP_ID VARCHAR(50) NOT NULL,
447 VEHICLE_ID VARCHAR(50) NOT NULL,
448 STOP_ID VARCHAR(50),
449 STOP_VT INTEGER,
450 PASSENGER_COUNT INTEGER,
451 RECORDED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
452
453 CONSTRAINT PK_CAPACITY_LOG PRIMARY KEY (CAPACITY_LOG_ID),
454
455 -- ако се избрише возењето, сите логови за капацитет се бришат автоматски
456 CONSTRAINT FK_CAPACITYLOG_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
457 ON DELETE CASCADE ON UPDATE CASCADE,
458
459 -- не може да се избрише возило додека постојат логови за него
460 CONSTRAINT FK_CAPACITYLOG_VEHICLE FOREIGN KEY (VEHICLE_ID) REFERENCES VEHICLE(VEHICLE_ID)
461 ON DELETE RESTRICT ON UPDATE CASCADE,
462
463 -- ако се избрише постојката, логот останува без референца на постојка
464 CONSTRAINT FK_CAPACITYLOG_STOP FOREIGN KEY (STOP_ID, STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
465 ON DELETE SET NULL ON UPDATE CASCADE,
466
467 CONSTRAINT CHK_CAPLOG_COUNT CHECK (
468 PASSENGER_COUNT IS NULL OR PASSENGER_COUNT >= 0
469 )
470);
471
472CREATE TABLE VEHICLE_LOG (
473 VEHICLE_LOG_ID SERIAL NOT NULL,
474 VEHICLE_ID VARCHAR(50) NOT NULL,
475 TRIP_ID VARCHAR(50) NOT NULL,
476 LATITUDE DECIMAL(10,7) NOT NULL,
477 LONGITUDE DECIMAL(10,7) NOT NULL,
478 SPEED_KMH DECIMAL(6,2),
479 HEADING DECIMAL(5,2),
480 RECORDED_AT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
481 OCCUPANCY_STATUS VARCHAR(30),
482 CONGESTION_LEVEL VARCHAR(20),
483 CURRENT_STOP_ID VARCHAR(50),
484 CURRENT_STOP_VT INTEGER,
485 NEXT_STOP_ID VARCHAR(50),
486 NEXT_STOP_VT INTEGER,
487 DISTANCE_TO_NEXT_STOP_M DECIMAL(8,2),
488
489 CONSTRAINT PK_VEHICLE_LOG PRIMARY KEY (VEHICLE_LOG_ID),
490
491 -- не може да се избрише возило додека постојат GPS логови за него
492 CONSTRAINT FK_VEHICLELOG_VEHICLE FOREIGN KEY (VEHICLE_ID) REFERENCES VEHICLE(VEHICLE_ID)
493 ON DELETE RESTRICT ON UPDATE CASCADE,
494
495 -- ако се избрише возењето, сите GPS логови се бришат автоматски
496 CONSTRAINT FK_VEHICLELOG_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
497 ON DELETE CASCADE ON UPDATE CASCADE,
498
499 -- ако се избрише тековната постојка, логот останува со NULL
500 CONSTRAINT FK_VEHICLELOG_CURRENTSTOP FOREIGN KEY (CURRENT_STOP_ID,CURRENT_STOP_VT) REFERENCES STOPS(STOP_ID,VEHICLE_TYPE_ID)
501 ON DELETE SET NULL ON UPDATE CASCADE,
502
503 -- ако се избрише следната постојка, логот останува со NULL
504 CONSTRAINT FK_VEHICLELOG_NEXTSTOP FOREIGN KEY (NEXT_STOP_ID,NEXT_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
505 ON DELETE SET NULL ON UPDATE CASCADE,
506
507 CONSTRAINT CHK_VLOG_OCCUPANCY CHECK (
508 OCCUPANCY_STATUS IS NULL OR OCCUPANCY_STATUS IN (
509 'EMPTY', 'MANY_SEATS_AVAILABLE', 'FEW_SEATS_AVAILABLE',
510 'STANDING_ROOM_ONLY', 'CRUSHED_STANDING_ONLY',
511 'FULL', 'NOT_ACCEPTING_PASSENGERS'
512 )
513 ),
514
515 CONSTRAINT CHK_VLOG_CONGESTION CHECK (
516 CONGESTION_LEVEL IS NULL OR
517 CONGESTION_LEVEL IN ('RUNNING_SMOOTHLY', 'STOP_AND_GO', 'CONGESTION', 'SEVERE_CONGESTION')
518 )
519);
520
521CREATE TABLE FUEL_LOG (
522 FUEL_LOG_ID SERIAL NOT NULL,
523 VEHICLE_ID VARCHAR(50) NOT NULL,
524 FUEL_AMOUNT DECIMAL(8,2),
525 FUEL_COST DECIMAL(10,2),
526 FUEL_DATE DATE DEFAULT CURRENT_DATE,
527 MILEAGE DECIMAL(10,2),
528
529 CONSTRAINT PK_FUEL_LOG PRIMARY KEY (FUEL_LOG_ID),
530
531 -- не може да се избрише возило додека постојат записи за гориво
532 CONSTRAINT FK_FUELLOG_VEHICLE FOREIGN KEY (VEHICLE_ID) REFERENCES VEHICLE(VEHICLE_ID)
533 ON DELETE RESTRICT ON UPDATE CASCADE,
534
535 CONSTRAINT CHK_FUELLOG_AMOUNT CHECK (FUEL_AMOUNT IS NULL OR FUEL_AMOUNT > 0),
536 CONSTRAINT CHK_FUELLOG_COST CHECK (FUEL_COST IS NULL OR FUEL_COST > 0),
537 CONSTRAINT CHK_FUELLOG_MILEAGE CHECK (MILEAGE IS NULL OR MILEAGE >= 0)
538);
539
540CREATE TABLE DRIVER_SCHEDULE (
541 DRIVER_SCHEDULE_ID SERIAL NOT NULL,
542 DRIVER_ID VARCHAR(50) NOT NULL,
543 TRIP_ID VARCHAR(50) NOT NULL,
544 SHIFT_ID INTEGER NOT NULL,
545 SHIFT_START TIMESTAMP,
546 SHIFT_END TIMESTAMP,
547 SCHEDULE_DATE DATE DEFAULT CURRENT_DATE,
548
549 CONSTRAINT PK_DRIVER_SCHEDULE PRIMARY KEY (DRIVER_SCHEDULE_ID),
550
551 -- не може да се избрише возач кој има закажани возења
552 CONSTRAINT FK_DRIVERSCHED_DRIVER FOREIGN KEY (DRIVER_ID) REFERENCES DRIVER(DRIVER_ID)
553 ON DELETE RESTRICT ON UPDATE CASCADE,
554
555 -- ако се избрише возењето, записот во распоредот се брише автоматски
556 CONSTRAINT FK_DRIVERSCHED_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
557 ON DELETE CASCADE ON UPDATE CASCADE,
558
559 -- не може да се избрише смена која е во употреба во распоред
560 CONSTRAINT FK_DRIVERSCHED_SHIFT FOREIGN KEY (SHIFT_ID) REFERENCES SHIFT(SHIFT_ID)
561 ON DELETE RESTRICT ON UPDATE CASCADE,
562
563 CONSTRAINT CHK_DRVSCHED_TIMES CHECK (
564 SHIFT_START IS NULL OR SHIFT_END IS NULL OR SHIFT_START <= SHIFT_END
565 )
566);
567
568CREATE TABLE CONDUCTOR_SCHEDULE (
569 CONDUCTOR_SCHEDULE_ID SERIAL NOT NULL,
570 CONDUCTOR_ID VARCHAR(50) NOT NULL,
571 TRIP_ID VARCHAR(50) NOT NULL,
572 SHIFT_ID INTEGER NOT NULL,
573 SHIFT_START TIMESTAMP,
574 SHIFT_END TIMESTAMP,
575 SCHEDULE_DATE DATE DEFAULT CURRENT_DATE,
576
577 CONSTRAINT PK_CONDUCTOR_SCHEDULE PRIMARY KEY (CONDUCTOR_SCHEDULE_ID),
578
579 -- не може да се избрише кондуктер кој има закажани возења
580 CONSTRAINT FK_CONDSCHED_CONDUCTOR FOREIGN KEY (CONDUCTOR_ID) REFERENCES CONDUCTOR(CONDUCTOR_ID)
581 ON DELETE RESTRICT ON UPDATE CASCADE,
582
583 -- ако се избрише возењето, записот во распоредот се брише автоматски
584 CONSTRAINT FK_CONDSCHED_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
585 ON DELETE CASCADE ON UPDATE CASCADE,
586
587 -- не може да се избрише смена која е во употреба во распоред
588 CONSTRAINT FK_CONDSCHED_SHIFT FOREIGN KEY (SHIFT_ID) REFERENCES SHIFT(SHIFT_ID)
589 ON DELETE RESTRICT ON UPDATE CASCADE,
590
591 CONSTRAINT CHK_CONDSCHED_TIMES CHECK (
592 SHIFT_START IS NULL OR SHIFT_END IS NULL OR SHIFT_START <= SHIFT_END
593 )
594);
595
596CREATE TABLE PASSENGER (
597 PASSENGER_ID VARCHAR(50) NOT NULL,
598 FIRST_NAME VARCHAR(50) NOT NULL DEFAULT 'Unknown',
599 LAST_NAME VARCHAR(50) NOT NULL DEFAULT 'Unknown',
600 EMAIL VARCHAR(100),
601 PHONE VARCHAR(30),
602 DATE_OF_BIRTH DATE,
603 REGISTRATION_DATE DATE DEFAULT CURRENT_DATE,
604 PASSENGER_TYPE VARCHAR(20) DEFAULT 'REGULAR',
605 PASSWORD_HASH VARCHAR(255),
606
607 CONSTRAINT PK_PASSENGER PRIMARY KEY (PASSENGER_ID),
608
609 CONSTRAINT CHK_PASS_EMAIL CHECK (
610 EMAIL IS NULL OR
611 EMAIL ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'
612 ),
613
614 CONSTRAINT CHK_PASS_PHONE CHECK (
615 PHONE IS NULL OR
616 PHONE ~ '^\+?[0-9\s\(\)\-]{7,20}$'
617 ),
618
619 CONSTRAINT CHK_PASS_TYPE CHECK (
620 PASSENGER_TYPE IS NULL OR
621 PASSENGER_TYPE IN ('REGULAR', 'STUDENT', 'SENIOR', 'DISABLED', 'CHILD')
622 ),
623
624 CONSTRAINT CHK_PASS_DOB CHECK (
625 DATE_OF_BIRTH IS NULL OR DATE_OF_BIRTH <= CURRENT_DATE
626 ),
627
628 CONSTRAINT CHK_PASS_REGDATE CHECK (
629 REGISTRATION_DATE IS NULL OR REGISTRATION_DATE <= CURRENT_DATE
630 )
631);
632
633CREATE TABLE TICKET_TYPE (
634 TICKET_TYPE_ID SERIAL NOT NULL,
635 NAME VARCHAR(50) NOT NULL DEFAULT 'Standard Ticket',
636 DESCRIPTION TEXT,
637 VALIDITY_DURATION INTEGER,
638 BASE_PRICE DECIMAL(8,2),
639
640 CONSTRAINT PK_TICKET_TYPE PRIMARY KEY (TICKET_TYPE_ID),
641
642 CONSTRAINT CHK_TT_VALIDITY CHECK (VALIDITY_DURATION IS NULL OR VALIDITY_DURATION > 0),
643 CONSTRAINT CHK_TT_PRICE CHECK (BASE_PRICE IS NULL OR BASE_PRICE >= 0)
644);
645
646CREATE TABLE FARE_RULE (
647 FARE_RULE_ID SERIAL NOT NULL,
648 ZONE_ID VARCHAR(50) NOT NULL,
649 TICKET_TYPE_ID INTEGER NOT NULL,
650 AGENCY_ID INTEGER NOT NULL,
651 ROUTE_ID VARCHAR(50),
652 PRICE DECIMAL(8,2) NOT NULL DEFAULT 0.00,
653 VALID_FROM DATE DEFAULT CURRENT_DATE,
654 VALID_TO DATE,
655
656 CONSTRAINT PK_FARE_RULE PRIMARY KEY (FARE_RULE_ID),
657
658 -- не може да се избрише зона со активни тарифни правила
659 CONSTRAINT FK_FARERULE_ZONE FOREIGN KEY (ZONE_ID) REFERENCES ZONE(ZONE_ID)
660 ON DELETE RESTRICT ON UPDATE CASCADE,
661
662 -- не може да се избрише тип на билет со активни тарифни правила
663 CONSTRAINT FK_FARERULE_TICKETTYPE FOREIGN KEY (TICKET_TYPE_ID) REFERENCES TICKET_TYPE(TICKET_TYPE_ID)
664 ON DELETE RESTRICT ON UPDATE CASCADE,
665
666 -- не може да се избрише агенција со активни тарифни правила
667 CONSTRAINT FK_FARERULE_AGENCY FOREIGN KEY (AGENCY_ID) REFERENCES AGENCY(AGENCY_ID)
668 ON DELETE RESTRICT ON UPDATE CASCADE,
669
670 -- ако се избрише линијата, правилото станува глобално (важи за сите линии)
671 CONSTRAINT FK_FARERULE_ROUTE FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE(ROUTE_ID)
672 ON DELETE SET NULL ON UPDATE CASCADE,
673
674 CONSTRAINT CHK_FARERULE_PRICE CHECK (PRICE >= 0),
675
676 CONSTRAINT CHK_FARERULE_DATES CHECK (
677 VALID_FROM IS NULL OR VALID_TO IS NULL OR VALID_FROM <= VALID_TO
678 )
679);
680
681CREATE TABLE TICKET (
682 TICKET_ID VARCHAR(50) NOT NULL,
683 PASSENGER_ID VARCHAR(50) NOT NULL,
684 TRIP_ID VARCHAR(50) NOT NULL,
685 TICKET_TYPE_ID INTEGER NOT NULL,
686 FARE_RULE_ID INTEGER,
687 DISCOUNT_ID INTEGER,
688 START_STOP_ID VARCHAR(50),
689 START_STOP_VT INTEGER,
690 END_STOP_ID VARCHAR(50),
691 END_STOP_VT INTEGER,
692 PURCHASE_DATE DATE DEFAULT CURRENT_DATE,
693 PRICE DECIMAL(8,2),
694 STATUS VARCHAR(20) DEFAULT 'VALID',
695
696 CONSTRAINT PK_TICKET PRIMARY KEY (TICKET_ID),
697
698 -- не може да се избрише патник кој има купени билети
699 CONSTRAINT FK_TICKET_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
700 ON DELETE RESTRICT ON UPDATE CASCADE,
701
702 -- билетот е финансиски запис, возењето не може да се избрише
703 CONSTRAINT FK_TICKET_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
704 ON DELETE RESTRICT ON UPDATE CASCADE,
705
706 -- не може да се избрише тип на билет додека постојат билети од тој тип
707 CONSTRAINT FK_TICKET_TICKETTYPE FOREIGN KEY (TICKET_TYPE_ID) REFERENCES TICKET_TYPE(TICKET_TYPE_ID)
708 ON DELETE RESTRICT ON UPDATE CASCADE,
709
710 -- ако се избрише тарифното правило, билетот ја задржува веќе пресметаната цена
711 CONSTRAINT FK_TICKET_FARERULE FOREIGN KEY (FARE_RULE_ID) REFERENCES FARE_RULE(FARE_RULE_ID)
712 ON DELETE SET NULL ON UPDATE CASCADE,
713
714 -- ако се избрише попустот, билетот ја задржува веќе пресметаната цена
715 CONSTRAINT FK_TICKET_DISCOUNT FOREIGN KEY (DISCOUNT_ID) REFERENCES DISCOUNT(DISCOUNT_ID)
716 ON DELETE SET NULL ON UPDATE CASCADE,
717
718 -- ако се избрише почетната постојка, билетот останува со NULL
719 CONSTRAINT FK_TICKET_STARTSTOP FOREIGN KEY (START_STOP_ID, START_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
720 ON DELETE SET NULL ON UPDATE CASCADE,
721
722 -- ако се избрише крајната постојка, билетот останува со NULL
723 CONSTRAINT FK_TICKET_ENDSTOP FOREIGN KEY (END_STOP_ID, END_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
724 ON DELETE SET NULL ON UPDATE CASCADE,
725
726 CONSTRAINT CHK_TICKET_STATUS CHECK (
727 STATUS IS NULL OR STATUS IN ('VALID', 'USED', 'EXPIRED', 'CANCELLED', 'REFUNDED')
728 ),
729
730 CONSTRAINT CHK_TICKET_PRICE CHECK (PRICE IS NULL OR PRICE >= 0)
731);
732
733CREATE TABLE PAYMENT (
734 PAYMENT_ID VARCHAR(50) NOT NULL,
735 TICKET_ID VARCHAR(50) NOT NULL,
736 PASSENGER_ID VARCHAR(50) NOT NULL,
737 AMOUNT DECIMAL(10,2) NOT NULL,
738 PAYMENT_METHOD VARCHAR(20) DEFAULT 'CASH',
739 PAYMENT_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
740 PAYMENT_STATUS VARCHAR(20) DEFAULT 'PENDING',
741 TRANSACTION_REFERENCE VARCHAR(100),
742
743 CONSTRAINT PK_PAYMENT PRIMARY KEY (PAYMENT_ID),
744
745 -- не може да се избрише билет кој има поврзано плаќање
746 CONSTRAINT FK_PAYMENT_TICKET FOREIGN KEY (TICKET_ID) REFERENCES TICKET(TICKET_ID)
747 ON DELETE RESTRICT ON UPDATE CASCADE,
748
749 -- не може да се избрише патник кој има плаќања
750 CONSTRAINT FK_PAYMENT_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
751 ON DELETE RESTRICT ON UPDATE CASCADE,
752
753 CONSTRAINT CHK_PAYMENT_AMOUNT CHECK (AMOUNT > 0),
754
755 CONSTRAINT CHK_PAYMENT_METHOD CHECK (
756 PAYMENT_METHOD IS NULL OR
757 PAYMENT_METHOD IN ('CASH', 'CARD', 'CONTACTLESS', 'MOBILE', 'ONLINE', 'VOUCHER')
758 ),
759
760 CONSTRAINT CHK_PAYMENT_STATUS CHECK (
761 PAYMENT_STATUS IS NULL OR
762 PAYMENT_STATUS IN ('PENDING', 'COMPLETED', 'FAILED', 'REFUNDED', 'CANCELLED')
763 )
764);
765
766CREATE TABLE SUBSCRIPTION_PASS (
767 PASS_ID VARCHAR(50) NOT NULL,
768 PASSENGER_ID VARCHAR(50) NOT NULL,
769 TICKET_TYPE_ID INTEGER,
770 ZONE_ID VARCHAR(50),
771 PASS_TYPE VARCHAR(50) DEFAULT 'MONTHLY',
772 START_DATE DATE DEFAULT CURRENT_DATE,
773 END_DATE DATE,
774 PRICE DECIMAL(8,2),
775 STATUS VARCHAR(20) DEFAULT 'ACTIVE',
776
777 CONSTRAINT PK_SUBSCRIPTION_PASS PRIMARY KEY (PASS_ID),
778
779 -- не може да се избрише патник со активна претплатна карта
780 CONSTRAINT FK_PASS_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
781 ON DELETE RESTRICT ON UPDATE CASCADE,
782
783 -- ако се избрише типот на билет, картата останува непроменета
784 CONSTRAINT FK_PASS_TICKETTYPE FOREIGN KEY (TICKET_TYPE_ID) REFERENCES TICKET_TYPE(TICKET_TYPE_ID)
785 ON DELETE SET NULL ON UPDATE CASCADE,
786
787 -- ако се избрише зоната, картата станува неограничена по зони
788 CONSTRAINT FK_PASS_ZONE FOREIGN KEY (ZONE_ID) REFERENCES ZONE(ZONE_ID)
789 ON DELETE SET NULL ON UPDATE CASCADE,
790
791 CONSTRAINT CHK_PASS_STATUS CHECK (
792 STATUS IS NULL OR STATUS IN ('ACTIVE', 'EXPIRED', 'SUSPENDED', 'CANCELLED')
793 ),
794
795 CONSTRAINT CHK_PASS_TYPE CHECK (
796 PASS_TYPE IS NULL OR
797 PASS_TYPE IN ('DAILY', 'WEEKLY', 'MONTHLY', 'ANNUAL', 'STUDENT', 'SENIOR')
798 ),
799
800 CONSTRAINT CHK_PASS_DATES CHECK (
801 START_DATE IS NULL OR END_DATE IS NULL OR START_DATE <= END_DATE
802 ),
803
804 CONSTRAINT CHK_PASS_PRICE CHECK (PRICE IS NULL OR PRICE >= 0)
805);
806
807CREATE TABLE NOTIFICATION (
808 NOTIFICATION_ID SERIAL NOT NULL,
809 PASSENGER_ID VARCHAR(50) NOT NULL,
810 TRIP_ID VARCHAR(50),
811 DELAY_LOG_ID INTEGER,
812 MESSAGE TEXT,
813 NOTIFICATION_TYPE VARCHAR(50) DEFAULT 'GENERAL',
814 SENT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
815 STATUS VARCHAR(20) DEFAULT 'PENDING',
816
817 CONSTRAINT PK_NOTIFICATION PRIMARY KEY (NOTIFICATION_ID),
818
819 -- не може да се избрише патник кој има известувања
820 CONSTRAINT FK_NOTIFICATION_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
821 ON DELETE RESTRICT ON UPDATE CASCADE,
822
823 -- ако се избрише возењето, известувањето станува општо (без конкретно возење)
824 CONSTRAINT FK_NOTIFICATION_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
825 ON DELETE SET NULL ON UPDATE CASCADE,
826
827 -- ако се избрише записот за доцнење, известувањето останува без референца
828 CONSTRAINT FK_NOTIFICATION_DELAYLOG FOREIGN KEY (DELAY_LOG_ID) REFERENCES DELAY_LOG(DELAY_ID)
829 ON DELETE SET NULL ON UPDATE CASCADE,
830
831 CONSTRAINT CHK_NOTIF_TYPE CHECK (
832 NOTIFICATION_TYPE IS NULL OR
833 NOTIFICATION_TYPE IN ('GENERAL', 'DELAY', 'CANCELLATION', 'PROMO', 'REMINDER', 'ALERT')
834 ),
835
836 CONSTRAINT CHK_NOTIF_STATUS CHECK (
837 STATUS IS NULL OR STATUS IN ('PENDING', 'SENT', 'FAILED', 'READ')
838 )
839);
840
841CREATE TABLE PASSENGER_DISCOUNT (
842 PASSENGER_DISCOUNT_ID SERIAL NOT NULL,
843 PASSENGER_ID VARCHAR(50) NOT NULL,
844 DISCOUNT_ID INTEGER NOT NULL,
845 OFFICER_ID VARCHAR(50),
846 ASSIGNED_DATE DATE DEFAULT CURRENT_DATE,
847 EXPIRY_DATE DATE,
848 STATUS VARCHAR(20) DEFAULT 'ACTIVE',
849
850 CONSTRAINT PK_PASSENGER_DISCOUNT PRIMARY KEY (PASSENGER_DISCOUNT_ID),
851
852 -- не може да се избрише патник кој има доделени попусти
853 CONSTRAINT FK_PD_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
854 ON DELETE RESTRICT ON UPDATE CASCADE,
855
856 -- не може да се избрише попуст кој е доделен на патници
857 CONSTRAINT FK_PD_DISCOUNT FOREIGN KEY (DISCOUNT_ID) REFERENCES DISCOUNT(DISCOUNT_ID)
858 ON DELETE RESTRICT ON UPDATE CASCADE,
859
860 -- ако се избрише службеникот, доделувањето останува евидентирано без службеник
861 CONSTRAINT FK_PD_OFFICER FOREIGN KEY (OFFICER_ID) REFERENCES DISCOUNT_OFFICER(OFFICER_ID)
862 ON DELETE SET NULL ON UPDATE CASCADE,
863
864 CONSTRAINT CHK_PD_STATUS CHECK (
865 STATUS IS NULL OR STATUS IN ('ACTIVE', 'EXPIRED', 'REVOKED', 'PENDING')
866 ),
867
868 CONSTRAINT CHK_PD_DATES CHECK (
869 ASSIGNED_DATE IS NULL OR EXPIRY_DATE IS NULL OR ASSIGNED_DATE <= EXPIRY_DATE
870 )
871);
872
873CREATE TABLE COMPLAINT (
874 COMPLAINT_ID SERIAL NOT NULL,
875 PASSENGER_ID VARCHAR(50) NOT NULL,
876 TRIP_ID VARCHAR(50) NOT NULL,
877 EMPLOYEE_ID VARCHAR(50),
878 COMPLAINT_TEXT TEXT,
879 COMPLAINT_DATE DATE DEFAULT CURRENT_DATE,
880 STATUS VARCHAR(20) DEFAULT 'OPEN',
881 RESOLUTION_NOTES TEXT,
882
883 CONSTRAINT PK_COMPLAINT PRIMARY KEY (COMPLAINT_ID),
884
885 -- не може да се избрише патник кој има поднесено поплаки
886 CONSTRAINT FK_COMPLAINT_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
887 ON DELETE RESTRICT ON UPDATE CASCADE,
888
889 -- не може да се избрише возење кое има поплаки (правна евиденција)
890 CONSTRAINT FK_COMPLAINT_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
891 ON DELETE RESTRICT ON UPDATE CASCADE,
892
893 -- ако се избрише вработениот, поплаката останува недоделена (NULL)
894 CONSTRAINT FK_COMPLAINT_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
895 ON DELETE SET NULL ON UPDATE CASCADE,
896
897 CONSTRAINT CHK_COMPLAINT_STATUS CHECK (
898 STATUS IS NULL OR
899 STATUS IN ('OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED', 'REJECTED')
900 )
901);