Assignment 3 Solution

Database Design [40 points]

1. (32 points) Consider a relation R(A; B; C; D; E; F; G) and the set F D of functional dependencies fB ! D; BC ! A; E ! F; AB ! C; AC ! B; AD ! Eg
        (a) Compute all candidate keys for R given F D. Show your work.

        (b) Calculate a minimal cover for F D. Show your work.

        (c) Is R in BCNF (given the set F D)? Explain. If not, provide a BCNF decomposition. Show your work.

        (d) Is R in 3NF (given the set F D)? Explain. If not, provide a 3NF decomposition. Show your work.


    2. (8 points) Prove that if a relation S has only one-attribute keys, S is in BCNF if and only if it is in 3NF.


Entity-Relationship Model [20 points]

Design an airport information system that will manage information about the airplanes that are stationed and maintained at the airport as well as about various ground personnel such as technicians and tra c controllers. The information that needs to be maintained is described in the remainder of this section.


Every airplane has a registration number and each airplane is of a particular model, identi ed by a model number (e.g., Airbus A330, Boeing 737). Each model has a few characteristics such as a max seating capac-ity (e.g, 330 for A330 and 149 for Boeing 737) and a maximum take-o weight. A number of technicians work at the airport. For each technician we need to store their full name, SIN, address, phone number, and salary. Each technician is an expert on one or more plane model(s). For each tra c controllers we must store information about their mandatory annual medical examination, i.e, the date of the most recent exam and the outcome of the exam. All airport employees (including technicians and tra c controllers) belong to a union and we must store the union membership number for each employee. We assume that each employee is uniquely identi ed by their social insurance number. Each airplane is tested periodically to ensure airworthiness. Each test has a Federal Aviation Administration (FAA) test number, a name, and a maximum possible score. The FAA requires a record of each time an airplane is tested, including information about the technician who administered the test, the date, the number of hours the technician spent doing the test, and the score that the airplane received on the test.


Draw an ER diagram based on the description above. Indicate the various attributes of each entity set and relationship and specify all key and participation constraints. Specify any other constraints, if necessary, in English.




1
Submission instructions

Your assignment must be typed; handwritten assignments will not be marked. You may use any word-processing software you like. Many academics use LaTeX. It produces beautifully typeset text and handles mathematical notation well. If you would like to learn LaTeX, there are helpful resources online. Whatever you choose to use, you need to produce a nal document in pdf format.

You must declare your team and hand in your work electronically using the MarkUs online system. Well before the due date, you should declare your team and try submitting with MarkUs. You can submit an empty le as a placeholder, and then submit a new version of the le later (before the deadline, of course).

For this assignment, hand in just one le: A3.pdf. If you are working in a team, only one of you should hand it in. Check that you have submitted the correct version of your le by downloading it from MarkUs;

















































2