The Assignment
Create new tables based on the database design agreed upon
by the class. Import the data from flat files (Excel or .txt files)
and migrate that data to the new final tables.
Database Maintenance and Lab Skills
Database Maintenance
- Team work log: download your team work record file from assignment L4d-LM&PM and record the team activities
- Before continuing to build your team's database,
first eliminate any prior issues:
- [req 4:00] Correct all errors (or other requested changes)
identified by your instructors in the previous Lab cycle.
Lab Skills
- How to make a column to contain only unique values (see the instruction in Create Other Features from the left navigation menu)
- Importing data from spreadsheets and other flat files.
- Converting "repeated attribute" (e.g. multiple
phone number columns) into a single attribute with
multiple rows (instances).
- 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 Lab4_YOUR_TEAM_NAME.sql. Record in the file all the 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 4.10]
Download the Lab 4 final physical data model in Assignment L4i-DB from Canvas.
Refer to (1) the class-approved physical data design
and (2) this link:
Lab 4 Implementation MAP
to create and populate the new tables.
-
Quality Assurance. Use the following information to test the objects created. These checks are minimal. Further testing may be appropriate.
- [req 4.10a] Fluency table: 6 records after importation and migration;
- [req 4.10b] Language table: 15 records after importation and migration;
- [req 4.10c] Person-Language table: 99 records after importation and migration;
Views. Create the following view. The view and
field names should conform to MIS 421 naming conventions.
- [req 4.20] Create a new view. Draw the data from the new
production tables for Person, Language, Fluency and
PersonLanguageFluency. (Do not base the view on any
BACKUP, IMPORT, or STAGING tables).
This view lists people and the languages
they can speak. (The corporations will, naturally,
be left out of the list.) This view returns one record
for each language a person speaks. In each record display
the first, middle and last names
of the people, as well as the language
and that person's fluency in that language. (For
"fluency", show fluency description,
not the fluency code.)
Quality Assurance:
Marco Polo speaks Italian as a native speaker.
Sacagawea speaks 4 languages.
Submission:
- [req 4:40] Submit to Canvas (L4i-DB) your most recent
Team Work Record and the SQL file: Lab4_YOUR_TEAM_NAME.sql.
End of Instructions
|