✓ The transformation steps from your EER Model (Appendix A) to your final tables (Task 1); Make sure to show each step of the transformation, and the final transformation tables; 

✓ The DDL implementation for the 'NEA Database System' tables from Task 1 (create table statements), and the required insert statements (Task 2a and 2b respectively). 

✓ The normalization of user-views and the comparison of the Top-Down and Bottom-Up database design approaches for this problem.

✓ The transformation steps from your EER Model (Appendix A) to your final tables (Task 1); Make sure to show each step of the transformation, and the final transformation tables; 

✓ The DDL implementation for the 'NEA Database System' tables from Task 1 (create table statements), and the required insert statements (Task 2a and 2b respectively). 

✓ The normalization of user-views and the comparison of the Top-Down and Bottom-Up database design approaches for this problem.

 

TASKS: 1. Transform the EER model (Appendix A) to Relational tables, making sure you show all the steps. The final set of tables should contain necessary information such as table names, attribute names, primary keys (underlined) and foreign keys (in italics). 

2. a) create table statements to implement the tables in the ORACLE Relational DBMS. When creating tables make sure you choose appropriate data types for the attributes, specify any null/not null or other constraints whenever applicable, and specify the primary and foreign keys. b) Write at least one insert statement for each of your tables using realistic data. Make sure you take into consideration all the necessary constraints.

3. Normalize the user views provided in Appendices A and B of assignment 1 part 1 specification. Clearly show individual steps of UNF, INF, 2NF and 3NF for each of the user views. List the final tables. Discuss the differences between the result of the top-down approach from question 1 above and the result of the bottom-up approach. Provide some analysis (with examples) on why they are similar or different for this particular problem.

 

APPENDIX A - EER Diagram for the NEA Database System

 

 

 

Transcribed Image Text:

FlightID estDeptDateTime actDeptDateTime actArrivDateTime avgSpeed avgHeight estDuration estFuel 1 (0,M) Flight M (1,1) 1 (0,M) mailCargoCapacity Short Haul Uses SH Aircraft AircraftID Aircraft M (1,1) M (1.1) 1 (0,M) Long Haul Uses LH Aircraft noOfMedPacks noOfDefibrillators belongs To M (1,1) M (1.1) M (0,M) M (O,M) Captain firstOfficer 1 (0,M) 1 (0,M) qualifications prevHrsPilotExp Pilot M (1.1) has Irregular Event 1 (0,M) eventNumber eventDateTime eventDescription M (1.1) 1 (1,M) Model modelID economySeats businessSeats firstClassSeats Takes cargoCapacity additional pilot Hosts Takes N (O,M) mealChoice medicalCondition activityCode activityDesc M (1.1) 1 (0,M) M (0,M) seatNum classCode JuggageLimit ticketNum Staff Flight Attendant Ticket M (1,1) Buys 1 (1,M) Customer M (1.1) DepartFrom Route 1 (0,M) Location staffID address 000003 email (customerID name address passportNume country phone DOB email passportNum phone fuelCapacity length wingspan serviceHours routeID description M (1,1) ArriveAt 1 (0,M) airportCode country Address Phone 1000 FlightID estDeptDateTime actDeptDateTime actArrivDateTime avgSpeed avgHeight estDuration estFuel 1 (0,M) Flight M (1,1) 1 (0,M) mailCargoCapacity Short Haul Uses SH Aircraft AircraftID Aircraft M (1,1) M (1.1) 1 (0,M) Long Haul Uses LH Aircraft noOfMedPacks noOfDefibrillators belongs To M (1,1) M (1.1) M (0,M) M (O,M) Captain firstOfficer 1 (0,M) 1 (0,M) qualifications prevHrsPilotExp Pilot M (1.1) has Irregular Event 1 (0,M) eventNumber eventDateTime eventDescription M (1.1) 1 (1,M) Model modelID economySeats businessSeats firstClassSeats Takes cargoCapacity additional pilot Hosts Takes N (O,M) mealChoice medicalCondition activityCode activityDesc M (1.1) 1 (0,M) M (0,M) seatNum classCode JuggageLimit ticketNum Staff Flight Attendant Ticket M (1,1) Buys 1 (1,M) Customer M (1.1) DepartFrom Route 1 (0,M) Location staffID address 000003 email (customerID name address passportNume country phone DOB email passportNum phone fuelCapacity length wingspan serviceHours routeID description M (1,1) ArriveAt 1 (0,M) airportCode country Address Phone 1000