Assignment Questions
1. Determine with explanations and examples (where appropriate) if each of the following is a trivial functional dependency, where Φ is the empty set, and A ≠ Φ,
(a) A → Φ
(b) Φ → A
(c) Φ → Φ
2. Consider the relation R(A1, A2, …, An), where each Ai, i = 1, 2, …, n, is an atomic (i.e.
simple) attribute. Let F be an arbitrary set of functional dependencies on R, show that
|F+| ≤ 22n .
3. Consider a relation consisting of the attributes A, B, C, with the following set of functional dependencies F
A→BC
B→AC
C→AB
Determine four different canonical covers for F.
4. Prove that functional dependency satisfies the formal definition of multivalued dependency.
5. Consider the following relations for an order processing application database at company Global-UK.
Order (O#, Odate, Cust#, Total_amount)
Order-Item (O#, I#, Qty_ordered, Total_price, Discount%)
1
Here O#, I#, Cust# denote respectively the order number, item number, and customer number. Assume that each item has a different discount. The Total_price refers to the total price of one item, Odate is the date on which the order was placed, and the Total_amount is the amount of the order. Let us apply a natural join on the relations Order-Item and Order and call the result RelationX.
(i) Write down the schema of RelationX.
(ii) Determine the primary key for RelationX.
(iii) What are the functional dependencies of RelationX. You should state clearly any assumptions that you make. These assumptions should be reasonable assumptions.
(iv) Is RelationX in 2NF or 3NF? You should justify your answers.
6. Consider the relation concerning refrigerators
Ref (Model#, Year, Price, Manuf_Plant, Color)
and the following set of functional dependencies:
Model# → Manuf_Plant
Model#, Year → Price
Manuf_Plant → Color
(i) Evaluate each of the following as a candidate key for Ref, giving reasons why it can or cannot be a candidate key:
a. {Model#},
b. {Model#, Year},
c. {Model#, Color}.
(ii) Based on the result of (i) above, determine whether the relation Ref is in 3NF and whether it is in BCNF. You should justify your answers.
(iii) Consider the decomposition of Ref into
R1 (Model#, Year, Price)
R2 (Model#, Manuf_Plant, Color)
Determine whether this is a lossless decomposition. You should justify your answers.
2
