Lecture 5 Examples
Lecture 5 Examples
Lecture 5 Examples
Database
Lecture 5 Examples
Topics: Normalization
Transitive Dependency
Partial Dependency
invoice_number
invoice_line
Class discussion
invoice_number invoice_line
invoice_line
This step eliminates
partial dependency
invoice_number invoice_line
Partial dependency
invoice_number invoice_line product_code product_name invoice_total (customer_number)
removed
Transitive Dependency
Example 1: Normalization – Step 3
Transitive Dependency
Step 3: Break all transitive dependencies to become new tables and leave a copy of determinant
in original table:
Table is 3NF,
A B C D E F G
not BCNF
Non-key Dependency
1. Switch attributes
A E C D B F G
Partial Dependency
2. Remove partial dependency No dependency,
THINK: When may a
Final
A DROP IT! Design 3NF table not be a
BCNF?
E B
A E C D F G
Example 3:Additional 3NF Normalization
Example
Consider a table in the following format:
Note: the naming convention for example 3 is different from the one that has been discussed
and adopted in this course
Example 3: Step 1 Conversion to 1NF
Identify the Primary Key: PK must uniquely identify any attribute value
1NF
Transitive
Partial
dependency
dependency
Partial
dependencies
Example 3: Step 2 Conversion to 2NF
Dependency Diagram
Eliminate partial dependency
2NF
Example 3: Step 2 Conversion to 2NF
Dependency Diagram
3NF
Example 4: Additional BCNF
Normalization Example
BCNF
Note: the naming convention for example 4 is different from the one that has been discussed
and adopted in this course