COMP3311 Final Exam 20T3

Question 10 (5 marks)

Consider the schema:

Flights(fid, from, to, distance, departs, arrives, price)
Aircraft(aid, aname, range)
Certified(employee, aircraft)
Employees(eid, ename, salary)

The attributes fid, aid and eid are primary keys in their respective relations. In the Certified relation, the attribute employee is a foreign key to the Employees relation, and the attribute aircraft is a foreign key to the Aircraft relation. In the Flights relation, the attributes from and to refer to airports and the distance attribute gives the distance between them. Also in the Flights relation, the attributes departs and arrives are timestamps that refer to departure and arrival times on a particular day. The meaning of all other attributes is obvious from their names.

While the schema might look OK, it is not (quite) in Boyce-Codd normal form.

Determine the functional dependencies in this schema.
Identify the functional dependency that violate BCNF and propose a new version of the schema that is in BCNF.

Hint: the notion of a route is relevant.

Instructions:

Type your answers to this question into a file called q10.txt
Submit via: give cs3311 exam_q10 q10.txt or Webcms3