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.
