Normalisation or Schema Refinement or Database design

Normalisation or Schema Refinement or Database design Normalisation or Schema Refinement is a technique of organizing the data in the database. It is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anomalies. The Schema Refinement refers to refine the schema by using some technique. The best technique of schema refinement is decomposition.

The Basic Goal of Normalisation is used to eliminate redundancy.

Redundancy refers to repetition of same data or duplicate copies of same data stored in different locations.

Normalization is used for mainly two purpose :

Eliminating redundant(useless) data.
Ensuring data dependencies make sense i.e data is logically stored.

Anomalies or Problems Facing without Normalisation :

Anomalies refers to the problems occurred after poorly planned and unnormalised databases where all the data is stored in one table which is sometimes called a flat file database. Let us consider such type of schema –

SID	Sname	CID	Cname	FEE
S1	A	C1	C	5k
S2	A	C1	C	5k
S1	A	C2	C	10k
S3	B	C2	C	10k
S3	B	C2	JAVA	15k

Primary Key(SID,CID) Here all the data is stored in a single table which causes redundancy of data or say anomalies as SID and Sname are repeated once for same CID . Let us discuss anomalies one bye one.

Types of Anomalies : (Problems because of Redundancy)

There are three types of Anomalies produced in the database because of redundancy –

Updation/Modification Anomaly
Insertion Anomaly
Deletion Anomaly

Problem in updation / updation anomaly – If there is updation in the fee from 5000 to 7000, then we have to update FEE column in all the rows, else data will become inconsistent.
Insertion Anomaly and Deleteion Anomaly- These anamolies exist only due to redundancy, otherwise they do not exist.
- Insertion Anomaly – New course is introduced C4, But no student is there who is having C4 subject. Because of insertion of some data, It is forced to insert some other dummy data.
```
Problem/Disadvantage to Insert Dummy Data - It results inconsistency. how?
Solution)Suppose if we want to know the number of students, then answer will be, 4 (S1,S2,S3, xx)
```
```
Why we eliminate redundancy or what is the use of eliminating redundancy ?
Solution)It is not actually the storage problem. The problem is anomalies as shown above as it gives inconsistent answers/ wrong answers.
```
- Deletion Anomaly – Deletion of S3 student cause the deletion of course. Because of deletion of some data forced to delete some other useful data. Deleting student S3 will permanently delete the course B.