Introduction to Schema Refinement

1. Introduction to Schema Refinement

2. Introduction to Schema Refinement • Different problems may arise when converting a relation into standard form • They are • Data redundancy • Update Anomalies • Deletion Anomalies • Insertion Anomalies

3. Introduction to Schema Refinement • Data Redundancy • Storing the information repeatedly, that is, in more than one place within a database, can lead to several problems • Redundancy leads to inconsistency • Inconsistency generate problem in insertion, deletion and updating

4. Introduction to Schema Refinement • Problems Caused by Redundancy • Update Anomalies: If one copy of such repeated data is updated, an inconsistency is created unless all copies similarly updated. • Insertion Anomalies: It is not be possible to store certain information unless some other, unrelated, information is stored as well. • Deletion Anomalies: It may not be possible to delete certain information without losing other, unrelated, information as well.

5. Introduction to Schema Refinement • Data Redundancy • In the student relation there are information is repeated several times • Update Anomalies: • If we change the name ‘Ravi’, it affects all 2 rows having sname information too • Deletion Anomalies: • Deleting row 2 result in loss of AAA college from the whole relation • Insertion Anomalies: • Cannot add a row which does not have value

6. Introduction to Schema Refinement Null Values Null value leads to wastage of memory space Null value have multiple interpretations, such as • The attribute does not apply to this tuple. • The attribute value for this tuple is unknown. • The value is known but absent; that is, it has not been recorded yet.

7. Introduction to Schema Refinement • The Process of Normalisation • Normalisation is a data analysis technique to design a database system. • It allows the database designer to understand the current data structures in an organisation. • Furthermore, it aids any future changes and enhancements to the system. • Normalisation is a technique for producing relational schema with the following properties: • No Information Redundancy • No Update Anomalies

8. Functional dependency • It play a main role in designing good database design from bad database design • A functional dependency (FD) is a constraint between two sets of attributes in a relation • Describes the relationship between attributes in a relation. • If A and B are attributes of a relation R, • B is functionally dependent on A • (denoted. A → B), if each value of A in R is associated with exactly one value of B in R.

9. Functional dependency • A → B • Determinant: attribute or set of attributes on the left hand side of the arrow. • In the above example A is determinant • Determinant may be attribute or group of attribute

10. Functional dependency • From the relation Customer • Cid Cname Because Cid is the primary key of the table It is always unique So Cid uniquely determines the customer name even in the case of duplicate Cname So Cname is functionally dependent to Cid Cnamecid It is not always true Because name of the customer may be same for different Cid So Cnamenot uniquely determines the customer Cid age true Cid salary true AgeCid false

11. Functional dependency • Unnormalized form (UNF): A table that contains one or more repeating groups. • Repeating group: an attribute or group of attributes within a table that occurs with multiple values in a single row • An unnormalized relation contains non atomic values • Example • The row corresponding to Jeet • Have more than one phone no • So this table is unnormalized • relation

12. Functional dependency • First normal form (1NF): A relation in which the each row and column contains one and only one value. • Is does not contain multivalued attribute • Every attribute value is atomic • Ie all cells are single values • A relation is in 1NF if and only if all underlying domains contain atomic values only • Or • One value is associated with each attribute

13. Functional dependency • Converting UNF to 1NF • Remove repeating groups(multivalue) • Entering appropriate data in the empty columns of rows. • For each repeating field value, create a new tuple

14. Functional dependency • Types of FD • Full FD • Partial Dependency • Transitive Dependency • Trival and Non-Trival Dependencies

15. Functional dependency • Types of FD • Full FD • For a relation schema R and FD • X  Y, Y is fully functional dependent on X if there is noZ, where Z is the proper subset of A, such that ZY • Or • XY is a full FD if the removal of any attribute A from X removes the dependency

16. Functional dependency • Types of FD • Full FD • An attribute is fully functionally dependent on a set of attributes X if it is • Functionally dependent on X, and • Not functionally dependent on any proper subset of X.

17. Functional dependency • Types of FD • Partial Dependency • A FD XY is partial dependency if some attribute A can be removed from X and the dependency sill hold for some attribute, then that dependency is called partial dependency • Or • if there is some attribute that can be removed from A and the dependency still holds. • Example • {Cid, Phone} Cname • Is partial because • Cid  Cname • Is full FD

18. Functional dependency • Types of FD • Transitive dependency: • A condition where A, B and C are attributes of a relation such that • if A → B and B → C, then C is transitively dependent on A via B • (provided that A is not functionally dependent on B or C). • Is • XY • YZ • Then • XZ

19. Functional dependency • Types of FD • Trival & Non Trival Dependency • Some FD are said to be trival, because they are satisfied by all relations • Example • A is satisfied by all relations involving attribute A • Similarly ABA is satisfied by all relations involving attribute A • FD is trival if right hand side is a subset of the left hand side • Non-trival dependency are one that is not trival • XY is non trival if and only if Y X • Example • Car(carno,carname,color,weight) FD of the relation car is • Carnocarname • Carnocolor