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.