[Solved] CIS560 Homework 5

Part 1

For the first three questions, use the below relation and known functional dependencies. Relation

Route(ArrivalTime, DepartureTime, Location, Bus) Functional Dependencies

{DepartureTime, Location} -+ {Bus} {ArrivalTime} {DepartureTime} {Bus, ArrivalTime} —> {Location}

Question 1

20 points – Compute the closure of all sets of attributes.

Question 2

5 points – Using the closures from Question 1, identify the keys for the relation Route. Remember we want minimal keys, not just any superkey.

Question 3

10 points – Using the closures from Question 1, list all non-trivial functional dependencies for the relation Route.

Part 2

For the below relation and known dependencies to answer Questions 4 and 5.

Relation

Shuttle(DepartureTime, Route, Driver, Bus, Capacity)

Functional Dependencies

{Bus, DepartureTime} {Driver} {Bus} -+ {Capacity}

{Route, DepartureTime} -+ {Bus}

Question 4

5 points – For each of the three provided functional dependencies above, X —> Y, compute X. Once you have the closures computed, identify the key(s) for the relation Shuttle.

Question 5

10 points – From Question 4, we can see that Bus —> Capacity is a violation of Boyce-Codd Normal Form (BCNF). Decompose the relation Shuttle into BCNF relations, starting with this dependency violation.

• Show each iteration in your work, and make clear which relations are your resulting BCNF relations.
• Identify the key in each of the resulting BCNF relations.