-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathFlightManagementInit.sql
164 lines (149 loc) · 8.74 KB
/
FlightManagementInit.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
CREATE SCHEMA FLIGHT_MANAGEMENT;
USE FLIGHT_MANAGEMENT;
CREATE TABLE COUNTRY
(CName VARCHAR(100) NOT NULL,
CountryCode VARCHAR(3) NOT NULL,
PRIMARY KEY(CountryCode) );
CREATE TABLE AIRLINE
(AirlineCountry VARCHAR(3),
AirlineName VARCHAR(30) NOT NULL,
ICAOCode VARCHAR(3) NOT NULL,
FlightCount Int NOT NULL,
PRIMARY KEY(ICAOCode),
FOREIGN KEY(AirlineCountry) REFERENCES COUNTRY(CountryCode) ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE PILOT
(PName VARCHAR(50) NOT NULL,
PActivity VARCHAR(50) NOT NULL,
SSN CHAR(9) NOT NULL,
BirthDate DATE,
LicenseNum INT NOT NULL,
EPTAgrade INT NOT NULL,
FExperience INT NOT NULL,
AirlineCode VARCHAR(3),
PRIMARY KEY(SSN),
FOREIGN KEY(AirlineCode) REFERENCES AIRLINE(ICAOCode) ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE AIRPLANE
(AirplaneID VARCHAR(7) NOT NULL,
TypeCode VARCHAR(4) NOT NULL,
Model VARCHAR(50) NOT NULL,
Occupant INT,
AActivity TEXT,
AirlineCode VARCHAR(3),
PRIMARY KEY(AirplaneID),
FOREIGN KEY(AirlineCode) REFERENCES AIRLINE(ICAOCode) ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE AIRPORT
(AirportIATACode VARCHAR(3) NOT NULL,
AirportName VARCHAR(100),
AirportCountry VARCHAR(3),
PRIMARY KEY(AirportIATACode),
FOREIGN KEY(AirportCountry) REFERENCES COUNTRY(CountryCode) ON DELETE CASCADE ON UPDATE CASCADE );
CREATE TABLE AIRPORT_GATE
(Gate VARCHAR(5) NOT NULL,
Terminal INT,
AirportCode VARCHAR(3) NOT NULL,
PRIMARY KEY(Gate, AirportCode),
FOREIGN KEY(AirportCode) REFERENCES AIRPORT(AirportIATACode) ON DELETE CASCADE ON UPDATE CASCADE );
CREATE TABLE FLIGHT
(FlightNumber VARCHAR(6) NOT NULL,
AirlineCode VARCHAR(3) NOT NULL,
DepartureTime DATETIME,
ArrivalTime DATETIME,
FGate VARCHAR(5) NOT NULL,
PilotSSN CHAR(9) NOT NULL,
AirplaneID VARCHAR(7) NOT NULL,
OIATACode VARCHAR(3) NOT NULL,
DIATACode VARCHAR(3) NOT NULL,
DvsI VARCHAR(20),
PRIMARY KEY(FlightNumber),
FOREIGN KEY(AirlineCode) REFERENCES AIRLINE(ICAOCode) ON DELETE CASCADE ON UPDATE CASCADE ,
FOREIGN KEY(FGate) REFERENCES AIRPORT_GATE(Gate) ON DELETE CASCADE ON UPDATE CASCADE ,
FOREIGN KEY(PilotSSN) REFERENCES PILOT(SSN) ON DELETE CASCADE ON UPDATE CASCADE ,
FOREIGN KEY(AirplaneID) REFERENCES AIRPLANE(AirplaneID) ON DELETE CASCADE ON UPDATE CASCADE ,
FOREIGN KEY(OIATACode) REFERENCES AIRPORT(AirportIATACode) ON DELETE CASCADE ON UPDATE CASCADE ,
FOREIGN KEY(DIATACode) REFERENCES AIRPORT(AirportIATACode) ON DELETE CASCADE ON UPDATE CASCADE );
INSERT INTO COUNTRY VALUES ('United States', 'K');
INSERT INTO COUNTRY VALUES ('Japen', 'RJ');
INSERT INTO COUNTRY VALUES ('Korea', 'RK');
INSERT INTO COUNTRY VALUES ('China', 'Z');
INSERT INTO COUNTRY VALUES ('Canada', 'C');
INSERT INTO COUNTRY VALUES ('Russia', 'U');
INSERT INTO COUNTRY VALUES ('Australia', 'Y');
INSERT INTO COUNTRY VALUES ('Germany', 'ED');
INSERT INTO COUNTRY VALUES ('France', 'LF');
INSERT INTO COUNTRY VALUES ('United Kingdom', 'EG');
INSERT INTO AIRLINE VALUES ('K','American','AAL', 2);
INSERT INTO AIRLINE VALUES ('K','United','UAL', 2);
INSERT INTO AIRLINE VALUES ('K','SouthWest','SWA', 0);
INSERT INTO AIRLINE VALUES ('K','Delta','DAL', 1);
INSERT INTO AIRLINE VALUES ('K','Skywalk','SYX', 1);
INSERT INTO AIRLINE VALUES ('RK','KoreanAir','KAL', 1);
INSERT INTO AIRLINE VALUES ('RK','Asiana','AAR', 0);
INSERT INTO AIRLINE VALUES ('Z','BeijingSouthern','CYH', 0);
INSERT INTO AIRLINE VALUES ('Z','AirChina','CCA', 0);
INSERT INTO AIRLINE VALUES ('C','Skylink','SKK', 0);
INSERT INTO AIRLINE VALUES ('C','Skycharter','SKL', 0);
INSERT INTO AIRLINE VALUES ('Y','AustraliaAsia','AAU', 0);
INSERT INTO AIRLINE VALUES ('EG','AirSouthwest','WOW', 2);
INSERT INTO AIRLINE VALUES ('EG','AirWales','AWW', 0);
INSERT INTO AIRLINE VALUES ('RJ','AirJapan','AJX', 0);
INSERT INTO AIRLINE VALUES ('ED','AirBerlin','BER', 0);
INSERT INTO PILOT VALUES ('James Smith','Available','987654321', 19800702, 987654, 2, 65, 'AAL');
INSERT INTO PILOT VALUES ('Jane Anna','Available','845123655', 19861205, 520565, 3, 100, 'AAL');
INSERT INTO PILOT VALUES ('Tomas Mike','Available','112255446', 19661113, 435273, 5, 90, 'AAL');
INSERT INTO PILOT VALUES ('Bill Johnson','available','876543219',19790505,876543,2,90,'UAL');
INSERT INTO PILOT VALUES ('Smith Williams', 'Available', '765432198',19820301, 765432, 2, 40, 'DAL');
INSERT INTO PILOT VALUES ('George Jones', 'Available', '654321987', 19720202, 654321, 2, 120, 'AAR');
INSERT INTO PILOT VALUES ('Hyunseo Jang', 'Available', '123456789', 19990210, 123456, 2, 50, 'KAL');
INSERT INTO PILOT VALUES ('Jungwon Lee', 'UnAvailable', '111222333', 19980313, 456456, 4, 60, 'KAL');
INSERT INTO PILOT VALUES ('Brian Alicia', 'Available', '753753753', 19961107, 159159, 3, 40, 'KAL');
INSERT INTO PILOT VALUES ('Boan Li', 'Available', '852852852', 19970112, 123123, 1, 5, 'CYH');
INSERT INTO PILOT VALUES ('Jim Andrews', 'Available', '485216489', 19900407, 264352, 2, 90, 'AAR');
INSERT INTO PILOT VALUES ('Alex Garcia', 'Available', '485416787', 19740107, 554869, 2, 100, 'UAL');
INSERT INTO PILOT VALUES ('Steve Miller', 'Available', '564568524', 19880808, 884652, 2, 180, 'SYX');
INSERT INTO AIRPLANE VALUES('A4F0131','A4F','Antonov AN-124 Ruslan',351,'Unavailable','AAL');
INSERT INTO AIRPLANE VALUES('A4F0103','A4F','Antonov AN-124 Ruslan',351,'Available','KAL');
INSERT INTO AIRPLANE VALUES('A810021','A81','Antonov An-148',320,'Available','UAL');
INSERT INTO AIRPLANE VALUES('32B5986' ,'32B','Airbus A321 (sharklets)',310,'Unavailable','UAL');
INSERT INTO AIRPLANE VALUES('AT73254' ,'AT7','Aerospatiale/Alenia ATR 72',330,'Unavailable','DAL');
INSERT INTO AIRPLANE VALUES('AT78516' ,'AT7','Aerospatiale/Alenia ATR 72',330,'Available','AJX');
INSERT INTO AIRPLANE VALUES('B725264' ,'B72','Boeing 720B',290,'Available','SYX');
INSERT INTO AIRPLANE VALUES('B720001' ,'B72','Boeing 720B',400,'Available','KAL');
INSERT INTO AIRPLANE VALUES('B720008' ,'B72','Boeing 720B',250,'Available','AAR');
INSERT INTO AIRPLANE VALUES('B720562' ,'B72','Boeing 720B',250,'Available','WOW');
INSERT INTO AIRPLANE VALUES('7731621' ,'773','Boeing 777-300',250,'Available','CYH');
INSERT INTO AIRPLANE VALUES('ATL0562' ,'ATL','RobinATL',150,'Available','CYH');
INSERT INTO AIRPLANE VALUES('7735122' ,'773','Boeing 777-300',250,'Available','CCA');
INSERT INTO AIRPLANE VALUES('ATL5162' ,'ATL','RobinATL',150,'Available','CCA');
INSERT INTO AIRPORT VALUES ('PHX','Phoenix Sky Harbor Airport','K');
INSERT INTO AIRPORT VALUES ('JFK','John F.Kennedy International Airport','K');
INSERT INTO AIRPORT VALUES ('BER','Berlin BrandenBurg Airport','ED');
INSERT INTO AIRPORT VALUES ('ICN','Incheon International Airport','RK');
INSERT INTO AIRPORT VALUES ('BJS','Beijing Capital International Airport','Z');
INSERT INTO AIRPORT VALUES ('LAX','Los Angeles International Airport','K');
INSERT INTO AIRPORT VALUES ('LHR','Heathrow Airport','EG');
INSERT INTO AIRPORT VALUES ('TYO','Haneda Airport','RJ');
INSERT INTO AIRPORT VALUES ('SEA','Tacoma International Airport','K');
INSERT INTO AIRPORT VALUES ('SYD','Kingsford Smith Airport','Y');
INSERT INTO AIRPORT_GATE VALUES('A12','3','PHX');
INSERT INTO AIRPORT_GATE VALUES('B08','2','PHX');
INSERT INTO AIRPORT_GATE VALUES('B05','2','PHX');
INSERT INTO AIRPORT_GATE VALUES('B01','2','PHX');
INSERT INTO AIRPORT_GATE VALUES('B15','2','PHX');
INSERT INTO AIRPORT_GATE VALUES('C23','3','JFK');
INSERT INTO AIRPORT_GATE VALUES('A17','1','BJS');
INSERT INTO AIRPORT_GATE VALUES('D44','4','ICN');
INSERT INTO AIRPORT_GATE VALUES('D29','4','ICN');
INSERT INTO AIRPORT_GATE VALUES('D25','4','ICN');
INSERT INTO AIRPORT_GATE VALUES('D20','4','ICN');
INSERT INTO AIRPORT_GATE VALUES('F11','4','LAX');
INSERT INTO AIRPORT_GATE VALUES('F12','4','LAX');
INSERT INTO AIRPORT_GATE VALUES('F23','2','LHR');
INSERT INTO FLIGHT VALUES('AAL001','AAL','2021-04-15 07:15:00','2021-04-15 12:14:00','B15','987654321','A4F0131','PHX','JFK','Domestic');
INSERT INTO FLIGHT VALUES('AAL002','AAL','2021-04-16 07:15:00','2021-04-16 12:14:00','B15','987654321','A4F0131','JFK','PHX','Domestic');
INSERT INTO FLIGHT VALUES('UAL001','UAL','2021-04-16 21:22:00','2021-04-17 03:15:00','C23','876543219','32B5986','BJS','ICN','International');
INSERT INTO FLIGHT VALUES('DAL001','DAL','2021-04-15 17:05:00','2021-04-16 06:30:00','A17','765432198','AT73254','ICN','LAX','International');
INSERT INTO FLIGHT VALUES('KAL001','KAL','2021-04-15 08:10:00','2021-04-15 16:30:00','D44','123456789','A4F0103','ICN','LAX','International');
INSERT INTO FLIGHT VALUES('SYX001','SYX','2021-04-17 22:12:00','2021-04-17 12:10:00','D44','564568524','B725264','LAX','ICN','International');
INSERT INTO FLIGHT VALUES('UAL002','UAL','2021-04-18 11:10:00','2021-04-18 22:45:00','D29','485416787','32B5986','BJS','LAX','International');
INSERT INTO FLIGHT VALUES('WOW001','WOW','2021-04-18 11:10:00','2021-04-18 18:45:00','F23','485416787','B720562','LHR','LAX','International');
INSERT INTO FLIGHT VALUES('WOW002','WOW','2021-04-10 11:10:00','2021-04-10 18:45:00','F23','485416787','B720562','LHR','LAX','International');