Normalization in SQL

  • When same set(group) of data has to be repeated into a table then we call it redundancy.
  • Redundancy – When a same data is stored multiple times unnecessarily in a database.
  • Problems caused by redundancy
    • Insertion Anomaly : When a certain data(attributes) cannot be inserted into Database, without the presence of the other data. Example in above table new branch cannot be created without a student
    • Deletion Anomaly : Suppose we want to delete a student with s_name as ‘F’, but the anomaly is that if we delete the student F then branch ‘ME’ i.e Mechanical will also be deleted along with it
    • Updation/Modification Anomaly : Suppose we want to change the HOD Name of ‘CS’ i.e Computer Science then we have to update the records at multiple places we call it Updation anomaly
    • Other problems include data inconsistency and the data size increases over time.

Concept of Normalisation : One table should contain only one idea(entity)

If above table was normalised, the table will look something like below.

Queries Used to create above tables :

create table STUDENT_INFO_TEMP0718
(
S_ID VARCHAR2(10),
S_NAME VARCHAR2(50),
S_AGE NUMBER(3),
BRANCH_CODE VARCHAR2(10),
BRANCH_NAME VARCHAR2(50),
HOD_NAME VARCHAR2(50)
);


--insert into STUDENT_INFO_TEMP0718 values ('1','A','18',101,'CS','XYZ');
--insert into STUDENT_INFO_TEMP0718 values ('2','B','19',101,'CS','XYZ');
--insert into STUDENT_INFO_TEMP0718 values ('3','C','18',101,'CS','XYZ');
--insert into STUDENT_INFO_TEMP0718 values ('4','D','21',102,'EC','PQR');
--insert into STUDENT_INFO_TEMP0718 values ('5','E','20',102,'EC','PQR');
--insert into STUDENT_INFO_TEMP0718 values ('6','F','19',103,'ME','KLM');


select * from STUDENT_INFO_TEMP0718;


create table STUDENT_INFO_TEMP0718_SPLIT
(
S_ID VARCHAR2(10),
S_NAME VARCHAR2(50),
S_AGE NUMBER(3),
BRANCH_CODE VARCHAR2(10)
);

--insert into STUDENT_INFO_TEMP0718_SPLIT values ('1','A','18',101);
--insert into STUDENT_INFO_TEMP0718_SPLIT values ('2','B','19',101);
--insert into STUDENT_INFO_TEMP0718_SPLIT values ('3','C','18',101);
--insert into STUDENT_INFO_TEMP0718_SPLIT values ('4','D','21',102);
--insert into STUDENT_INFO_TEMP0718_SPLIT values ('5','E','20',102);
--insert into STUDENT_INFO_TEMP0718_SPLIT values ('6','F','19',103);

create table BRANCH_INFO_TEMP0718_SPLIT
(
BRANCH_CODE VARCHAR2(10),
BRANCH_NAME VARCHAR2(50),
HOD_NAME VARCHAR2(50)
);

--insert into BRANCH_INFO_TEMP0718_SPLIT values (101,'CS','XYZ');
--insert into BRANCH_INFO_TEMP0718_SPLIT values (102,'EC','PQR');
--insert into BRANCH_INFO_TEMP0718_SPLIT values (103,'ME','KLM');


select * from STUDENT_INFO_TEMP0718_SPLIT;
select * from branch_info_temp0718_split;

Leave a Comment