Lab 3 Design Phase:
Normalize the Design

The Assignment

You may have noticed what appears to be one or more transitive dependencies in the data in tblPerson. If so, you were correct! In this assignment, your task is to normalize the design of tblPerson. During the Implementation Phase, you will normalize the data itself.

Design Deliverables Summary

Notes:
  • During Design Phase no changes are allowed to the team database.
  • Download your team work record log file from assignment (L2i-DB), record the activities
  • [req 3:01] watch the Lab 3 design video from Canvas to see the functional dependencies among the attributes in the tblPerson table and the conceptual data model for normalized tables:
    The functional dependencies and conceptual data model are based on the following data:
    • Lab 2 data (imported from spreadsheets), and
    • Lab 3 new information that is under Analyst Notes & Deliverables Details section of this Lab
Before the next database class:
  • In your latest team work record log file (downloaded from previoius assignment on Canvas), record the activities
  • Download the "final" models from Lab 2: (L2i-DB).
    Based on all information above,
  • [req 3:03] Revise/update the Lab 2 Visio Logical Data Model (make sure entities including the new entity and their attributes are updated)
  • [req 3:04] Revise/update the Lab 2 MySQL Workbench Physical Data Design (make tables including the new table and their columns are updated)
  • Place the LM, PM, and team work record log files in a zipped folder and upload the folder to the assignment L3d-LM&PM on Canvas.
  • Only one submission from one team member is required

Analyst Notes & Deliverables Details

Lab 3 new information: The types of memberships are as follows:
  • Corporate: Annual fee $250; Group.
  • Patron: Annual fee $100; Individual.
  • Family: Annual fee $50; Group.
  • Individual: Annual fee $25; Individual.
  • Student: Annual fee $10; Individual.
  • Note: Memberships are either "Group" or "Individual" memberships.
    • "Group" memberships have no limit on the number of people included under the membership.
    • "Individual" memberships can include only one person.
Functional Dependencies.
  • This is an example of the standard notation to use in this course for a functional dependency:
    StudentID --> StudentName, StudentAddress

End of Instructions