UPSC Exam  >  UPSC Notes  >  Famous Books for UPSC Exam (Summary & Tests)  >  Database: Types of Normalization

Database: Types of Normalization | Famous Books for UPSC Exam (Summary & Tests) PDF Download

What is meant by Normalization in RDBMS?

 

Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anamolies.

Update anomalies − If data items are scattered and are not linked to each other properly, then it could lead to strange situations. For example, when we try to update one data item having its copies scattered over several places, a few instances get updated properly while a few others are left with old values. Such instances leave the database in an inconsistent state.

Deletion anomalies – When you tried to delete a record, but parts of it was not deleted because of unawareness, the data is also saved somewhere else.

Insert anomalies – When you tried to insert data in a record that does not exist at all.

Normalization is a method to remove all these anomalies and bring the database to a consistent state.

What are different types of Normal Forms?

  • 1st NF (1st normal form)
  • 2nd NF (2nd normal form)
  • 3rd NF (3rd normal form)
  • BCNF (BOYCE CODD NF)
  • 4th NF
  • 5th NF

1 NF (First Normal Form):

 

For First Normal form following rules must be followed:

  • Every column in the table must be unique
  • Separate tables must be created for each set of related data
  • Each table must be identified with a unique column or concatenated columns called the primary key
  • No rows may be duplicated
  • no columns may be duplicated
  • no row/column intersections contain a null value
  • no row/column intersections contain multivalued fields

2NF (Second Normal Form):

 

Second normal form states that it should meet all the rules for 1NF and there must be no partial dependences of any of the columns on the primary key.

A database is in second normal form if it satisfies the following conditions:

  • It is in first normal form.
  • All non-key attributes are fully functional dependent on the primary key.

Consider a table with following attributes:

CREATE TABLE LIBRARY(

       ST_ID    INT              NOT NULL,

       ST_NAME VARCHAR (30)      NOT NULL,

       BOOK_ID   INT              NOT NULL,

       BOOK_NAME VARCHAR (20)  NOT NULL,

       AUTHOR_NAME CHAR   (30) NOT NULL,

       ISSUE_DATE  DATETIME NOT NULL,

       RETURN_DATE  DATETIME NOT NULL,

       PRIMARY KEY (ST_ID, BOOK_ID)
);

This table is in first normal form, in that it obeys all the rules of first normal form. In this table, the primary key consists of ST_ID and BOOK__ID.

However, the table is not in second normal form because there are partial dependencies of primary keys and columns. ST_NAME is dependent on ST_ID, and there's no real link between a Student's name and what book he issued. Book name and author are also dependent on BOOK_ID, but they are not dependent on ST_ID, because there's no link between a ST_ID and an AUTHOR_NAME or their ISSUE_DATE.

To make this table comply with second normal form, you need to separate the columns into three tables.

First, create a table to store the STUDENT details as follows:

 

CREATE TABLE STUDENT(

       ST_ID    INT              NOT NULL,

       ST_NAME VARCHAR (20)      NOT NULL,

       PRIMARY KEY (ST_ID)

);

Next, create a table to store details of each BOOK:

CREATE TABLE BOOKS(

       BOOK_ID   INT              NOT NULL,

       AUTHOR_NAME CHAR (20)  NOT NULL,

       PRIMARY KEY (BOOK_ID)

);

Finally, create a third table storing just ST_ID and BOOK_ID to keep track of all the books issue to a student:

CREATE TABLE LIBRARY_ISSUE(

       ST_ID    INT              NOT NULL,

       BOOK_ID   INT              NOT NULL,

       ISSUE_DATE  DATETIME NOT NULL,

       RETURN_DATE  DATETIME NOT NULL,

       PRIMARY KEY (ST_ID, BOOK_ID)

);

NF (Third Normal Form) 

First and foremost thing is that a table has to be in 2NF to be in 3NF. Next the rule is: remove to a new table any non-key attributes that are more dependent on other non-key attributes than the table key. Ignore tables with zero or only one non-key attribute (these go straight to 3NF with no conversion). 

The process is as follows:

If a non-key attribute is more dependent on another non-key attribute than the table key:

  • Move the dependent attribute, together with a copy of the non-key attribute upon which it is dependent, to a new table.
  • Make the non-key attribute, upon which it is dependent, the key in the new table. Underline the key in this new table.
  • Leave the non-key attribute, upon which it is dependent, in the original table and mark it a foreign key 

 

Thus a table is in third normal form if:

  • A table is in 2nd normal form.
  • It contains only columns that are non-transitively dependent on the primary key.
The document Database: Types of Normalization | Famous Books for UPSC Exam (Summary & Tests) is a part of the UPSC Course Famous Books for UPSC Exam (Summary & Tests).
All you need of UPSC at this link: UPSC
545 videos|948 docs|362 tests

Top Courses for UPSC

FAQs on Database: Types of Normalization - Famous Books for UPSC Exam (Summary & Tests)

1. What is database normalization?
Ans. Database normalization is the process of organizing data in a database to eliminate redundancy and improve data integrity. It involves splitting data into multiple tables and establishing relationships between them.
2. What are the different types of database normalization?
Ans. There are five normal forms in database normalization: 1. First Normal Form (1NF) 2. Second Normal Form (2NF) 3. Third Normal Form (3NF) 4. Boyce-Codd Normal Form (BCNF) 5. Fourth Normal Form (4NF)
3. How does the first normal form (1NF) achieve database normalization?
Ans. First Normal Form (1NF) ensures that each column in a table contains only atomic values. It eliminates repeating groups and ensures that each cell contains a single value, avoiding duplication of data.
4. What is the purpose of the second normal form (2NF) in database normalization?
Ans. Second Normal Form (2NF) eliminates partial dependencies within a table. It achieves this by ensuring that each non-key column depends on the entire primary key, rather than just a part of it. This helps to remove redundancy and improve data integrity.
5. How does Boyce-Codd Normal Form (BCNF) differ from other normal forms?
Ans. Boyce-Codd Normal Form (BCNF) is a stricter form of normalization compared to the others. It focuses on eliminating all functional dependencies within a table, including those that are not dependent on the primary key. BCNF ensures that every determinant in a table is a candidate key, resulting in a higher level of normalization.
545 videos|948 docs|362 tests
Download as PDF
Explore Courses for UPSC exam

Top Courses for UPSC

Signup for Free!
Signup to see your scores go up within 7 days! Learn & Practice with 1000+ FREE Notes, Videos & Tests.
10M+ students study on EduRev
Related Searches

mock tests for examination

,

Summary

,

practice quizzes

,

Database: Types of Normalization | Famous Books for UPSC Exam (Summary & Tests)

,

Previous Year Questions with Solutions

,

Important questions

,

Sample Paper

,

Extra Questions

,

Free

,

study material

,

Exam

,

Database: Types of Normalization | Famous Books for UPSC Exam (Summary & Tests)

,

past year papers

,

video lectures

,

Viva Questions

,

pdf

,

Objective type Questions

,

MCQs

,

ppt

,

shortcuts and tricks

,

Semester Notes

,

Database: Types of Normalization | Famous Books for UPSC Exam (Summary & Tests)

;