Question

database design

you are tasked with creating a logical database design that will be used later to implement the physical database named State_Capitals. The first step of creating a logical database design is normalization.

States of the United States
Country	Region	States
United States	Midwest	Iowa (IA), Illinois (IL), Indiana (IN), Kansas (KS), Michigan (MI), Minnesota (MN), Missouri (MO), North Dakota (ND), Nebraska (NE), Ohio (OH), South Dakota (SD), Wisconsin (WI)
United States	Northeast	Connecticut (CT), Delaware (DE), Massachusetts (MA), Maryland (MD), Maine (ME), New Hampshire (NH), New Jersey (NJ), New York (NY), Pennsylvania (PA), Rhode Island (RI), Vermont (VT)
United States	South	Oklahoma (OK), Texas (TX)
United States	Southeast	Alabama (AL), Arkansas (AR), Florida (FL), Georgia (GA), Kentucky (KY), Louisiana (LA), Mississippi (MS), North Carolina (NC), South Carolina (SC), Tennessee (TN), Virginia (VA), West Virginia (WV)
United States	West	Alaska (AK), Arizona (AZ), California (CA), Colorado (CO), Hawaii (HI), Idaho (ID), Montana (MT), New Mexico (NM), Nevada (NV), Oregon (OR), Utah (UT), Washington (WA), Wyoming (WY)

Instructions for the project including the following.

• Introduction: Explain what normalization is, and the difference between third normal form (3NF) and Boyce-Codd normal form (BCNF).
• Body: Attached is an Excel spreadsheet that contains data of the 50 states: country, region, and states.
• Use the normalization technique in this unit to normalize the relation.
• Conclusion: Explain the resulting relations.

Submit your assignment as a word document

Answer 1

Normalization:

Normalization is a process of decomposing a relation in sub relations so that:

1. There is no insertion, update, delete anomaly in the table

2. It becomes easy to store the consistent data with reference between the tables

3. It becomes easy to retrieve the data from the tables.

4. There is minimal or no data redundancy (repetition)

There are various normal forms in the data base normalization process as: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF.

********************************************************

Difference between 3^rd Normal Form and BCNF:

3^rd normal form: It has the condition that a table should be in 2^nd normal form and there should be no transitive dependency between non key attributes such that a non key attribute C can be determined from another non key attribute B which further depends on key attribute A. The relation is shown as :

C -> B -> A; thus C -> A i.e. C transitively depends on A

BCNF: It is Boycee Codd Normal form which is an extension of 3^rd NF where a table should be in 3Nf aling with the condition that all non key attributes should fully depend on super key where super key is combination of primary key and non key attribute.

Consider the relation R (A,B,C,D) with a as primary key. BCNF holds:

(A, B)-> C, D i.e. C and D can be uniquely identified by composite of A,B

(A, C)-> C,B i.e. C and B can be uniquely identified by composite of A,C

(A, D)-> C,B i.e. C and B can be uniquely identified by composite of A,D

Difference between 3NF and BCNF:

3NF deals with transitive dependency where as BCNF also deals with super key dependency.

All relations in BCNF needs to be 3NF but all 3NF relations may or may not be in BCNF

********************************************************

Given data’s normal form:

The given data is in un-normalized form as:

The States column has multiple values for same record

********************************************************

Normalising given data: Below are the steps to normalize the given data

Normalizing to 1^st NF:

A data table is considered to be in 1^st NF if:

· It has a primary key

· All tuples hold single value.

But state column has multiple values in it, thus let’s break the relation as below:

CountryRegions(Country, Region)

RegionStates(Region, States)

If we further observe, state is a combination of state name and its code, thus, let’s break States column as below:

RegionStates(Region, StateCode, StateName)

The above relation will still suffer with data redundancy as same Region will repeat in many states, let’s break the table further to add a RegionCode as bellow:

CountryRegions(RegionCode, Region, Country):

RegionStates(StateCode, StateName, RegionCode)

The final 1^st NF tables are as below:

1. CountryRegions(RegionCode, Region, Country): RegionCode as primary key

2. RegionStates(StateCode, StateName, RegionCode) : RegionCode is reference key from CountryRegion

Normalizing to 2^nd NF:

A relation is considered to be in 2^nd NF if:

· It is in 1^st NF (already achieved in previous step)

· The non key attributes depends on key attributes.

Consider the Functional dependencies in 1NF tables:

CounrtyRegion: RegionCode -> Region, Country

RegionStates: StateCode -> StateName, RegionCode

As non key attributes has dependency on key attributes, thus it is already in 2NF

Normalising to 3^rd NF

A table is considered to be in 3^rd NF if:

· It is in 2NF (already achieved in previous step)

· There is no transitive dependency i.e. the non key attribute should not depend on another non key attribute.

The obtained tables has below transitive dependencies:

Region -> Country as Region can determin Country, it is a transitive dependency

State -> RegionCode as State can determine RegionCode, it is a transitive dependency.

Let’s break the relations as below to obtain the 3^rd NF:

*Bold underlined are primary keys, italic are foreign keys

Region(RegionCode, Region)

CountryRegions(Country, RegionCode)

State(StateCode, State)

RegionStates(RegionCode, StateCode)

The above 4 tables are in final normalization form.

********************************************************

Logic design for the normalized tables: