Database Normalization: 1NF, 2NF, 3NF

• An unnormalized data is raw data in its original state
• Unnormalized data might contain redundant data, multivalued data, and/or other data anomalies
• Data anomaly is an undesirable consequence of a data modification
• Normalization assigns attributes to entities
• Normalization reduces or eliminates data redundancies

Objectives of Normalization

• Each table/relation represents a single subject
• Each row/column intersection contains only one value and not a group of values
• No data item is unnecessarily stored in more than one table
• All nonprime attributes (not part of a key) in a table are dependent on the primary key
• Each table has no insertion, update, or deletion anomalies
• Each rule in database normalization is a normal form

Common Normal Forms

• First Normal Form (1NF) has no repeating groups, and the primary key is identified
• Second Normal Form (2NF) is 1NF, and all columns depend on the table's primary key
• Third Normal Form (3NF) is 2NF, and there are no non-key attributes determined by another non-key attribute
• Boyce-Codd Normal Form (BCNF) is every determinant is a candidate key (special case of 3NF)
• Fourth Normal Form (4NF) is 3NF and no independent multivalued dependencies
• While other normalization levels exist, 3NF is considered the highest level necessary for most applications

First Normal Form Example

• ADV_NAME: A. Santos, SUBJ_CODE: MAT1, SUBJECT: Math, STU_NUM: 018, STU_NAME: John L.
• ADV_NAME: A. Santos, SUBJ_CODE: MAT1, SUBJECT: Math, STU_NUM: 021, STU_NAME: Mae R.
• ADV_NAME: A. Santos, SUBJ_CODE: MAT1, SUBJECT: Math, STU_NUM: 026, STU_NAME: Mark V.
• ADV_NAME: C. Reyes, SUBJ_CODE: CHEM, SUBJECT: Chemistry, STU_NUM: 020, STU_NAME: Zac S.
• ADV_NAME: C. Reyes, SUBJ_CODE: CHEM, SUBJECT: Chemistry, STU_NUM: 019, STU_NAME: Ed P.

Second Normal Form Examples

• Table 1: Advisers includes ADV_ID and ADV_NAME; ADV_ID 0001 corresponds to ADV_NAME A. Santos and ADV_ID 0002 corresponds to ADV_NAME C. Reyes
• Table 2: Subjects includes SUBJ_CODE and SUBJECT; SUBJ_CODE MAT1 corresponds to SUBJECT Math, and SUBJ_CODE CHEM corresponds to SUBJECT Chemistry
• Table 3: Students includes STU_NUM and STU_NAME; STU_NUM 018 corresponds to STU_NAME John L., STU_NUM 021 corresponds to STU_NAME Mae R., STU_NUM 026 corresponds to STU_NAME Mark V., STU_NUM 020 corresponds to STU_NAME Zac S., and STU_NUM 019 corresponds to STU_NAME Ed P.

Third Normal Form Example

• It includes all three tables from 2NF, listed above in the "Second Normal Form Examples" section.
• Table 4: StudentAdvisers includes ADV_ID, SUBJ_CODE, and STU_NUM
• ADV_ID: 0001, SUBJ_CODE: MAT1, STU_NUM: 018
• ADV_ID: 0001, SUBJ_CODE: MAT1, STU_NUM: 021
• ADV_ID: 0001, SUBJ_CODE: MAT1, STU_NUM: 026
• ADV_ID: 0002, SUBJ_CODE: CHEM, STU_NUM: 020
• ADV_ID: 0002, SUBJ_CODE: CHEM, STU_NUM: 019

Unnormalized Table

• This has ADVISOR, SUBJECT, and STUDENTS columns
• Santos A. advises on Math, and has John L., Mae R., and Mark V. as students
• Reyes C. advises on Chemistry, and has Zac S. and Ed P. as students