Database Reference
In-Depth Information
3.3.1 Why Table Normalization
A normalization process is a process to eliminate anomalies that occur in a database operation. To
see what those anomalies are, let us consider the CLASS_REGISTRATON_INFO table shown
in Table 3.1.
In Table 3.1, information about students, faculty members, courses, and classes are stored in a
single table CLASS_REGISTRATON_INFO. his fact indicates that the primary key has to be
a combination of several columns. Indeed, StudentID alone cannot determine the column Course.
For example, the value 11 in the StudentID column cannot determine the values in the Course
column. he same 11 relates to two diferent values, Database and E-Commerce. By the same
argument, StudentID cannot determine ClassID. On the other hand, the column Course alone
cannot determine StudentID and some other columns. Some students may take the same course
more than once. In such a case, the values of ClassID cannot be determined by the combination
(StudentID, Course). herefore, the primary key is a combination of three columns (StudentID,
Course, ClassID) for the table CLASS_REGISTRATON_INFO.
he table displayed in Table 3.1 is in fact a relation. Each cell has only a single data value, the
values in each column have the same data type, and there are no duplicated rows. Unfortunately, a
table that satisies the requirements of a relation is far from ideal in a relational database. he table
CLASS_REGISTRATON_INFO has a data redundancy problem. For example, the name Smith
in the Faculty Name column is repeated three times. Data redundancy such as this will cost extra
labor for data entry and waste storage space on the hard drive.
In addition to the data redundancy problem, the table CLASS_REGISTRATON_INFO has
other more serious problems. he structure of the table is not good enough to avoid problems that
will occur during a data modiication process. When you modify the existing data in the table,
the current table structure may cause undesired results, which are called
modiication anomalies
.
here are three types of
modiication anomalies
.
Update anomalies
: Update anomalies occur during the process of updating the existing data in
a table. Suppose that you want to change the faculty for the student with ID 11 from Lee to Boyd.
Table 3.1
CLASS_REGISTRATON_INFO Table
Student
ID
Student
Name
Faculty
ID
Faculty
Name
Pre-
requisite
Class
ID
Grade
Course
10
Liz
1
Smith
A
VB
None
1000
11
Joe
2
Lee
C
Database
VB
1001
12
Linda
1
Smith
B
E-Commerce
Database
1002
13
Don
3
Fry
C
VB
None
1000
14
Jan
4
Garza
B
Database
VB
1001
10
Liz
1
Smith
A
Database
VB
1003
16
Bruce
3
Fry
B
Info-Systems
Database
1004
11
Joe
2
Lee
B
E-Commerce
Database
1006
13
Don
3
Fry
A
Database
VB
1005
