Логички и физички дизајн - Креирање база податоци: ddlscript-create.sql

File ddlscript-create.sql, 3.6 KB (added by 173067, 10 days ago)
Line 
1create table application_user (
2 userid INT PRIMARY KEY,
3 name varchar(255),
4 surname varchar(255),
5 email varchar(255),
6 password varchar(255),
7 phone_number varchar(20),
8 date_joined date
9 );
10
11 create table destination(
12 destinationid int primary key,
13 name varchar(255),
14 country varchar(255),
15 description text,
16 popular_attraction text,
17 best_time_to_visit varchar(255)
18 );
19
20 create table airport(
21 airportid int primary key,
22 name varchar(255),
23 city int,
24 country varchar(255),
25 code varchar(3),
26 foreign key (city) references destination(destinationid)
27 );
28
29 create table flight (
30 flightid int primary key,
31 flight_number varchar(50),
32 departure_airport int,
33 arrival_airport int,
34 departure_time time,
35 arrival_time time,
36 price decimal(10,2),
37 available_seats int,
38 foreign key (arrival_airport) references airport(airportid),
39 foreign key (departure_airport) references airport(airportid)
40 );
41
42 create table booking(
43 bookingid int primary key ,
44 userid int,
45 flightid int,
46 booking_date date,
47 payment_status varchar(20) check(booking.payment_status in ('PENDING','COMPLETED','CANCELLED')),
48 total_cost decimal(10,2),
49 seat_number int,
50 foreign key (userid) references application_user(userid),
51 foreign key (flightid) references flight(flightid)
52 );
53
54 create table review (
55 reviewid int primary key ,
56 userid int,
57 targetid int,
58 review_comment text,
59 rating int,
60 date date,
61 foreign key (userid) references application_user(userid),
62 constraint FK_targetid_flight foreign key (targetid) references flight(flightid) on delete cascade ,
63 constraint FK_targetid_destination foreign key (targetid) references destination(destinationid) on delete cascade
64 );
65
66 create table payment (
67 paymentid int primary key,
68 bookingid int,
69 userid int,
70 payment_method varchar(20) check (payment.payment_method in ('PAYPAL', 'DEBIT', 'CREDIT')),
71 amount decimal(10,2),
72 transaction_date date,
73 payment_status varchar(20) check (payment.payment_status in ('PENDING', 'COMPLETED', 'CANCELLED')),
74 foreign key (bookingid) references booking(bookingid),
75 foreign key (userid) references application_user(userid)
76 );
77
78 create table notification(
79 notificationid int primary key,
80 userid int,
81 message text,
82 type varchar(20) check ( Type in ('BOOKING_CONFIRMATION','FLIGHT_DELAY','GENERAL_UPDATE')),
83 date_sent date,
84 foreign key (userid) references application_user(userid)
85 );
86
87 create table wishlist(
88 wishlistid int primary key ,
89 userid int,
90 targetid int,
91 date_added date,
92 foreign key (userid) references application_user(userid),
93 constraint FK_targetid_flight foreign key (targetid) references flight(flightid) on delete cascade ,
94 constraint FK_targetid_destination foreign key (targetid) references destination(destinationid) on delete cascade
95 );
96
97 create table administrator
98 (
99 adminid int primary key,
100 email varchar(255) unique
101
102 );
103
104 create table supportticket(
105 ticketid int primary key ,
106 userid int,
107 subjectid varchar(255),
108 description text,
109 status varchar(20) check ( Status in ('OPEN','IN_PROGRESS','RESOLVED')),
110 date_created date,
111 date_resolved date,
112 assigned_to int,
113 foreign key (userid) references application_user(userid),
114 foreign key (assigned_to) references administrator(adminid)
115 );