Sample Deliverable #3: Relational Model

1.   Schema Diagram

During the conversion of the model, a few decisions were made:

• The GROUP entity was renamed SWAPPER_GROUP as a table, because "group" is a reserved word in SQL.
• A surrogate key was used for the SWAPPER table. This was to simplifies foreign references to this table. Of course, the email column of SWAPPER could have been used as the primary key. With our choice of a surrogate identifier however, we must enforce the uniqueness of the email field by a UNIQUE constraint in SQL.
• The OwnerID column in ITEM and the BorrowerID column in BORROW are both renamed foreign keys of ID in the SWAPPER table.
• The combination key (ItemID, BorrowerID, RequestDate) was chosen as the primary key for the BORROW table since a swapper should only be able to request a single item once per day.

2.  Functional Dependencies and 3NF

All tables have primary keys, and all columns in each table contain atomic values, so the schema is in 1NF.

The functional dependencies are evaluated separately for each table.

• BORROW Table

  (ItemID, BorrowerID, RequestDate) -> LoanDate
  (ItemID, BorrowerID, RequestDate) -> ReturnDate
  (ItemID, BorrowerID, RequestDate) -> ReturnCondition

  No column is dependent on anything other than the full primary key.

• CONDITION Table

  RetCondition -> Points

  No column is dependent on anything other than the full primary key.

• GROUP_MEMBERSHIP Table

  (GroupName, SwapperID) -> JoinDate

  No column is dependent on anything other than the full primary key.

• ITEM Table

  There is no natural primary key for the ITEM table, and so a surrogate key was used. Since, by definition, all columns are dependent upon a surrogate key, the only type of validation problem that can occur is if any column is dependent upon a column other than the surrogate key. There are no such functional dependencies, and so the table is in 3NF.

• CATEGORY Table

  This table has no functional dependencies.

• SWAPPER Table

  Since the Email column could have been the primary key, we will use it instead of the ID surrogate key column to validate:

  Email -> Name
  Email -> Password

  No column is dependent on anything other than the full primary key.

• SWAPPER_GROUP Table

  GroupName -> IsInvitationOnly

  No column is dependent on anything other than the full primary key.

3.   Changes from the Previous Deliverable

No changes were made to either of the Entity-Relationship Model or Proposal documents.