Scribd
Upload
13 views5 pages

Normlaization in DBMS

Uploaded by

moazmizan666
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views5 pages

Normlaization in DBMS

Uploaded by

moazmizan666
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

Normalization in DBMS

 Normalization is a process used in a Database Management System (DBMS) to reduce


data redundancy and improve data integrity by organizing tables and relationships
efficiently.
 It involves dividing larger tables into smaller ones and defining relationships between
them.
 The ultimate goal of normalization is to ensure that data is stored in a way that reduces
redundancy (repetition of data) and minimizes anomalies (insertion, update, and deletion
anomalies).

Normalization is done through several stages, called normal forms (NFs). The most commonly
used normal forms are First Normal Form (1NF), Second Normal Form (2NF), and Third
Normal Form (3NF).

First Normal Form (1NF)

A table is in 1NF if:

1. All attributes (columns) contain only atomic (indivisible) values, i.e., no multiple
values in a single column.
2. Each entry must be unique, with no repeating groups or arrays.

Example:

Consider a table Student_Course that records students and the courses they have taken:

Student_ID Student_Name Courses


101 Alice Math, English
102 Bob Science, History
103 Charlie Math, Science, Art

In the above table, the column Courses contains multiple values for each student, violating the
1NF rule of atomicity.

Faculty of computer science and digital innovation 1


Solution (Convert to 1NF):

To bring this table into 1NF, each course should be listed in a separate row:

Student_ID Student_Name Course


101 Alice Math
101 Alice English
102 Bob Science
102 Bob History
103 Charlie Math
103 Charlie Science
103 Charlie Art

Now, each column contains atomic values, and the table is in 1NF.

Second Normal Form (2NF)

A table is in 2NF if:

1. It is already in 1NF.
2. All non-key attributes are fully dependent on the primary key (no partial dependency).

Example:

Consider the Student_Course table after it has been normalized to 1NF:

Student_ID Student_Name Course Instructor


101 Alice Math Mr. X
101 Alice English Ms. Y
102 Bob Science Mr. Z
102 Bob History Mr. A

Here, the primary key is a composite key made up of Student_ID and Course. However, the
Student_Name depends only on Student_ID, and the Instructor depends only on Course.
This violates 2NF because there are partial dependencies (attributes depend on part of the
composite key, not the whole key).

Solution (Convert to 2NF):

Faculty of computer science and digital innovation 2


To bring the table into 2NF, separate the attributes based on their dependencies:

1. Student Table:

Student_ID Student_Name
101 Alice
102 Bob
103 Charlie

2. Course Table:

Course Instructor
Math Mr. X
English Ms. Y
Science Mr. Z
History Mr. A
Art Ms. B

3. Student_Course Table:

Student_ID Course
101 Math
101 English
102 Science
102 History
103 Math
103 Science
103 Art

Now, each table has no partial dependencies, and the database is in 2NF.

Third Normal Form (3NF)

A table is in 3NF if:

1. It is already in 2NF.
Faculty of computer science and digital innovation 3
2. There are no transitive dependencies (i.e., no non-key attribute depends on another non-
key attribute).

Example:

Consider the following Employee_Department table that is already in 2NF:

Employee_ID Employee_Name Dept_ID Dept_Name Dept_Location


201 John D001 HR New York
202 Sarah D001 HR New York
203 Mike D002 Finance Chicago

Here, Dept_Name and Dept_Location are dependent on Dept_ID, not on the primary key
(Employee_ID). This creates a transitive dependency: Employee_ID → Dept_ID →
Dept_Name/Dept_Location.

Solution (Convert to 3NF):

To remove transitive dependencies, split the table into two:

1. Employee Table:

Employee_ID Employee_Name Dept_ID


201 John D001
202 Sarah D001
203 Mike D002

2. Department Table:

Dept_ID Dept_Name Dept_Location


D001 HR New York
D002 Finance Chicago

Now, the database is in 3NF because there are no transitive dependencies; all non-key attributes
depend only on the primary key.

Summary of Normal Forms:

1. 1NF (First Normal Form): Eliminate repeating groups; ensure each column has atomic
values.

 Issue: Repeating groups or multiple values in one column.


 Solution: Break data into individual rows.
Faculty of computer science and digital innovation 4
2. 2NF (Second Normal Form): Eliminate partial dependencies (all non-key attributes
must depend on the entire primary key).

 Issue: Some non-key attributes depend only on part of a composite primary key.
 Solution: Separate tables based on full functional dependency.

3. 3NF (Third Normal Form): Eliminate transitive dependencies (non-key attributes must
depend only on the primary key).

 Issue: Non-key attributes depend on other non-key attributes.


 Solution: Separate tables based on transitive dependencies.

Faculty of computer science and digital innovation 5

You might also like