· 6 years ago · May 10, 2019, 11:32 PM
1/* ---------------------------------------------------- */
2/* Generated by Enterprise Architect Version 13.5 */
3/* Created On : 10-May-2019 6:52:50 PM */
4/* DBMS : MySql */
5/* ---------------------------------------------------- */
6
7SET FOREIGN_KEY_CHECKS=0
8;
9
10/* Drop Tables */
11
12DROP TABLE IF EXISTS `groupProj_admin` CASCADE
13;
14
15DROP TABLE IF EXISTS `groupProj_country` CASCADE
16;
17
18DROP TABLE IF EXISTS `groupProj_customer` CASCADE
19;
20
21DROP TABLE IF EXISTS `groupProj_flight` CASCADE
22;
23
24DROP TABLE IF EXISTS `groupProj_flightType` CASCADE
25;
26
27DROP TABLE IF EXISTS `groupProj_location` CASCADE
28;
29
30DROP TABLE IF EXISTS `groupProj_payment` CASCADE
31;
32
33DROP TABLE IF EXISTS `groupProj_reservation` CASCADE
34;
35
36/* Create Tables */
37
38CREATE TABLE `groupProj_admin`
39(
40 `adminID` INT NOT NULL,
41 `firstName` VARCHAR(50) NULL,
42 `lastName` VARCHAR(50) NULL,
43 `userName` VARCHAR(50) NULL,
44 `password` VARCHAR(50) NULL,
45 CONSTRAINT `PK_groupProj_staff` PRIMARY KEY (`adminID` ASC)
46)
47
48;
49
50CREATE TABLE `groupProj_country`
51(
52 `countryID` INT NOT NULL,
53 `countryName` VARCHAR(50) NULL,
54 CONSTRAINT `PK_groupProj_country` PRIMARY KEY (`countryID` ASC)
55)
56
57;
58
59CREATE TABLE `groupProj_customer`
60(
61 `customerID` INT NOT NULL,
62 `firstName` VARCHAR(50) NULL,
63 `lastName` VARCHAR(50) NULL,
64 `gender` VARCHAR(50) NULL,
65 `birthDate` VARCHAR(50) NULL,
66 `address` VARCHAR(50) NULL,
67 `email` VARCHAR(50) NULL,
68 `emergencyPhone` VARCHAR(50) NULL,
69 CONSTRAINT `PK_groupProj_customer` PRIMARY KEY (`customerID` ASC)
70)
71
72;
73
74CREATE TABLE `groupProj_flight`
75(
76 `flightID` INT NOT NULL,
77 `origin` INT NULL,
78 `destination` INT NULL,
79 `departureDate` DATE NULL,
80 `departureTime` TIME NULL,
81 `arrivalDate` DATE NULL,
82 `arrivalTime` TIME NULL,
83 `cost` DOUBLE(10,2) NULL,
84 `totalSeats` INT NULL,
85 `remainingSeats` INT NULL,
86 `flightType` INT NULL,
87 `fightTypeID` INT NULL,
88 CONSTRAINT `PK_FLIGHT` PRIMARY KEY (`flightID` ASC)
89)
90
91;
92
93CREATE TABLE `groupProj_flightType`
94(
95 `fightTypeID` INT NOT NULL,
96 `flightTypeName` VARCHAR(50) NULL,
97 CONSTRAINT `PK_groupProj_flightType` PRIMARY KEY (`fightTypeID` ASC)
98)
99
100;
101
102CREATE TABLE `groupProj_location`
103(
104 `locationID` INT NOT NULL,
105 `countryID` INT NULL,
106 `airportName` VARCHAR(50) NULL,
107 CONSTRAINT `PK_groupProj_location` PRIMARY KEY (`locationID` ASC)
108)
109
110;
111
112CREATE TABLE `groupProj_payment`
113(
114 `paymentID` INT NOT NULL,
115 `cardNumber` VARCHAR(50) NULL,
116 `expiryDate` DATE NULL,
117 `securityNumber` INT NULL,
118 `cardholderName` VARCHAR(50) NULL,
119 `nationality` VARCHAR(50) NULL,
120 `billingAddress` VARCHAR(50) NULL,
121 CONSTRAINT `PK_groupProj_payment` PRIMARY KEY (`paymentID` ASC)
122)
123
124;
125
126CREATE TABLE `groupProj_reservation`
127(
128 `flightID` INT NOT NULL,
129 `customerID` INT NOT NULL,
130 `bookingCode` VARCHAR(50) NOT NULL,
131 `paymentID` INT NULL,
132 CONSTRAINT `PK_groupProj_reservation` PRIMARY KEY (`flightID` ASC, `customerID` ASC)
133)
134
135;
136
137/* Create Primary Keys, Indexes, Uniques, Checks */
138
139
140
141
142ALTER TABLE `groupProj_reservation`
143 ADD CONSTRAINT `uniqueBookID` UNIQUE (`bookingCode` ASC)
144;
145
146
147
148
149
150/* Create Foreign Key Constraints */
151
152ALTER TABLE `groupProj_flight`
153 ADD CONSTRAINT `FK_groupProj_flight_groupProj_flightType`
154 FOREIGN KEY (`fightTypeID`) REFERENCES `groupProj_flightType` (`fightTypeID`) ON DELETE Restrict ON UPDATE Restrict
155;
156
157ALTER TABLE `groupProj_flight`
158 ADD CONSTRAINT `FK_groupProj_flight_groupProj_location_02`
159 FOREIGN KEY (`origin`) REFERENCES `groupProj_location` (`locationID`) ON DELETE Restrict ON UPDATE Restrict
160;
161
162ALTER TABLE `groupProj_flight`
163 ADD CONSTRAINT `FK_groupProj_flight_groupProj_location_03`
164 FOREIGN KEY (`destination`) REFERENCES `groupProj_location` (`locationID`) ON DELETE Restrict ON UPDATE Restrict
165;
166
167ALTER TABLE `groupProj_location`
168 ADD CONSTRAINT `FK_groupProj_location_groupProj_country`
169 FOREIGN KEY (`countryID`) REFERENCES `groupProj_country` (`countryID`) ON DELETE Restrict ON UPDATE Restrict
170;
171
172ALTER TABLE `groupProj_reservation`
173 ADD CONSTRAINT `FK_groupProj_reservation_groupProj_customer`
174 FOREIGN KEY (`customerID`) REFERENCES `groupProj_customer` (`customerID`) ON DELETE Restrict ON UPDATE Restrict
175;
176
177ALTER TABLE `groupProj_reservation`
178 ADD CONSTRAINT `FK_groupProj_reservation_groupProj_flight`
179 FOREIGN KEY (`flightID`) REFERENCES `groupProj_flight` (`flightID`) ON DELETE Restrict ON UPDATE Restrict
180;
181
182SET FOREIGN_KEY_CHECKS=1
183;