Reference no: EM131416

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.