Question 1. The systems analyst proposes the subsequent two relationships between the Flight and Pilot entities: What question do you need to answer to decide which of the relationships is correct?
Question 2. A part of the initial ER diagram for the Baxter Aviation database seems as follows:
(a) State in words the meaning of this relationship.
(b) Why can't this relationship be show directly in a relational database?
(c) Redraw the diagram so that it is compatible with the design of a relational database.
Question 3. Consider that you have been presented with the subsequent relation for the Baxter Aviation database: Charters (Pilot#, Pilot name, Aircraft ID#, #seats, Village, Flight#, Flight date, Flight time, Customer#, Customer name)
(a) How many entities are shown by this relation?
(b) What is the primary key of relation? (Hint: Flight# is unique; flight numbers do not repeat as they do on commercial airlines with regularly scheduled flights. Each flight will go to many villages and over time a village can receive many flights.)
(c) Give a example of one insertion anomaly present in this relation.
(d) Give a example of one deletion anomaly present in this relation.
(e) Is this relation in first, second, or third normal form? Why?
Question 4. A portion of the Baxter Aviation database is as given:
Aircraft (Aircraft ID #, Make, Model, #seats, total airtime, hours on engine)
Village (Village name, Type of runway)
Flight (Flight time, Flight date, Aircraft ID#, Flight#, Customer #)
Destinations (Flight#, Village name)
Customers (Customer name, House number, Phone, Customer #)
(a) Show the primary key for each relation.
(b) Show all foreign keys and the primary keys they reference.
(c) List all the constraints that have to be verified whenever a row is inserted into the Flight relation.