The problems in this exam are based on the Baxter Aviation scenario that you used for the first exam. Please read the following narrative carefully, as many of the rules have changed!

Baxter Aviation’s business has increased so dramatically that the company is now going to offer regularly scheduled flights to the remote villages. Each flight is scheduled to make a circle of two or more villages.

In terms of the database used to manage flights and associated data, this change has the following impacts:

A) Flight numbers for the regularly scheduled flights are not unique. However, any flight number will be used at most once a day.

B) The database must now store passenger lists. In other words, the database must include every person that has purchased a ticket and the flight(s) on which they fly.

C) Children under two years of age fly free if they are held by their parents; children under 12 years of age pay half price for their tickets. The database must therefore also include data about the price paid for each ticket. Otherwise, Baxter Aviation will not have enough data to do its accounting.

D) The database must store the villages to which each flight flies. The order of the village in a flight’s route is given by the Order_in_route attribute. For ex, if a flight goes from Ambler to Shungnak to Kobuk and then back to Kotzebue, the value of Order_in_route for Ambler will be 1, for Shungnak 2, and Kobuk 3.

Consider the following relation in light of the revised rules presented above:

Flights (Pilot#, Flight#, Village name, Flight date, Aircraft ID#, Customer #, Flight time, Customer first name, Customer last name, Customer home phone number, Ticket price paid, Pilot home phone number, Pilot house number, #seats in aircraft, Order in route)

1) What is the primary key of this relation?

2)

a) Give a specific ex of an insertion anomaly in this relation.

b) Give a specific ex of a deletion anomaly in this relation.

c) Give a specific ex of a modification/update anomaly in this relation.

3) What are the functional dependencies among the data represented in this first normal form relation? (Major hint: Don’t forget that functional dependencies are independent of the structure of a relation.)

4) In which normal form is the relation? Why?

5) Transform this relation into a set of third normal form relations. For each table indicate its primary key; underlining is the easiest way to show this. Then list the foreign keys in each table and indicate that relations that they reference.

6) Consider the following request for information from the database:

“Display the first and last names of every passenger that flew on flight #180 on 4/6/11.”

Using the relations you created for problem 5, prepare the sequence of relational algebra operations needed to perform the query.

7) Consider the following request for information from the database:

“Display the names of all the villages that are not scheduled to have flights on 12/1/11.”

Using the relations you created for problem 5, prepare the sequence of relational algebra operations needed to perform the query.