FOR FREE MATERIALS

Schema Refinement

Prerequisite: Before seeing this chapter follow the previous chapter Functional Dependencies Concept

Schema Refinement: 

Normalization is used to eliminate/reduce redundancy. Redundancy means duplicate copies of the same data.

Three types of redundancy

1. File Level: File Level redundancy means where store duplicate copy of the same file like a.txt store in c:/ and d:/ also

2. Entire Record Level: If while the entity is redundant, follow the example

3. Few attributes have redundancy

Important: 

This redundancy comes from the Functional Dependency Cid → Cname whenever Cid is the same Cname must be the same which creates redundancy.

Redundancy exists and it is coming from Functional Dependency. Sid is the primary key here.

There are some problem can occur because of Redundancy:

1. Update anomalies

2. Insertion anomalies

3. Deletion anomalies

Update Anomalies: If we want to update the salary of F1 is 50k to 55k, then we have to update multiple times.

Insertion Anomalies: If we want to insert a new faulty F3 but problem is initially he/she has no student. So, in the table, we can't enter Sid and Sname, and because Sid is the primary key we have to insert it. So, in this, we can do another thing we insert some dummy values at Sid and Sname. Let Sd and α. But the problem occurs when we want to count the number of students then it shows one extra student who is physically not present. These are called insertion anomalies.

Deletion Anomalies: If we want to delete Sy student, then the total information will be going to delete. Unnecessary faculty, course information is deleted at the same time. The entire problem coming from redundancy.

To reduce the redundancy and solve the problem of insertion, update, delete. We decompose the table into two tables.

In these two tables after decomposition update, insertion and deletion problems are resolved. We can check it.

Properties of Decomposition:

1. Lossless Join Decomposition: It means after decomposition if we join two table (S1, S2) we will get exactly the same relation student.

Why join this table after decomposition because when we want to know some query like find name of the student where faulty guidance is F1. 

In this case from only S1 table it is not possible or individual by S2 table it is also not possible. But from the student table we can find the query, this reason we join two tables for some time.

2. Dependency Preserving Property: It means after decomposition no FDs must be lost. Functional Dependency which is present at the student table must keep all FDs at S1 and S2, this is called Dependency Preserving.