Lab 3b Implementaton Phase:
Migrate the Data

The Assignment

In this assignment, your primary task is to migrate the Persons data (currently stored in tblPersonSTAGING) to the newly normalized table structure.

Database Maintenance and Lab Skills

Database Maintenance
  • Team work log: download your team work record file from assignment L3Ai-DB 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

Analyst Notes & Deliverables Details

NOTE: Create a single .sql file named as Lab3b_YOUR_TEAM_NAME.sql. Record in the file all DML statements used in the lab. Preceed each SQL statement with a brief comment describing what that SQL statement is designed to accomplish
Tables: [req 3.10] Populating the new tables with data. Note the order of tables being populated. Think about why you need to populate the tables in that order: tblMembshipType, tblMembership, tblPerson. Please ask if you are not sure why.
  • [req 3.10a] Migrate data from tblPersonSTAGING to tblMembershipType.
    1. Use INSERT INTO ... SELECT DISTINCT to copy unique membership type data to tblMembershipType
    2. If you have not already done so, add the field, MembershipTypeID to tblPersonSTAGING. ("Allow Nulls")
    3. Use a bulk UPDATE statement to fill the MembershipTypeID field of tblPersonSTAGING.
    4. Currently the isGroup, AnnualFee fields have null value in tblMembershipType. Based on the information about each membership type in Lab 3 Design (see the menu at left),
      write individual UPDATE statements to update those fields one by one
    5. Finish populating tblMembershipType: according to the information in Lab 3 Design (see the menu at left) there are two records (i.e. two membership types) missing from tblMembershipType. Use two individual INSERT statements to....
      * Add the two missing records with MembershipType, AnnualFee and IsGroup data.
    • Quality Assurance check for tblMembershipType:
      * 5 records after data migration.
      * PK filled automatically.
  • [req 3.10b] Migrate data from tblPersonSTAGINGto tblMembership, and
    1. Use INSERT INTO ... SELECT DISTINCT to copy unique membership data to tblMembership
    2. If you have not already done so, add the field, MembershipID, to tblPersonSTAGING. ("Allow Nulls")
    3. Use a bulk UPDATE to fill MembershipID in tblPersonSTAGING.
    • Quality Assurance check for tblMembership:
      * 48 records after data migration.
      * PK filled automatically.
  • [req 3.10c] Populate tblPerson.
    1. Use INSERT INTO ... SELECT DISTINCT to copy data from all relevant fields and rows in tblPersonSTAGING to tblPerson
    • Quality Assurance check for tblPerson:
      * 59 records after data migration.
      * PK filled automatically.
Views: Create or revise views only after the three production tables have been populated.
  • Note: All views required in the SCA project throughout the quarter will draw data only from production tables (e.g. tblPerson, tblMembership, and tblMembershipType). Views required in the SCA project will never use transitory tables, such as tblPersonBACKUP OR tblPersonSTAGING.
  • The views created in Lab 2 (see the requirements [req 2.30] and [req 2.40] in Lab 2 Implementation) are now broken because the data that was previously stored in just one table (tblPerson) is now distributed across several tables. You must repair the views to draw their data from only these three tables -- (you need to remove the existing tables, then add new tables to the view. Reference the note on views with multiple tables on the page of Create Views in the How To section of the left navigation pane): tblPerson, tblMembership and tblMembershipType.
    • [req 2.30] Rename the view to vuePersonMemberType_2_30
      This view returns all persons and their membership type. The output should include the person's first, middle and last names, membership type, and email address.
      Quality Assurance: 59 rows and 5 columns returned.
    • [req 2.40] Rename the view to vueIndis&Students_2_40 This view returns all persons who hold individual or student memberships. The output should include the person's first, middle and last names, membership type, and email address.
      Quality Assurance: 40 rows and 5 columns returned.
  • [req 3.30] Create a view named vueMembershipNumberInfo_3_30 that lists of all membership numbers that are currently paid up. Columns to show:
    • MembershipType
    • MembershipNumber
    • Count of the number of people associated with that MembershipNumber.
    • Label the IsCurrentMember column with the alias IsPaidUp. (See the illustration below.)
    • Quality Assurance: View: 35 rows.
      For example the Perry family membership will appear on one line as:
      View Member Count

Submission:

  • [req 3:40] Submit to Canvas (L3Bi-DB) your most recent Team Work Record and the SQL file: Lab3b_YOUR_TEAM_NAME.sql.

End of Instructions