Lab 6 Implementation:
Committee Officers

The Assignment

Import the data about SCA officers and migrate it to the normalized tables developed in Lab 6 Design.

Database Maintenance and Lab Skills

Database Maintenance
  • Team work log: download your team work record file from assignment L6d-LM&PM and record the team activities
  • Before continuing to build your team's database, first eliminate any prior issues:
    • [req 6:00] Correct all errors (or other requested changes) identified by your instructors in the previous Lab cycle.
Lab Skills
  • Creating production tables using DDL
  • Importing data from spreadsheets and other flat files.
  • Organizing/Migrating data to STAGING tables.
  • Migrating "staged" data into the final normalized tables.

Analyst Notes & Deliverables Details

NOTE: Create a single .sql file named as Lab6_YOUR_TEAM_NAME.sql. Record in the file all the DDL statements for creating tables and DML statements for populating the new tables. Preceed each SQL statement with a brief comment describing what that SQL statement is designed to accomplish
Prepping the Spreadsheet Data. [req 6.02] Note: the spreadsheet data is not organized properly in rows and columns. Committee Names, for example span several columns. Such "merged cells" cannot exist in a database table. Instead, the Committee names should be in their own column.
  • Add a collumn at the left
  • Copy the Committee names into the Committee column in the appropriate rows.
  • Unmerge the original merged cells.
  • Remove spaces and special characters from column headings.
  • Check your reformatting against this partial illustration.
New Data. [req 6.05] Note: In the data for "SCA Officers" there is a committee ("Programs") that was not included in the list of committees that you imported in Lab 5. "Programs" is a Support committee. You will need to enter the data for the Programs committee before you can import the SCA Officers information. Write a script to enter the data. (Two INSERT INTO statements: one into tblCommittee and one into tblCommitteeSupport.)  
The information you need to enter is here:
  • Use INSERT INTO to add the “Programs” committee to your database. Their mission is to coordinate the programs offered by the various city committees. They meet the third Friday of each month at 7 PM. Their budget is $500 and they have already spent $50. Their contact is the Parks and Rec. Dir.
    Quality Assurance.
    • [req 6.05-a] Table -- Committee: 14 records.
    • [req 6.05-b] Table -- Support Committee: 8 records.
Tables. [req 6.10] Download the Lab 6 final physical data model in Assignment L6i-DB from Canvas. Based on the data model using DDL to create tables in your database to hold the data.
  • For each new table, use a single CREATE TABLE ... command to create both the table and any constraints on the table, including FKs and field constraints.
    Do not use any ALTER statements.
  • After creating the new tables and relationships required by Lab 6 final physical data model, import the data into a new tblCommitteeOfficers_IMPORT table.
  • Migration Map
    Follow the steps numbered in the migration map.
    Be aware of some 'gotchas' that can occur when working with messy user data.
    • Be careful not to INSERT blank/invalid data into the three new tables. Carefully use the WHERE clauses of your INSERT statements to elimininate any blank/invalid data.
    • Clients don't always name or spell things the same way in different places. The official name for one of the committees is "Pac Rim Regatta". Any other spelling is incorrect and can cause errors.
    • There are two people named George Vancouver. It is important to distiguish between them and assign the right PersonID to their service records. The only hint in the new data is their phone numbers. There are several ways you can fix this problem. Choose any method you like.
    • Quality Assurance.
      • [req 6.10-1]  Table -- IMPORT: 122 records (107 instances of people in a committee position + 15 committee positions that have never been occupied (e.g. co-chair of Programs committee; In addition, there may be some number of empty rows).
      • [req 6.10-4b] Table -- Position Titles: 8 records ("Ex Officio" appears just once).
      • [req 6.10-6a] Table -- Authorized Committee Positions: 85 records.
      • [req 6.10-6b] Table -- Service History: 107 records.
        • 12 records have an EndDate.

Views. [req 6.30] Create a view that counts the number of authorized positions for each committee, as shown in this illustration.
Note: the sort order is irrelevant and does not need to match the order in the illustration.
  • Quality Assurance.[req 6.30] View -- # of positions for each committee:
    • 5 records for Executive Committee.
    • 7 records for Finance Committee.
    • 6 records for Membership Committee.
    • 6 records for Programs Committee.
    • ...and so on...

Submission:

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

End of Instructions