Lab 3a Implementation Phase:
Normalize the table design

The Assignment

In this assignment, your task is to normalize the table structure of your database table tblPerson. This is a two-part process (Lab 3a Implementation and Lab 3b Implementation). In this assignment (part 3a) you will create the final table structure, but you will not yet move the data from tblPersonSTAGING to the new tables.

Database Maintenance and Lab Skills

Database Maintenance
  • Team work log: download your team work record log file from assignment L3d-LM&PM and record the team activities
  • Before continuing to build your team's database, first eliminate any prior issues:
    [req 3:00] Correct all errors (or other requested changes) identified by your instructors in the previous Lab cycle.
Lab Skills
  • For every relationship that exists in the database design at this time, there must be a relationship established between the tables, with referential integrity enforced through appropriate primary keys (PKs) and foreign keys (FKs). To establish the relationships, follow the instructions in How to Create an FK Relationship in the left menu. Later, in the quarter you will also learn to establish FK relationships by writing and executing appropriate SQL scripts.
  • Follow the instructions in How to Create Other Features in the left menu to create unique columns for some tables .
*Note: At this point in the project, tblSisterCity is a production table, but it is unrelated to any other tables. (tblSisterCity will remain unrelated for several weeks.)
Begin implementing the new design changes (below) only after all issues have been corrected.

Analyst Notes & Deliverables Details

Tables.
  • [req 3:10] Since our analysis this week has revealed that tblPerson in the team database from last week is not in 3NF (3rd normal form) we will "re-purpose" the existing tblPerson from a "production" table to a "transition" table:
    1. If your team created a PersonID field in tblPerson, delete that field. (Why? The PersonID field is no longer appropriate since this is no longer a "production" table.)
    2. Rename tblPerson to tblPersonSTAGING.
  • [req 3:11] Using the outline in Backup a Table in the left menu, make a backup copy of tblPersonSTAGING. Rename the backup table to tblPersonBACKUP.
  • [req 3:12] Download the Lab 3 final physical data model in Assignment L3Ai-DB from Canvas. Based on the physical data model create the new tables: tblPerson, tblMembership, and tblMembershipType.

    Note: The PK for each production table will be an auto-increment field. (See Create an Identity PK in the left menu).
Referential Integrity:
  • [req 3:14] Create the foreign key relationships between tblPerson*, tblMembership*, and tblMembershipType*, based on the final physical design (.mwb) from Lab 3 Design (See Create an FK Relationship in the left menu).
Unique keys:
  • [req 3:15] Create the unique keys in the tables according to the final physical model (See Create Other Features in the left menu).
Submission:
  • [req 3:90] Submit to Canvas (L3Ai-DB) your most recent Team Work Record .

End of Instructions