Need for Normalization

1
Need for Normalization
Figure 4.1
2
Figure 4.1 Observations

• PRO_NUM intended to be primary key
• Table entries invite data inconsistencies
• Table displays data anomalies
• Update
• Modifying JOB_CLASS
• Insertion
• New employee must be assigned project
• Deletion
• If employee deleted, other vital data lost

3
Dependency Diagram (1NF)
Figure 4.4
4
Data Organization 1NF(Flattening the table)
Primary Key is PROJ_NUM, EMP_NUM Figure 4.3
5
1NF Summarized

• All key attributes defined (non-null)
• No repeating groups in table
• All attributes dependent on primary key
• Consequently all rows unique
• All this is part of being relational

6
2NF Conversion Results
PROJECT (PROJ_NUM, PROJ_NAME) EMPLOYEE (EMP_NUM,
EMP_NAME, JOB_CLASS, CHG_HOUR) ASSIGN (PROJ_NUM,
EMP_NUM, HOURS)
Figure 4.5
7
2NF Summarized

• In 1NF
• Includes no partial dependencies
• No attribute dependent on a portion of primary
  key
• 1NF with singleton primary key always 2NF

8
Transitive Dependencies

• A ? C is a transitive dependency if there is a B
  such that A ? B and B ? C where A is not
  functionally dependent on B and A is not
  functionally dependent on C

9
3NF

• In 2NF
• Contains no transitive dependencies outside the
  primary key
• To convert to 3NF, project out the dependency

JOB (JOB_CLASS, CHG_HOUR) PROJECT (PROJ_NUM,
PROJ_NAME) ASSIGN (PROJ_NUM, EMP_NUM,
HOURS) EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS)
10
Boyce-Codd Normal Form (BCNF)

• Codd originally did not consider the case where
• 1. There are 2 or more candidate keys
• 2. The keys are composite
• 3. They overlap
• To be in BCNF, every determinant must be a
  candidate key
• Notes
• Determinant is attribute on the left of a FD
• 3NF table with only one candidate key is always
  in BCNF

11
Solutions for problems 1 3
12
3NF Table Not in BCNF
Figure 4.7
13
Decomposition of Table Structure to Meet BCNF
Figure 4.8
14
Decomposition into BCNF
Figure 4.9