· 7 years ago · Oct 18, 2018, 02:18 PM
1-- DROP TABLE IF EXISTS `User`;
2-- DROP TABLE IF EXISTS `Ticket`;
3-- DROP TABLE IF EXISTS `Role`;
4--
5-- CREATE TABLE User(
6-- USER_ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
7-- USER_NAME VARCHAR(100) NOT NULL ,
8-- USER_MAIL VARCHAR(100) NOT NULL ,
9-- USER_ROLE VARCHAR(100) NOT NULL ,
10-- USER_PASSWORD VARCHAR(100) NOT NULL
11-- );
12--
13-- CREATE TABLE Ticket(
14-- TICKET_ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
15-- USER_ID INT NOT NULL ,
16-- TICKET_COST DOUBLE NOT NULL ,
17-- TICKET_STARTPOINT VARCHAR(100) NOT NULL ,
18-- TICKET_DESTINATIONPOINT VARCHAR(100) NOT NULL ,
19-- TICKET_DATE VARCHAR(100) NOT NULL ,
20-- FOREIGN KEY (USER_ID) REFERENCES User(USER_ID)
21-- ON DELETE CASCADE
22-- ON UPDATE CASCADE
23-- );
24--
25-- CREATE TABLE Role (
26-- ROLE_ID INT ,
27-- USER_ID INT NOT NULL ,
28-- ROLE VARCHAR(100) NOT NULL ,
29-- FOREIGN KEY (USER_ID) REFERENCES User(USER_ID)
30-- ON DELETE CASCADE
31-- ON UPDATE CASCADE
32-- );
33--
34--
35
36DROP TABLE IF EXISTS `User`;
37DROP TABLE IF EXISTS `Ticket`;
38DROP TABLE IF EXISTS `User_Role`;
39DROP TABLE IF EXISTS `Passport`;
40DROP TABLE IF EXISTS `Ticket`;
41DROP TABLE IF EXISTS `Markup_discount`;
42DROP TABLE IF EXISTS `Flight`;
43DROP TABLE IF EXISTS `City`;
44DROP TABLE IF EXISTS `Airport`;
45DROP TABLE IF EXISTS `Aircraft`;
46
47
48CREATE TABLE City
49(
50 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
51 NAME VARCHAR(100) NOT NULL,
52 COUNTRY VARCHAR(100) NOT NULL
53);
54CREATE TABLE Markup_discount
55(
56 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
57 ISBAGGAGE BIT NOT NULL,
58 ISPRIORITY BIT NOT NULL,
59 ISBUSINESS BIT NOT NULL
60
61);
62
63CREATE TABLE User_Role
64(
65 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
66 ROLE VARCHAR(100) NOT NULL
67);
68
69CREATE TABLE User
70(
71 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
72 NAME VARCHAR (100) NOT NULL,
73 MAIL VARCHAR(100) NOT NULL,
74 PASSWORD VARCHAR(100) NOT NULL,
75 ROLE_ID INT NOT NULL,
76 PASSPORT_ID INT NOT NULL,
77 TICKED_ID INT,
78 --FOREIGN KEY (ROLE_ID) REFERENCES User_Role(ID)
79 -- ON DELETE CASCADE
80 -- ON UPDATE CASCADE,
81 --FOREIGN KEY (PASSPORT_ID) REFERENCES Passport(ID)
82 -- ON DELETE CASCADE
83 -- ON UPDATE CASCADE,
84 --FOREIGN KEY (TICKED_ID) REFERENCES Ticket(ID)
85 -- ON DELETE CASCADE
86 -- ON UPDATE CASCADE
87);
88
89CREATE TABLE Passport
90(
91 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
92 FIRST_NAME VARCHAR(100) NOT NULL,
93 LAST_NAME VARCHAR(100) NOT NULL,
94 PATRON VARCHAR(100) NOT NULL,
95 SERIAL INT NOT NULL,
96 NUMBER INT NOT NULL,
97 FOREIGN KEY (ID) REFERENCES User(ID)
98
99);
100
101CREATE TABLE Aircraft
102(
103 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
104 MODEL VARCHAR(100) NOT NULL,
105 ECONOMY_CAPACITY INT NOT NULL,
106 BUSINESS_CAPACITY INT NOT NULL,
107 RANGE INT NOT NULL
108);
109CREATE TABLE Flight
110(
111 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
112 BASECOST DOUBLE NOT NULL,
113 DATETIME_ARR DATETIME NOT NULL,
114 DATETIME_DEP DATETIME NOT NULL,
115 FROM_ID INT NOT NULL,
116 TO_ID INT NOT NULL,
117 AIRCRAFT_ID INT NOT NULL,
118 BOUGHT_BUSINESS_SEAT INT NOT NULL,
119 BOUGHT_ECONOMY_SEAT INT NOT NULL,
120
121);
122CREATE TABLE Ticket
123(
124 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
125 PASSPORT_ID INT NOT NULL,
126 FLIGHT_ID INT NOT NULL,
127 MARKUP_DISCOUNT_ID INT NOT NULL,
128 TOTALPRICE DOUBLE NOT NULL,
129
130);
131
132
133CREATE TABLE Airport
134(
135 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
136 NAME VARCHAR(100) NOT NULL,
137 CITY_ID INT NOT NULL,
138 LONGITUDE DOUBLE NOT NULL,
139 LATITUDE DOUBLE NOT NULL,
140
141);
142
143
144ALTER TABLE User
145ADD FOREIGN KEY (Role_ID) REFERENCES User_Role(ID)
146 ON DELETE CASCADE
147 ON UPDATE CASCADE;
148 ALTER TABLE User
149ADD FOREIGN KEY (PASSPORT_ID) REFERENCES Passport(ID)
150 ON DELETE CASCADE
151 ON UPDATE CASCADE;
152ALTER TABLE User
153ADD FOREIGN KEY (TICKED_ID) REFERENCES Ticket(ID)
154 ON DELETE CASCADE
155 ON UPDATE CASCADE;
156
157
158ALTER TABLE Flight
159ADD FOREIGN KEY (FROM_ID) REFERENCES City(ID)
160 ON DELETE CASCADE
161 ON UPDATE CASCADE;
162ALTER TABLE Flight
163ADD FOREIGN KEY (TO_ID) REFERENCES City(ID)
164 ON DELETE CASCADE
165 ON UPDATE CASCADE;
166ALTER TABLE Flight
167ADD FOREIGN KEY (AIRCRAFT_ID) REFERENCES Aircraft(ID)
168 ON DELETE CASCADE
169 ON UPDATE CASCADE;
170
171
172ALTER TABLE Ticket
173 ADD FOREIGN KEY (PASSPORT_ID) REFERENCES Passport(ID)
174 ON DELETE CASCADE
175 ON UPDATE CASCADE;
176ALTER TABLE Ticket
177 ADD FOREIGN KEY (FLIGHT_ID) REFERENCES Flight(ID)
178 ON DELETE CASCADE
179 ON UPDATE CASCADE;
180ALTER TABLE Ticket
181 ADD FOREIGN KEY (MARKUP_DISCOUNT_ID) REFERENCES Markup_discount(ID)
182 ON DELETE CASCADE
183 ON UPDATE CASCADE;
184
185ALTER TABLE Airport
186ADD FOREIGN KEY (CITY_ID) REFERENCES City(ID)
187 ON DELETE CASCADE
188 ON UPDATE CASCADE;