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.

• SQL Joins

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, and compare with your group to prepare for our whole-class discussion. 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

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

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

Left Outer Join

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.

---

Facilitating Integrity with Modeling and the SQL Join

In the field of database management, ensuring data integrity is of utmost importance. Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. One of the ways to facilitate data integrity is through the use of modeling and the SQL Join operation.

SQL Joins

A SQL Join is a powerful operation that combines rows from two or more tables based on a related column between them. It allows data from multiple tables to be retrieved and combined in a meaningful way. The result of a join is a new table, also known as a result set, that combines rows from the joined tables based on a specified condition. The condition is typically the equality between the values in the related columns.

The Wikipedia article on SQL Joins provides a comprehensive introduction to the topic. It explains various types of joins, including inner join, left join, right join, and outer join, along with their semantics and practical examples. This article serves as an excellent starting point for understanding SQL joins and their applications.

Submission

I encourage you to submit your answers to the questions (and ask your own 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. You can find the link to the class notebook on the syllabus.