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
  1. 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.
    updation anamoly - Normalisation

  2. 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.
      Insertion Anamoly - Normalisation
      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.
      Deletion Anamoly - Normalisation or Database Design
      Deleting student S3 will permanently delete the course B.

Solutions To Anomalies : Decomposition of Tables – Schema Refinement


decomposition of tables - Normalisation

There are some Anomalies in this again –

Anamoly again - Normalisation

What is the Solution ??
Solution :

Anamolies Solution 2 - Normalisation


Previous Home Next
Questions on Minimal Cover Desirable Properties
of Decomposition

     

Incoming search terms:

  • schema refinement in dbms
  • schema refinement
  • what is schema refinement
  • schema refinement in database design
  • introduction to schema refinement
  • what is schema refinement in dbms
  • introduction to schema refinement in dbms
  • schema refinement in data base design
  • in dbms schema refinement
  • explain schema refinement in database design

This article has 3 comments

Leave a Reply