LOGISTICS

• Turn-in: Please turn in your written work before the beginning of the class due date. Please put your name and preferred email address on every page that you turn in - you'd be surprised how many times assignments mysteriously come apart. Please remember that written work will not be accepted. All submissions are to be made  electronically via the MOODLE.
• Legibility: Please use a word processor or type your homework assignments. E/R diagrams should be done using a suitable ER diagramming tool or a graphics editor..
• Deadline: The on-time deadline for all students is in class on the due date.
• Late policy: All written work is to be turned in during class on the day that it is due. All assignments turned in late will be penalized at the rate of 10% per day for each unexcused late day.

THE ASSIGNMENT

1. This question is based on the following E/R diagram:

   

   It represents a "parts explosion," in which any part is either a basic part, or an assembly consisting of some number of one or more subparts. For instance, a bicycle might be described as an assembly consisting of one frame and two wheels, while a frame is a basic part and a wheel is an assembly consisting of one tire, one rim, and 48 spokes.

   (a)

   Convert the E/R diagram to relations, using the strategy described in the text for going from E/R to relations.

   (b)

   Your answer to (a) should have several relations whose schemas are the same or one is a subset of the other. Do you want to retain all these relations? Justify your answer. Note: there can be more than one right answer; we are more interested in your reasoning than in "yes" or "no."

2. There are a number of different kinds of automobiles. All automobiles have a model name and a manufacturer (e.g., Honda = manufacturer; Accord = model). Most automobiles have gas engines; these have a number of cylinders (noCyl, as an attribute). A few automobiles have electric engines; these have a voltage. Some automobiles are SUV's; these may be either gas or electric, and they have an attribute capacity.

   (a)

   Draw an E/R diagram for the classes of automobiles. Hint: You do not need to invent entity sets such as "electric SUV"; the entities that are electric SUV's will have a representative in both the "electric" entity set and the SUV entity set.

   (b)

   Convert your E/R diagram to relations, using the "E/R" style of translation. Show the schema of each relation, including keys, and also show how the data would be placed in this database schema, if the cars in question were a Honda Accord, 4 cyl., a Nissan Pathfinder SUV with a 6-cylinder engine and a 100 cu. ft. capacity, a General Motors EV1 electric with a 12-volt engine, and a Range-Rover eRover model electric SUV with a 110-volt motor, a 90 cu. ft. capacity, and a 200-mile extension cord (OK; the extension-cord bit is a joke, but use the rest of the data).

   (c)

   Show the design using the "object-oriented" style of translation to relations. Show how the data of part (b) would be stored in the relations.

   (d)

   Show the design using a single relation with nulls. Again, show the data of part (b) in this relation.

3. Consider a relation with schema R(A,B,C,D,E) and functional dependencies
   B->E, C->D, E->A, DA ->B

   (a)

   What are all the nontrivial functional dependencies that follow from the given dependencies? You need report only those that have singleton right sides and minimal left sides; e.g., you do not have to report XY->F if X->F is a given or inferred FD.

   (b)

   What are all the keys of R?

   (c)

   How many superkeys for R are there that are not keys? Explain your reasoning for partial credit.

   (d)

   Which of the 4 given dependencies violate BCNF, if any?

   (e)

   Which of the 4 given dependencies violate 3NF, if any?

   (f)

   Suppose we decompose relation R(A,B,C,D,E) into relation S(A,B,C) and other relations. Give the nontrivial functional dependencies that hold in S. Your answer must include derived dependencies, but as in part (a) it is sufficient to limit your answer to FD's with singleton right sides and minimal left sides.

4. Consider a relation R(A,B,C,D,E). Suppose that the following five functional dependencies hold on R:

   A -> D
   AB -> C
   B -> E
   D -> C
   E -> A
   

   Now suppose that we decompose relation R so that one of the new relations is R1(A,B,C). Given the complete set of FD's above, specify all keys for R1. Don't forget that a key must be minimal, i.e., no strict subset of the attributes in a key can also form a key.

5. A database designer has as their first assignment to design the schema for a company database. Each employee has an ID (unique across employees), Name, Address, Office, and Salary. The designer decides to create the following four relations:

   EmpName(ID, Name)
   EmpAddress(ID, Address)
   EmpOffice(ID, Office)
   EmpSalary(ID, Salary)
   

   a)

   State the completely nontrivial functional dependencies for each relation.

   b)

   Are all four relations in Boyce-Codd Normal Form (BCNF)?

   c)

   Is this a good database design? Why or why not?