Normalization

Purpose of Normalization

 

n  Minimize redundancy in data

n  Remove insert, delete and update anomaly (irregularity) during db activities.

n  Reduce the need to recognize the data when it is modified or enhanced.

n  Because of duplicate data elimination, we will be able to reduce the overall size of the db.

Normal Forms

n  The different stages of normalization is called as normal forms. They are,

n        * 1NF

n        * 2NF

n        * 3NF

n        * BCNF

 

 

First Normal Form(1NF)

n  A relation schema R is in 1NF if

* all the attributes of the relation R are atomic in nature.

 

E.G…                                                            DEPT

normalization

Suppose we extend it by including DLOCATIONS attribute as shown above. We assume that each dept may have a no. of Locations.

This is not 1NF bcoz DLOCATIONS is not an atomic attribute.

 

Dept:

 

DNAME DNO DHEAD DLOCATIONS
Research 3 John (Mianus,Rye,Stratford)
Administrator 2 prince Mianus
Headquarter 1 Peter Rye

 

n  There are 2 main techniques to achieve 1NF,

  1. Remove the attribute DLOCATIONS and place it in separate relation DEPT_LOCATIONS along with a primary key DNO. The primary key of the original DEPT  is the combination {DNO,DLOCATIONS}

DEPT-LOCATIONS

 

DNO  DLOCATIONS  
 
1

2

3

3

3

Rye

Mianus

Rye

Mianus

stratford

 

 

 

 

 

 

n  2. Expand the key so that there will be a separate tuple in the orginal DEPT relation for each location of a DEPT as shown below,

 

DNAME DNO DHEAD DLOCATIONS
Research

Research

Research

Administration

HQ

3

3

3

2

1

John

John

John

Princy

Peter

Mianus

Rye

Statford

Mianus

Rye

 

 

Second Normal Form(2NF)

n  A relation R is in 2NF if and only if,

  • It is in the 1NF and
  • No partial dependency exists between non-key attributes and key attributes.
  •      The test for 2NF involves testing for functional dependencies whose left hand side attributes are part of primary key. If the primary key contains a single attribute, the test need not be applied at all.
    •  A relation schema R is in 2NF if every non-prime attribute A in R is fully functionally dependent on the primary key of R.
  • E.G.. Consider the EMP_PROJ relation,  it is in 1NF but not in 2NF,

The non-prime attribute ENAME violates 2NF because of FD2, as do the non-prime attribute PNAME and PLOCATION because of FD2 and FD3 make ENAME, PNAME and PLOCATION partially dependent on the primary key {SSN,PNO}, thus violating 2NF test.

 

EMP-PROJ                                                   EMP-PROJ Relation in 1NF

2nd NF

n  The Functional dependencies FD1,FD2 and FD3 leads to the decomposition of EMP_PROJ into the 3 relation schemas EP1,EP2 and EP3, each of which is in 2NF.

 

EP1

SSN PNO HOURS

FD1

 

EP2

SSN ENAME

FD2

 

 

EP3

PNO PNAME PLOCATION

FD3

 

 

Third Normal Form (3NF)

n  A relation R is said to be in the 3NF if and only if

*   It is in 2 NF and

*   No transitive dependency exists between non-key attributes and key attributes.

E.G… Consider the relation schema EMP_DEPT

 

EMP_DEPT Relation Schema in 2NF

ENAME SSN BDATE ADDRESS DNO DNAME DNGRSSN

The dependency SSN —à  DNGRSSN is transitive through DNO in EMP-DEPT, because both the dependencies SSN  –à DNO and DNO  -àDNGRSSN hold and DNO a key itself nor a subset of the key of EMP-DEPT is neither.

A relation schema R is n 3NF, if it satisfies 2NF and no non-prime attribute of R is transitively dependent on the primary key.

 

n  We can normalize schemas ED1 and ED2,

n  3NF relation schemas ED1 and ED2

 

ED1

ENAME SSN  BDATE ADDRESS DNO

 

 

ED2

DNO DNAME DNGRSSN

 

Here ED1 and ED2 represet independent entity facts about employees and departments.

 

 

 

 

 

Registration


A password will be e-mailed to you.

Feedback Form

Name (required)

Email (required)

Feedback