Homework

• Do you see any potential data redundancy here? If so, give 1 or 2 examples.

  You need to store a member's first/last name and address for every group they are a member of, and you need to store a group's title/time/topic for every member it has. For example, if a Jenny is a member of 4 groups, then her first/last name and address must be stored 4 times (when once would be enough). This not only wastes space, but also invites inconsistency in the database.

• What are the functional dependencies? List them in the form A -> B, C, meaning A functionally determines B and C.

  memberID -> firstName, lastName, street, city, state, zipcode
  groupTitle -> groupTime, groupTopic
  

  Paraphrases: If you know a member's ID, then you know that there is a unique name and address associated with that ID. If you know a group's title, then you can find a unique time and topic value. We'll ignore the issues related to zipcodes, cities, and street addresses.

• Is this schema in BCNF? If so, explain why, if not, decompose it so that it is.

  The key of the given relation is:

  {memberID, groupTitle} 
  

  The relation is not in BCNF because there exists at least one functional dependency whose left hand side is not a key (i.e., the two FDs listed above have left-hand sides that are not keys).

  A reasonable decomposition of the given relation is as follows:

  Members(MEMBERID, firstName, lastName, street, city, state, zipcode)
  Group(TITLE, time, topic)
  Membership(MEMBERID, GROUPTITLE)
  

  Note here that we have a separate relation for each of the functional dependencies given above, and that the FDs in each of these relations have keys on their left hand side. We also added the linking table to make sure that we save the relationships between the two tables. This linking table is in BCNF because there are no (non-trivial) FDs for it at all, and thus, there can be no FDs with non-keys on the left hand side.

Personal Database Project

As usual, I accepted just about everything here, so long as you built something that made sense and did it properly.