1. 2NF and 3NF
Welcome back to data normalization. We've seen how the first normal form ensures precise atomic data. Let's now explore the second and third normal forms.
2. Introduction to 2NF
The steps of the data normalization process are sequential. After the first normal form, we proceed to the second normal form, or 2NF, which confirms that all non-key attributes are entirely functionally dependent on the primary key.
The second normal form targets the elimination of partial dependencies.
3. Introduction to 2NF (1)
Functional dependency means that the key explicitly identifies an attribute.
For example, if you can always get the employee's name just by having their ID, the name depends on the ID.
4. Introduction to 2NF (2)
On the other hand, consider an ID number with two parts; team code and individual number. If finding a person's name requires only their number, not the team code, this shows a partial dependency. The name depends only on one part of the ID.
5. The second normal form
To confirm the second normal form, ensure every non-key attribute is fully functionally dependent on the primary key. In our table consolidating product data, we'll assess if all attributes are associated with product_id. Product_name 'Apple' is uniquely identified by product_id 9274, establishing its dependence on the primary key and the second normal form.
6. The second normal form
The detail_description of a product is not only linked to the product_id. An apple and a pineapple can be 'Non-GMO'. This suggests a breach of the second normal form.
7. The second normal form
In the 'allproducts' table, each product is made by a manufacturer, but the manufacturer's name and location are independent of the product. Changing the manufacturer's name or location doesn't alter the product itself. The product is linked to the manufacturer by ID, not by their name or location details.
8. Transitioning to 2NF
To reach the second normal form, we create separate entities for manufacturers and details. The new entities will help us separate the data so each attribute entirely depends on its primary key.
9. Transitioning to 2NF
We have to fill our new entities with data. We use INSERT INTO the entity and list of attributes. We are selecting the DISTINCT values that contain that combination of attributes extracted from the unnormalized entity.
10. Transitioning to 2NF
This segregation reduces redundancy, as a manufacturer is now only listed once per manufacturer ID and for detail per detail ID.
11. Introduction to 3NF
Our data is now compliant with the second normal form. Next is the third normal form, or 3NF, which ensures that an entity's attributes depend only on the primary key without transitive dependencies.
12. Introduction to 3NF
A transitive dependency occurs when an attribute relies on another attribute that is not a primary key. For example, a team determines the phone extension, not the employee ID. This indirect link, from phone extension to employee ID, is a transitive dependency.
13. The third normal form
In manufacturers, the location looks linked to the manufacturer ID but is independent, revealing a transitive dependency.
14. Transitioning to 3NF
To align with the third normal form, we extract location into its own locations entity. Now, each manufacturer is associated with a location without directly depending on it.
15. Transitioning to 3NF
We're moving the location attribute to a 'locations' entity with unique identifiers using ROW NUMBER. GROUP BY ensures each location is listed just once.
We must drop the columns to comply with the third normal form, creating unnormalized data shapes. Here, we remove location from 'manufacturers'.
16. Transitioning to 3NF
We've aligned our product entity with the third normal form. Next, we'll form a final product entity to include the remaining attributes from the unnormalized data.
17. Finalizing the model
We've applied the second and third normal form, eliminating redundancies and dependencies. Let's review the steps again.
18. Finalizing the model
For the first normal form, we broke down the category attribute from multiple values locked in one attribute into individual values inside the 'categories' entity.
19. Finalizing the model
With the second normal form, we ensured all attributes entirely depend on the primary key, creating separate 'manufacturers' and 'details' entities.
20. Finalizing the model
Finally, the third normal form eliminated transitive dependencies, separating 'manufacturers' locations into another entity and producing a clean and compliant 'allproducts' table.
21. Finalizing the model
And since this is a relational data model, we can visualize the final stage once data normalization is completed.
22. Terminology and functions overview
This slide summarizes the key concepts for a quick review during the exercises.
23. Functions overview
Take note of the implementations of the functions as well.
24. Let's practice!
Time to practice our knowledge!