Functional Dependencies, Candidate Keys and DB Design.
Slide 16 of the ER Review with the "Meeting Locations" example includes the following entities:
Organization(OrganizationName)
Date(Day, Time)
MeetingLocation(LocationID) - ID added to allow conversion to relational database.
and the relationship MeetsAt relating those three entities.
Transforming the MeetsAt relationship to a relation yields
MeetsAt(LocID, OrganizationName, Day, Time)
If we model MeetsAt as a ternary relationship, as done in the slide,
the primary key would be
LocID, OrganizationName, Day, Time
If we had modeled MeetsAt as a binary relationship between
Organization and MeetingLocation, with
Day and Time as attributes, then the primary key
would be just
LocID, OrganizationName
Questions:
-
What functional dependency is implied by the second design that is not implied by the first design?
-
What likely facts could not be represented in the
second case?
- Give an example.
- State the general rule in English.
Suppose we want to enforce the following two constraints:
-
An organization may not meet at two places at the same time.
-
Two organizations may not meet at the same place at the same time.
Questions:
-
What two functional dependencies express these two constraints?
-
Given the above functional dependencies, what are the candidate
keys for the relation MeetsAt?
Note that this problem could have been given in CIS 670.