Answer / firoz basha

Normalization is a series of steps followed to obtain a
database design that allows for consistent storage and
efficient acess of data in a relational database.
These steps reduce data redundancy and risk of data
becoming inconsistent.
there are 5 types of normal forms(NF) :

1 NF , 2NF, 3NF, 4NF, 5NF

1NF:- A relation is said to be in first normal form , if it
dows not contain any repeating groups or elements (or) if
all the values are atomic

2 NF:- A relation is said to be in Second Normal form, if
and only if it is in 1 NF and all the non-key attributes
are fully functionally dependent on the primary key.

3 NF:- A relation is said to be in 3rd normal form, if and
only if it is in 2NF and every non-key attribute is
non-transitively dependent on the primary key.
(or) every attribute is independent of all the other non-
key attributes.

BCNF:- A relation is said to be in BCNF, if every attribute
on which some other attribute is also a candidate for
primary key of a relation.

4 NF:- A relation is said to be in 4th normal form, if and
only if it is in 3NF and it should not contain any
multivalued dependencies.

5 NF :-A relation is said to be in 5 NF , if and only it it
is in 4 NF and every join dependency in R is implied by the
candidate key.

Any clarifications and for examples,
mail me : firozbashashaik@yahoo.com

Answer / shivanibehere

Normalization is a design technique that is widely used as
a guide in designing relational databases. Normalization is
essentially a two step process that puts data into tabular
form by removing repeating groups and then removes
duplicated data from the relational tables.

Normalization theory is based on the concepts of normal
forms. A relational table is said to be a particular normal
form if it satisfied a certain set of constraints. There
are currently five normal forms that have been defined. In
this section, we will cover the first three normal forms
that were defined by E. F. Codd.

Basic Concepts
The goal of normalization is to create a set of relational
tables that are free of redundant data and that can be
consistently and correctly modified. This means that all
tables in a relational database should be in the third
normal form (3NF). A relational table is in 3NF if and only
if all non-key columns are (a) mutually independent and (b)
fully dependent upon the primary key. Mutual independence
means that no non-key column is dependent upon any
combination of the other columns. The first two normal
forms are intermediate steps to achieve the goal of having
all tables in 3NF. In order to better understand the 2NF
and higher forms, it is necessary to understand the
concepts of functional dependencies and lossless
decomposition.

Answer / nitin bisht

NORMALIZATION: The process of structuring data to minimise
duplication and inconsistencies. The process usually
involves breaking down a single table into two or more
tables and defining relationships between those tables.
Normalisation is usually done in stages, with each stage
applying more rigourous rules to the types of information
which can be stored in a table. While full adherence to
normalisation principles increases the efficiency of a
particular database, the process can become so esoteric that
you need a professional to create and understand the table
design. Most people, when creating a database, don't need to
go beyond the third level of normalisation, called third
normal form. And there's no need to know the terminology:
simply applying the principles is sufficient.

The first three levels in normalising a database are:

First Normal Form (1NF): There should be no repeating groups
in a table.

For example, say you have a students table with the
following structure:

student ID
name
date of birth
advisor
advisor's telephone
student
course ID 1
course description 1
course instructor 1
course ID 2
course description 2
course instructor 2

The repeating course fields are in conflict with first
normal form.To fix the problems created by such repeating
fields, you should place the course information in a
separate course table, and then provide a linking field
(most likely student ID) between the students table and the
course table.

Second Normal Form (2NF): No non-key fields may depend on a
portion of the primary key.

For example, say we create a course table with the structure:

student ID
course ID
course description
course instructor

We can create a unique primary key by combining student ID +
course ID (student ID is not unique in itself, as one
student may take multiple courses; similarly, course ID is
not unique in itself as many students may take the same
course; however, each student will only be taking a
particular course once at any one time, so the combination
of student ID + course ID gives us a unique primary key).

Now, in 2NF, no non-key fields (course description, course
instructor) may depend on a portion of the primary key.
That, however, is exactly what we have here: the course
instructor and course description are the same for any
course, regardless of the student taking the course.

To fix this and put the database in second normal form, we
create a third table, so our database structure now looks
like this (with key fields in italics):

Student table

student ID
name
date of birth
advisor
advisor's telephone

Student courses table

student ID
course ID

Courses table

course ID
course description
course instructor

Third Normal From (3FN): No fields may depend on other
non-key fields. In other words, each field in a record
should contain information about the entity that is defined
by the primary key.

In our students table, for example, each field should
provide information about the particular student referred to
by the key field, student ID. That certainly applies to the
student's name and date of birth. But the advisor's name and
telephone doesn't change depending on the student. So, to
put this database in third normal form, we need to place the
advisor's information in a separate table:

Student table

student ID
name
date of birth
advisor ID

Student courses table

student ID
course ID

Courses table

course ID
course description
course instructor

Advisor table

advisor ID
advisor name
advisor telephone

Answer / amresh

In simple words process of replacing a relation with more
than one relation so as to come round with problems of bad
database design(i.e redundancy,insertion,updation,deletion
anomalies_---
The three primitive normal forms r
1> 1-nf:asserts that relation with
atomic values.
2> 2-nf:asserts with eliminating partial
dependency.
3> 3-nf:asserts at eliminating
transitive dependency.

Answer / sudhakar

Normalization is the process of efficiently organizing data
in a database. There are two goals of the normalization
process: eliminating redundant data (for example, storing
the same data in more than one table) and ensuring data
dependencies make sense (only storing related data in a
table). Both of these are worthy goals as they reduce the
amount of space a database consumes and ensure that data is
logically stored.


The Normal Forms

The database community has developed a series of guidelines
for ensuring that databases are normalized. These are
referred to as normal forms and are numbered from one (the
lowest form of normalization, referred to as first normal
form or 1NF) through five (fifth normal form or 5NF). In
practical applications, you'll often see 1NF, 2NF, and 3NF
along with the occasional 4NF. Fifth normal form is very
rarely seen and won't be discussed in this article.

Before we begin our discussion of the normal forms, it's
important to point out that they are guidelines and
guidelines only. Occasionally, it becomes necessary to
stray from them to meet practical business requirements.
However, when variations take place, it's extremely
important to evaluate any possible ramifications they could
have on your system and account for possible
inconsistencies. That said, let's explore the normal forms.

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an
organized database:
Eliminate duplicative columns from the same table.
Create separate tables for each group of related data and
identify each row with a unique column or set of columns
(the primary key).

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of
removing duplicative data:
Meet all the requirements of the first normal form.
Remove subsets of data that apply to multiple rows of a
table and place them in separate tables.
Create relationships between these new tables and their
predecessors through the use of foreign keys.

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:
Meet all the requirements of the second normal form.
Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional
requirement:
Meet all the requirements of the third normal form.
A relation is in 4NF if it has no multi-valued
dependencies.
Remember, these normalization guidelines are cumulative.
For a database to be in 2NF, it must first fulfill all the
criteria of a 1NF database.

Answer / harjeet singh

thanks for your appriciate definitions

Answer / latesh sarode

REDUCING DATA REDUNDANCY

A table structure has been suggested for employees database.
After testing the structure with data we find that there is
a problem in adding, deleting and modifying data.

We see that the table structure could lead to inconsistency
in data and that it occupies lot of disk space.

The table structure is as follows
Employee code
First name
Last name
Candidate code
Address
City
Telno
Qualification
Birthdate
Skill

The table with some sample data is as follows
Emp code First Name Last Name City …………. Skill
00001 Angela Smith Mumbai Communication
00001 Angela Smith Mumbai Presentation
00001 Angela Smith Mumbai Team Leading
00002 Betty Williams Pune Communication
00002 Betty Williams Pune Presentation
00002 Betty Williams Pune Convincing
00002 Betty Williams Pune Planning
00003 Carol Jones Hyderabad Communication
00003 Carol Jones Hyderabad Team leading
00003 Carol Jones Hyderabad Inter personal




Solution

Identify how to reduce data redundancy and inconsistency in
updating, adding and deleting data.

Understanding Data Redundancy

Redundancy means repetition of data. Redundancy increases
the time involved in updating, adding and deleting data. It
also increases utilization of disk space and hence disk I/O.

Redundancy can therefore lead to

Update anomalies – Inserting, modifying and deleting data
may cause inconsistencies

Inconsistencies – Errors are most likely to occur when facts
are repeated

Un-necessary utilization of disk space

We can use systematic approaches like normalization to
reduce redundancy and duplicity

“Normalization is a scientific method of breaking down
complex table structures into simple table structures by
using certain rules. We can therefore reduce redundancy in a
table and eliminate the problems of inconsistency and disk
space usage.”










Normalization has numerous benefits

It enables faster sorting and index creation, few nulls and
an increase in compactness of database.

Normalization helps to simplify the structure of tables

“Performance of an application is directly linked to the
database design”

A poor design hinders the performance of the system

Logical design of the database lays the foundation for an
optimal database

Rules to be followed to achieve a good database design

Each table should have an identifier (tablename)

Each table should store data for single type of entity

Columns that accept nulls should be avoided

Repetition of columns or values should be avoided

Normalization results in formation of tables that satisfy
certain specified rules and represent certain normal forms

The normal forms are used to ensure that various types of
anomalies and inconsistencies are not introduced in the
database.

A table structure is always in certain normal form






First Normal Form (1 NF)

A table is said to be in 1st Normal Form when each cell
contains precisely one value.

Following is the content of project table

Emp code Dept Proj Code Hours
E 101 Systems P27 90
P51 101
P20 60
E 305 Sales P27 109
P 22 98
E 508 Admin P 51 NULL
P27 72

The data in the table is not normalized because the cells in
Projcode and Hours have more than 1 value.

By applying 1NF definition to the project table we would
arrive at the following:-

Emp code Dept Proj Code Hours
E 101 Systems P27 90
E 101 Systems P51 101
E 101 Systems P20 60
E 305 Sales P27 109
E 305 Sales P 22 98
E 508 Admin P 51 NULL
E 508 Admin P27 72








Functional Dependency
Normalization theory is based on the fundamental notion of
functional dependency

Given a relation R, attribute A is functionally dependent on
attribute B if each value of A in R is associated with
precisely one value of B

In other words attribute A is functionally dependent on B if
and only if for each value of B there is exactly one value of A
Thus attribute B is called determinant

For Example consider the employee table

Code Name City
E1 Rajesh Delhi
E2 Ashish Mumbai
E3 Manish Kolkata
Given a particular value of code there is precisely one
corresponding value for name. For example for code E1 there
is exactly one value of Name (Rajesh). Hence name is
functionally dependent on code. Similarly there is exactly
one value of city for each value of code. Hence attribute
city is functionally dependent on code. The attribute code
is determinant. We can also say that Code determines name
and city.













Second Normal Form (2NF)

A table is said to be in 2nd normal form when it is in 1NF
and every attribute in the row is functionally dependent
upon the whole key and not just part of the key.

Consider the project table

Empcode
Projcode
Dept
Hours

The table has following rows

Empcode Projcode Dept Hours
E101 P27 Systems 90
E305 P27 Finance 10
E508 P51 Admin NULL
E101 P51 Systems 101
E101 P20 Systems 60
E508 P27 Admin 72

The situation could lead to following problems

Insertion - The department of a particular employee cannot
be recorded until the employee is assigned a project

Updation - For a given employee the employee code and
department are repeated several times. Hence if an employee
is transferred to another department, this change will have
to be recorded in every row of the employee table pertaining
to that employee. Any omission will lead to inconsistencies.

Deletion - If an employee completes work on a project the
employees record will be deleted as a result the information
regarding the department to which the employee belongs will
also be lost.



The primary key here is composite (Empcode + Projcode)

The table satisfies definition of 1NF, we now need to check
if it satisfies 2NF

In the table for each value of empcode, there is more than 1
value of hours. Thus for empcode E101 there are 3 values of
hours 90,101 and 60 respectively.

Hence hours is not functionally dependent on Empcode.

Similarly for each value of projcode there is more than
1value of hours thus for projcode P27, there are three
values of hours 90, 10 and 72.

However for a combination of ecode and projcode there is
exactly one value of hours hence hours is functionally
dependent on the whole key ecode + projcode

Now we must check if dept is functionally dependent on the
whole key ecode + projcode

For each value of ecode there is exactly 1 value of dept,
for ecode E101 there is exactly 1 value system department.
Hence dept is functionally dependent on ecode.

However for each value of projcode there is more than 1
value of department, for projcode P27 there are 2 values of
department System and Finance. Hence Dept is not
functionally dependent on p4rojcode. Dept is therefore
functionally dependent on part of key (which is ecode) and
not functionally dependent on whole key (empcode + projcode).

Therefore the table Project is not in 2NF, for a table to be
in 2NF, the non key attributes must be fully functionally
dependent on the whole key and not just part of key.

Converting a table into 2NF

• Find and remove attributes that are functionally dependent
on only a part of the key and not on the whole key, place
them in a different table.
• Group the remaining attributes.

To convert the project table into 2NF we must remove the
attributes that are not fully functionally dependent on the
whole key and place them in a different table along with the
attribute that it is functionally dependent on.

In the above example since Dept is not fully functionally
dependent on the whole key ecode+projcode, we place dept
along with ecode in a separate table called Employeedept

Now the project table will contain Ecode, Projcode and Hours
EmployeeDept

Ecode Dept
E101 Systems
E305 Sales
E508 Admin

Project
Ecode Projcode Hours
E101 P27 90
E101 P51 101
E101 P20 60
E305 P27 10
E508 P51 NULL
E508 P27 72






Third Normal Form (3 NF)

A relation is said to be in 3 NF when it is in 2 NF and
every non key attribute is functionally dependent only on
the primary key.

Consider the employee table
Ecode Dept Depthead
E101 Systems E901
E305 Finance E906
E402 Sales E906
E508 Admin E908
E607 Finance E909
E608 Finance E909

The problem with dependencies of this kind are :-

Insertion - The department head of a new department that
does not have any employees at present cannot be entered in
the depthead column . This is because the primary key is
unknown.

Updation - For a given department, the code for a particular
department head (depthead) is repeated several times. Hence,
if a depthead moves to another department, the change will
have to be made consistently across the table.

Deletion - If the record of an employee is deleted,
information regarding the head of the department will also
be deleted, hence there is loss of information.

We must check if the table is in 3 NF, since each cell in
the table has a single value the table is in 1 NF
The primary key in employee table is ecode . For each value
of ecode there is exactly one value of dept. Hence the
attribute dept is functionally dependent on the primary key,
Ecode. Similarly for each value of Ecode there is exactly
one value of depthead. Hence depthead is functionally
dependent on the primary key ecode. Hence all the attributes
are functionally dependent on the whole key Ecode. Hence
table is in 2 NF

However the attribute depthead is dependent on the attribute
dept also. As per 3 NF all non-key attributes have to be
functionally dependent only on the primary key. This table
is not in 3 NF since depthead is functionally dependent o
dept which is not a primary key.

Converting a table into 3 NF

Find and remove non key attributes that are functionally
dependent on attributes that are not the primary key. Place
them in a different table.
Group the remaining attributes.
To convert the table employee into 3NF we must remove the
column depthead since it is not functionally dependent only
on the primary key Ecode and place it in another table
called department along with the attribute dept which it is
functionally dependent on.

Employee
Ecode Dept
E101 Systems
E305 Finance
E402 Sales
E508 Admin
E607 Finance
E608 Finance

Department
Dept Depthead
Systems E901
Sales E906
Admin E908
Finance E909

Answer / balaji

Normalization is the process of efficiently organizing data
in a database. There are two goals of the normalization
process: eliminating redundant data (for example, storing
the same data in more than one table) and ensuring data
dependencies make sense (only storing related data in a
table). Both of these are worthy goals as they reduce the
amount of space a database consumes and ensure that data is
logically stored.


The Normal Forms

The database community has developed a series of guidelines
for ensuring that databases are normalized. These are
referred to as normal forms and are numbered from one (the
lowest form of normalization, referred to as first normal
form or 1NF) through five (fifth normal form or 5NF). In
practical applications, you'll often see 1NF, 2NF, and 3NF
along with the occasional 4NF. Fifth normal form is very
rarely seen and won't be discussed in this article.

Before we begin our discussion of the normal forms, it's
important to point out that they are guidelines and
guidelines only. Occasionally, it becomes necessary to
stray from them to meet practical business requirements.
However, when variations take place, it's extremely
important to evaluate any possible ramifications they could
have on your system and account for possible
inconsistencies. That said, let's explore the normal forms.

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an
organized database:
Eliminate duplicative columns from the same table.
Create separate tables for each group of related data and
identify each row with a unique column or set of columns
(the primary key).

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of
removing duplicative data:
Meet all the requirements of the first normal form.
Remove subsets of data that apply to multiple rows of a
table and place them in separate tables.
Create relationships between these new tables and their
predecessors through the use of foreign keys.

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:
Meet all the requirements of the second normal form.
Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional
requirement:
Meet all the requirements of the third normal form.
A relation is in 4NF if it has no multi-valued
dependencies.
Remember, these normalization guidelines are cumulative.
For a database to be in 2NF, it must first fulfill all the
criteria of a 1NF database.


regard
Balaji B.Bhadre
HCL

Explain entity ?

0 Answers  

How does rows store in Teradata

1 Answers  

What is difference between server and database?

0 Answers  

What is ole db in ssis?

0 Answers  

what is the difference between tuple calculus and domain calculus ?

2 Answers   Infosys,

How to implement database security?

0 Answers  

What is a Transformation?

0 Answers   Informatica,

Explain data independence & its types.

0 Answers  

how can we view column head in first page but not in remaining pages?

0 Answers   TCS,

What are the validations performed on the source data? If there are junk data available in the source data, how to eliminate it?

0 Answers   BDI Systems, Google,

A design problem was given in which I was supposed to make a design for the data base for a given problem.

0 Answers   Motorola,

How concurrency problems effect the database?

0 Answers   BirlaSoft,