DatabaseCreation: SBAirlines.ddl

File SBAirlines.ddl, 20.4 KB (added by 231044, 7 days ago)
Line 
1CREATE TABLE TicketStatus
2(
3 ID SERIAL PRIMARY KEY,
4 Name text UNIQUE
5);
6
7CREATE TABLE PilotRank
8(
9 ID SERIAL PRIMARY KEY,
10 Name text UNIQUE
11);
12
13CREATE TABLE Country
14(
15 ID SERIAL,
16 Name varchar(50) UNIQUE,
17 PRIMARY KEY (ID)
18);
19
20CREATE TABLE TimeZone
21(
22 ID SERIAL,
23 Name text UNIQUE,
24 TimeZoneOffset numeric,
25 PRIMARY KEY (ID)
26);
27
28CREATE TABLE Language
29(
30 Code char(2),
31 Name text,
32 PRIMARY KEY (Code)
33);
34
35CREATE TABLE FlightStatus
36(
37 ID SERIAL,
38 Name text UNIQUE,
39 PRIMARY KEY (ID)
40);
41
42CREATE TABLE SeatClass
43(
44 ID SERIAL,
45 Name text UNIQUE,
46 PRIMARY KEY (ID)
47);
48
49CREATE TABLE ShiftType
50(
51 ID SERIAL,
52 Name text UNIQUE,
53 PRIMARY KEY (ID)
54);
55
56CREATE TABLE TerminalType
57(
58 ID SERIAL,
59 Name text UNIQUE,
60 PRIMARY KEY (ID)
61);
62
63CREATE TABLE MealType
64(
65 ID SERIAL,
66 Name text UNIQUE,
67 PRIMARY KEY (ID)
68);
69
70CREATE TABLE BaggageStatusType
71(
72 ID SERIAL,
73 Name text UNIQUE,
74 PRIMARY KEY (ID)
75);
76
77CREATE TABLE AirplaneModel
78(
79 ID SERIAL,
80 Model varchar(100) UNIQUE,
81 Manufacturer varchar(50),
82 NumberOfSeats int4,
83 Width int4,
84 Height int4,
85 Length int4,
86 EmptyWeight int4,
87 MaxFlightDistance int4,
88 FuelCapacity int4,
89 RequiredAttendants int4,
90 SeatColumns int4,
91 SeatRows int4,
92 PRIMARY KEY (ID),
93 CHECK (RequiredAttendants > 0),
94 CHECK ( FuelCapacity > 0 ),
95 CHECK ( MaxFlightDistance > 100 ),
96 CHECK ( Width > 0 ),
97 CHECK ( Length > 0 ),
98 CHECK ( Height > 0 ),
99 CHECK ( EmptyWeight > 0 ),
100 CHECK ( NumberOfSeats > 0 ),
101 CHECK ( SeatRows > 0 ),
102 CHECK ( SeatColumns > 0 )
103);
104
105CREATE TABLE PartType
106(
107 ID SERIAL,
108 Name text,
109 Material text,
110 Price numeric(10, 2),
111 Weight int4,
112 PRIMARY KEY (ID),
113 CHECK ( Price > 0 ),
114 CHECK ( Weight > 0 )
115);
116
117CREATE TABLE BaggageType
118(
119 ID SERIAL,
120 Name text UNIQUE,
121 MaxWeight int4,
122 MaxHeight int4,
123 MaxWidth int4,
124 MaxLength int4,
125 PRIMARY KEY (ID),
126 CHECK ( MaxWeight > 0 ),
127 CHECK ( MaxHeight > 0 ),
128 CHECK ( MaxWidth > 0 ),
129 CHECK ( MaxLength > 0 )
130);
131
132ALTER TABLE BaggageType
133ADD COLUMN Price decimal;
134
135ALTER TABLE BaggageType
136ADD CHECK ( Price >= 0 );
137
138CREATE TABLE Meal
139(
140 ID SERIAL,
141 Protein text,
142 Side text,
143 Salad text,
144 Snack text,
145 SeatClassID int4,
146 MealTypeID int4,
147 PRIMARY KEY (ID),
148 FOREIGN KEY (SeatClassID) REFERENCES SeatClass (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
149 FOREIGN KEY (MealTypeID) REFERENCES MealType (ID) ON DELETE RESTRICT ON UPDATE CASCADE
150);
151
152CREATE TABLE Item
153(
154 ID SERIAL,
155 Name text,
156 Description text,
157 Price numeric(10, 2),
158 Brand text,
159 PRIMARY KEY (ID),
160 CHECK ( Price > 0 )
161);
162
163CREATE TABLE City
164(
165 ID SERIAL,
166 Name text,
167 CountryID int4,
168 TimeZoneID int4,
169 PRIMARY KEY (ID),
170 FOREIGN KEY (CountryID) REFERENCES Country (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
171 FOREIGN KEY (TimeZoneID) REFERENCES TimeZone (ID) ON DELETE RESTRICT ON UPDATE CASCADE
172);
173
174CREATE TABLE Airport
175(
176 ID SERIAL,
177 Code char(3) UNIQUE,
178 Name text,
179 CityID int4,
180 TimezoneID int4,
181 PRIMARY KEY (ID),
182 CHECK ( Code ~ '^[A-Z]{3}$' ),
183 FOREIGN KEY (CityID) REFERENCES City (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
184 FOREIGN KEY (TimezoneID) REFERENCES TimeZone (ID) ON DELETE RESTRICT ON UPDATE CASCADE
185);
186
187
188CREATE TABLE Airplane
189(
190 ID SERIAL,
191 ModelID int4,
192 RegistrationNumber char(6) UNIQUE,
193 ManufactureDate date DEFAULT now(),
194 Active bool DEFAULT TRUE,
195 TotalFlightHours int4 DEFAULT 0,
196 LastMaintenanceID int4,
197 PRIMARY KEY (ID),
198 FOREIGN KEY (ModelID) REFERENCES AirplaneModel (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
199 CHECK ( TotalFlightHours >= 0 ),
200 CHECK ( ManufactureDate <= now() AND ManufactureDate >= now() - INTERVAL '50 years'),
201 CHECK ( RegistrationNumber ~ '^[A-Z0-9]{6}$')
202);
203
204CREATE TABLE Maintenance
205(
206 ID SERIAL,
207 AirplaneID int4,
208 Date timestamp DEFAULT now(),
209 Comment text DEFAULT '',
210 PRIMARY KEY (ID),
211 CHECK ( Date <= now() )
212);
213
214ALTER TABLE Maintenance
215ADD CONSTRAINT AirplaneMaintenanceFK FOREIGN KEY (AirplaneID) REFERENCES Airplane (ID) ON DELETE RESTRICT ON UPDATE CASCADE;
216
217ALTER TABLE Airplane
218ADD CONSTRAINT LastMaintenanceFK FOREIGN KEY (LastMaintenanceID) REFERENCES Maintenance (ID) ON DELETE RESTRICT ON UPDATE CASCADE;
219
220CREATE TABLE PartOnAirplane
221(
222 ID SERIAL,
223 PartTypeID int4,
224 AirplaneID int4,
225 InUse bool DEFAULT TRUE,
226 PRIMARY KEY (ID),
227 FOREIGN KEY (PartTypeID) REFERENCES PartType (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
228 FOREIGN KEY (AirplaneID) REFERENCES Airplane (ID) ON DELETE RESTRICT ON UPDATE CASCADE
229);
230
231CREATE TABLE Repair
232(
233 ID SERIAL,
234 MaintenanceID int4,
235 PartOnAirplaneID int4,
236 Description text DEFAULT '',
237 Price numeric(10, 2),
238 PRIMARY KEY (ID),
239 FOREIGN KEY (MaintenanceID) REFERENCES Maintenance (ID) ON DELETE CASCADE ON UPDATE CASCADE,
240 FOREIGN KEY (PartOnAirplaneID) REFERENCES PartOnAirplane (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
241 CHECK ( Price >= 0 )
242);
243
244CREATE TABLE SeatConfiguration
245(
246 ID SERIAL,
247 Number int4,
248 Letter char(1),
249 ExitRow bool,
250 SeatClassID int4,
251 AirplaneModelID int4,
252 Price numeric(10, 2),
253 PRIMARY KEY (ID),
254 FOREIGN KEY (SeatClassID) REFERENCES SeatClass (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
255 FOREIGN KEY (AirplaneModelID) REFERENCES AirplaneModel (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
256 CHECK ( Number > 0 ),
257 CHECK ( Letter ~ '^[A-K]$' ),
258 CHECK ( Price > 0 ),
259 UNIQUE (AirplaneModelID, Letter, Number)
260);
261
262CREATE TABLE Terminal
263(
264 ID SERIAL,
265 AirportID int4,
266 Name text,
267 TerminalTypeID int4,
268 Capacity int4,
269 PRIMARY KEY (ID),
270 UNIQUE (AirportID, Name),
271 FOREIGN KEY (AirportID) REFERENCES Airport (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
272 FOREIGN KEY (TerminalTypeID) REFERENCES TerminalType (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
273 CHECK ( Capacity > 0 )
274);
275
276CREATE TABLE Runway
277(
278 ID SERIAL,
279 AirportID int4,
280 Number int4,
281 Length int4,
282 Width int4,
283 MaxWeight int4,
284 PRIMARY KEY (ID),
285 FOREIGN KEY (AirportID) REFERENCES Airport (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
286 UNIQUE (AirportID, Number),
287 CHECK ( Number > 0 ),
288 CHECK ( Length >= 800 ),
289 CHECK (
290 (Length >= 800 AND Length < 1200 AND Width >= 23) OR
291 (Length >= 1200 AND Length < 1800 AND Width >= 30) OR
292 (Length >= 1800 AND Width >= 45)
293 ),
294 CHECK ( MaxWeight >= 38000 )
295);
296
297CREATE TABLE Gate
298(
299 ID SERIAL,
300 TerminalID int4,
301 Number varchar(5),
302 Capacity int4,
303 PRIMARY KEY (ID),
304 UNIQUE (TerminalID, Number),
305 FOREIGN KEY (TerminalID) REFERENCES Terminal (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
306 CHECK ( Capacity > 0 )
307);
308
309CREATE TABLE Slot
310(
311 ID SERIAL,
312 GateID int4,
313 RunwayID int4,
314 Time time,
315 DayOfWeek char(3),
316 Active bool,
317 PRIMARY KEY (ID),
318 FOREIGN KEY (GateID) REFERENCES Gate (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
319 FOREIGN KEY (RunwayID) REFERENCES Runway (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
320 CHECK ( DayOfWeek IN ('MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN') ),
321 UNIQUE (GateID, Time, DayOfWeek),
322 UNIQUE (RunwayID, Time, DayOfWeek)
323);
324
325CREATE TABLE Employee
326(
327 ID SERIAL,
328 Name varchar(50) DEFAULT 'Unknown',
329 Surname varchar(50) DEFAULT 'Unknown',
330 SSN text UNIQUE,
331 DateOfBirth date,
332 DateOfEmployment date DEFAULT now(),
333 Gender char(1),
334 Salary numeric(10, 2),
335 PhoneNumber text DEFAULT 'Unknown',
336 Active bool DEFAULT TRUE,
337 PRIMARY KEY (ID),
338 CHECK ( Salary >= 0 ),
339 CHECK ( Gender IN ('F', 'M') ),
340 CHECK ( DateOfBirth <= now() - INTERVAL '18 years'),
341 CHECK ( DateOfEmployment > DateOfBirth AND DateOfEmployment <= now() )
342);
343
344CREATE TABLE AirlineEmployee
345(
346 EmployeeID int4,
347 PRIMARY KEY (EmployeeID),
348 FOREIGN KEY (EmployeeID) REFERENCES Employee (ID) ON DELETE RESTRICT ON UPDATE CASCADE
349);
350
351CREATE TABLE AirportEmployee
352(
353 EmployeeID int4,
354 AirportID int4,
355 PRIMARY KEY (EmployeeID),
356 FOREIGN KEY (EmployeeID) REFERENCES Employee (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
357 FOREIGN KEY (AirportID) REFERENCES Airport (ID) ON DELETE RESTRICT ON UPDATE CASCADE
358);
359
360CREATE TABLE Pilot
361(
362 EmployeeID int4,
363 FlightHours int4,
364 LicenceNumber text UNIQUE,
365 Rank int4,
366 PRIMARY KEY (EmployeeID),
367 FOREIGN KEY (EmployeeID) REFERENCES AirlineEmployee (EmployeeID) ON DELETE RESTRICT ON UPDATE CASCADE,
368 FOREIGN KEY (Rank) REFERENCES PilotRank (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
369 CHECK ( FlightHours >= 0 ),
370 CHECK ( LicenceNumber ~ '^[A-Z][0-9]{9}$' )
371);
372
373CREATE TABLE FlightAttendant
374(
375 EmployeeID int4,
376 CertificationLevel int4,
377 PrimaryLanguage char(2),
378 SecondaryLanguage char(2),
379 PRIMARY KEY (EmployeeID),
380 FOREIGN KEY (EmployeeID) REFERENCES AirlineEmployee (EmployeeID) ON DELETE RESTRICT ON UPDATE CASCADE,
381 FOREIGN KEY (PrimaryLanguage) REFERENCES Language (Code) ON DELETE RESTRICT ON UPDATE CASCADE,
382 FOREIGN KEY (SecondaryLanguage) REFERENCES Language (Code) ON DELETE RESTRICT ON UPDATE CASCADE,
383 CHECK ( CertificationLevel >= 1 AND CertificationLevel <= 5 ),
384 CHECK ( PrimaryLanguage <> SecondaryLanguage )
385);
386
387CREATE TABLE TicketingAgent
388(
389 EmployeeID int4,
390 CounterNumber int4,
391 PRIMARY KEY (EmployeeID),
392 FOREIGN KEY (EmployeeID) REFERENCES AirportEmployee (EmployeeID) ON DELETE RESTRICT ON UPDATE CASCADE,
393 CHECK ( CounterNumber > 0 )
394);
395
396CREATE TABLE BaggageHandler
397(
398 EmployeeID int4,
399 ShiftTypeID int4,
400 PRIMARY KEY (EmployeeID),
401 FOREIGN KEY (EmployeeID) REFERENCES AirportEmployee (EmployeeID) ON DELETE RESTRICT ON UPDATE CASCADE,
402 FOREIGN KEY (ShiftTypeID) REFERENCES ShiftType (ID) ON DELETE RESTRICT ON UPDATE CASCADE
403);
404
405CREATE TABLE Mechanic
406(
407 EmployeeID int4,
408 HangarNumber text,
409 PRIMARY KEY (EmployeeID),
410 FOREIGN KEY (EmployeeID) REFERENCES AirportEmployee (EmployeeID) ON DELETE RESTRICT ON UPDATE CASCADE
411);
412
413CREATE TABLE FlightCertification
414(
415 ID SERIAL,
416 AirlineEmployeeID int4,
417 AirplaneModelID int4,
418 CertificationType text,
419 TestDate date DEFAULT now(),
420 ExpirationDate date,
421 Notes text DEFAULT '',
422 PRIMARY KEY (ID),
423 FOREIGN KEY (AirplaneModelID) REFERENCES AirplaneModel (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
424 FOREIGN KEY (AirlineEmployeeID) REFERENCES AirlineEmployee (EmployeeID) ON DELETE RESTRICT ON UPDATE CASCADE,
425 CHECK ( CertificationType IN ('Pilot', 'Flight Attendant') ),
426 CHECK ( TestDate <= now() AND TestDate < ExpirationDate ),
427 CHECK ( ExpirationDate <= TestDate + INTERVAL '1 year')
428);
429
430CREATE TABLE MedicalCheckup
431(
432 ID SERIAL,
433 CheckupDate date DEFAULT now(),
434 ExpirationDate date,
435 Notes text DEFAULT ' ',
436 EmployeeID int4,
437 PRIMARY KEY (ID),
438 FOREIGN KEY (EmployeeID) REFERENCES AirlineEmployee (EmployeeID) ON DELETE RESTRICT ON UPDATE CASCADE,
439 CHECK ( CheckupDate <= now() AND CheckupDate < ExpirationDate ),
440 CHECK ( ExpirationDate <= CheckupDate + INTERVAL '1 year' )
441);
442
443CREATE TABLE RepairCertification
444(
445 ID SERIAL,
446 ExpirationDate date,
447 TestDate date DEFAULT now(),
448 Notes text DEFAULT ' ',
449 AirplaneModelID int4,
450 MechanicID int4,
451 PRIMARY KEY (ID),
452 FOREIGN KEY (AirplaneModelID) REFERENCES AirplaneModel (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
453 FOREIGN KEY (MechanicID) REFERENCES Mechanic (EmployeeID) ON DELETE RESTRICT ON UPDATE CASCADE,
454 CHECK ( TestDate <= now() AND TestDate < ExpirationDate ),
455 CHECK ( ExpirationDate <= TestDate + INTERVAL '1 year')
456);
457
458CREATE TABLE WorksOnMaintenance
459(
460 MaintenanceID int4,
461 MechanicEmployeeID int4,
462 PRIMARY KEY (MaintenanceID, MechanicEmployeeID),
463 FOREIGN KEY (MaintenanceID) REFERENCES Maintenance (ID) ON DELETE CASCADE ON UPDATE CASCADE,
464 FOREIGN KEY (MechanicEmployeeID) REFERENCES Mechanic (EmployeeID) ON DELETE CASCADE ON UPDATE CASCADE
465);
466
467CREATE TABLE Passenger
468(
469 ID SERIAL,
470 Name text DEFAULT 'Unknown',
471 Surname text DEFAULT 'Unknown',
472 DateOfBirth date,
473 Gender char(1),
474 Email text UNIQUE,
475 MilePoints int4 DEFAULT 0,
476 AssistanceRequirements text DEFAULT 'None',
477 SSN text UNIQUE,
478 PhoneNumber text DEFAULT 'Unknown',
479 PRIMARY KEY (ID),
480 CHECK ( Gender IN ('F', 'M') ),
481 CHECK ( DateOfBirth <= now() - INTERVAL '14 days'),
482 CHECK ( Email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' ),
483 CHECK ( MilePoints >= 0 )
484);
485
486CREATE TABLE Passport
487(
488 ID SERIAL PRIMARY KEY,
489 PassengerID int4,
490 PassportNumber text,
491 Expiration date,
492 IssuingCountry int4,
493 FOREIGN KEY (PassengerID) REFERENCES Passenger (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
494 FOREIGN KEY (IssuingCountry) REFERENCES Country (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
495 CHECK ( Expiration > now() ),
496 UNIQUE (PassportNumber, IssuingCountry)
497);
498
499CREATE TABLE ScheduledFlight
500(
501 ID SERIAL,
502 Departure int4,
503 Arrival int4,
504 Distance int4,
505 Active bool DEFAULT True,
506 PRIMARY KEY (ID),
507 FOREIGN KEY (Departure) REFERENCES Slot (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
508 FOREIGN KEY (Arrival) REFERENCES Slot (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
509 CHECK ( Distance > 0 ),
510 CHECK ( Departure <> Arrival )
511);
512
513CREATE TABLE Flight
514(
515 ID SERIAL,
516 FlightNumber varchar(6) UNIQUE,
517 Departure timestamp,
518 Arrival timestamp,
519 ScheduleID int4,
520 AirplaneID int4,
521 StatusID int4,
522 Pilot int4,
523 Copilot int4,
524 ActualGateDepartureID int4,
525 ActualGateArrivalID int4,
526 ActualDepartureSlot int4,
527 ActualArrivalSlot int4,
528 PRIMARY KEY (ID),
529 FOREIGN KEY (ScheduleID) REFERENCES ScheduledFlight (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
530 FOREIGN KEY (ActualGateArrivalID) REFERENCES Gate (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
531 FOREIGN KEY (ActualGateDepartureID) REFERENCES Gate (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
532 FOREIGN KEY (ActualDepartureSlot) REFERENCES Slot (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
533 FOREIGN KEY (ActualArrivalSlot) REFERENCES Slot (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
534 FOREIGN KEY (AirplaneID) REFERENCES Airplane (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
535 FOREIGN KEY (StatusID) REFERENCES FlightStatus (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
536 FOREIGN KEY (Pilot) REFERENCES Pilot (EmployeeID) ON DELETE RESTRICT ON UPDATE CASCADE,
537 FOREIGN KEY (Copilot) REFERENCES Pilot (EmployeeID) ON DELETE RESTRICT ON UPDATE CASCADE,
538 CHECK ( FlightNumber ~ '^[A-Z]{2}[0-9]{1,4}$' ),
539 CHECK ( Arrival >= Departure ),
540 CHECK ( Pilot <> Copilot )
541);
542
543CREATE TABLE RecommendedAirplaneModel
544(
545 ScheduleID int4,
546 AirplaneModelID int4,
547 PRIMARY KEY (ScheduleID, AirplaneModelID),
548 FOREIGN KEY (ScheduleID) REFERENCES ScheduledFlight (ID) ON DELETE CASCADE ON UPDATE CASCADE,
549 FOREIGN KEY (AirplaneModelID) REFERENCES AirplaneModel (ID) ON DELETE RESTRICT ON UPDATE CASCADE
550);
551
552CREATE TABLE CabinCrew
553(
554 FlightID int4,
555 FlightAttendantID int4,
556 PRIMARY KEY (FlightID, FlightAttendantID),
557 FOREIGN KEY (FlightID) REFERENCES Flight (ID) ON DELETE CASCADE ON UPDATE CASCADE,
558 FOREIGN KEY (FlightAttendantID) REFERENCES FlightAttendant (EmployeeID) ON DELETE CASCADE ON UPDATE CASCADE
559);
560
561CREATE TABLE Reservation
562(
563 ID SERIAL,
564 PassengerID int4,
565 CreatedAt timestamp DEFAULT now(),
566 PRIMARY KEY (ID),
567 FOREIGN KEY (PassengerID) REFERENCES Passenger (ID) ON DELETE RESTRICT ON UPDATE CASCADE
568);
569
570CREATE TABLE Seat
571(
572 ID SERIAL,
573 SeatConfigurationID int4,
574 FlightID int4,
575 Reserved bool DEFAULT FALSE,
576 PRIMARY KEY (ID),
577 FOREIGN KEY (SeatConfigurationID) REFERENCES SeatConfiguration (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
578 FOREIGN KEY (FlightID) REFERENCES Flight (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
579 UNIQUE (SeatConfigurationID, FlightID)
580);
581
582CREATE TABLE Ticket
583(
584 ID SERIAL,
585 SeatID int4,
586 PassengerID int4,
587 BasePrice numeric(10, 2),
588 FinalPrice int4,
589 SeatClassID int4,
590 TicketStatusID int4 DEFAULT 1,
591 ReservationID int4,
592 FlightID int4,
593 PurchaseDate timestamp DEFAULT now(),
594 PRIMARY KEY (ID),
595 FOREIGN KEY (SeatID) REFERENCES Seat (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
596 FOREIGN KEY (PassengerID) REFERENCES Passenger (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
597 FOREIGN KEY (ReservationID) REFERENCES Reservation (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
598 FOREIGN KEY (FlightID) REFERENCES Flight (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
599 FOREIGN KEY (SeatClassID) REFERENCES SeatClass (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
600 FOREIGN KEY (TicketStatusID) REFERENCES TicketStatus (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
601 CHECK ( BasePrice > 0 ),
602 CHECK ( FinalPrice >= BasePrice ),
603 UNIQUE ( PassengerID, FlightID )
604);
605
606CREATE TABLE Baggage
607(
608 ID SERIAL,
609 BaggageTypeID int4,
610 Weight int4,
611 Height int4,
612 Length int4,
613 Width int4,
614 TicketID int4,
615 PRIMARY KEY (ID),
616 FOREIGN KEY (BaggageTypeID) REFERENCES BaggageType (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
617 FOREIGN KEY (TicketID) REFERENCES Ticket (ID) ON DELETE CASCADE ON UPDATE CASCADE,
618 CHECK ( Weight > 0 ),
619 CHECK ( Height > 0 ),
620 CHECK ( Length > 0 ),
621 CHECK ( Width > 0 )
622);
623
624CREATE TABLE BaggageStatus
625(
626 ID SERIAL,
627 BaggageStatusTypeID int4,
628 TimeStamp timestamp DEFAULT now(),
629 BaggageID int4,
630 AirportID int4,
631 PRIMARY KEY (ID),
632 FOREIGN KEY (BaggageStatusTypeID) REFERENCES BaggageStatusType (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
633 FOREIGN KEY (BaggageID) REFERENCES Baggage (ID) ON DELETE CASCADE ON UPDATE CASCADE,
634 FOREIGN KEY (AirportID) REFERENCES Airport (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
635 CHECK ( TimeStamp <= now() )
636);
637
638CREATE TABLE MealRequest
639(
640 TicketID int4,
641 MealID int4,
642 SpecialRequest text,
643 PRIMARY KEY (TicketID, MealID),
644 FOREIGN KEY (TicketID) REFERENCES Ticket (ID) ON DELETE CASCADE ON UPDATE CASCADE,
645 FOREIGN KEY (MealID) REFERENCES Meal (ID) ON DELETE CASCADE ON UPDATE CASCADE
646);
647
648CREATE TABLE ServedMeals
649(
650 FlightID int4,
651 MealID int4,
652 Quantity int4,
653 Served int4 DEFAULT 0,
654 PRIMARY KEY (FlightID, MealID),
655 FOREIGN KEY (FlightID) REFERENCES Flight (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
656 FOREIGN KEY (MealID) REFERENCES Meal (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
657 CHECK ( Quantity > 0 ),
658 CHECK ( Served <= Quantity ),
659 CHECK ( Served >= 0 )
660);
661
662CREATE TABLE ItemsSold
663(
664 FlightID int4,
665 ItemID int4,
666 Quantity int4,
667 PRIMARY KEY (FlightID, ItemID),
668 FOREIGN KEY (FlightID) REFERENCES Flight (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
669 FOREIGN KEY (ItemID) REFERENCES Item (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
670 CHECK ( Quantity > 0 )
671);
672
673CREATE TABLE AdditionalCargo
674(
675 ID SERIAL,
676 Description text DEFAULT ' ',
677 Weight int4,
678 Height int4,
679 Width int4,
680 Length int4,
681 FlightID int4,
682 PRIMARY KEY (ID),
683 FOREIGN KEY (FlightID) REFERENCES Flight (ID) ON DELETE CASCADE ON UPDATE CASCADE,
684 CHECK ( Weight > 0 ),
685 CHECK ( Height > 0 ),
686 CHECK ( Width > 0 ),
687 CHECK ( Length > 0 )
688);
689
690CREATE TABLE CanceledFlight
691(
692 FlightID int4,
693 Reason text,
694 CancellationTime timestamp DEFAULT now(),
695 PRIMARY KEY (FlightID),
696 FOREIGN KEY (FlightID) REFERENCES Flight (ID) ON DELETE RESTRICT ON UPDATE CASCADE
697);
698
699
700