What is Normalization in the database


Tuesday, March 24, 2020

ADVERTISEMENT

What is Normalization in the database


What is Normalization in the database


Normalization :

1). Normalization is the process of organizing the tables to minimize data redundancy
2)Redundancy  is storing the same data item in more than one place.
Database normalization is a design technique) by which an existing database is
modified to minimize redundancy and dependency of data.
4) Edgar F. Codd introduced the concept of normalization in 1970.Objectives  of normalization:
Edgar F. Codd stated the objectives of normalization are as follows.
To free the collection of relations from undesirable insertion, update and deletion

dependencies.

2) To reduce the need for restructuring the collection of relations as new types of data
are introduced, and thus increase the life span of application programs.
To make the relations model more informative to users.
4 To make the collection of relations neutral to the query statistics.
is normalization? Explain 1 NF with suitable example _____

Normalization .

1) Normalization is the process of organizing the tables to minimize data redundancy.
2) Database normalization is a design technique, by which an existing database is
modified to minimize redundancy and dependency of data.
3) Edgar F Codd introduced the concept of normalization in 1970.
4) There are normal forms for relations as follows.

4) There are normal forms for relations as follows.
a. INF — First Normal Form
b. 2NF — Second Normal Form
c. 3NF — Third Normal Form
d. BCNF — Boyce-Codd Normal Form
e. 4NF — Fourth Normal Form
f. 5NF — FifTh Normal Form etc.
5) Initially, E.F.Codd proposed three normal forms
INF, 2NF and 3NF. Subsequently, BCNF was
introduced jointly by R.Boyee and E.F.Codd later.
INF — First Norma Forms:

INF— First Normal Forms

I) A relation is said to be in 1NF if the value in the domain of each attribute contains
atomic (simple and un-divisible) value and each row provides a unique combination
of values.
2) 1NF is an essential property of a relation in a relational database.
3) The possibility of NULL value is allowed. NULL value represents unknown or
inapplicable value. 1f the person doesn ‘t have contact number, the contact field will
contain NULL value.
4) Consider the following table in Unnormalized Form (UNF)

6) hi the above table each row contains atomic value.
7) Using the 1-NF, data redundancy increases, as there will be many columns will
same data in multiple rows but each row as a whole will be unique.
8) For example: there is redundant information such as student_Id and student Name.
stored in the table

2-NF —Second Normal Form:

1) The primary key is usually a single column, but sometimes more than one column
can be combined to create a single primary key. This is called determinant set.
2) When every attribute is functionally dependent entirely and solely on the primaray
key, a relation/table is said to be in second normal form.
In other words, a table is in 2NF, if there is no partial dependency.
4) For a relation to be in 2NF, every non-key column (called dependent or descriptionh_
must be functionally dependent on every primary-key column in the relation. It is
called Full Functional Dependency.


TNF — Third Normal Form:

I) The table is considered in 3NF, when a table Is in 2-NF and there is no transitive
dependency, that is no non-primary key columns is functionally dependent on other
non-primary key column. Consider the following table.
3) To convert table of 2NF to 3NF,
a. Create new table to hold all fields that belong together.
b. Think about which fields belong together and create new tables to hold them.
c. Identify the primary key for each table and, if necessary, add foreign key.
Primary  Key

BCNF: Boyce-Codd Normal Form:

I) It is higher version of the 3NF, sometimes considered 3.5-NF.
2) Initially, E.F.Codd proposed three normal forms 1NF, 2NF and 3NF. Subsequently,
BCNF was introduced jointly by R.Boyce and E.F.Codd later.
3) The 3NF table, which does not have multiple overlapping candidate keys is said to
be in BCNF.
4) The relation is in BCNF, if and only if, every determinant is a candidate key.
5) A relation schema R is considered to be in BCNF if, for every one of its
dependencies X->Y, one of the following condition is true.
a. X-> Y ¡s a trivial functional dependency (ie. Y is a sub set of X)
b. X is a super key for schema R.
6) In other words, 3NF and all tables in the database should have only one primary
key. 


Reactions