What is Normalization?

Normalization is the process of organizing the data in the database. It simply means there should be no redundancy . Redundancy means duplicate data , so avoid redundancy we should use normalization.

• If you are managing the Duplicate Data again and again it will consume more memory .

• To avoid anomalies like Insertion , Deletion & Update . Anomalies means problems you will understand just keep up reading the blog .

Normalization simply means to split your data into different tables. If you have a big table and you are facing problem in Insertion , Deletion & Update then you can split your data in small tables.

To understand the normalization we all must have to understand what are keys?

What is a Candidate Key?

Candidate key is an attribute that can uniquely identify a Tuple . In layman's language except primary key , the remaining attributes are considered as a candidate key . The keys are strong as primary key and by "attributes" I mean columns in the table.

What is Primary Key ?

The key which are unique or in other words we can say that candidate key . As you all know that data is stored in SQL in form of tables basically in form rows and columns. Let me elaborate a little primary key a column which contains unique data . For example roll no. of students - 33,54 76 ,77,21,66 as you can see that the roll numbers are unique and are not repeated . So this kind of data contained by primary key but their can be multiple keys in a table which can be unique like email_id , driving_license etc but it depends upon us which candidate key we want to make as a primary key their can be multiple candidate key in a table but among them we can make one as a primary key.

Example of Primary Key 👇

Let's understand with an example let's assume their is an election in your area and there are 10 person who are eligible and they have filed their nomination it means they are eligible to be our leader . Amongst them 1 person become leader so now that person is a primary key and rest 9 are candidate key. Now , suppose in a table you have columns like bank account no. , house number , city , phone number , gender among them you have choice which column you want to make primary key . Neither city nor gender can be primary key because that data can't unique. But still you can make primary key among bank account no. , house number , phone number because no person can have same bank account no. , house number , phone number but gender can be same of two persons and two or more people can belong to same city . If you choose phone number as a primary key then rest all columns which were eligible to be primary key will be said to be candidate keys.

What is a Super Key?

Super key is an attribute set that can uniquely identify a tuple. A Super Key is a superset of a candidate key. Super key is candidate key + attribute .

In the EMPLOYEE_TABLE (EMPLOYEE_ID , EMPLOYEE_NAME) the name of 2 employees can be same , but their EMPLOYEE_ID can't be the same . Hence , this combination can also be called as Super-key . As Employee_ID is a candidate key , If I merge Employee_Name , which is a regular attribute in this table then that key will be known as Super Key. 👇

A collection of keys which includes a single candidate key and a attribute is said to be Super Key. This collection can include multiple attribute but there must be a single candidate key.

What is Foreign key ?

Foreign keys are the column of the table used to point to the primary key of another table. Sounds confusing right , let's understand with an example If you visit other country in different continent you will be treated as foreigner. Likewise If a primary key of table_1 is used in table_2 it will be called as Foreign Key.

What is a Composite Key?

Whenever a primary key consist of more than one attribute , it is known as composite key. A combination of 2 columns , in which one is primary key and other is any attribute that combination is known as composite key.

Example of Composite Key 👇

Here is a student table 👇

Now in this table a student has opted a single or a multiple subjects . StudentId is primary key here in this table but **what if I want to see the marks of "Saghir"??**👇

I can't see marks because database will get confuse . So its a problem . Now here comes the use of composite key . Now I will merge an attribute Course with StudentId then they both will act as a primary key. 👇

Now If I want to fetch the marks of 201 I can fetch easily because now 👆 I have a composite key which is combination of two keys StudentId which is the primary key & Course.

What is Functional Dependency?

Let's understand with an example. If I want to fetch name from a table . I can fetch using roll no. because it is a primary key . ight. So , I can call it like that name is functionally dependent on roll no. then roll no. is said to be determinant and Name is said to be Dependent.

The attribute which is primary key is known as key attribute and rest of them are known as non-key attribute.

All non-prime attributes are dependent on prime attributes.

What are Anomalies in Database?

Data Anomalies are inconsistencies in the data stored in a database as a result of an operation such as Insertion , Deletion & Update .

Anomalies in Database

Insertion Anomaly

An Insertion anomaly occurs when we are not able to insert certain attribute in the database without the presence of other attribute.

Let's understand with an example 👇

Suppose I am Professor a Subject Matter Expert in 'Taxation' which is the subject of commerce. Today I joined a new university. So today is an introduction day . As 'Taxation' is a part of both commerce and management . Department is not allotted to me till now it will be allotting to me after 2-3 days . But my data must be their in the table from the first day when I joined the university . Now , the problem is that faculty wants to insert my details into the table but can't insert because department is not allotted till now. This is known as Insertion Anomaly

Deletion Anomaly

Deletion Anomaly occurs where deletion of some data is deleted because of deletion of some other data.

Let's understand with an example 👇

According to this 👆 table If I runs a command to delete Anushka from Prof_Name column at the same time Sec D & Sec E both will be deleted along with 'HR' which is in the Dept. column but I don't want to delete the data from 'Dept.' & 'Course Group' . Now , this a problem as it is a forceful loss of data this is known as Deletion Anomaly.

Update Anomaly

This occurs in the case of data redundancy and partial update. In other words a correct update of database needs other actions such as addition , deletion or both.

Let's understand with an example 👇

If I want to update data from 'HR' to 'IT' in the 'Dept.' column of the last row 👇 the problem is that the second last row's data will automatically updated that's I don't want I just want to update data of the last row. So this Deletion Anomaly.

<------------END-------------->