NEW INTERVIEW QUESTIONS.COM DATABASE INTERVIEW QUESTIONS SQL SERVER INTERVIEW QUESTIONS DETAILS

Add to My Questions | Forward | Print

Question

Explain Normalization and Denormalization with examples.

Answer

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 © NewInterviewQuestions.com

Answer:*
Name:*
Email Address:*
Verification Code	Not readable? Load New Code
Enter the above shown code:*

Category	SQL Server Interview Questions
Rating	(0.0) By 0 users
Added	7/25/2006
Views	153762
Rate it!	* * * * * * * * * * * * * * *
Free Offers:	300 Free Magazines for you. No credit card required. No Shipping Charges. Absolutely Free for 1 Year.

Please Note: We keep on updating better answers to this site. Subscribe to our newsletter to get notified when better answer is posted.

Notify me when better answer is posted!
Email: