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 –
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.
- Insertion Anomaly :
Solutions To Anomalies : Decomposition of Tables – Schema Refinement
What is the Solution ??
|Questions on Minimal Cover||Desirable Properties
Incoming search terms:
- schema refinement
- schema refinement in dbms
- schema refinement in database design
- what is schema refinement
- introduction to schema refinement
- what is schema refinement in dbms
- introduction to schema refinement in dbms
- what do you mean by schema refinement
- schema refinement and normal forms
- schema refinement in dbms tutorial point