Scribd
Upload
314 views

Unit-3 Part 1 Normalization

The document discusses database normalization and issues with bad database design such as redundancy, anomalies, and memory wastage. It covers various normal forms including 1NF, 2NF, 3NF, BCNF, 4NF and 5NF. The goal of normalization is to organize data in tables to minimize duplication and dependency issues by splitting tables into smaller, more manageable pieces. Normalization helps ensure data consistency and integrity when tables are updated or new data is added.

Uploaded by

219X1A3359 SINGASANI JAYA PRATHAP REDDY
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
314 views

Unit-3 Part 1 Normalization

The document discusses database normalization and issues with bad database design such as redundancy, anomalies, and memory wastage. It covers various normal forms including 1NF, 2NF, 3NF, BCNF, 4NF and 5NF. The goal of normalization is to organize data in tables to minimize duplication and dependency issues by splitting tables into smaller, more manageable pieces. Normalization helps ensure data consistency and integrity when tables are updated or new data is added.

Uploaded by

219X1A3359 SINGASANI JAYA PRATHAP REDDY
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 31

Problems with a Bad DB Design

● Redundancy : Duplication of Data

● Insert Anomalies: It is not possible to insert some data with out


inserting some other data.

● Delete Anomalies: It is not possible to delete some data with out


loosing some other data.

● Update Anomalies: If we want to modify some data, we have to


modify all the copies of it.

● Memory Wastage: Redundant storage of data always leads to


memory wastage.
Normalization
● Normalization is the process of schema refinement to
remove redundant storage of data in the database.

● We normalize the database through decomposition.


● Decomposition is the process of dividing a bigger table into
smaller multiple tables.

● To ensure the consistency of database the decomposition must


be loss-less decomposition.
● Lossy decomposition leads to inconsistency of database.
Normalization
● Based on to which extent we want to normalize the database,
we have different normal forms:
➢First Normal Form (1NF)
➢Second Normal Form (2NF)
➢Third Normal Form (3NF)
➢Boyce Codd Normal Form (BCNF)
➢Fourth Normal Form (4NF)
➢Fifth Normal Form (5NF)
Functional Dependency
● In a Given Relation R, X and Y are attributes.
Attribute Y is functionally dependent on
attribute X, if each value of X determines
exactly one value in Y.
● This is Represented as X -> Y
● X Determines the value of Y or
● Y is Functionally Dependent on X
● Ex: Marks -> Grade
● Studentid -> StudentName
Determinant:
● Attribute X can be defined as determinant if it
uniquely defines the attribute value Y in a given
relation R.
● It is Represented as X -> Y, which means
● Attribute X decides attribute Y.
● X is called Determinant.
Functional Dependency types:
1)Fully Functional Dependency.
2)Partial Functional Dependency.
3)Transitive Functional Dependency.
Let us consider the Student_Cource_Result Table.
Sid and Cid both together is called the composite Key
1) Fully Functional Dependency
Ex: Sid + Cid -> Marks
Both studentid and courceid determines Marks.

2) Partial Functional dependency


Ex: sid -> sname ( Sid is alone enough to determine the
Sname, Cid is not Required)
cid -> cname ( Cid is alone enough to determine the
Cname, Sid is not Required)
3) Transitive Functional Dependency
Consider:
SidCid->Marks-> Grade.
● In the above representation Sid and Cid Determines
Marks, and Marks Determines Grade.
● That is Sid Cid determines Grade.

Note: Both Partial and Transitive Dependencies must be


eliminated to make the Database design Good.
First Normal Form (1NF)
● A Relation ‘R’ is said to be in first normal form if and only
if all the attributes of ‘R’ are atomic in nature.

● Composite and Multi valued attributes should be avoided.


● Atomic: The Smallest levels to which data may be broken
down and remains meaningful.
Relation which is not in 1NF
Relation in 1NF
Second Normal Form (2NF)
● A Relation ‘R’ is said to be in second normal form if and
only if: (i) it is in first normal form.
(ii) no partial dependency exists between
non key attributes and key attributes.
Relations in 2NF
Relations in 2NF
Third Normal Form (3NF)
● A Relation ‘R’ is said to be in third normal form if and
only if: (i) it is in second normal form.
(ii) no transitive dependency exists between
non key attributes and key attributes.
Boyce Codd Normal Form (BCNF)
● A Relation ‘R’ is said to be in BCNF if and only
(i) it is in 3NF
(ii) all the determinants are key Attributes.

● BCNF is a strong 3NF. But not every 3NF relation is


in BCNF

● If a table has only one non-composite candidate key


and if it is in 3NF, then the table will also be in BCNF.
Normalization Merits and Demerits
Merits:
● Removes the redundancy to the greater extent.
● Removes insert, delete and update anomalies.
Demerits:
● Performance of Data retrieval (select) operations will be
affected.
● Sometimes full normalization may not be desirable.
Fourth Normal Form (4NF)
● A Relation ‘R’ is said to be in fourth normal form if and only
if: (i) it is in BCNF.
(ii) it does not contain more than one multi
valued dependency.

● Multi valued dependency is the special case of Functional


dependency

X->Y (Every X value determines exactly one Y value)


X->>Y (Every X value may determine more than one Y
value)
EmpName ProjectName AssistName
Smith X John
smith X Anna
smith Y John
smith Y Anna
Course Teacher Book
DBMS YRM Korth
DBMS YRM Raghuram
Krishnan
DBMS KSH Korth
DBMS KSH Raghuram
Krishnan
DBMS CSR Korth
DBMS CSR Raghuram
Krishnan
Course Teacher Course Book

DBMS YRM DBMS Korth


DBMS KSH
DBMS Raghuram
DBMS CSR Krishnan
Fifth Normal Form (5NF)
● A Relation ‘R’ is said to be in fifth normal form if and
only if: (i) it is in 4NF.
(ii) it does not contain join dependency.
● A Relation that has a join dependency can not be
divided into two (or more) relations such that the
resulting relations can be combined to form the
original relation.
● 5NF is also called as PJNF (Project Join Normal
Form).
Department Course Student
Department Course Course Student
CSE CP ED Ravi
ME ED BEE Ramesh
ECE BEE CP Rajesh

Department Student
CSE Ravi
ME Ramesh
ECE Rajesh
Proj Project Emp Emp Category Hourly
id name id name wages
E501 Ravi A 600
P101 Banking E502 Ramesh B 500
E503 Rajesh C 400
P102 Shopping E501 Ravi A 600
E507 Siva B 500

You might also like