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

File 2016s-cs122a-hw5-script.sql, 30.1 KB (added by alsaudi.ad, 5 years ago)

HW5-schema

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,'6304024332622300','201812'),
115        (2,'630412174737258','202004'),
116        (3,'6304429389692652','201808'),
117        (4,'6304679458477656','202103'),
118        (5,'670606318399688','201910'),
119        (6,'6706128880281791','202107'),
120        (7,'6706132245942255','201801'),
121        (8,'6706765266242193','201805'),
122        (9,'6706781561247840','202102'),
123        (10,'6706968896765021','202010'),
124        (11,'6709243479317623','202107'),
125        (12,'670948959313969','202109'),
126        (13,'6709897613915008','202104'),
127        (14,'6709951136973132','202008'),
128        (15,'6709954900858513','201909'),
129        (16,'6709973267001367','201903'),
130        (17,'6771091258473167','202002'),
131        (18,'6771613848432668','202101'),
132        (19,'6771750197139891','202109'),
133        (20,'6771881004597828','201803'),
134        (1,'6771881224597828','201804'),
135        (1,'6771881224597800','201805'),
136        (1,'7718812245978280','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:21: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 22: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:55: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:55: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        (404,'section D19','LGB'),
242        (84,'section B06','SAN');
243
244
245# Dump of table MaintenanceEngineer
246
247DROP TABLE IF EXISTS `MaintenanceEngineer`;
248
249CREATE TABLE `MaintenanceEngineer` (
250  `meid` int(11) NOT NULL,
251  `phone_number` varchar(20) DEFAULT NULL,
252  `birthdate` date DEFAULT NULL,
253  `ssn` char(9) DEFAULT NULL,
254  `job_title` varchar(50) DEFAULT NULL,
255  `address_street` varchar(50) DEFAULT NULL,
256  `address_city` varchar(20) DEFAULT NULL,
257  `address_state` varchar(20) DEFAULT NULL,
258  `address_zipcode` char(5) DEFAULT NULL,
259  `skill` varchar(50) DEFAULT NULL,
260  PRIMARY KEY (`meid`)
261) ENGINE=InnoDB DEFAULT CHARSET=utf8;
262
263INSERT INTO `MaintenanceEngineer` (`meid`, `phone_number`, `birthdate`, `ssn`, `job_title`, `address_street`, `address_city`, `address_state`, `address_zipcode`, `skill`)
264VALUES
265        (990001,'804-543-7860','1989-12-23','187826814','Senior Engineer','984 Wood Street','Bolingbrook','IL','60440','Engines, Mechanincal'),
266        (990002,'219-775-7988','1987-03-21','281653141','junior engineer','847 Route 44','Orange','NJ','07050','Airframes, Engines'),
267        (990003,'817-712-3731','1989-12-25','968920642','senior engineer','216 Washington Avenue','Worcester','MA','01604','Electrical Systems'),
268        (990004,'850-338-7148','1983-12-22','461306391','Speicialist','262 Aspen Court','Far Rockaway','NY','11691','Physics'),
269        (990005,'724-938-6285','1989-08-09','811035535','Principal Engineer','232 Locust Street','Palm Bay','FL','32907','Mechanical'),
270        (990006,'724-938-6286','1989-08-10','811035536','Engineer','233 Locus Street','Palm Bay','FL','32908','Engines, Airframes');
271
272
273# Dump of table OperationStaff
274
275DROP TABLE IF EXISTS `OperationStaff`;
276
277CREATE TABLE `OperationStaff` (
278  `osid` int(11) NOT NULL,
279  `phone_number` varchar(20) DEFAULT NULL,
280  `birthdate` date DEFAULT NULL,
281  `ssn` char(9) DEFAULT NULL,
282  `job_title` varchar(50) DEFAULT NULL,
283  `address_street` varchar(50) DEFAULT NULL,
284  `address_city` varchar(20) DEFAULT NULL,
285  `address_state` varchar(20) DEFAULT NULL,
286  `address_zipcode` char(5) DEFAULT NULL,
287  `department` varchar(50) DEFAULT NULL,
288  PRIMARY KEY (`osid`)
289) ENGINE=InnoDB DEFAULT CHARSET=utf8;
290
291INSERT INTO `OperationStaff` (`osid`, `phone_number`, `birthdate`, `ssn`, `job_title`, `address_street`, `address_city`, `address_state`, `address_zipcode`, `department`)
292VALUES
293        (990101,'954-465-7930','1989-06-28','282462223','Senior Staff','557 Warren Avenue','Shepherdsville','KY','40165','Customer Service'),
294        (990102,'847-647-4341','1989-05-29','929040801','Junior Staff','223 Mulberry Lane','Asbury Park','GA','30075','Quality Ensurance'),
295        (990103,'508-983-8873','1984-07-30','650572255','Officer','19 Riverside Drive','Gettysburg','NJ','07712','Administration'),
296        (990104,'712-834-4242','1989-12-31','793285475','Classified','525 Route 6','Moncks Corner','PA','17325','Secret Service');
297
298
299# Dump of table Pilot
300
301DROP TABLE IF EXISTS `Pilot`;
302
303CREATE TABLE `Pilot` (
304  `pid` int(11) NOT NULL,
305  `phone_number` varchar(20) DEFAULT NULL,
306  `birthdate` date DEFAULT NULL,
307  `ssn` char(9) DEFAULT NULL,
308  `job_title` varchar(50) DEFAULT NULL,
309  `address_street` varchar(50) DEFAULT NULL,
310  `address_city` varchar(20) DEFAULT NULL,
311  `address_state` varchar(20) DEFAULT NULL,
312  `address_zipcode` char(5) DEFAULT NULL,
313  `since` int(11) DEFAULT NULL,
314  PRIMARY KEY (`pid`)
315) ENGINE=InnoDB DEFAULT CHARSET=utf8;
316
317INSERT INTO `Pilot` (`pid`, `phone_number`, `birthdate`, `ssn`, `job_title`, `address_street`, `address_city`, `address_state`, `address_zipcode`, `since`)
318VALUES
319        (990201,'941-655-5246','1974-01-01','854666700','Chief Pilot','155 Locust Street','Howard Beach','SC','29461',1997),
320        (990202,'510-896-8715','1990-01-02','913281306','captain','231 York Road','Sun City','NY','11414',2000),
321        (990203,'765-548-8980','1995-02-05','979417494','Navigator','427 Delaware Avenue','Hialeah','AZ','85351',2001),
322        (990204,'765-548-8981','1995-02-06','979417495','Pilot','428 Delaware Avenue','Hialeah','AZ','85351',2002),
323        (990205,'765-548-8982','1995-02-07','979417496','Pilot','429 Dela Avenue','Hialeah','AZ','85351',2008),
324        (990206,'765-548-8983','1995-02-08','979417497','Pilot','430 Dela Dr','Hialeah','AZ','85351',2013);
325
326
327# Dump of table Dish
328
329DROP TABLE IF EXISTS `Dish`;
330
331CREATE TABLE `Dish` (
332  `lid` int(11) NOT NULL,
333  `name` varchar(40) NOT NULL,
334  `price` decimal(6,2) DEFAULT NULL,
335  PRIMARY KEY (`lid`,`name`),
336  CONSTRAINT `dish_ibfk_1` FOREIGN KEY (`lid`) REFERENCES `Lounge` (`lid`)
337) ENGINE=InnoDB DEFAULT CHARSET=utf8;
338
339INSERT INTO `Dish` (`lid`, `name`, `price`)
340VALUES
341        (112,'alioi spanish',19.00),
342        (112,'japchae mari',6.50),
343        (112,'kalbi burger',7.95),
344        (112,'tacos',7.50),
345        (113,'grilled free range chicken',10.50),
346        (113,'grilled steak',10.50),
347        (113,'oven baked salmon',12.50),
348        (113,'tempura',31.50),
349        (212,'grilled steak',49.00),
350        (212,'salmon',13.00),
351        (212,'skewered shrimp',10.99),
352        (212,'swordfish',11.50),
353        (212,'wafu steak',45.99),
354        (213,'bacon relish',35.50),
355        (213,'seafood salad',12.00),
356        (213,'surf and turf',23.99),
357        (213,'wafu steak',14.00),
358        (314,'burger',11.99),
359        (314,'galbitang',16.00),
360        (314,'samgyetang',29.99),
361        (314,'yukgejang',12.99),
362        (315,'hummus',13.99),
363        (315,'king prawn',97.10),
364        (315,'seafood salad',17.99),
365        (315,'the burger combo',32.99),
366        (315,'the karma burger',18.00),
367        (409,'fresh lemonade',12.99),
368        (409,'sandwich',16.99),
369        (409,'sesame chicken',11.00),
370        (409,'the thai wrap',13.00),
371        (501,'hamburger',19.12),
372        (502,'hamburger',17.12),
373        (503,'hamburger',13.12),
374        (601,'sandwich',9.12),
375        (404,'hamburger',15.19),
376        (404,'seafood salad',20.09),
377        (84,'hamburger',12.92),
378        (84,'salmon',15.19);
379
380# Dump of table DishOrder
381
382DROP TABLE IF EXISTS `DishOrder`;
383
384CREATE TABLE `DishOrder` (
385  `oid` int(11) NOT NULL,
386  `cid` int(11) DEFAULT NULL,
387  `lid` int(11) DEFAULT NULL,
388  `order_datetime` datetime DEFAULT NULL,
389  `total_amount` decimal(7,2) DEFAULT NULL,
390  PRIMARY KEY (`oid`),
391  KEY `cid` (`cid`),
392  KEY `lid` (`lid`),
393  CONSTRAINT `dishorder_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `Customer` (`cid`),
394  CONSTRAINT `dishorder_ibfk_2` FOREIGN KEY (`lid`) REFERENCES `Lounge` (`lid`)
395) ENGINE=InnoDB DEFAULT CHARSET=utf8;
396
397INSERT INTO `DishOrder` (`oid`, `cid`, `lid`, `order_datetime`, `total_amount`)
398VALUES
399        (1,1,112,'2015-01-01 12:34:34',22.50),
400        (2,2,212,'2015-01-02 11:11:11',115.00),
401        (3,5,212,'2015-01-03 12:22:22',96.50),
402        (4,1,409,'2015-01-04 13:33:33',85.96),
403        (5,2,314,'2015-01-05 16:33:33',153.97),
404        (6,3,113,'2015-01-06 17:17:17',62.50),
405        (7,12,213,'2015-01-07 15:34:34',22.50),
406        (8,14,315,'2015-01-08 18:34:34',358.85),
407        (12,1,212,'2015-01-12 09:22:22',709.80),
408        (13,1,213,'2015-01-13 11:45:45',1199.50),
409        (15,1,314,'2015-01-15 14:22:22',129.90),
410        (16,1,315,'2015-01-16 15:31:31',41.97),
411        (19,3,112,'2015-01-19 21:12:12',32.50),
412        (20,1,113,'2015-01-20 20:54:54',210.00),
413        (22,1,501,'2015-01-15 14:22:22',19.12),
414        (23,1,601,'2015-01-16 15:31:31',27.36),
415        (24,1,404,'2015-01-28 14:22:22',20.09),
416        (25,1,84,'2015-08-16 03:03:03',30.38);
417
418
419# Dump of table DishOrder_Contains_Dish
420
421DROP TABLE IF EXISTS `DishOrder_Contains_Dish`;
422
423CREATE TABLE `DishOrder_Contains_Dish` (
424  `oid` int(11) NOT NULL,
425  `lid` int(11) NOT NULL,
426  `name` varchar(40) NOT NULL,
427  `quantity` int(11) DEFAULT NULL,
428  PRIMARY KEY (`oid`,`lid`,`name`),
429  KEY `lid` (`lid`,`name`),
430  CONSTRAINT `dishorder_contains_dish_ibfk_1` FOREIGN KEY (`oid`) REFERENCES `DishOrder` (`oid`),
431  CONSTRAINT `dishorder_contains_dish_ibfk_2` FOREIGN KEY (`lid`, `name`) REFERENCES `Dish` (`lid`, `name`)
432) ENGINE=InnoDB DEFAULT CHARSET=utf8;
433
434INSERT INTO `DishOrder_Contains_Dish` (`oid`, `lid`, `name`, `quantity`)
435VALUES
436        (1,112,'tacos',3),
437        (2,212,'swordfish',10),
438        (3,212,'salmon',3),
439        (3,212,'swordfish',5),
440        (4,409,'fresh lemonade',2),
441        (4,409,'sandwich',2),
442        (4,409,'the thai wrap',2),
443        (5,314,'galbitang',4),
444        (5,314,'samgyetang',3),
445        (6,113,'oven baked salmon',5),
446        (7,213,'wafu steak',1),
447        (8,315,'hummus',10),
448        (8,315,'the burger combo',5),
449        (8,315,'the karma burger',3),
450        (12,212,'salmon',20),
451        (12,212,'skewered shrimp',20),
452        (12,212,'swordfish',20),
453        (13,213,'surf and turf',50),
454        (15,314,'yukgejang',10),
455        (16,315,'hummus',3),
456        (19,112,'japchae mari',5),
457        (20,113,'grilled free range chicken',20),
458        (22,501,'hamburger',1),
459        (23,601,'sandwich',3),
460        (24,404,'seafood salad',1),
461        (25,84,'salmon',2);
462       
463
464
465# Dump of table Customer_Reserves_Flight
466
467DROP TABLE IF EXISTS `Customer_Reserves_Flight`;
468
469CREATE TABLE `Customer_Reserves_Flight` (
470  `cid` int(11) NOT NULL,
471  `flight_number` varchar(8) NOT NULL,
472  `projected_departure_datetime` datetime NOT NULL,
473  `purchased_datetime` datetime DEFAULT NULL,
474  `purchased_price` decimal(7,2) DEFAULT NULL,
475  `quantity` int(11) DEFAULT NULL,
476  PRIMARY KEY (`cid`,`flight_number`,`projected_departure_datetime`),
477  KEY `flight_number` (`flight_number`,`projected_departure_datetime`),
478  CONSTRAINT `customer_reserves_flight_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `Customer` (`cid`),
479  CONSTRAINT `customer_reserves_flight_ibfk_2` FOREIGN KEY (`flight_number`, `projected_departure_datetime`) REFERENCES `Flight` (`flight_number`, `projected_departure_datetime`)
480) ENGINE=InnoDB DEFAULT CHARSET=utf8;
481
482INSERT INTO `Customer_Reserves_Flight` (`cid`, `flight_number`, `projected_departure_datetime`, `purchased_datetime`, `purchased_price`, `quantity`)
483VALUES
484        (13,'U987','2015-06-07 10:23:00','2015-04-27 13:22:00',2821.00,4),
485        (1,'U987','2015-06-07 10:23:00','2015-06-06 01:01:00',137.22,1),
486        (1,'U987','2015-07-07 10:23:00','2015-07-06 01:01:00',137.22,555),
487        (1,'N124','2015-09-07 08:21:00','2015-08-01 18:11:00',531.00,2),
488        (1,'U987','2015-08-07 10:23:00','2015-08-06 01:01:00',137.22,1),
489        (1,'N124','2015-08-09 08:21:00','2015-08-06 07:21:00',1135.00,2),
490        (14,'N124','2015-09-07 08:21:00','2015-08-06 21:21:00',473.00,1),
491        (15,'N124','2015-10-07 08:21:00','2015-08-07 21:19:00',44730.00,198),
492        (16,'N124','2015-10-07 08:21:00','2015-08-08 13:19:00',13530.00,40),
493        (17,'N124','2015-10-07 08:21:00','2015-08-09 13:39:00',9672.88,40),
494        (1,'U987','2015-09-07 10:23:00','2015-09-06 01:01:00',137.22,1),
495        (18,'N124','2015-10-07 08:21:00','2015-09-09 13:39:00',1572.34,5),
496        (19,'N124','2015-10-07 08:21:00','2015-10-01 13:49:00',1572.34,5),
497        (2,'U987','2015-10-07 10:23:00','2015-10-02 01:01:00',137.22,1),
498        (3,'U987','2015-10-07 10:23:00','2015-10-02 02:01:00',250.00,2),
499        (4,'U987','2015-10-07 10:23:00','2015-10-02 03:01:00',500.00,4),
500        (5,'U987','2015-10-07 10:23:00','2015-10-02 04:01:00',400.00,3),
501        (6,'U987','2015-10-07 10:23:00','2015-10-02 05:01:00',125.00,1),
502        (7,'U987','2015-10-07 10:23:00','2015-10-02 06:01:00',150.00,1),
503        (8,'U987','2015-10-07 10:23:00','2015-10-02 07:01:00',2500.00,10),
504        (9,'U987','2015-10-07 10:23:00','2015-10-02 08:01:00',2800.00,12),
505        (20,'N124','2015-10-07 08:21:00','2015-10-02 13:32:00',420.98,1),
506        (1,'N124','2015-10-07 08:21:00','2015-10-02 18:32:00',430.00,1),
507        (1,'U987','2015-10-07 10:23:00','2015-10-06 01:01:00',137.22,1),
508        (10,'U987','2015-10-07 10:23:00','2015-10-02 09:01:00',290.00,2);
509
510
511
512
513
514# Dump of table FlightAttendant_Participates_Flight
515# ------------------------------------------------------------
516
517DROP TABLE IF EXISTS `FlightAttendant_Participates_Flight`;
518
519CREATE TABLE `FlightAttendant_Participates_Flight` (
520  `faid` int(11) NOT NULL,
521  `flight_number` varchar(8) NOT NULL,
522  `projected_departure_datetime` datetime NOT NULL,
523  PRIMARY KEY (`faid`,`flight_number`,`projected_departure_datetime`),
524  KEY `flight_number` (`flight_number`,`projected_departure_datetime`),
525  CONSTRAINT `flightattendant_participates_flight_ibfk_1` FOREIGN KEY (`faid`) REFERENCES `FlightAttendant` (`faid`),
526  CONSTRAINT `flightattendant_participates_flight_ibfk_2` FOREIGN KEY (`flight_number`, `projected_departure_datetime`) REFERENCES `Flight` (`flight_number`, `projected_departure_datetime`)
527) ENGINE=InnoDB DEFAULT CHARSET=utf8;
528
529INSERT INTO `FlightAttendant_Participates_Flight` (`faid`, `flight_number`, `projected_departure_datetime`)
530VALUES
531        (990301,'N124','2015-08-09 08:21:00'),
532        (990302,'N124','2015-08-09 08:21:00'),
533        (990303,'N124','2015-08-09 08:21:00'),
534        (990301,'N124','2015-09-07 08:21:00'),
535        (990304,'N124','2015-09-07 08:21:00'),
536        (990301,'N124','2015-10-07 08:21:00'),
537        (990304,'N124','2015-10-07 08:21:00'),
538        (990301,'U987','2015-06-07 10:23:00'),
539        (990303,'U987','2015-06-07 10:23:00'),
540        (990301,'U987','2015-07-07 10:23:00'),
541        (990302,'U987','2015-07-07 10:23:00'),
542        (990301,'U987','2015-08-07 10:23:00'),
543        (990304,'U987','2015-08-07 10:23:00'),
544        (990301,'U987','2015-09-07 10:23:00'),
545        (990304,'U987','2015-09-07 10:23:00'),
546        (990301,'U987','2015-10-07 10:23:00'),
547        (990302,'U987','2015-10-07 10:23:00'),
548        (990301,'UC725','2015-10-11 11:25:00'),
549        (990302,'UC725','2015-10-11 11:25:00'),
550        (990303,'UC725','2015-10-11 11:25:00'),
551        (990301,'UC725','2015-10-12 11:25:00'),
552        (990304,'UC725','2015-10-12 11:25:00'),
553        (990301,'UC725','2015-10-13 11:25:00'),
554        (990303,'UC725','2015-10-13 11:25:00'),
555        (990301,'UC725','2015-10-14 11:25:00'),
556        (990303,'UC725','2015-10-14 11:25:00'),
557        (990301,'UC725','2015-10-15 11:25:00'),
558        (990302,'UC725','2015-10-15 11:25:00'),
559        (990304,'UC725','2015-10-15 11:25:00'),
560        (990301,'UC6024','2016-01-02 06:45:00'),
561        (990302,'UC6024','2016-01-02 06:45:00'),
562        (990304,'UC6024','2016-01-02 06:45:00'),
563        (990301,'UC6024','2016-01-03 06:45:00'),
564        (990302,'UC6024','2016-01-03 06:45:00'),
565        (990304,'UC6024','2016-01-03 06:45:00'),
566        (990301,'UC6024','2016-01-04 06:45:00'),
567        (990302,'UC6024','2016-01-04 06:45:00'),
568        (990304,'UC6024','2016-01-04 06:45:00'),
569        (990301,'UC6024','2016-01-05 06:45:00'),
570        (990302,'UC6024','2016-01-05 06:45:00'),
571        (990304,'UC6024','2016-01-05 06:45:00'),
572        (990301,'UC2084','2016-02-01 10:35:00'),
573        (990302,'UC2084','2016-02-01 10:35:00'),
574        (990303,'UC2084','2016-02-01 10:35:00'),
575        (990301,'UC2084','2016-02-02 10:35:00'),
576        (990304,'UC2084','2016-02-02 10:35:00'),
577        (990301,'UC2084','2016-02-03 10:35:00'),
578        (990302,'UC2084','2016-02-03 10:35:00'),
579        (990301,'UC2084','2016-02-04 10:35:00'),
580        (990303,'UC2084','2016-02-04 10:35:00'),
581        (990304,'UC2084','2016-02-04 10:35:00'),
582        (990301,'UC2084','2016-02-05 10:35:00'),
583        (990304,'UC2084','2016-02-05 10:35:00'),
584        (990301,'UC2084','2016-02-06 10:35:00'),
585        (990302,'UC2084','2016-02-06 10:35:00'),
586        (990303,'UC2084','2016-02-06 10:35:00');
587
588
589# Dump of table MaintenanceEngineer_Maintains_Airplane
590
591DROP TABLE IF EXISTS `MaintenanceEngineer_Maintains_Airplane`;
592
593CREATE TABLE `MaintenanceEngineer_Maintains_Airplane` (
594  `meid` int(11) NOT NULL,
595  `Aiplane_registration_number` varchar(10) NOT NULL,
596  PRIMARY KEY (`meid`,`Aiplane_registration_number`),
597  KEY `Aiplane_registration_number` (`Aiplane_registration_number`),
598  CONSTRAINT `maintenanceengineer_maintains_airplane_ibfk_1` FOREIGN KEY (`meid`) REFERENCES `MaintenanceEngineer` (`meid`),
599  CONSTRAINT `maintenanceengineer_maintains_airplane_ibfk_2` FOREIGN KEY (`Aiplane_registration_number`) REFERENCES `Airplane` (`registration_number`)
600) ENGINE=InnoDB DEFAULT CHARSET=utf8;
601
602INSERT INTO `MaintenanceEngineer_Maintains_Airplane` (`meid`, `Aiplane_registration_number`)
603VALUES
604        (990001,'N12345'),
605        (990002,'N12345'),
606        (990003,'N12345'),
607        (990004,'N12345'),
608        (990005,'N12345'),
609        (990006,'N12345'),
610        (990002,'N17523'),
611        (990003,'N17523'),
612        (990004,'N23456'),
613        (990005,'N23456'),
614        (990006,'N23456'),
615        (990001,'N98765'),
616        (990002,'N98765'),
617        (990003,'N98765'),
618        (990004,'N98765'),
619        (990001,'N90001'),
620        (990002,'N90001'),
621        (990003,'N90001'),
622        (990001,'N90002'),
623        (990002,'N90002'),
624        (990003,'N90002'),
625        (990004,'N90003'),
626        (990005,'N90003'),
627        (990002,'N90004'),
628        (990004,'N90004'),
629        (990006,'N90004'),
630        (990001,'N90005'),
631        (990002,'N90005'),
632        (990006,'N90005');
633
634
635# Dump of table Pilot_Operates_Flight
636# ------------------------------------------------------------
637
638DROP TABLE IF EXISTS `Pilot_Operates_Flight`;
639
640CREATE TABLE `Pilot_Operates_Flight` (
641  `pid` int(11) NOT NULL,
642  `flight_number` varchar(8) NOT NULL,
643  `projected_departure_datetime` datetime NOT NULL,
644  PRIMARY KEY (`pid`,`flight_number`,`projected_departure_datetime`),
645  KEY `flight_number` (`flight_number`,`projected_departure_datetime`),
646  CONSTRAINT `pilot_operates_flight_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `Pilot` (`pid`),
647  CONSTRAINT `pilot_operates_flight_ibfk_2` FOREIGN KEY (`flight_number`, `projected_departure_datetime`) REFERENCES `Flight` (`flight_number`, `projected_departure_datetime`)
648) ENGINE=InnoDB DEFAULT CHARSET=utf8;
649
650INSERT INTO `Pilot_Operates_Flight` (`pid`, `flight_number`, `projected_departure_datetime`)
651VALUES
652        (990201,'N124','2015-08-09 08:21:00'),
653        (990202,'N124','2015-08-09 08:21:00'),
654        (990203,'N124','2015-08-09 08:21:00'),
655        (990201,'N124','2015-09-07 08:21:00'),
656        (990204,'N124','2015-09-07 08:21:00'),
657        (990205,'N124','2015-09-07 08:21:00'),
658        (990201,'N124','2015-10-07 08:21:00'),
659        (990204,'N124','2015-10-07 08:21:00'),
660        (990201,'U987','2015-06-07 10:23:00'),
661        (990203,'U987','2015-06-07 10:23:00'),
662        (990205,'U987','2015-06-07 10:23:00'),
663        (990201,'U987','2015-07-07 10:23:00'),
664        (990202,'U987','2015-07-07 10:23:00'),
665        (990206,'U987','2015-07-07 10:23:00'),
666        (990201,'U987','2015-08-07 10:23:00'),
667        (990204,'U987','2015-08-07 10:23:00'),
668        (990206,'U987','2015-08-07 10:23:00'),
669        (990201,'U987','2015-09-07 10:23:00'),
670        (990204,'U987','2015-09-07 10:23:00'),
671        (990201,'U987','2015-10-07 10:23:00'),
672        (990202,'U987','2015-10-07 10:23:00'),
673        (990201,'UC725','2015-10-11 11:25:00'),
674        (990202,'UC725','2015-10-11 11:25:00'),
675        (990203,'UC725','2015-10-11 11:25:00'),
676        (990201,'UC725','2015-10-12 11:25:00'),
677        (990204,'UC725','2015-10-12 11:25:00'),
678        (990205,'UC725','2015-10-12 11:25:00'),
679        (990201,'UC725','2015-10-13 11:25:00'),
680        (990203,'UC725','2015-10-13 11:25:00'),
681        (990206,'UC725','2015-10-13 11:25:00'),
682        (990201,'UC725','2015-10-14 11:25:00'),
683        (990203,'UC725','2015-10-14 11:25:00'),
684        (990206,'UC725','2015-10-14 11:25:00'),
685        (990201,'UC725','2015-10-15 11:25:00'),
686        (990202,'UC725','2015-10-15 11:25:00'),
687        (990204,'UC725','2015-10-15 11:25:00'),
688        (990201,'UC6024','2016-01-02 06:45:00'),
689        (990202,'UC6024','2016-01-02 06:45:00'),
690        (990204,'UC6024','2016-01-02 06:45:00'),
691        (990201,'UC6024','2016-01-03 06:45:00'),
692        (990202,'UC6024','2016-01-03 06:45:00'),
693        (990204,'UC6024','2016-01-03 06:45:00'),
694        (990201,'UC6024','2016-01-04 06:45:00'),
695        (990202,'UC6024','2016-01-04 06:45:00'),
696        (990204,'UC6024','2016-01-04 06:45:00'),
697        (990201,'UC6024','2016-01-05 06:45:00'),
698        (990202,'UC6024','2016-01-05 06:45:00'),
699        (990204,'UC6024','2016-01-05 06:45:00'),
700        (990201,'UC2084','2016-02-01 10:35:00'),
701        (990202,'UC2084','2016-02-01 10:35:00'),
702        (990203,'UC2084','2016-02-01 10:35:00'),
703        (990201,'UC2084','2016-02-02 10:35:00'),
704        (990204,'UC2084','2016-02-02 10:35:00'),
705        (990205,'UC2084','2016-02-02 10:35:00'),
706        (990201,'UC2084','2016-02-03 10:35:00'),
707        (990202,'UC2084','2016-02-03 10:35:00'),
708        (990206,'UC2084','2016-02-03 10:35:00'),
709        (990201,'UC2084','2016-02-04 10:35:00'),
710        (990203,'UC2084','2016-02-04 10:35:00'),
711        (990204,'UC2084','2016-02-04 10:35:00'),
712        (990201,'UC2084','2016-02-05 10:35:00'),
713        (990204,'UC2084','2016-02-05 10:35:00'),
714        (990205,'UC2084','2016-02-05 10:35:00'),
715        (990201,'UC2084','2016-02-06 10:35:00'),
716        (990202,'UC2084','2016-02-06 10:35:00'),
717        (990203,'UC2084','2016-02-06 10:35:00');