Normalization is one of the favorite topics of interviewee, here are the basics about what Normalization is all about.
What is Normalization?
A normalization is the process of organizing the columns, tables of a database to minimize the redundancy of data and ensure data dependencies make sense( means storing related data in a table).
It involves in dividing large tables into smaller tables and defining relationships between them.
What are different types of Normalization Levels or Normalization Forms?
The technique of normalization includes some rules, which when applied, changes the unnormalized data to a normalized-structured state. The process of nomalizing is implemented in 3 levels which as known as normal forms.
Inner and Outer Joins!..
First Normal Form:
1). Identify the primary key.
2). Remove all duplicate columns from the same table.
3). Create separate tables for each group of related data and identify each row with a unique column or set of columns (Primary Key).
4). Add a primary key column to the new table. It should be the same column as the foreign key in the base table.
Second Normal Form:
First it should meet the requirement of first normal form.
All the non-primary key column should be dependent on the whole primary key. When a non primary key column is partially dependent on primary key then it is known as partial dependency. In 2nd normal form this partial dependency is removed.
Frequently asked SQL queries in interview..
How to remove Partial Dependency?
a). First you need to identify the columns which are partially dependent on primary key.
b). Remove those column from the base table and create another table, in which all the non-primary column are dependent on primary key of newly created table.
Third Normal Form:
First it should meet the requirements of second normal form. in 3rd NF all the non-primary key columns in the tables are directly dependent on the primary key and are not dependent on any other non-primary key.
1). First step is to identify the non-primary key column that depend on other non-primary key columns.
2). Then you need to remove these columns from the base table.
3). Create another table with this removed columns and include the non-primary key column that they are dependent on making it the primary key.
4). Create a foreign key in the base table and link it to primary key of newly created table.
What is Transitive Dependency?
It's a situation where a non-key column of a table is dependent on another non-key column.
What is De-normalization?
The de-normalization is the process of optimizing the read performance of a database by adding redundant data or by grouping data.
Let us take an example:
-K Himaanshu Shukla...
Copyright © 2014 - ScrutinyByKHimaanshu
What is Normalization?
A normalization is the process of organizing the columns, tables of a database to minimize the redundancy of data and ensure data dependencies make sense( means storing related data in a table).
It involves in dividing large tables into smaller tables and defining relationships between them.
What are different types of Normalization Levels or Normalization Forms?
The technique of normalization includes some rules, which when applied, changes the unnormalized data to a normalized-structured state. The process of nomalizing is implemented in 3 levels which as known as normal forms.
Inner and Outer Joins!..
First Normal Form:
1). Identify the primary key.
2). Remove all duplicate columns from the same table.
3). Create separate tables for each group of related data and identify each row with a unique column or set of columns (Primary Key).
4). Add a primary key column to the new table. It should be the same column as the foreign key in the base table.
Second Normal Form:
First it should meet the requirement of first normal form.
All the non-primary key column should be dependent on the whole primary key. When a non primary key column is partially dependent on primary key then it is known as partial dependency. In 2nd normal form this partial dependency is removed.
Frequently asked SQL queries in interview..
How to remove Partial Dependency?
a). First you need to identify the columns which are partially dependent on primary key.
b). Remove those column from the base table and create another table, in which all the non-primary column are dependent on primary key of newly created table.
Third Normal Form:
First it should meet the requirements of second normal form. in 3rd NF all the non-primary key columns in the tables are directly dependent on the primary key and are not dependent on any other non-primary key.
1). First step is to identify the non-primary key column that depend on other non-primary key columns.
2). Then you need to remove these columns from the base table.
3). Create another table with this removed columns and include the non-primary key column that they are dependent on making it the primary key.
4). Create a foreign key in the base table and link it to primary key of newly created table.
What is Transitive Dependency?
It's a situation where a non-key column of a table is dependent on another non-key column.
What is De-normalization?
The de-normalization is the process of optimizing the read performance of a database by adding redundant data or by grouping data.
Let us take an example:
Student Details
|
Course Details
|
Result Details
|
||||||
Roll No
|
Student Name
|
DOB
|
Course ID
|
Course Name
|
Duration in Month
|
Date Of Exams
|
Marks Obtained
|
Grade
|
1001
|
Ram
|
11/9/1986
|
M4
|
Basic Maths
|
7
|
11/11/2004
|
89
|
A
|
1002
|
Shyam
|
12/8/1987
|
M4
|
Basic Maths
|
7
|
11/12/2004
|
78
|
B
|
1001
|
Ram
|
23/06/1987
|
H6
|
4
|
11/13/2004
|
87
|
A
|
|
1003
|
Sita
|
16/07/1985
|
C3
|
Basic Chemistry
|
11
|
11/14/2004
|
90
|
A
|
1004
|
Gita
|
24/09/1988
|
B3
|
8
|
11/15/2004
|
78
|
B
|
|
1002
|
Shyam
|
23/06/1988
|
P3
|
Basic Physics
|
13
|
11/16/2004
|
67
|
C
|
1005
|
Sunita
|
14/09/1987
|
P3
|
Basic Physics
|
13
|
11/17/2004
|
78
|
B
|
1003
|
Sita
|
23/10/1987
|
B4
|
5
|
11/18/2004
|
67
|
C
|
|
1005
|
Sunita
|
13/03/1990
|
H6
|
4
|
11/19/2004
|
56
|
D
|
|
1004
|
Gita
|
21/08/1987
|
M4
|
Basic Maths
|
7
|
11/20/2004
|
78
|
E
|
The above table contains the student and result details. Above table has below issues:
1). Firstly, we cannot insert prospective course which does not have any registered student or we cannot insert student details that is yet to register for any course.
2). Secondly, if we want to update the course M4’s name we need to do this operation three times. Similarly we may have to update student 1003’s name twice if it changes.
3). Also, if we want to delete a course M4 , in addition to M4 occurs details , other critical details of student also will be deleted. This kind of deletion is harmful to business. Moreover, M4 appears thrice in above table and needs to be deleted thrice.
4). Course M4’s data is stored thrice and student 1002’s data stored twice .This redundancy will increase as the number of course offerings increases.
To remove above issues, normalization is performed on above table.
1). Firstly, we cannot insert prospective course which does not have any registered student or we cannot insert student details that is yet to register for any course.
2). Secondly, if we want to update the course M4’s name we need to do this operation three times. Similarly we may have to update student 1003’s name twice if it changes.
3). Also, if we want to delete a course M4 , in addition to M4 occurs details , other critical details of student also will be deleted. This kind of deletion is harmful to business. Moreover, M4 appears thrice in above table and needs to be deleted thrice.
4). Course M4’s data is stored thrice and student 1002’s data stored twice .This redundancy will increase as the number of course offerings increases.
To remove above issues, normalization is performed on above table.
-K Himaanshu Shukla...
Copyright © 2014 - ScrutinyByKHimaanshu
No comments:
Post a Comment