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

• Tweet
• Email

• Tweet
• Email

Question No. 4

Question Paper: Subjective Exam (Paper II)

Exam: Computer Officer Exam 2010

Conducted by:  Public Service Commission.

Marks it carried: 10 marks.

Answer:

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

Tagged As: 3NF, BCNF, differences between BCNF and 3NF, Normalization of database, relational database