17.INTRODUCTION TO SCHEMA REFINEMENT.pptx

dbms schema

1. INTRODUCTION TO SCHEMA REFINEMENT

2. SCHEMA REFINEMENT Normalization 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.

3. SCHEMA REFINEMENT Identifying and clearing the future problems in the database is called schema refinement. In this refinement main problem is data redundancy. It is avoided by normalization technique. The Basic Goal of Normalization is used to eliminate redundancy . Redundancy refers to repetition of same data or duplicate copies of same data stored in different locations.

4. Problems Caused by redundancy Storing the same information redundantly, can lead to several problems 1. Redundant Storage: Some information is stored repeatedly. 2. Update Anomalies: If one copy of such repeated data is updated, an inconsistency is created unless all copies are similarly updated. 3. Insertion Anomalies: It may not be possible to store certain information unless some other, unrelated, information is stored as well. 4.Deletion Anomalies: It may not be possible to delete certain information without losing some other, unrelated, information as well.

5. Student details:

6. Continue… As it can be observed that values of attribute college name, college rank, course is being repeated which can lead to problems. Problems caused due to redundancy are: Insertion anomaly, Deletion anomaly, and Updation anomaly

7. 1. Insertion Anomaly –   If a student detail has to be inserted whose course is not being decided yet then insertion will not be possible till the time course is decided for student. This problem happens when the insertion of a data record is not possible without adding some additional unrelated data to the record. 

8. 2. Deletion Anomaly –   If the details of students in this table are deleted then the details of college will also get deleted which should not occur by common sense.  This anomaly happens when deletion of a data record results in losing some unrelated information that was stored as part of the record that was deleted from a table.   It is not possible to delete some information without losing some other information in the table as well.

9. 3. Updation Anomaly –   Suppose if the rank of the college changes then changes will have to be all over the database which will be time-consuming and computationally costly.  If updation do not occur at all places then database will be in inconsistent state. 

10. Decompositions : Decomposition means replacing a relation with a collection of smaller relations. or The process of breaking down the relation into smaller relations is called Decomposition. Decomposition is the process of breaking down in parts or elements. It replaces a relation with a collection of smaller relations. It breaks the table into multiple tables in a database. It should always be lossless, because it confirms that the information in the original relation can be accurately reconstructed based on the decomposed relations. If there is no proper decomposition of the relation, then it may lead to problems like loss of information.