Picture of the author
Study Locus

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

Secure payment powered by PayPal
Type of Paper
Academic Level
Deadline (days)
Pages
0
0
Approximate Price $0 -0 words

Our writing services are affordable

High School
From$10page
College
From$13page
University
From$14page
We understand how long and what it takes to complete a research paper. Our expert team is always ready to help you, take notes,write, format, edit and do your citations just right on any topic of your reasearch.