Lab 4 Design Phase:
Languages Spoken by People in the SCA Database

The Assignment

Your SCA client occasionally needs volunteer interpreters and translators for events or correspondence. For this reason, the SCA database should keep track of which people in the database speak which languages, and at what level of fluency. Your task is to analyze new data from your SCA client and to modify the SCA data model and physical database design accordingly.

Design Deliverables Summary

Notes:
  • During Design Phase no changes are allowed to the team database.
  • Download your team work record log file from assignment (L3Bi-DB), record the activities
 
  • watch from Canvas the Lab 4 design video that discusses the conceptual data model for normalizing the SCALanguage table:
    The conceptual data model are based on the following data:
    • The SCALanguage Excel file
Before the next database class: work on the Lab 4 LM/PM:
  • In your latest team work record log file (downloaded from previoius assignment on Canvas), record the activities
  • Download the "final" data models from Lab 3 on Canvas: L3Ai-DB
  • [req 4:00] Based on the new information for Lab 4, expand the database logical and physical models from Lab 3 to include information about who speaks which language(s), and at what fluency level.
    • Review the Lab 1 notes on languages.
    • Review the lecture slides and notes in Design Challenges section below on repeated columns/attributes.
  • Place the LM, PM, and team work record log files in a zipped folder and upload the folder to the assignment L4d-LM&PM on Canvas.
    Only one submission from one team member is required.

Analyst Notes & Deliverables Details


Lab 4 new information: In this lab, we want to track who speaks which language(s), and at what fluency level. People and their language fluencies are presented in this Excel file: SCALanguages.xls.
Additional Notes:
  • The native language of all persons should be included, as well as any other languages in which the person has some fluency.
  • Any language, such as English, may be spoken by many people, and a person may speak multiple languages.
  • For each language a person speaks, the database must record only one fluency level.

Design Challenges

Repeated Attribute. Notice that there are 4 "language" columns in the original spreadsheet.
  • If there is one column (attribute) for each language a person speaks, then 'Language' is a "repeated attribute". Is it possible someone could speak 5, 6, or more languages? Yes! Kraig once took a linguistics course from Max Mangold, who reputedly spoke "nearly 40 languages". Do we want to design our table with 40 language fields? That would be a nightmare.
  • Your database design must allow for recording any number of languages for an individual ... without needing to modify the table design by adding new columns. Instead, each language spoken by a person should be recorded in a Person-Language table that matches each person and each language spoken spoken by that person in a different row (record).

End of Instructions