· 7 years ago · Oct 18, 2018, 05:16 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 ENUM('admin', 'user')
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 GENDER VARCHAR (100) NOT NULL,
77 PASSPORT_ID INT NOT NULL,
78 TICKED_ID INT,
79 --FOREIGN KEY (ROLE_ID) REFERENCES User_Role(ID)
80 -- ON DELETE CASCADE
81 -- ON UPDATE CASCADE,
82 --FOREIGN KEY (PASSPORT_ID) REFERENCES Passport(ID)
83 -- ON DELETE CASCADE
84 -- ON UPDATE CASCADE,
85 --FOREIGN KEY (TICKED_ID) REFERENCES Ticket(ID)
86 -- ON DELETE CASCADE
87 -- ON UPDATE CASCADE
88);
89
90CREATE TABLE Passport
91(
92 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
93 FIRST_NAME VARCHAR(100) NOT NULL,
94 LAST_NAME VARCHAR(100) NOT NULL,
95 PATRON VARCHAR(100) NOT NULL,
96 GENDER VARCHAR (100)NOT NULL,
97 SERIAL INT NOT NULL,
98 NUMBER INT NOT NULL,
99 FOREIGN KEY (ID) REFERENCES User(ID)
100);
101
102CREATE TABLE Aircraft
103(
104 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
105 MODEL VARCHAR(100) NOT NULL,
106 ECONOMY_CAPACITY INT NOT NULL,
107 BUSINESS_CAPACITY INT NOT NULL,
108 RANGE INT NOT NULL
109);
110CREATE TABLE Flight
111(
112 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
113 BASECOST DOUBLE NOT NULL,
114 DATETIME_ARR DATETIME NOT NULL,
115 DATETIME_DEP DATETIME NOT NULL,
116 FROM_ID INT NOT NULL,
117 TO_ID INT NOT NULL,
118 AIRCRAFT_ID INT NOT NULL,
119 BOUGHT_BUSINESS_SEAT INT NOT NULL,
120 BOUGHT_ECONOMY_SEAT INT NOT NULL,
121
122);
123CREATE TABLE Ticket
124(
125 ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
126 PASSPORT_ID INT NOT NULL,
127 FLIGHT_ID INT NOT NULL,
128 MARKUP_DISCOUNT_ID INT NOT NULL,
129 TOTALPRICE DOUBLE NOT NULL,
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
143ALTER TABLE User
144ADD FOREIGN KEY (Role_ID) REFERENCES User_Role(ID)
145 ON DELETE CASCADE
146 ON UPDATE CASCADE;
147 ALTER TABLE User
148ADD FOREIGN KEY (PASSPORT_ID) REFERENCES Passport(ID)
149 ON DELETE CASCADE
150 ON UPDATE CASCADE;
151ALTER TABLE User
152ADD FOREIGN KEY (TICKED_ID) REFERENCES Ticket(ID)
153 ON DELETE CASCADE
154 ON UPDATE CASCADE;
155
156
157ALTER TABLE Flight
158ADD FOREIGN KEY (FROM_ID) REFERENCES City(ID)
159 ON DELETE CASCADE
160 ON UPDATE CASCADE;
161ALTER TABLE Flight
162ADD FOREIGN KEY (TO_ID) REFERENCES City(ID)
163 ON DELETE CASCADE
164 ON UPDATE CASCADE;
165ALTER TABLE Flight
166ADD FOREIGN KEY (AIRCRAFT_ID) REFERENCES Aircraft(ID)
167 ON DELETE CASCADE
168 ON UPDATE CASCADE;
169
170
171ALTER TABLE Ticket
172 ADD FOREIGN KEY (PASSPORT_ID) REFERENCES Passport(ID)
173 ON DELETE CASCADE
174 ON UPDATE CASCADE;
175ALTER TABLE Ticket
176 ADD FOREIGN KEY (FLIGHT_ID) REFERENCES Flight(ID)
177 ON DELETE CASCADE
178 ON UPDATE CASCADE;
179ALTER TABLE Ticket
180 ADD FOREIGN KEY (MARKUP_DISCOUNT_ID) REFERENCES Markup_discount(ID)
181 ON DELETE CASCADE
182 ON UPDATE CASCADE;
183
184ALTER TABLE Airport
185ADD FOREIGN KEY (CITY_ID) REFERENCES City(ID)
186 ON DELETE CASCADE
187 ON UPDATE CASCADE;