GeekInterview.com
Series: Subject: Topic:
Question: 106 of 118

Explain Normalization and Denormalization with examples?

Asked by: Beena | Member Since Sep-2005 | Asked on: Sep 19th, 2005

View all questions by Beena   View all answers by Beena

Showing Answers 1 - 5 of 5 Answers
rz

Answered On : Oct 6th, 2005

in short, to guarantee the data integrity, i.e., "All or None"

Yes  1 User has rated as useful.
  
Login to rate this answer.
plo

Answered On : Oct 18th, 2005

Normalization
 
Record design process that identifies and avoids data problems and redundancy
Specifies the fields and the primary key
Normalization analyzes record structure through four stages
Unnormalized records
First normal form (1NF) records
Second normal form (2NF) records
Third normal form (3NF) records
Normalization
 
First normal form
Unnormalized records contain a repeating group
A repeating group refers to a single record that has multiple values in a particular field
Example: multiple product numbers in a single order record
A 1NF record cannot have a repeating group
Normalization
 
First normal form
To convert an unnormalized record to 1NF, the repeating group must be removed
Expand the primary key to include the primary key of the repeating group
The new primary key is a combination of the original primary key and the key of the repeating group
Instead of a single record with a repeating group, the result is many records, one for each instance of the repeating group
Normalization
 
Second normal form (2NF)
To be in second normal form, a record must be in 1 NF, and all nonkey fields must be functionally dependent on the entire primary key - not just part of it
Functional dependency means that a value in one field determines a value in another field
If the primary key is a single field, then any record in 1 NF is automatically in 2 NF
In 2NF, all nonkey fields are functionally dependent on the entire primary key
Normalization
 
Second normal form (2NF)
To convert a 1NF record to 2NF
Create a new record design for each field (or combination of fields) in the primary key
Place remaining fields with the appropriate record
The result will be several records, each with a primary key field (or combination of fields) that determines the values of the other fields in that record
Normalization
 
Third normal form (3NF)
To be in 3NF, a record must be in 2NF and no nonkey field is functionally dependent on another nonkey field
In 3NF, all nonkey fields are functionally dependent on the primary key, the entire key, and nothing but the key
Normalization
 
Third normal form (3NF)
To convert a 2NF record to 3NF
Remove all nonkey fields that depend on another nonkey field and place them in a new record that has the determining field as a primary key
Normalization
 
A normalization example
Identify the entities
ADVISOR
STUDENT
COURSE
Identify the relationships
One advisor advises many students (1:M)
Students take one or more courses, and courses have one or more students (M:N)
Normalization
 
A normalization example
Identify the entities
ADVISOR
STUDENT
COURSE
Identify the relationships
One advisor advises many students (1:M)
Students take one or more courses, and courses have one or more students (M:N)
Document the unnormalized record
Note the repeating group of courses
Normalization
 
A normalization example
Convert the unnormalized record to 1 NF
Remove the repeating group
Create a primary key composed of the original primary key (student number) and the primary key of the repeating group (course number)
The result is one record for each instance of the combination primary key
Normalization
 
A normalization example
Convert the 1 NF record to 2NF
Create a separate record design for each field and combination of fields in the primary key
Place functionally dependent fields with an appropriate primary key
The result is three records instead of one, each with a unique primary key
Now all nonkey fields are dependent on the entire primary key, not just a portion of it
Normalization
 
A normalization example
Convert the 2NF record to 3NF
The STUDENT record contains a nonkey field (advisor name) that is dependent on another nonkey field (advisor number)
Create a new record with advisor number as the primary key
Remove the dependent nonkey field (advisor name) and include it in the new record
Normalization
 
A normalization example
Convert the 2NF record to 3NF
The STUDENT record contains a nonkey field (advisor name) that is dependent on another nonkey field (advisor number)
Create a new record with advisor number as the primary key
Remove the dependent nonkey field (advisor name) and include it in the new record
Now all nonkey fields are dependent on the entire primary key, and nothing but the key

Yes  1 User has rated as useful.
  
Login to rate this answer.
nitya.palagiri

Answered On : Jun 28th, 2008

View all answers by nitya.palagiri

Normalization : It is the process of decomposing a relation with anomalies into a well structured relation.

Basic Terminology of Normalization:-

Well-structured relation : A relation with minimum redundancy and no anomalies

Anolamy : is an error during either of insert,update or delete operation


Primary key attribute: An attribute(column) on which Primary key constraint is applied - so as to avoid duplicate and null values - Primary key attribute assists to identify rows uniquely in a relation :

Non-Key attributes : All other columns apart from primary key attribute are called Non key attributes:

multi-valued attribute : An attribute that contains more than one value per instance

Functional- dependency: - IF a non-key attributes completely rely or depend on Primay key attribute it is termed as functional dependency

Ex:   EMP (eno,ename,job,sal) - here eno is Primary key attribute and all other are Non key attributes - 

if eno is known - corresponding ename,job,sal can be identified - Hence i say - ename,job,sal are the non key attribute which completely rely on primary key attribute called eno;

Partial - functional dependency:

                      if a non-key attribute partially depends on primary key-attribute - then tat kind of dependency is called Partial functioan dependency.

Transitive dependency:
    if a non key atttribute depends on another non-key attribute then that kind of dependency is called transitive dependency

multivalued dependency : Consider a relation with A,B,C as columns - the term multivalued dependency states that for each value of A there exists a set of values for the column B and a set of values for the cokumn C - but B values and C values are independent of each other


Normalization is achieved through stages and each stage corresponds to normal form- WE have basic normal forms and higher or advanced normal forms

Under basic

1NF : A relation is said to be in 1 NF - if it does not contain multivalued attributes
2NF : A relations is said to be in 2 NF - if it does not contain partial functional dependencies
3NF- A relation is said to be in 3 NF - if it does not contain transitive dependecies

Advanced

BCNF : Every determinant is a candidate key
4NF: A relation is said to be in 4NF if it does not contain multivalued dependencies
5NF - Domain key normal f orm - if any of the remaining anomalies have been removed

  
Login to rate this answer.
siva

Answered On : Apr 27th, 2012

Your Explnation may be good but it was too dificult to understand with out examples

  
Login to rate this answer.
siva

Answered On : Apr 27th, 2012

It is good.I understood if you put some brief explanation with data, is very usable to the persons who are not
understanding about normalization like me.
Thanku.

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.