Description
Assignment Questions
-
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
-
-
-
Φ → Φ
-
-
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 .
-
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.
-
Prove that functional dependency satisfies the formal definition of multivalued dependency.
-
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.
-
Write down the schema of RelationX.
-
Determine the primary key for RelationX.
-
What are the functional dependencies of RelationX. You should state clearly any assumptions that you make. These assumptions should be reasonable assumptions.
-
Is RelationX in 2NF or 3NF? You should justify your answers.
-
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
-
Evaluate each of the following as a candidate key for Ref, giving reasons why it can or cannot be a candidate key:
-
-
{Model#},
-
-
-
{Model#, Year},
-
-
-
{Model#, Color}.
-
-
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.
-
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