Computer Forurm
Chapter 5
Normalization of Database Tables
Discussion Focus
Why are some table structures considered to be bad and others good and how do you recognize the difference between good and bad structures?
From an information management point of view, possibly the most vexing and destructive problems are created through uncontrolled data redundancies. Such redundancies produce update and delete anomalies that create data integrity problems. The loss of data integrity can destroy the usefulness of the data within the database. (If necessary, review Chapter 1, Section 1.4.4, “Data Redundancy”, to make sure that your students understand the terminology and that they appreciate the dangers of data redundancy.)
Table structures are poor whenever they promote uncontrolled data redundancy. For example, the table structure shown in Figure IM5.1 is poor because it stores redundant data. In this example, the AC_MODEL, AC_RENT_CHG, and AC_SEATS attributes are redundant. (For example, note that the hourly rental charge of $58.50 is stored four times, once for each of the four Cessna C-172 Skyhawk aircraft – check records 1, 2, 4, and 9.)
Figure IM5.1 A Poor Table Structure
[pic]
|[pic] The figures shown in this discussion show the contents of the IM_Discussion database. This database is located on the teacher’s CD. |
If you use the AIRCRAFT_1 table as shown in Figure IM5.1, a change in hourly rental rates for the Cessna 172 Skyhawk must be made four times; if you forget to change just one of those rates, you have a data integrity problem. How much better it would be to have critical data in only one place! Then, if a change must be made, it need be made only once.
In contrast to the poor AIRCRAFT_1 table structure shown in Figure IM5.1, table structures are good when they preclude the possibility of producing uncontrolled data redundancies. You can produce such a happy circumstance by splitting the AIRCRAFT_1 table shown in Figure IM5.1 into
Normalization of Database Tables
Discussion Focus
Why are some table structures considered to be bad and others good and how do you recognize the difference between good and bad structures?
From an information management point of view, possibly the most vexing and destructive problems are created through uncontrolled data redundancies. Such redundancies produce update and delete anomalies that create data integrity problems. The loss of data integrity can destroy the usefulness of the data within the database. (If necessary, review Chapter 1, Section 1.4.4, “Data Redundancy”, to make sure that your students understand the terminology and that they appreciate the dangers of data redundancy.)
Table structures are poor whenever they promote uncontrolled data redundancy. For example, the table structure shown in Figure IM5.1 is poor because it stores redundant data. In this example, the AC_MODEL, AC_RENT_CHG, and AC_SEATS attributes are redundant. (For example, note that the hourly rental charge of $58.50 is stored four times, once for each of the four Cessna C-172 Skyhawk aircraft – check records 1, 2, 4, and 9.)
Figure IM5.1 A Poor Table Structure
[pic]
|[pic] The figures shown in this discussion show the contents of the IM_Discussion database. This database is located on the teacher’s CD. |
If you use the AIRCRAFT_1 table as shown in Figure IM5.1, a change in hourly rental rates for the Cessna 172 Skyhawk must be made four times; if you forget to change just one of those rates, you have a data integrity problem. How much better it would be to have critical data in only one place! Then, if a change must be made, it need be made only once.
In contrast to the poor AIRCRAFT_1 table structure shown in Figure IM5.1, table structures are good when they preclude the possibility of producing uncontrolled data redundancies. You can produce such a happy circumstance by splitting the AIRCRAFT_1 table shown in Figure IM5.1 into