ALTER TABLE service_point ADD CONSTRAINT pk_service_point PRIMARY KEY (location_code); ALTER TABLE flight ADD CONSTRAINT fk_origin_to_service_point FOREIGN KEY (origin ) REFERENCES service_point;...

• Format:
  
  
  • based on the New West Airlines Database
  
  
  • 7 questions (32 marks)
    
    
    
    
    1. simple query (3 marks)
    
    
    2. join (5 marks)
    
    
    3. single row function (5 marks)
    
    
    4. group function (5 marks)
    
    
    5. subqueries and set operators (5 marks)
    
    
    6. formatted report (9 marks)
    
    
    7. BONUS (5 marks)
    
    
    
    
  
  
  
  

ALTER TABLE service_point ADD CONSTRAINT pk_service_point PRIMARY KEY (location_code); ALTER TABLE flight ADD CONSTRAINT fk_origin_to_service_point FOREIGN KEY (origin ) REFERENCES service_point; ALTER TABLE flight ADD CONSTRAINT fk_dest_to_service_point FOREIGN KEY (destination) REFERENCES service_point; ALTER TABLE flight ADD CONSTRAINT pk_flight PRIMARY KEY (flight_no); ALTER TABLE flight_occurrence ADD CONSTRAINT fk_occurence_to_flight FOREIGN KEY (flight_no) REFERENCES flight; ALTER TABLE flight_occurrence ADD CONSTRAINT pk_flight_occurrence PRIMARY KEY (flight_no, flight_day); ALTER TABLE plane ADD CONSTRAINT pk_plane PRIMARY KEY (plane_registration); ALTER TABLE flight_occurrence ADD CONSTRAINT fk_occurrence_to_plane FOREIGN KEY (plane_registration) REFERENCES plane; ALTER TABLE pilot ADD CONSTRAINT pk_pilot PRIMARY KEY (pilot_no); ALTER TABLE pilot_qualification ADD CONSTRAINT fk_qualification_to_pilot FOREIGN KEY (pilot_no) REFERENCES pilot; ALTER TABLE flight_occurrence ADD CONSTRAINT fk_occurrence_to_pilot FOREIGN KEY (pilot_no) REFERENCES pilot; drop table FLIGHT_OCCURRENCE; drop table PILOT_QUALIFICATION; drop table PILOT; drop table FLIGHT; drop table SERVICE_POINT; drop table PLANE; create table SERVICE_POINT ( LOCATION_CODE varchar2 (3), LOCATION_NAME varchar2 (20)); create table PILOT ( PILOT_NO varchar2 (4), NAME varchar2 (25), ADDRESS varchar2 (25), CITY varchar2 (25), PHONE varchar2 (12), TOTAL_HOURS number (7,2)); create table PLANE ( PLANE_REGISTRATION varchar2 (5), TYPE varchar2 (20), CAPACITY number(2), MAX_SPEED number(3)); create table PILOT_QUALIFICATION ( PILOT_NO varchar2 (4), TYPE varchar2 (20)); create table FLIGHT ( FLIGHT_NO varchar2 (4), ORIGIN varchar2 (3), DESTINATION varchar2 (3), ETD number (4,2), ESTIMATED_DURATION number (4,2)); create table FLIGHT_OCCURRENCE ( FLIGHT_NO varchar2 (4), FLIGHT_DAY date, PILOT_NO varchar2 (4), PLANE_REGISTRATION varchar2 (5)); rem theDate is the date that is used when creating the rem dates for the flights, it can be altered to be more current rem date is a Sunday define theDate = '02-May-21' set echo off set verify off DELETE FROM flight_occurrence; DELETE FROM pilot_qualification; DELETE FROM pilot; DELETE FROM plane; DELETE FROM flight; DELETE FROM service_point; insert into SERVICE_POINT values ('YXD','Edmonton'); insert into SERVICE_POINT values ('YVG', 'Vermillion'); insert into SERVICE_POINT values ('YYC', 'Calgary'); insert into SERVICE_POINT values ('YXH', 'Medicine Hat'); insert into SERVICE_POINT values ('YQL', 'Lethbridge'); insert into SERVICE_POINT values ('YXE', 'Saskatoon'); insert into SERVICE_POINT values ('YYN', 'Swift Current'); insert into SERVICE_POINT values ('BIL', 'Billings, Montana'); insert into SERVICE_POINT values ('YQF', 'Red Deer, Alberta'); commit; insert into PILOT values ('0001', 'Whiteknuckles, Harry', '9 Muddy Lane', 'Swift Current', '306.911.1234', 0); insert into PILOT values ('0002', 'Timson, Jean', '2302 - 67th St NE', 'Calgary', '403.288.1492',0); insert into PILOT values ('0003', 'Lee, Wong', '17 Chester Place', 'Calgary', '403.247.6543',0); insert into PILOT values ('0004', 'Bedard, George', '7902 - 69 Ave', 'Saskatoon', '306.444.5555',0); insert into PILOT values ('0005', 'Trachuk, Tony', '123 - 3rd St', 'Saskatoon', '306.354.1234',0); insert into PILOT values ( '0006', 'Paulson, Penny', '49 Gaetz Ave', 'Red Deer', '403.356.4321',0); commit; insert into PLANE values ( 'CFUJX', 'Challenger 350', 10, 882); insert into PLANE values ( 'CGONE', 'Challenger 350', 10, 882); insert into PLANE values ( 'CGONG', 'Global 5000', 16, 944); insert into PLANE values ( 'CGOPH', 'Global 5000', 16, 944); insert into PLANE values ( 'CFILE', 'Global 5000', 16, 944); insert into PLANE values ( 'CFONG', 'Global 7500', 19, 982); insert into PLANE values ( 'CGOOH', 'Global 7500', 19, 982); commit; insert into PILOT_QUALIFICATION values ( '0001', 'Global 5000'); insert into PILOT_QUALIFICATION values ( '0001', 'Challenger 350'); insert into PILOT_QUALIFICATION values ( '0002', 'Global 5000'); insert into PILOT_QUALIFICATION values ( '0002', 'Global 7500'); insert into PILOT_QUALIFICATION values ( '0003', 'Challenger 350'); insert into PILOT_QUALIFICATION values ( '0003', 'Global 5000'); insert into PILOT_QUALIFICATION values ( '0003', 'Global 7500'); insert into PILOT_QUALIFICATION values ( '0005', 'Challenger 350'); insert into PILOT_QUALIFICATION values ( '0005', 'Global 5000'); insert into PILOT_QUALIFICATION values ( '0006', 'Global 5000'); insert into PILOT_QUALIFICATION values ( '0006', 'Challenger 350'); insert into PILOT_QUALIFICATION values ( '0006', 'Global 8000'); insert into PILOT_QUALIFICATION values ( '0006', 'Learjet 75'); insert into PILOT_QUALIFICATION values ( '0004', 'Challenger 350'); insert into PILOT_QUALIFICATION values ( '0004', 'Global 5000'); insert into PILOT_QUALIFICATION values ( '0004', 'Learjet 75'); commit; rem coding scheme rem G1XX Alberta Flight rem G2XX Interprovincial Flights rem Calgary to Edmonton Flights G10X codes rem two flights a day insert into FLIGHT values ( 'G101', 'YYC', 'YXD', 7, 1.0); insert into FLIGHT values ( 'G102', 'YXD', 'YYC', 17.00, 1.00); rem Calgary - Edmonton Flights Occurrences rem insert into FLIGHT_OCCURRENCE values ( 'G101', to_date('&theDate')+0, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G102', to_date('&theDate')+0, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G101', to_date('&theDate')+1, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G102', to_date('&theDate')+1, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G101', to_date('&theDate')+2, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G102', to_date('&theDate')+2, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G101', to_date('&theDate')+3, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G102', to_date('&theDate')+3, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G101', to_date('&theDate')+4, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G102', to_date('&theDate')+4, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G101', to_date('&theDate')+5, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G102', to_date('&theDate')+5, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G101', to_date('&theDate')+6, '0002', 'CFONG'); insert into FLIGHT_OCCURRENCE values ( 'G102', to_date('&theDate')+6, '0002', 'CFONG'); rem 2nd daily flight Calgary-Edmonton insert into FLIGHT values ( 'G103', 'YXD', 'YYC', 7.00, 1.00); insert into FLIGHT values ( 'G104', 'YYC', 'YXD', 17.00, 1.00); insert into FLIGHT_OCCURRENCE values ( 'G103', to_date('&theDate')+0, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G104', to_date('&theDate')+0, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G103', to_date('&theDate')+1, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G104', to_date('&theDate')+1, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G103', to_date('&theDate')+2, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G104', to_date('&theDate')+2, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G103', to_date('&theDate')+3, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G104', to_date('&theDate')+3, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G103', to_date('&theDate')+4, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G104', to_date('&theDate')+4, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G103', to_date('&theDate')+5, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G104', to_date('&theDate')+5, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G103', to_date('&theDate')+6, '0003', 'CGOOH'); insert into FLIGHT_OCCURRENCE values ( 'G104', to_date('&theDate')+6, '0003', 'CGOOH'); rem Calgary to Swift Current G20X codes rem one flight, twice a week, T and R insert into FLIGHT values ( 'G200', 'YYC', 'YYN', 6.30, 1.5); insert into FLIGHT values ( 'G201', 'YYN', 'YYC', 16.5, 1.75); insert into FLIGHT_OCCURRENCE values ( 'G200', to_date('&theDate')+2, '0001', 'CFUJX'); insert into FLIGHT_OCCURRENCE values ( 'G201', to_date('&theDate')+2, '0001', 'CFUJX'); insert into FLIGHT_OCCURRENCE values ( 'G200', to_date('&theDate')+4, '0001', 'CFUJX'); insert into FLIGHT_OCCURRENCE values ( 'G201', to_date('&theDate')+4, '0001', 'CFUJX'); rem Saskatoon to Edmonton G21X codes rem one flight, twice a week, M, W, F insert into FLIGHT values ( 'G210', 'YXD', 'YXE', 6.00, 2.0); insert into FLIGHT values ( 'G211', 'YXE', 'YXD', 16, 2.25); rem flight occurrence insert into FLIGHT_OCCURRENCE values ( 'G210', to_date('&theDate')+1, '0004', 'CGOPH'); insert into FLIGHT_OCCURRENCE values ( 'G211', to_date('&theDate')+1, '0004', 'CGOPH'); insert into FLIGHT_OCCURRENCE values ( 'G210', to_date('&theDate')+3, '0004', 'CGOPH'); insert into FLIGHT_OCCURRENCE values ( 'G211', to_date('&theDate')+3, '0004', 'CGOPH'); insert into FLIGHT_OCCURRENCE values ( 'G210', to_date('&theDate')+5, '0004', 'CGOPH'); insert into FLIGHT_OCCURRENCE values ( 'G211', to_date('&theDate')+5, '0004', 'CGOPH'); rem Saskatoon - Calgary routes G22X codes rem once a week on M, W insert into FLIGHT values ( 'G221', 'YXE', 'YYC', 6.5, 2.5); insert into FLIGHT values ( 'G222', 'YYC', 'YQF', 17.00, 2.75); insert into FLIGHT_OCCURRENCE values ( 'G221', to_date('&theDate')+1, '0005', 'CGONG'); insert into FLIGHT_OCCURRENCE values ( 'G222', to_date('&theDate')+1, '0005', 'CGONG'); insert into FLIGHT_OCCURRENCE values ( 'G221', to_date('&theDate')+3, '0005', 'CGONG'); insert into FLIGHT_OCCURRENCE values ( 'G222', to_date('&theDate')+3, '0005', 'CGONG'); rem Calgary to Red Deer G11x codes rem three times a week M, W, F insert into FLIGHT values ( 'G110', 'YQF', 'YYC', 8.00, 0.75); insert into FLIGHT values ( 'G111', 'YYC', 'YQF', 16.00, 0.75); rem flight occurrences insert into FLIGHT_OCCURRENCE values ( 'G110', to_date('&theDate')+1, '0006', 'CFUJX'); insert into FLIGHT_OCCURRENCE values ( 'G111', to_date('&theDate')+1, '0006', 'CFUJX'); insert into FLIGHT_OCCURRENCE values ( 'G110', to_date('&theDate')+3, '0006', 'CFUJX'); insert into FLIGHT_OCCURRENCE values ( 'G111', to_date('&theDate')+3, '0006', 'CFUJX'); insert into FLIGHT_OCCURRENCE values ( 'G110', to_date('&theDate')+5, '0006', 'CFUJX'); insert into FLIGHT_OCCURRENCE values ( 'G111', to_date('&theDate')+5, '0006', 'CFUJX'); commit; UPDATE pilot SET total_hours = (SELECT SUM (estimated_duration) FROM flight, flight_occurrence WHERE flight.flight_no = flight_occurrence.flight_no AND pilot_no = pilot.pilot_no); commit; Visio-NWA ERD.vsd New West Air (NWA) Physical ERD SERVICE_POINT Location_code (PK) VARCHAR2(3) Location_name VARCHAR2(20) PLANE Plane_registration (PK) VARCHAR2(5) Type VARCHAR2(20) Capacity NUMBER(2) max_speed NUMBER(2) FLIGHT_OCCURRENCE Flight_no (PK)(FK1) VARCHAR2(4) Flight_day (PK) DATE Pilot_no (FK2) VARCHAR2(4) Plane_registration (FK3) VARCHAR2(5) FLIGHT Flight_no (PK) VARCHAR2(4) Origin (FK1) VARCHAR2(3) Destination (FK2) VARCHAR2(3) Etd NUMBER(4,2) Estimated_duration NUMBER(4,2) PILOT Pilot_no (PK) VARCHAR2(4) Name VARCHAR2(25) Addess VARCHAR2(25) City VARCHAR2(25) Phone VARCHAR2(7) Total_hours NUMBER(7,2) PILOT_QUALIFICATION Pilot_no (FK) VARCHAR2(4) Type VARCHAR2(20) Execute the scripts in the following order: 1. create_nwa.sql 2. constraints_nwa.sql 3. load_nwa.sql March 2021: GAL is a regional airline using the latest Bombardier aircraft: https://businessaircraft.bombardier.com/en/aircraft GAL services Calgary, Edmonton, Red Deer, Swift Current and Edmonton with a modern fleet to meet all your business and personal travel needs. Calgary-Edmonton flights are available twice daily. Red Deer - Calgary flights are available M, W, F. Swift Current - Calgary flights are available T and R. Saskatoon - Calgary flights are available M and W. Saskatoon - Edmonton flights are available M, W and F. Instructor Notes: Set the variable 'theDate'in the script to establish the dates for one week of scheduled flights.