What do you mean by normalization of database? Normalize any relational database using 3NF and write differences between BCNF and 3NF

Question No. 4

Question Paper: Subjective Exam (Paper II)

Exam: Computer Officer Exam 2010

Conducted by:  Public Service Commission.

Marks it carried: 10 marks.

Question Answer Yes No

Answer to ‘what do you mean by normalization of database’

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process:

1. Eliminating redundant data (for example, storing the same data in more than one table) and

2. 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.

Edgar F. Codd, the inventor of the relational model, introduced the concept of normalization and what we now know as the First Normal Form (1NF) in 1970. Codd went on to define the Second Normal Form (2NF) and Third Normal Form (3NF) in 1971, and Codd and Raymond F. Boyce defined the Boyce-Codd Normal Form (BCNF) in 1974. Higher normal forms were defined by other theorists in subsequent years, the most recent being the Sixth normal form (6NF) introduced by Chris Date, Hugh Darwen, and Nikos Lorentzos in 2002.

Answer to ‘normalize any relational database using 3NF’

Normalize a database to 3nf

Step 1:

Ask the following question:

Does the table in question have any repeating groups?

No:

It is in 1nf.

Yes:

It is unnormalized, so to put it in 1nf, remove the repeating groups.

Step 2:

Does the primary key contain only one column?

Yes:

Then the table is already in 2nf.

No:

Ask the following question:

Do any of the columns that are not keys have their values determined by only some, and not all, of the columns that make up the key?

Yes- then the table is not in 2nf.

To put it in 2nf, remove any columns that are dependent upon only a portion of the key, and create separate or separate tables.

No- then the table is in 2nf.

Step 3:

Are all of the columns in the table determined only by candidate keys?

Yes:

Then the table is in 3nf.

No:

Remove any columns that are not fully determined by candidate keys, and again create additional table(s) that contain those columns.

Answer to ‘differences between BCNF and 3NF’

3NF is a Table that is in 1NF and 2NF and in which no non primary key attribute is transitively dependent on primary key. A Table is in BCNF if and only if every determinant(it is an attribute or a group of attributes on which some other attribute is fully functionally dependent, see functional dependency described above) is a candidate key. BCNF is a stronger form of 3NF.

The difference between 3NF and BCNF is that for a Functional dependency A—>B, 3NF allows this dependency in a table if attribute B is a primary key attribute and attribute A is not a candidate key, where as BCNF insists that for this dependency to remain in a table, attribute A must be a candidate key.

 

Additional References and Downloads

Download Class Notes PDF database-normalization.pdf
Download Teacher’s PowerPoint Presentation Slides The-Normal-Forms.ppt

Digiprove sealCopyright secured by Digiprove © 2013 Suresh Khanal

by Suresh Khanal

I wish I could help everyone but I wonder what God would do then? Anyway, I try to help as much as I can and this site is an attempt in that direction. You can help me to make this site more useful by sending the materials you can prepare or MCQs you've collected. Please mail me to contact@icttrends.com.

Leave a Comment

  1. Location croatie

    My friend is very interested in Oracle. I'll tell this to her. Hope she finds this useful.

    Reply
  2. Inventory Control Software

    I agree with you Suresh. We learned about normalization of database in college, I'm an It graduate by the way. Normalization of database is confusing at first but with practice and good resources it will be easier. Thanks for this post.

    Reply
    1. Suresh Khanal

      This was a question asked for computer officers candidates in Public Service Commission exam in 2010. I hope the answer deservs that 10 marks!

      Reply
  3. Ashish Pandey

    Hi,

    I was reading your article and I would like to appreciate you for making it very simple and understandable.
    This article gives me a basic idea of normalization in database and it will help me a lot.
    Check out this link too its having a nice post with wonderful explanation on how to use normalization in database.

    http://mindstick.com/Articles/88d5a271-b369-4c0a-ae80-01e66df98b6a/?What%20is%20Normalization?

    Thank you very much!

    Reply