miniworld

1. miniworld Requirements & collection analysis refinement Database Requirements Conceptual Design Conceptual Schema ( ER diagram ) DBMS independent Data Model Mapping DBMS specific Conceptual Schema ( Relations ) • primary key constraint • foreign key constraint

2. Conceptual design Schemas ICs Schema Refinement and Normal Forms • Conceptual database design gives us a set of relation schemas and integrity constraints • Given a design, how do we know it is good or not? • A design can be evaluated from various perspectives, our focus is on data redundancy

3. The Evils of Redundancy • Redundancyis at the root of several problems associated with relational schemas: • redundant storage • Insertion/update/deletion anomalies

4. Example • Schema • Hourly_Emps (ssn, name, lot, rating, hrly_wages, hrs_worked) • Constraints: • ssnis the primary key • If two tuples have the same value onrating, they have the same value onhrly_wages

5. Solution: Decomposition • If we break Hourly_Emps into Hourly_Emps2 and Wages, then we don’t have updates, insertion, deletion anomalies. Wages Hourly_Emps2

6. Decomposition Concerns • Should a relation be decomposed? • If a relation is not in certain form, some problems (e.g., redundancy) will arise, are these problems tolerable? • Aforementioned anomalies • Potential performance loss: Queries over the original relation may required to join the decomposed relations • How to decompose a relation? Two properties must be preserved: • lossless-join: the data in the original relation can be recovered from the smaller relations • dependency-preservation: all constraints on the original relation must still hold by enforcing some constraints on each of the small relations

7. Functional Dependencies (FDs) • In a relation schema R, a set of attributes X functionally determines a set of attributes Y if and only if whenever two tuples of R agree on X value, they must necessarily agree on the Y value. • XY where r(R) is an instance of R, • XY: Y is functionally dependent on X, or • X uniquely determines Y or • X functionally determines Y, or • X determines Y

8. Does this data set violate X->Y? Does this data set violate Z->Y? Does this data set violate X->Y? Does this data set violate XY->Z? Does this data set violate Z->X?

9. An FD is a statement about all allowable relations. • Must be identified based on semantics of application. • Given some allowable instance r1 of R, we can check if it violates some FD f, but we cannot tell if f holds over R! • A primary key constraint is a special case of an FD • The attributes in the key play the role of X, and the set of all attributes in the relation plays the role of Y

10. Example 1 • Hourly_Emps (ssn, name, lot, rating, hrly_wages, hrs_worked) • Notation: We will denote this relation schema by listing the attributes: SNLRWH • This is really the set of attributes {S,N,L,R,W,H}. • Sometimes, we will refer to all attributes of a relation by using the relation name. (e.g., Hourly_Emps for SNLRWH) • Some FDs on Hourly_Emps: • ssn is the key: SSNLRWH (or {S}{S,N,L,R,W,H}) • rating determines hrly_wages : RW

11. since name dname ssn lot Employees Works_for Example 2 did budget Departments Additional Constraints: Employees are assigned parking lots based on their department. All employees in the same department is given the same lot. FD: did->lot Works_for(ssn,name,did,since) Department (did,dname,budget,lot);

12. F {SSN}->{DNAME,DMGRSSN} F {SSN}->{SSN} F {DNUMBER}->{DNAME} Dependency Reasoning A set of dependencies may imply some additional dependencies. EMP_DEPT(ENAME,SSN,BDATE,ADDRESS,DNUMBER,DNAME,DMGRSSN) • F={SSN->{ENAME,BDATE,ADDRESS,DNUMBER}, • DNUMBER->{DNAME,DMGRSSN} } F infers the following additional functional dependencies:

13. Dependency Reasoning A set of dependencies may imply some additional dependencies. Some important questions Given a set of attributes X, what attributes can be determined by X Given an FD set, what other dependencies are implied Given an FD set F, what is the minimum set of dependencies that is equivalent to F

14. Armstrong’s Axioms where X, Y, Z are sets of attributes: • Reflexivity:If X Y, then XY. • Augmentation:If XY, then XZ YZ for any Z. • Transitivity:If X Y and YZ, then XZ. Armstrong’s Axioms

15. PROOFS • Reflexive rule: If X Y, then XY. Let {t1,t2} r(R) such that t1[X]=t2[X] Since Y X, t1[X]=t2[X] t1[Y]=t2[Y] XY.

16. PROOFS (Cont’d) • Transitive rule: If XY and YZ, then XZ. Let XY and (1) YZ (2) such that t1[X]=t2[X], (3) we have: (1) t1[Y]=t2[Y] (4) (2)&(4) t1[Z]=t2[Z] (5) (3)&(5) XZ

17. PROOFS (Cont’d) • Augmentation rule: If XY, then XZYZ. Assume that the Augmentation rule is not true. t1[X] = t2[X] (1) t1[Y] = t2[Y] (2) t1[XZ] = t2[XZ] (3) t1[YZ] != t2[YZ] (4) (1)&(3) t1[Z]=t2[Z] (5) (2)&(5) t1[YZ]=t2[YZ] (6) (6) Contradicts (4)

18. Additional Inference Rules for Functional Dependencies • Union: • If X Y and X  Z, then X  YZ. • Decomposition: • If XYZ, then XY and XZ. • Pseudotransitive Rule: • If XY and WYZ then WXZ.

19. PROOFS (Cont’d) • Union rule: If XY and XZ, then XYZ. Given XY and (1) XZ. (2) Applying Augmentation rule on (1), we have XXXY XXY. (3) Applying Augmentation rule on (2), we have XYZY XYYZ . (4) Applying Transitive rule on (3) and (4), we have XYZ.

20. PROOFS (Cont’d) • Decomposition rule: If XYZ then XY and XZ. Given XYZ. (1) Since Y YZ, reflexive rule gives YZY. (2) Applying Transitive rule on (1) and (2), we have XY. XZ is derived in a similar way.

21. PROOFS (Cont’d) • Pseudotransitive rule: If XY and WYZ, then WXZ. Given XY (1) and WYZ. (2) Applying Augmentation rule on (1), we have WXWY. (3) Applying Transitive rule on (3)&(2), we have WXZ.

22. Exercise • Prove or disprove the following inference rules • {WY,XZ} |= {WXY} • {XY,XW,WYZ} |= {XZ} • {XY} |= {XYZ} • {XY, Z Y} |= {XZY} • Prove using inference rules • Disprove by showing a counter example