0
28kviews
What is Normalization? Explain 1NF, 2NF, 3NF and BCNF giving examples.
1 Answer
3
971views
  • Database normalization is a technique of organizing the data in the database.
  • Normalization of data can be considered a process of analysing the given relation schemas based on their Functional Dependencies and primary keys to achieve the following properties:

    i. Minimizing redundancy

    ii. Minimizing the insertion, deletion, and update anomalies

    iii. Ensuring data is stored in correct table

  • It can be considered as a filtering process to make the design have successively better quality

  • It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.
  • Without normalization it becomes difficult to handle and update database without facing data loss.
  • The various forms of normalization are described below:

I. First Normal Form (1NF):

  • First normal form (1NF) states that the domain of an attribute must include only atomic values and that the value of any attribute in a tuple must be a single value from the domain of that attribute.
  • A relation is said to be in 1NF if it contains no non-atomic values and each row can provide a unique combination of values.
  • 1NF disallows having a set of values, a tuple of values, or a combination of both as an attribute value for a single tuple.
  • The only attribute values permitted by 1NF are single atomic values.
  • Example: Un-Normalized Table-
Student Age Subject
Rooney 15 Java, C++
Kane 16 HTML, PHP

Normalized Table: Any Row must not have a column in which more than one value is saved, instead data is separated in multiple rows as shown below.

Student Age Subject
Rooney 15 JAVA
Rooney 15 C++
Kane 16 HTML
Kane 16 PHP

II. Second Normal Form (2NF):

  • A relation is said to be in 2NF, if it is already in 1NF and each and every attribute fully depends on the primary key of the relation.
  • There must not be any partial dependency of any column on the primary key.
  • Second normal form (2NF) is based on the concept of full functional dependency. A functional dependency X -> Y is a full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.
  • A functional dependency X->Y is a partial dependency if some attribute A belongs to X can be removed from X and the dependency still holds.
  • Example:

Student_Project Table

Stud_ID Proj_ID Stud_Name Proj_Name
100 001 Rooney Cloud
200 002 Kane Servers

Stud_Name depends on Stud_ID and Proj_Name depends on Proj_ID

The above table can be normalized to 2NF as shown below.

Student Table in 2NF

Stud_ID Proj_ID Stud_Name
100 001 Rooney
200 001 Kane

Project Table in 2NF

Proj_ID Proj_Name
001 001
002 Servers

III. Third Normal Form (3NF):

  • A relation is said to be in 3NF, if it is already in 2NF and there exists no transitive dependency in that relation.
  • If a table contains transitive dependency, then it is not in 3NF, and the table must be split to bring it into 3NF.
  • What is a transitive dependency?

    A -> B [B depends on A] & B -> C [C depends on B]

    Then A -> C[C depends on A] can be derived.

  • Example:Below table not in 3NF
Stud_ID Stud_Name City Zip
100 Rooney Manchester 4001
200 Kane Stoke 4002

Stud_ID is the only prime key attribute. City can be identified by Stu_ID as well as Zip. Neither Zip is a superkey nor City is a prime attribute.

Stud_ID -> Zip -> City, so there exists transitive dependency. Hence 3NF table is below

Student_Detail

Stud_ID Stud_Name Zip
100 Rooney 4001
200 Kane 4002

Zip_Code

Zip City
4001 Manchester
4002 Stoke

IV. Boyce-Codd Normal Form (BCNF):

  • BCNF is an extension of Third Normal Form in strict way.
  • A relationship is said to be in BCNF if it is already in 3NF and for any non-trivial functional dependency, X -> A, then X must be a super-key.
  • A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF.
  • Example:

    In the 3NF example,Stud_ID is super-key in Student_Detail relation and Zip is super-key in ZipCodes relation.

    So Stud_ID ->Stud_Name, Zip and

    Zip ->City

    Confirms, that both relations are in BCNF.

Please log in to add an answer.