Answered: SQL database normalization - US states example
Introduction
Normalization is a data organization technique as applied in relational database management systems. Normalization plays two key roles. First, it helps minimize data redundancy from a relation or from a set of relations. Second, it helps eliminate insertion, update, and deletion anomalies. Normalized data is linked using relationships.
Normalization takes several normal forms with the most common forms being 1NF, 2NF, 3NF and BCNF. BCNF is an extension of 3NF. The following are the differences between 3NF and BCNF.
| 3NF | BCNF |
| The functional dependencies are already present in INF and 2NF | The functional dependencies are present in 1NF, 2NF and 3NF |
| It has high redundancy | It has low redundancy in comparison to 3NF |
| 3NF shouldn’t have any transitive dependency | For any relation A->B, ‘A’ should be a super key of that specific relation |
| It can be used to achieve lossless decomposition | It is difficult to achieve lossless decomposition |
US states data normalization
| Country ID | Country name |
| C1 | United States |
| Region ID | Country ID | Region Name |
| R1 | C1 | Midwest |
| R2 | C1 | Northeast |
| R3 | C1 | South |
| R4 | C1 | Southeast |
| R5 | C1 | West |
Conclusion
Normalization of US states data to 3NF yields three tables. That is, a country table, a regions’ table, and a table of states. The tables have Country ID, Region ID, and State ID as unique identifiers (primary keys) respectively. The Country ID is the foreign key in the region table and the Region ID is the foreign key in the states table.
Get unique answers on this homework or any other assignment. order here
Our writing services are affordable
From$10page
From$13page
From$14page