Lab 5 Implementation:
Committee Data

The Assignment

Create tables to hold “committee” data.

Database Maintenance and Lab Skills

Database Maintenance
  • Team work log: download your team work record file from assignment L5d-LM&PM and record the team activities
  • Before continuing to build your team's database, first eliminate any prior issues:
    • [req 5:00] Correct all errors (or other requested changes) identified by your instructors in the previous Lab cycle.
Lab Skills Needed for Implementation

Analyst Notes & Deliverables Details

NOTE: Create a single .sql file named as Lab5_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
Tables.
  • [req 5.10]
    • Download the Lab 5 final physical data model in Assignment L5i-DB from Canvas. Based on the data model, using DDL scripts to create the three new final tables for this lab in your database to hold the data. Establish all required keys and type fields, constraints, indexes and relationships. Don't forget the relationship between the sister city committee table and sister city table

    Quality Assurance.
  • Click here to see the example of what steps need to be completed to implement supertype and exclusive subtypes tables
  • The following tables should be empty after req 5.10 has been completed:
    • [req 5.10a] Committee table:
      • 6 columns; type field has no default, constrained to 'C' or 'S'
      • PK is "identity"
      • Alternate key (AK) defined
    • [req 5.10b] Sister City SubCommittee table:
      • 8 columns; type field default 'C', constrained to 'C'
      • PK is not "identity"
      • FK is established with the supertype table AK
    • [req 5.10c] Support SubCommittee table:
      • 4 columns; type field default 'S', constrained to 'S'
      • PK is not "identity"
      • FK is established with the supertype table AK
  • [req 5.15] Download SCACommittees.xlsx from Lab 5 Design page. Prepare the worksheet, then import the processed worksheet data to tblCommitteeIMPORT.
    Quality Assurance
    • tblCommitteeIMPORT table:
      • No PK should have been defined. Check the data types are correctly set
      • 13 records of data.
      • 12 columns imported with data or NULL. Verify the data:
        • 4 columns filled with data for every record
        • 6 columns filled with data ONLY for sister city committees
        • 2 columns filled with data ONLY for support committees
  • [req 5.15A-E] Migration: Study this Migration Map to populate the new production tables.
    Quality Assurance
    • [req 5.15A] tblCommittee: 13 records INSERTED.
    • [req 5.15B] tblCommitteeIMPORT: 13 records CommitteeID UPDATED
    • [req 5.15C] tblCommitteeSupport: 7 records INSERTED
    • [req 5.15D] tblCommitteeSisterCity: 6 records INSERTED
    • [req 5.15E] tblCommitteeSisterCity: 6 records SisterCityID UPDATED


Views. Use CREATE VIEW scripts to create the followin views. The CREATE VIEW scripts need to be written in Lab5_YOUR_TEAM_NAME.sql. The table and field names should conform to MIS 421 Naming Conventions. After the CREATE VIEW scripts are written in the file, don't forget to highlight and execute the scripts to create the views in your team database.
  • [req 5.20] A view (based on the production tables tblCommittee, tblCommitteeSisterCity and tblCommitteeSupport) that returns a list of each committee including all details. The view should contain all of the data in the original spreadsheet. Hint: Outer joins might be required.
    Quality Assurance. 13 rows. (Compare view result with the original spreadsheet data.)
  • [req 5.22] A view that returns a list of city committees, including information about the city with which they are associated (all fields from the committee table[s] and at least three fields from the sister city table).
    Quality Assurance. 6 rows. (Compare view result with the original spreadsheet data.)
  • [req 5.24] A view that returns the financial status of all committees, budgeted expenditures, expenditures to date, and calculates the amount available to spend. In each record returned, include the committee name, the budgeted expenditures, the expenditures to date, and a calculated field: Amount Available to Spend.
    Quality Assurance. 13 rows. (Compare view result with the original spreadsheet data.)
    • Tateyama: Budgeted Expenditures = $500.
    • Tateyama: Expenditures to Date = $25.
    • Tateyama: Amount Available to Spend = $475.

Submission:

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

End of Instructions