Database Systems: Design, Implementation and Management 13th Edition solution manual

 
9.    Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure P1.9. (The file is meant to be used as a teacher class assignment schedule. One of the many problems with data redundancy is the likely occurrence of data inconsistencies – that two different initials have been entered for the teacher named Maria Cordoza.)
 
FIGURE P1.9 The File Structure for Problems 9-10

 
Note that the teacher characteristics occur multiple times in this file. For example, the teacher named Maria Cordoza’s first name, last name, and initial occur three times. If changes must be made for any given teacher, those changes must be made multiple times. All it takes is one incorrect entry or one forgotten change to create data inconsistencies. Redundant data are not a luxury you can afford in a data environment.
 

10. Given the file structure shown in Figure P1.9, what problem(s) might you encounter if building KOM were deleted?

 
You would lose all the time assignment data about teachers Williston, Cordoza, and Hawkins, as well as the KOM rooms 204E, 123, and 34. Here is yet another good reason for keeping data about specific entities in their own tables! This kind of an anomaly is known as a deletion anomaly.
 

11. Using your school’s student information system, print your class schedule. The schedule probably would contain the student identification number, student name, class code, class name, class credit hours, class instructor name, the class meeting days and times, and the class room number.  Use Figure P1.11 as a template to complete the following actions.

 

1. Create a spreadsheet using the template shown in Figure P1.11 and enter your current class schedule.
2. Enter the class schedule of two of your classmates into the same spreadsheet.
3. Discuss the redundancies and anomalies caused by this design.

 
This could be a good “mini-group” problem – groups of 3 students max. Ask them to create their individual class schedules in separate spreadsheets and then, a single spreadsheet containing all their class schedules.
 
This exercise should incentivate “group discussion” and discover data anomalies and brain storm better wayd to store the class schedule data.
 
Students are likely to use MS Excel or Google Sheets to create a simple tabular spreasheet containing the data outlined in Figure P1.11. The rows of the spreadsheet(s) will represent each one of the classes they are taking.
 
Students are likely to identify the redundancies around the class information since all three schedules (the student’s own schedule plus the schedules of the two classmates) will have at least the database class in common.  This easily leads to discussions of separating the data into at least two tables in a database.  However, that still leaves the redundancies of redundant student data with each class that they are taking.  Astute students might realize that this is analogous to the Employee Skill Certifications shown in Figures 1.4 and Figure 1.5, such that a table for student data, a table for class data, and a table to relate the students and classes is appropriate.