CS377: Database Design - Data Modeling and the Join

Activity Goals

The goals of this activity are:
  1. To model table joins
  2. To differentiate between the different type of joins
  3. To identify potential anomalies in data schemas
  4. To take steps to normalize data schemas to avoid anomalies

Supplemental Reading

Feel free to visit these resources for supplemental background reading material.

The Activity

Directions

Consider the activity models and answer the questions provided. First reflect on these questions on your own briefly, before discussing and comparing your thoughts with your group. Appoint one member of your group to discuss your findings with the class, and the rest of the group should help that member prepare their response. Answer each question individually from the activity on the Class Activity Questions discussion board. After class, think about the questions in the reflective prompt and respond to those individually in your notebook. Report out on areas of disagreement or items for which you and your group identified alternative approaches. Write down and report out questions you encountered along the way for group discussion.

Model 1: Relational Design

Bank Accounts Table from Database Design 2nd Ed by Watt and Eng

Questions

  1. What data redundancies do you see in this table, and how can you fix each?
  2. How would you change the address of a bank? Remove a bank? Insert a new account an an existing back, but with an updated address? These are update anomalies, deletion anomalies, and insertion anomalies.
  3. Design a schema that eliminates data redundancy in this table.

Model 2: Case Study

Flawed Projects Table from Database Design 2nd Ed by Watt and Eng
An Improved Projects Schema from Database Design 2nd Ed by Watt and Eng
The Improved Projects Table from Database Design 2nd Ed by Watt and Eng

Questions

  1. Induce as many anomalies as you can in the flawed table above!
  2. How does the improved (normalized) schema help prevent the anomalies you were able to identify?

Model 3: The SQL Join

Inner Join

SQL Join - 07 A Inner Join B

Left Outer Join

SQL Join - 01 A Left Join B

Questions

  1. Joins are classified by which records are included if a corresponding match is not found in one table. An inner join includes only records that match across both tables. A left outer join includes all rows from the first table and their corresponding match from the second table (or NULL if no match exists from the second table. What do you think a right outer join is, and a full outer join?
  2. What records are included in an inner join, a left outer join, a right outer join, and a full outer join, for this data table? Write the SQL JOIN statements required to implement each join by linking the DepartmentID column.

Submission

Submit your answers to the questions using the Class Activity Questions discussion board. You may also respond to questions or comments made by others, or ask follow-up questions there. Answer any reflective prompt questions in the Reflective Journal section of your OneNote Classroom personal section.