cs122a-2016-spring: 2016s-cs122a-hw4-script.sql

File 2016s-cs122a-hw4-script.sql, 29.7 KB (added by waans11, 5 years ago)
Line 
1# Drop and Create database
2DROP DATABASE IF EXISTS CS122a;
3CREATE DATABASE CS122a;
4USE CS122a;
5
6
7# Dump of table Airplane
8
9DROP TABLE IF EXISTS `Airplane`;
10
11CREATE TABLE `Airplane` (
12  `registration_number` varchar(10) NOT NULL,
13  `model_number` varchar(10) DEFAULT NULL,
14  `purchased_year` int(11) DEFAULT NULL,
15  `manufactured_year` int(11) DEFAULT NULL,
16  `capacity` int(11) DEFAULT NULL,
17  PRIMARY KEY (`registration_number`)
18) ENGINE=InnoDB DEFAULT CHARSET=utf8;
19
20INSERT INTO `Airplane` (`registration_number`, `model_number`, `purchased_year`, `manufactured_year`, `capacity`)
21VALUES
22        ('N12345','B747',2007,2005,467),
23        ('N17523','A380',2010,2009,555),
24        ('N23456','B787',2009,2008,290),
25        ('N98765','B737',2013,1990,190),
26        ('N90001','A320',2011,1999,150),
27        ('N90002','A330',2012,2000,400),
28        ('N90003','A340',2013,2001,350),
29        ('N90004','A380',2015,2015,850),
30        ('N90005','B777',2014,2012,310);
31
32
33# Dump of table Airport
34
35DROP TABLE IF EXISTS `Airport`;
36
37CREATE TABLE `Airport` (
38  `IATA_code` char(3) NOT NULL,
39  `name` varchar(40) DEFAULT NULL,
40  `airport_city` varchar(20) DEFAULT NULL,
41  `airport_state` varchar(20) DEFAULT NULL,
42  PRIMARY KEY (`IATA_code`)
43) ENGINE=InnoDB DEFAULT CHARSET=utf8;
44
45INSERT INTO `Airport` (`IATA_code`, `name`, `airport_city`, `airport_state`)
46VALUES
47        ('JFK','John F. Kennedy International Airport','New York ','NY'),
48        ('LAX','Los Angeles International Airport','Los Angeles','CA'),
49        ('SAT','San Antonio International Airport  ','San Antonio','TX'),
50        ('SNA','John Wayne Airport','Santa Ana','CA'),
51        ('LGB','Long Beach Airport','Long Beach','CA'),
52        ('SAN','San Diego International Airport','San Diego','CA'),
53        ('SJC','San Jose International Airport','San Jose','CA'),
54        ('SFO','San Francisco International Airport','San Francisco','CA');
55
56
57
58
59# Dump of table Customer
60
61DROP TABLE IF EXISTS `Customer`;
62
63CREATE TABLE `Customer` (
64  `cid` int(11) NOT NULL,
65  `ssn` char(9) DEFAULT NULL,
66  `gender` varchar(6) DEFAULT NULL,
67  `email` varchar(30) DEFAULT NULL,
68  `address_street` varchar(50) DEFAULT NULL,
69  `address_city` varchar(20) DEFAULT NULL,
70  `address_state` varchar(20) DEFAULT NULL,
71  `address_zipcode` char(5) DEFAULT NULL,
72  PRIMARY KEY (`cid`)
73) ENGINE=InnoDB DEFAULT CHARSET=utf8;
74
75INSERT INTO `Customer` (`cid`, `ssn`, `gender`, `email`, `address_street`, `address_city`, `address_state`, `address_zipcode`)
76VALUES
77        (1,'988843736','M','153m@4bjsvkd6f.com','9091 Spectrum Pointe Drive','Lake Forest','CA','92630'),
78        (2,'582902877','F','gimodc@qg5a543.com','9091 Watermarke Place','Irvine','CA','92612'),
79        (3,'455171051','F','l91bi@rds0z4ct9n2d.com','9091290 North Hancock Street','Anaheim','CA','92807'),
80        (4,'117911362','F','r1d0800121@8w-4lb.com','90914780 Pipeline Avenue','Chino Hills','CA','91709'),
81        (5,'813694086','M','eu3@4l4ligbm2d4.com','90914851 Jeffrey Road','Irvine','CA','92618'),
82        (6,'830930154','F','2ldx.6l9@pk11l2s.com','90915241 Laguna Canyon Road','Irvine','CA','92618'),
83        (7,'481161938','F','ijvbv5hx@e0z8o6w.com','9091536 East Warner Avenue','Santa Ana','CA','92705'),
84        (8,'340130873','M','iz4tvg5j0e.@otiw34ymv68z.com','9091536 West Warner Avenue','Santa Ana','CA','92705'),
85        (9,'140686813','M','xhrh8pptf0bm2@ki4jwmsiek.com','90915661 Red Hill Avenue','Tustin','CA','92780'),
86        (10,'670322800','M','93nwu_g3pow65d@0zekopshz.com','90916470 Bake Parkway','Irvine','CA','92618'),
87        (11,'145282363','M','gmzs@hdm9q8rpd.com','90924367 Von Karman Avenue','Irvine','CA','92606'),
88        (12,'459777569','F','s4yk8@v006nomucpd.com','90917772 17th Street','Tustin','CA','92780'),
89        (13,'891455675','F','42-8b8@l0m1bttskf3.com','909195 North Euclid Avenue','Upland','CA','91786'),
90        (14,'958708596','M','3ewd@s8xum24csra.com','90937654 Savi Ranch Pkwy','Yorba Linda','CA','92887'),
91        (15,'662905075','M','9zsiot3@8aab4f3tj.com','90923046 Avenida De La Carlota','Laguna Hills','CA','92653'),
92        (16,'390453600','F','4ikmvae@xsvx5etrv2.com','90923726 Birtcher Drive','Lake Forest','CA','92630'),
93        (17,'908856821','M','s3et50zvq-9b1@dlayle.com','9092603 Main Street','Irvine','CA','92614'),
94        (18,'989812336','M','2-z6sf@oh-sz1whst6.com','90927261 Las Ramblas','Mission Viejo','CA','92691'),
95        (19,'22152601','F','5-.@rblrzxw-ql9c.com','90931 Creek Road','Irvine','CA','92604'),
96        (20,'261504907','M','iuxh@al8wsoem.com','90932565 Golden Lantern St.','Dana Point','CA','92629');
97
98
99# Dump of table Credit_Card
100
101DROP TABLE IF EXISTS `Credit_Card`;
102
103CREATE TABLE `Credit_Card` (
104  `cid` int(11) DEFAULT NULL,
105  `card_number` varchar(20) NOT NULL,
106  `expr_date` char(6) DEFAULT NULL,
107  PRIMARY KEY (`card_number`),
108  KEY `cid` (`cid`),
109  CONSTRAINT `credit_card_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `Customer` (`cid`)
110) ENGINE=InnoDB DEFAULT CHARSET=utf8;
111
112INSERT INTO `Credit_Card` (`cid`, `card_number`, `expr_date`)
113VALUES
114        (1,'63040243326223001','201812'),
115        (2,'63041217473725819','202004'),
116        (3,'63044293896926521','201808'),
117        (4,'63046794584776563','202103'),
118        (5,'67060631839968862','201910'),
119        (6,'67061288802817912','202107'),
120        (7,'67061322459422559','201801'),
121        (8,'67067652662421939','201805'),
122        (9,'67067815612478404','202102'),
123        (10,'67069688967650218','202010'),
124        (11,'67092434793176230','202107'),
125        (12,'67094895931396906','202109'),
126        (13,'67098976139150083','202104'),
127        (14,'67099511369731325','202008'),
128        (15,'67099549008585132','201909'),
129        (16,'67099732670013670','201903'),
130        (17,'67710912584731679','202002'),
131        (18,'67716138484326683','202101'),
132        (19,'67717501971398912','202109'),
133        (20,'67718812245978283','201803'),
134        (1,'67718812245978284','201804'),
135        (1,'67718812245978285','201805'),
136        (1,'67718812245978286','201806');
137
138
139# Dump of table Flight
140
141DROP TABLE IF EXISTS `Flight`;
142
143CREATE TABLE `Flight` (
144  `flight_number` varchar(8) NOT NULL,
145  `projected_departure_datetime` datetime NOT NULL,
146  `projected_arrival_datetime` datetime DEFAULT NULL,
147  `aiplane_registration_number` varchar(10) DEFAULT NULL,
148  `departure_airport_IATA_code` char(3) DEFAULT NULL,
149  `actual_departure_datetime` datetime DEFAULT NULL,
150  `arrival_airport_IATA_code` char(3) DEFAULT NULL,
151  `actual_arrival_datetime` datetime DEFAULT NULL,
152  PRIMARY KEY (`flight_number`,`projected_departure_datetime`),
153  KEY `aiplane_registration_number` (`aiplane_registration_number`),
154  KEY `departure_airport_IATA_code` (`departure_airport_IATA_code`),
155  KEY `arrival_airport_IATA_code` (`arrival_airport_IATA_code`),
156  CONSTRAINT `flight_ibfk_1` FOREIGN KEY (`aiplane_registration_number`) REFERENCES `Airplane` (`registration_number`),
157  CONSTRAINT `flight_ibfk_2` FOREIGN KEY (`departure_airport_IATA_code`) REFERENCES `Airport` (`IATA_code`),
158  CONSTRAINT `flight_ibfk_3` FOREIGN KEY (`arrival_airport_IATA_code`) REFERENCES `Airport` (`IATA_code`)
159) ENGINE=InnoDB DEFAULT CHARSET=utf8;
160
161INSERT INTO `Flight` (`flight_number`, `projected_departure_datetime`, `projected_arrival_datetime`, `aiplane_registration_number`, `departure_airport_IATA_code`, `actual_departure_datetime`, `arrival_airport_IATA_code`, `actual_arrival_datetime`)
162VALUES
163        ('N124','2015-08-09 08:21:00','2015-08-09 08:41:00','N23456','LAX','2015-08-09 08:51:00','SNA','2015-08-09 09:21:00'),
164        ('N124','2015-09-07 08:21:00','2015-09-07 08:41:00','N23456','LAX','2015-09-07 08:21:00','SNA','2015-09-07 08:41:00'),
165        ('N124','2015-10-07 08:21:00','2015-10-07 08:41:00','N23456','LAX','2015-09-07 08:31:00','SNA','2015-09-07 08:51:00'),
166        ('U987','2015-06-07 10:23:00','2015-06-07 21:23:00','N17523','SNA','2015-06-07 10:23:00','JFK','2015-06-07 21:25:00'),
167        ('U987','2015-07-07 10:23:00','2015-07-07 21:23:00','N17523','SNA','2015-07-07 10:23:00','JFK','2015-07-07 21:25:00'),
168        ('U987','2015-08-07 10:23:00','2015-08-07 21:23:00','N17523','SNA','2015-08-07 10:23:00','JFK','2015-08-07 21:25:00'),
169        ('U987','2015-09-07 10:23:00','2015-09-07 21:23:00','N17523','SNA','2015-09-07 10:24:00','JFK','2015-09-07 21:24:00'),
170        ('U987','2015-10-07 10:23:00','2015-10-07 21:23:00','N17523','SNA','2015-10-07 10:27:00','JFK','2015-10-07 21:27:00'),
171        ('UC725','2015-10-11 11:25:00','2015-10-11 12:25:00','N98765','SNA','2015-10-11 11:19:00','SFO','2015-10-11 12:19:00'),
172        ('UC725','2015-10-12 11:25:00','2015-10-12 12:25:00','N98765','SNA','2015-10-12 11:29:00','SFO','2015-10-12 12:29:00'),
173        ('UC725','2015-10-13 11:25:00','2015-10-13 12:25:00','N98765','SNA','2015-10-13 11:39:00','SFO','2015-10-13 12:39:00'),
174        ('UC725','2015-10-14 11:25:00','2015-10-14 12:25:00','N98765','SNA','2015-10-14 11:49:00','SFO','2015-10-14 12:49:00'),
175        ('UC725','2015-10-15 11:25:00','2015-10-15 12:25:00','N98765','SNA','2015-10-15 11:59:00','SFO','2015-10-15 12:59:00'),
176        ('UC6024','2016-01-02 06:45:00','2016-01-02 08:30:00','N90001','SFO','2016-01-02 06:40:00','SNA','2016-01-02 08:10:00'),
177        ('UC6024','2016-01-03 06:45:00','2016-01-03 08:30:00','N90002','SFO','2016-01-03 06:45:00','SNA','2016-01-03 08:20:00'),
178        ('UC6024','2016-01-04 06:45:00','2016-01-04 08:30:00','N90003','SFO','2016-01-04 06:55:00','SNA','2016-01-04 08:45:00'),
179        ('UC6024','2016-01-05 06:45:00','2016-01-05 08:30:00','N90003','SFO','2016-01-05 06:55:00','SNA','2016-01-05 08:45:00'),
180        ('UC2084','2016-02-01 10:35:00','2016-02-01 11:50:00','N90004','SJC','2016-02-01 10:35:00','SNA','2016-02-01 11:50:00'),
181        ('UC2084','2016-02-02 10:35:00','2016-02-02 11:50:00','N90004','SJC','2016-02-02 10:45:00','SNA','2016-02-02 11:58:00'),
182        ('UC2084','2016-02-03 10:35:00','2016-02-03 11:50:00','N90004','SJC','2016-02-03 10:55:00','SNA','2016-02-03 11:57:00'),
183        ('UC2084','2016-02-04 10:35:00','2016-02-04 11:50:00','N90004','SJC','2016-02-04 10:15:00','SNA','2016-02-04 11:40:00'),
184        ('UC2084','2016-02-05 10:35:00','2016-02-05 11:50:00','N90004','SJC','2016-02-05 10:25:00','SNA','2016-02-05 11:20:00'),
185        ('UC2084','2016-02-06 10:35:00','2016-02-06 11:50:00','N90004','SJC','2016-02-06 10:25:00','SNA','2016-02-06 12:40:00');
186
187
188# Dump of table FlightAttendant
189
190DROP TABLE IF EXISTS `FlightAttendant`;
191
192CREATE TABLE `FlightAttendant` (
193  `faid` int(11) NOT NULL,
194  `phone_number` varchar(20) DEFAULT NULL,
195  `birthdate` date DEFAULT NULL,
196  `ssn` char(9) DEFAULT NULL,
197  `job_title` varchar(50) DEFAULT NULL,
198  `address_street` varchar(50) DEFAULT NULL,
199  `address_city` varchar(20) DEFAULT NULL,
200  `address_state` varchar(20) DEFAULT NULL,
201  `address_zipcode` char(5) DEFAULT NULL,
202  `service_year` int(11) DEFAULT NULL,
203  PRIMARY KEY (`faid`)
204) ENGINE=InnoDB DEFAULT CHARSET=utf8;
205
206INSERT INTO `FlightAttendant` (`faid`, `phone_number`, `birthdate`, `ssn`, `job_title`, `address_street`, `address_city`, `address_state`, `address_zipcode`, `service_year`)
207VALUES
208        (990301,'314-471-6335','1991-10-21','388667456','Purser','234 Valley View Drive','Holly Springs','FL','33010',1),
209        (990302,'623-217-2561','1992-01-05','546373819','Chief Purser','349 Devon Court','Lutherville Timonium','NC','27540',7),
210        (990303,'623-217-2562','1992-01-06','546373810','Purser','350 Devoni Court','Irvine','CA','92697',2),
211        (990304,'623-217-2563','1992-01-07','546373811','Purser','2350 Ion Dr','Long Beach','CA','91693',3);
212
213
214
215# Dump of table Lounge
216
217DROP TABLE IF EXISTS `Lounge`;
218
219CREATE TABLE `Lounge` (
220  `lid` int(11) NOT NULL,
221  `location` varchar(50) DEFAULT NULL,
222  `airport_IATA_code` char(3) DEFAULT NULL,
223  PRIMARY KEY (`lid`),
224  KEY `airport_IATA_code` (`airport_IATA_code`),
225  CONSTRAINT `lounge_ibfk_1` FOREIGN KEY (`airport_IATA_code`) REFERENCES `Airport` (`IATA_code`)
226) ENGINE=InnoDB DEFAULT CHARSET=utf8;
227
228INSERT INTO `Lounge` (`lid`, `location`, `airport_IATA_code`)
229VALUES
230        (112,'section D32','SNA'),
231        (113,'section E21','SNA'),
232        (212,'section C12','SAT'),
233        (213,'section C24','SAT'),
234        (314,'section A12','JFK'),
235        (315,'section D12','JFK'),
236        (409,'section B15','LAX'),
237        (501,'section A01','SFO'),
238        (502,'section A02','SFO'),
239        (503,'section A03','SFO'),
240        (601,'section K09','SJC');
241
242
243# Dump of table MaintenanceEngineer
244
245DROP TABLE IF EXISTS `MaintenanceEngineer`;
246
247CREATE TABLE `MaintenanceEngineer` (
248  `meid` int(11) NOT NULL,
249  `phone_number` varchar(20) DEFAULT NULL,
250  `birthdate` date DEFAULT NULL,
251  `ssn` char(9) DEFAULT NULL,
252  `job_title` varchar(50) DEFAULT NULL,
253  `address_street` varchar(50) DEFAULT NULL,
254  `address_city` varchar(20) DEFAULT NULL,
255  `address_state` varchar(20) DEFAULT NULL,
256  `address_zipcode` char(5) DEFAULT NULL,
257  `skill` varchar(50) DEFAULT NULL,
258  PRIMARY KEY (`meid`)
259) ENGINE=InnoDB DEFAULT CHARSET=utf8;
260
261INSERT INTO `MaintenanceEngineer` (`meid`, `phone_number`, `birthdate`, `ssn`, `job_title`, `address_street`, `address_city`, `address_state`, `address_zipcode`, `skill`)
262VALUES
263        (990001,'804-543-7860','1989-12-23','187826814','Senior Engineer','984 Wood Street','Bolingbrook','IL','60440','Engines, Mechanincal'),
264        (990002,'219-775-7988','1987-03-21','281653141','junior engineer','847 Route 44','Orange','NJ','07050','Airframes, Engines'),
265        (990003,'817-712-3731','1989-12-25','968920642','senior engineer','216 Washington Avenue','Worcester','MA','01604','Electrical Systems'),
266        (990004,'850-338-7148','1983-12-22','461306391','Speicialist','262 Aspen Court','Far Rockaway','NY','11691','Physics'),
267        (990005,'724-938-6285','1989-08-09','811035535','Principal Engineer','232 Locust Street','Palm Bay','FL','32907','Mechanical'),
268        (990006,'724-938-6286','1989-08-10','811035536','Engineer','233 Locus Street','Palm Bay','FL','32908','Engines, Airframes');
269
270
271# Dump of table OperationStaff
272
273DROP TABLE IF EXISTS `OperationStaff`;
274
275CREATE TABLE `OperationStaff` (
276  `osid` int(11) NOT NULL,
277  `phone_number` varchar(20) DEFAULT NULL,
278  `birthdate` date DEFAULT NULL,
279  `ssn` char(9) DEFAULT NULL,
280  `job_title` varchar(50) DEFAULT NULL,
281  `address_street` varchar(50) DEFAULT NULL,
282  `address_city` varchar(20) DEFAULT NULL,
283  `address_state` varchar(20) DEFAULT NULL,
284  `address_zipcode` char(5) DEFAULT NULL,
285  `department` varchar(50) DEFAULT NULL,
286  PRIMARY KEY (`osid`)
287) ENGINE=InnoDB DEFAULT CHARSET=utf8;
288
289INSERT INTO `OperationStaff` (`osid`, `phone_number`, `birthdate`, `ssn`, `job_title`, `address_street`, `address_city`, `address_state`, `address_zipcode`, `department`)
290VALUES
291        (990101,'954-465-7930','1989-06-28','282462223','Senior Staff','557 Warren Avenue','Shepherdsville','KY','40165','Customer Service'),
292        (990102,'847-647-4341','1989-05-29','929040801','Junior Staff','223 Mulberry Lane','Asbury Park','GA','30075','Quality Ensurance'),
293        (990103,'508-983-8873','1984-07-30','650572255','Officer','19 Riverside Drive','Gettysburg','NJ','07712','Administration'),
294        (990104,'712-834-4242','1989-12-31','793285475','Classified','525 Route 6','Moncks Corner','PA','17325','Secret Service');
295
296
297# Dump of table Pilot
298
299DROP TABLE IF EXISTS `Pilot`;
300
301CREATE TABLE `Pilot` (
302  `pid` int(11) NOT NULL,
303  `phone_number` varchar(20) DEFAULT NULL,
304  `birthdate` date DEFAULT NULL,
305  `ssn` char(9) DEFAULT NULL,
306  `job_title` varchar(50) DEFAULT NULL,
307  `address_street` varchar(50) DEFAULT NULL,
308  `address_city` varchar(20) DEFAULT NULL,
309  `address_state` varchar(20) DEFAULT NULL,
310  `address_zipcode` char(5) DEFAULT NULL,
311  `since` int(11) DEFAULT NULL,
312  PRIMARY KEY (`pid`)
313) ENGINE=InnoDB DEFAULT CHARSET=utf8;
314
315INSERT INTO `Pilot` (`pid`, `phone_number`, `birthdate`, `ssn`, `job_title`, `address_street`, `address_city`, `address_state`, `address_zipcode`, `since`)
316VALUES
317        (990201,'941-655-5246','1974-01-01','854666700','Chief Pilot','155 Locust Street','Howard Beach','SC','29461',1997),
318        (990202,'510-896-8715','1990-01-02','913281306','captain','231 York Road','Sun City','NY','11414',2000),
319        (990203,'765-548-8980','1995-02-05','979417494','Navigator','427 Delaware Avenue','Hialeah','AZ','85351',2001),
320        (990204,'765-548-8981','1995-02-06','979417495','Pilot','428 Delaware Avenue','Hialeah','AZ','85351',2002),
321        (990205,'765-548-8982','1995-02-07','979417496','Pilot','429 Dela Avenue','Hialeah','AZ','85351',2008),
322        (990206,'765-548-8983','1995-02-08','979417497','Pilot','430 Dela Dr','Hialeah','AZ','85351',2013);
323
324
325# Dump of table Dish
326
327DROP TABLE IF EXISTS `Dish`;
328
329CREATE TABLE `Dish` (
330  `lid` int(11) NOT NULL,
331  `name` varchar(40) NOT NULL,
332  `price` decimal(6,2) DEFAULT NULL,
333  PRIMARY KEY (`lid`,`name`),
334  CONSTRAINT `dish_ibfk_1` FOREIGN KEY (`lid`) REFERENCES `Lounge` (`lid`)
335) ENGINE=InnoDB DEFAULT CHARSET=utf8;
336
337INSERT INTO `Dish` (`lid`, `name`, `price`)
338VALUES
339        (112,'alioi spanish',19.00),
340        (112,'japchae mari',6.50),
341        (112,'kalbi burger',7.95),
342        (112,'tacos',7.50),
343        (113,'grilled free range chicken',10.50),
344        (113,'grilled steak',10.50),
345        (113,'oven baked salmon',12.50),
346        (113,'tempura',31.50),
347        (212,'grilled steak',49.00),
348        (212,'salmon',13.00),
349        (212,'skewered shrimp',10.99),
350        (212,'swordfish',11.50),
351        (212,'wafu steak',45.99),
352        (213,'bacon relish',35.50),
353        (213,'seafood salad',12.00),
354        (213,'surf and turf',23.99),
355        (213,'wafu steak',14.00),
356        (314,'burger',11.99),
357        (314,'galbitang',16.00),
358        (314,'samgyetang',29.99),
359        (314,'yukgejang',12.99),
360        (315,'hummus',13.99),
361        (315,'king prawn',97.10),
362        (315,'seafood salad',17.99),
363        (315,'the burger combo',32.99),
364        (315,'the karma burger',18.00),
365        (409,'fresh lemonade',12.99),
366        (409,'sandwich',16.99),
367        (409,'sesame chicken',11.00),
368        (409,'the thai wrap',13.00),
369        (501,'hamburger',19.12),
370        (502,'hamburger',17.12),
371        (503,'hamburger',13.12);
372
373
374# Dump of table DishOrder
375
376DROP TABLE IF EXISTS `DishOrder`;
377
378CREATE TABLE `DishOrder` (
379  `oid` int(11) NOT NULL,
380  `cid` int(11) DEFAULT NULL,
381  `lid` int(11) DEFAULT NULL,
382  `order_datetime` datetime DEFAULT NULL,
383  `total_amount` decimal(7,2) DEFAULT NULL,
384  PRIMARY KEY (`oid`),
385  KEY `cid` (`cid`),
386  KEY `lid` (`lid`),
387  CONSTRAINT `dishorder_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `Customer` (`cid`),
388  CONSTRAINT `dishorder_ibfk_2` FOREIGN KEY (`lid`) REFERENCES `Lounge` (`lid`)
389) ENGINE=InnoDB DEFAULT CHARSET=utf8;
390
391INSERT INTO `DishOrder` (`oid`, `cid`, `lid`, `order_datetime`, `total_amount`)
392VALUES
393        (1,1,112,'2015-01-01 12:34:34',22.50),
394        (2,2,212,'2015-01-02 11:11:11',115.00),
395        (3,3,212,'2015-01-03 12:22:22',96.50),
396        (4,1,409,'2015-01-04 13:33:33',85.96),
397        (5,2,314,'2015-01-05 16:33:33',153.97),
398        (6,3,113,'2015-01-06 17:17:17',62.50),
399        (7,12,213,'2015-01-07 15:34:34',22.50),
400        (8,14,315,'2015-01-08 18:34:34',358.85),
401        (12,1,212,'2015-01-12 09:22:22',709.80),
402        (13,1,213,'2015-01-13 11:45:45',1199.50),
403        (15,1,314,'2015-01-15 14:22:22',129.90),
404        (16,1,315,'2015-01-16 15:31:31',41.97),
405        (19,3,112,'2015-01-19 21:12:12',32.50),
406        (20,1,113,'2015-01-20 20:54:54',210.00);
407
408
409# Dump of table DishOrder_Contains_Dish
410
411DROP TABLE IF EXISTS `DishOrder_Contains_Dish`;
412
413CREATE TABLE `DishOrder_Contains_Dish` (
414  `oid` int(11) NOT NULL,
415  `lid` int(11) NOT NULL,
416  `name` varchar(40) NOT NULL,
417  `quantity` int(11) DEFAULT NULL,
418  PRIMARY KEY (`oid`,`lid`,`name`),
419  KEY `lid` (`lid`,`name`),
420  CONSTRAINT `dishorder_contains_dish_ibfk_1` FOREIGN KEY (`oid`) REFERENCES `DishOrder` (`oid`),
421  CONSTRAINT `dishorder_contains_dish_ibfk_2` FOREIGN KEY (`lid`, `name`) REFERENCES `Dish` (`lid`, `name`)
422) ENGINE=InnoDB DEFAULT CHARSET=utf8;
423
424INSERT INTO `DishOrder_Contains_Dish` (`oid`, `lid`, `name`, `quantity`)
425VALUES
426        (1,112,'tacos',3),
427        (2,212,'swordfish',10),
428        (3,212,'salmon',3),
429        (3,212,'swordfish',5),
430        (4,409,'fresh lemonade',2),
431        (4,409,'sandwich',2),
432        (4,409,'the thai wrap',2),
433        (5,314,'galbitang',4),
434        (5,314,'samgyetang',3),
435        (6,113,'oven baked salmon',5),
436        (7,213,'wafu steak',1),
437        (8,315,'hummus',10),
438        (8,315,'the burger combo',5),
439        (8,315,'the karma burger',3),
440        (12,212,'salmon',20),
441        (12,212,'skewered shrimp',20),
442        (12,212,'swordfish',20),
443        (13,213,'surf and turf',50),
444        (15,314,'yukgejang',10),
445        (16,315,'hummus',3),
446        (19,112,'japchae mari',5),
447        (20,113,'grilled free range chicken',20);
448
449
450# Dump of table Customer_Reserves_Flight
451
452DROP TABLE IF EXISTS `Customer_Reserves_Flight`;
453
454CREATE TABLE `Customer_Reserves_Flight` (
455  `cid` int(11) NOT NULL,
456  `flight_number` varchar(8) NOT NULL,
457  `projected_departure_datetime` datetime NOT NULL,
458  `purchased_datetime` datetime DEFAULT NULL,
459  `purchased_price` decimal(7,2) DEFAULT NULL,
460  `quantity` int(11) DEFAULT NULL,
461  PRIMARY KEY (`cid`,`flight_number`,`projected_departure_datetime`),
462  KEY `flight_number` (`flight_number`,`projected_departure_datetime`),
463  CONSTRAINT `customer_reserves_flight_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `Customer` (`cid`),
464  CONSTRAINT `customer_reserves_flight_ibfk_2` FOREIGN KEY (`flight_number`, `projected_departure_datetime`) REFERENCES `Flight` (`flight_number`, `projected_departure_datetime`)
465) ENGINE=InnoDB DEFAULT CHARSET=utf8;
466
467INSERT INTO `Customer_Reserves_Flight` (`cid`, `flight_number`, `projected_departure_datetime`, `purchased_datetime`, `purchased_price`, `quantity`)
468VALUES
469        (13,'U987','2015-06-07 10:23:00','2015-04-27 13:22:00',2821.00,4),
470        (1,'U987','2015-06-07 10:23:00','2015-06-06 01:01:00',137.22,1),
471        (1,'U987','2015-07-07 10:23:00','2015-07-06 01:01:00',137.22,1),
472        (1,'N124','2015-09-07 08:21:00','2015-08-01 18:11:00',531.00,2),
473        (1,'U987','2015-08-07 10:23:00','2015-08-06 01:01:00',137.22,1),
474        (1,'N124','2015-08-09 08:21:00','2015-08-06 07:21:00',1135.00,2),
475        (14,'N124','2015-09-07 08:21:00','2015-08-06 21:21:00',473.00,1),
476        (15,'N124','2015-10-07 08:21:00','2015-08-07 21:19:00',4730.00,10),
477        (16,'N124','2015-10-07 08:21:00','2015-08-08 13:19:00',3530.00,8),
478        (17,'N124','2015-10-07 08:21:00','2015-08-09 13:39:00',9672.88,24),
479        (1,'U987','2015-09-07 10:23:00','2015-09-06 01:01:00',137.22,1),
480        (18,'N124','2015-10-07 08:21:00','2015-09-09 13:39:00',1572.34,5),
481        (19,'N124','2015-10-07 08:21:00','2015-10-01 13:49:00',1572.34,5),
482        (2,'U987','2015-10-07 10:23:00','2015-10-02 01:01:00',137.22,1),
483        (3,'U987','2015-10-07 10:23:00','2015-10-02 02:01:00',250.00,2),
484        (4,'U987','2015-10-07 10:23:00','2015-10-02 03:01:00',500.00,4),
485        (5,'U987','2015-10-07 10:23:00','2015-10-02 04:01:00',400.00,3),
486        (6,'U987','2015-10-07 10:23:00','2015-10-02 05:01:00',125.00,1),
487        (7,'U987','2015-10-07 10:23:00','2015-10-02 06:01:00',150.00,1),
488        (8,'U987','2015-10-07 10:23:00','2015-10-02 07:01:00',2500.00,10),
489        (9,'U987','2015-10-07 10:23:00','2015-10-02 08:01:00',2800.00,12),
490        (20,'N124','2015-10-07 08:21:00','2015-10-02 13:32:00',420.98,1),
491        (1,'N124','2015-10-07 08:21:00','2015-10-02 18:32:00',430.00,1),
492        (1,'U987','2015-10-07 10:23:00','2015-10-06 01:01:00',137.22,1),
493        (10,'U987','2015-10-07 10:23:00','2015-10-02 09:01:00',290.00,2);
494
495
496
497
498
499# Dump of table FlightAttendant_Participates_Flight
500# ------------------------------------------------------------
501
502DROP TABLE IF EXISTS `FlightAttendant_Participates_Flight`;
503
504CREATE TABLE `FlightAttendant_Participates_Flight` (
505  `faid` int(11) NOT NULL,
506  `flight_number` varchar(8) NOT NULL,
507  `projected_departure_datetime` datetime NOT NULL,
508  PRIMARY KEY (`faid`,`flight_number`,`projected_departure_datetime`),
509  KEY `flight_number` (`flight_number`,`projected_departure_datetime`),
510  CONSTRAINT `flightattendant_participates_flight_ibfk_1` FOREIGN KEY (`faid`) REFERENCES `FlightAttendant` (`faid`),
511  CONSTRAINT `flightattendant_participates_flight_ibfk_2` FOREIGN KEY (`flight_number`, `projected_departure_datetime`) REFERENCES `Flight` (`flight_number`, `projected_departure_datetime`)
512) ENGINE=InnoDB DEFAULT CHARSET=utf8;
513
514INSERT INTO `FlightAttendant_Participates_Flight` (`faid`, `flight_number`, `projected_departure_datetime`)
515VALUES
516        (990301,'N124','2015-08-09 08:21:00'),
517        (990302,'N124','2015-08-09 08:21:00'),
518        (990303,'N124','2015-08-09 08:21:00'),
519        (990301,'N124','2015-09-07 08:21:00'),
520        (990304,'N124','2015-09-07 08:21:00'),
521        (990301,'N124','2015-10-07 08:21:00'),
522        (990304,'N124','2015-10-07 08:21:00'),
523        (990301,'U987','2015-06-07 10:23:00'),
524        (990303,'U987','2015-06-07 10:23:00'),
525        (990301,'U987','2015-07-07 10:23:00'),
526        (990302,'U987','2015-07-07 10:23:00'),
527        (990301,'U987','2015-08-07 10:23:00'),
528        (990304,'U987','2015-08-07 10:23:00'),
529        (990301,'U987','2015-09-07 10:23:00'),
530        (990304,'U987','2015-09-07 10:23:00'),
531        (990301,'U987','2015-10-07 10:23:00'),
532        (990302,'U987','2015-10-07 10:23:00'),
533        (990301,'UC725','2015-10-11 11:25:00'),
534        (990302,'UC725','2015-10-11 11:25:00'),
535        (990303,'UC725','2015-10-11 11:25:00'),
536        (990301,'UC725','2015-10-12 11:25:00'),
537        (990304,'UC725','2015-10-12 11:25:00'),
538        (990301,'UC725','2015-10-13 11:25:00'),
539        (990303,'UC725','2015-10-13 11:25:00'),
540        (990301,'UC725','2015-10-14 11:25:00'),
541        (990303,'UC725','2015-10-14 11:25:00'),
542        (990301,'UC725','2015-10-15 11:25:00'),
543        (990302,'UC725','2015-10-15 11:25:00'),
544        (990304,'UC725','2015-10-15 11:25:00'),
545        (990301,'UC6024','2016-01-02 06:45:00'),
546        (990302,'UC6024','2016-01-02 06:45:00'),
547        (990304,'UC6024','2016-01-02 06:45:00'),
548        (990301,'UC6024','2016-01-03 06:45:00'),
549        (990302,'UC6024','2016-01-03 06:45:00'),
550        (990304,'UC6024','2016-01-03 06:45:00'),
551        (990301,'UC6024','2016-01-04 06:45:00'),
552        (990302,'UC6024','2016-01-04 06:45:00'),
553        (990304,'UC6024','2016-01-04 06:45:00'),
554        (990301,'UC6024','2016-01-05 06:45:00'),
555        (990302,'UC6024','2016-01-05 06:45:00'),
556        (990304,'UC6024','2016-01-05 06:45:00'),
557        (990301,'UC2084','2016-02-01 10:35:00'),
558        (990302,'UC2084','2016-02-01 10:35:00'),
559        (990303,'UC2084','2016-02-01 10:35:00'),
560        (990301,'UC2084','2016-02-02 10:35:00'),
561        (990304,'UC2084','2016-02-02 10:35:00'),
562        (990301,'UC2084','2016-02-03 10:35:00'),
563        (990302,'UC2084','2016-02-03 10:35:00'),
564        (990301,'UC2084','2016-02-04 10:35:00'),
565        (990303,'UC2084','2016-02-04 10:35:00'),
566        (990304,'UC2084','2016-02-04 10:35:00'),
567        (990301,'UC2084','2016-02-05 10:35:00'),
568        (990304,'UC2084','2016-02-05 10:35:00'),
569        (990301,'UC2084','2016-02-06 10:35:00'),
570        (990302,'UC2084','2016-02-06 10:35:00'),
571        (990303,'UC2084','2016-02-06 10:35:00');
572
573
574# Dump of table MaintenanceEngineer_Maintains_Airplane
575
576DROP TABLE IF EXISTS `MaintenanceEngineer_Maintains_Airplane`;
577
578CREATE TABLE `MaintenanceEngineer_Maintains_Airplane` (
579  `meid` int(11) NOT NULL,
580  `Aiplane_registration_number` varchar(10) NOT NULL,
581  PRIMARY KEY (`meid`,`Aiplane_registration_number`),
582  KEY `Aiplane_registration_number` (`Aiplane_registration_number`),
583  CONSTRAINT `maintenanceengineer_maintains_airplane_ibfk_1` FOREIGN KEY (`meid`) REFERENCES `MaintenanceEngineer` (`meid`),
584  CONSTRAINT `maintenanceengineer_maintains_airplane_ibfk_2` FOREIGN KEY (`Aiplane_registration_number`) REFERENCES `Airplane` (`registration_number`)
585) ENGINE=InnoDB DEFAULT CHARSET=utf8;
586
587INSERT INTO `MaintenanceEngineer_Maintains_Airplane` (`meid`, `Aiplane_registration_number`)
588VALUES
589        (990001,'N12345'),
590        (990002,'N12345'),
591        (990003,'N12345'),
592        (990004,'N12345'),
593        (990005,'N12345'),
594        (990006,'N12345'),
595        (990002,'N17523'),
596        (990003,'N17523'),
597        (990004,'N23456'),
598        (990005,'N23456'),
599        (990006,'N23456'),
600        (990001,'N98765'),
601        (990002,'N98765'),
602        (990003,'N98765'),
603        (990004,'N98765'),
604        (990001,'N90001'),
605        (990002,'N90001'),
606        (990003,'N90001'),
607        (990001,'N90002'),
608        (990002,'N90002'),
609        (990003,'N90002'),
610        (990004,'N90003'),
611        (990005,'N90003'),
612        (990002,'N90004'),
613        (990004,'N90004'),
614        (990006,'N90004'),
615        (990001,'N90005'),
616        (990002,'N90005'),
617        (990006,'N90005');
618
619
620# Dump of table Pilot_Operates_Flight
621# ------------------------------------------------------------
622
623DROP TABLE IF EXISTS `Pilot_Operates_Flight`;
624
625CREATE TABLE `Pilot_Operates_Flight` (
626  `pid` int(11) NOT NULL,
627  `flight_number` varchar(8) NOT NULL,
628  `projected_departure_datetime` datetime NOT NULL,
629  PRIMARY KEY (`pid`,`flight_number`,`projected_departure_datetime`),
630  KEY `flight_number` (`flight_number`,`projected_departure_datetime`),
631  CONSTRAINT `pilot_operates_flight_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `Pilot` (`pid`),
632  CONSTRAINT `pilot_operates_flight_ibfk_2` FOREIGN KEY (`flight_number`, `projected_departure_datetime`) REFERENCES `Flight` (`flight_number`, `projected_departure_datetime`)
633) ENGINE=InnoDB DEFAULT CHARSET=utf8;
634
635INSERT INTO `Pilot_Operates_Flight` (`pid`, `flight_number`, `projected_departure_datetime`)
636VALUES
637        (990201,'N124','2015-08-09 08:21:00'),
638        (990202,'N124','2015-08-09 08:21:00'),
639        (990203,'N124','2015-08-09 08:21:00'),
640        (990201,'N124','2015-09-07 08:21:00'),
641        (990204,'N124','2015-09-07 08:21:00'),
642        (990205,'N124','2015-09-07 08:21:00'),
643        (990201,'N124','2015-10-07 08:21:00'),
644        (990204,'N124','2015-10-07 08:21:00'),
645        (990201,'U987','2015-06-07 10:23:00'),
646        (990203,'U987','2015-06-07 10:23:00'),
647        (990205,'U987','2015-06-07 10:23:00'),
648        (990201,'U987','2015-07-07 10:23:00'),
649        (990202,'U987','2015-07-07 10:23:00'),
650        (990206,'U987','2015-07-07 10:23:00'),
651        (990201,'U987','2015-08-07 10:23:00'),
652        (990204,'U987','2015-08-07 10:23:00'),
653        (990206,'U987','2015-08-07 10:23:00'),
654        (990201,'U987','2015-09-07 10:23:00'),
655        (990204,'U987','2015-09-07 10:23:00'),
656        (990201,'U987','2015-10-07 10:23:00'),
657        (990202,'U987','2015-10-07 10:23:00'),
658        (990201,'UC725','2015-10-11 11:25:00'),
659        (990202,'UC725','2015-10-11 11:25:00'),
660        (990203,'UC725','2015-10-11 11:25:00'),
661        (990201,'UC725','2015-10-12 11:25:00'),
662        (990204,'UC725','2015-10-12 11:25:00'),
663        (990205,'UC725','2015-10-12 11:25:00'),
664        (990201,'UC725','2015-10-13 11:25:00'),
665        (990203,'UC725','2015-10-13 11:25:00'),
666        (990206,'UC725','2015-10-13 11:25:00'),
667        (990201,'UC725','2015-10-14 11:25:00'),
668        (990203,'UC725','2015-10-14 11:25:00'),
669        (990206,'UC725','2015-10-14 11:25:00'),
670        (990201,'UC725','2015-10-15 11:25:00'),
671        (990202,'UC725','2015-10-15 11:25:00'),
672        (990204,'UC725','2015-10-15 11:25:00'),
673        (990201,'UC6024','2016-01-02 06:45:00'),
674        (990202,'UC6024','2016-01-02 06:45:00'),
675        (990204,'UC6024','2016-01-02 06:45:00'),
676        (990201,'UC6024','2016-01-03 06:45:00'),
677        (990202,'UC6024','2016-01-03 06:45:00'),
678        (990204,'UC6024','2016-01-03 06:45:00'),
679        (990201,'UC6024','2016-01-04 06:45:00'),
680        (990202,'UC6024','2016-01-04 06:45:00'),
681        (990204,'UC6024','2016-01-04 06:45:00'),
682        (990201,'UC6024','2016-01-05 06:45:00'),
683        (990202,'UC6024','2016-01-05 06:45:00'),
684        (990204,'UC6024','2016-01-05 06:45:00'),
685        (990201,'UC2084','2016-02-01 10:35:00'),
686        (990202,'UC2084','2016-02-01 10:35:00'),
687        (990203,'UC2084','2016-02-01 10:35:00'),
688        (990201,'UC2084','2016-02-02 10:35:00'),
689        (990204,'UC2084','2016-02-02 10:35:00'),
690        (990205,'UC2084','2016-02-02 10:35:00'),
691        (990201,'UC2084','2016-02-03 10:35:00'),
692        (990202,'UC2084','2016-02-03 10:35:00'),
693        (990206,'UC2084','2016-02-03 10:35:00'),
694        (990201,'UC2084','2016-02-04 10:35:00'),
695        (990203,'UC2084','2016-02-04 10:35:00'),
696        (990204,'UC2084','2016-02-04 10:35:00'),
697        (990201,'UC2084','2016-02-05 10:35:00'),
698        (990204,'UC2084','2016-02-05 10:35:00'),
699        (990205,'UC2084','2016-02-05 10:35:00'),
700        (990201,'UC2084','2016-02-06 10:35:00'),
701        (990202,'UC2084','2016-02-06 10:35:00'),
702        (990203,'UC2084','2016-02-06 10:35:00');