Assignment 3 Database Design

CSC343
Assignment 3

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
{B → D, BC → A, E → F, AB → C, AC → B, AD → E}
(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 traffic
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, identified by a model
number (e.g., Airbus A330, Boeing 737). Each model has a few characteristics such as a max seating capacity (e.g, 330 for A330 and 149 for Boeing 737) and a maximum take-off 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 traffic 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 traffic controllers)
belong to a union and we must store the union membership number for each employee. We assume that
each employee is uniquely identified 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 wordprocessing 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 final 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 file as a placeholder, and then submit a new version of the file later (before the deadline, of course).
For this assignment, hand in just one file: 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 file by downloading it from MarkUs;
2