Module 6

1. Module 6 Relational Database Design

2. Topics to be covered • Pitfalls in relational database design • Functional dependencies • Armstrong Axioms • Decomposition • Desirable properties of decomposition • Boyce-code normal form • 3rd and 4th normal form • Mention of other normal forms Module 6

3. Evaluating relation schemas • Two levels of relation schemas • The logical or conceptual view • How users interpret the relation schemas and the meaning of their attributes. • Implementation or storage view • How the tuples in the base relation are stored and updated. Module 6

4. Informal Design Guidelines for Relational Databases • Four informal measures of quality for relation schema design are: • Imparting clear semantics to attributes in Relations • Reducing the redundant values in tuples • Reducing the null values in tuples • Disallowing the possibility of generating spurious tuples. Module 6

5. 1.Semantics of the Relation Attributes • GUIDELINE 1: Informally, each tuple in a relation should represent one entity or relationship instance. (Applies to individual relations and their attributes). • Attributes of different entities (EMPLOYEEs, DEPARTMENTs, PROJECTs) should not be mixed in the same relation • Only foreign keys should be used to refer to other entities • Entity and relationship attributes should be kept apart as much as possible. • Bottom Line:Design a schema that can be explained easily relation by relation. The semantics of attributes should be easy to interpret. Module 6

6. A Simplified COMPANY relational database schema Module 6

7. Two relation schemas suffering from update anomalies EMP_DEPT ENAME SSN BDATE ADDRESS DNUMBER DNAME DMGRSSN EMP_PROJ SSN PNUMBER HOURS ENAME PNAME PLOCATION Module 6

8. Two relation schemas suffering from update anomalies • Although there is nothing wrong logically with these 2 relations, they are considered poor designs because they violate guideline 1 by mixing attributes from distinct real world entities. • EMP_DEPT mixes attributes of employee and department and EMP_PROJ mixes attributes of employees & projects and the WORKS_ON relationship • They may be used as views but they cause problems when used as base relations Module 6

9. 2.Redundant Information in Tuples and Update Anomalies • Goal of schema design is to minimize the storage space used by the base relations. • Information is stored redundantly • Wastes storage • Causes problems with update anomalies • Insertion anomalies • Deletion anomalies • Modification anomalies Module 6

10. Two relation schemas suffering from update anomalies EMP_DEPT ENAME SSN BDATE ADDRESS DNUMBER DNAME DMGRSSN EMP_PROJ SSN PNUMBER HOURS ENAME PNAME PLOCATION Module 6

11. EXAMPLE OF AN INSERT ANOMALY • Consider the relation: • EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours) • Insert Anomaly: • Cannot insert a project unless an employee is assigned to it. • Conversely • Cannot insert an employee unless an he/she is assigned to a project. Module 6

12. EXAMPLE OF AN DELETE ANOMALY • Consider the relation: • EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours) • Delete Anomaly: • When a project is deleted, it will result in deleting all the employees who work on that project. • Alternately, if an employee is the sole employee on a project, deleting that employee would result in deleting the corresponding project. Module 6

13. EXAMPLE OF AN UPDATE ANOMALY • Consider the relation: • EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours) • Update Anomaly: • Changing the name of project number P1 from “Billing” to “Customer-Accounting” may cause this update to be made for all 100 employees working on project P1. Module 6

14. Module 6

15. Guideline to Redundant Information in Tuples and Update Anomalies • GUIDELINE 2: • Design a schema that does not suffer from the insertion, deletion and update anomalies. • If there are any anomalies present, then note them so that applications can be made to take them into account. • In general, it is advisable to use anomaly free base relations and to specify views that include the joins for placing together the attributes frequently referenced in important queries. Module 6

16. Problems with Nulls • If many attributes are grouped together as a fat relation, it gives rise to many nulls in the tuples. • Waste storage • Problems in understanding the meaning of the attributes • Difficult while using Nulls in aggregate operators like count or sum Module 6

17. 3. Null Values in Tuples • Interpretations of nulls: • Attribute not applicable or invalid • Attribute value unknown (may exist) • Value known to exist, but unavailable • GUIDELINE 3: • Relations should be designed such that their tuples will have as few NULL values as possible • Attributes that are NULL frequently could be placed in separate relations (with the primary key) • Example:- • if only 10% of employees have individual offices, it is better not to include office_number as an attribute in the employee relation. • Better create a new relation emp_offices(essn, office_number) Module 6

18. Example of Spurious Tuples Module 6

19. Generation of spurious tuples • The two relations EMP_PROJ1 and EMP_LOCS as the base relations of EMP_PROJ, is not a good schema design. • Problem is if a Natural Join is performed on the above two relations it produces more tuples than original set of tuples in EMP_PROJ. • These additional tuples that were not in EMP_PROJ are called spurious tuples because they represent spurious or wrong information that is not valid. • This is because the PLOCATION attribute which is used for joining is neither a primary key, nor a foreign key in either EMP_LOCS AND EMP_PROJ1. Module 6

20. Example of Spurious Tuples contd Module 6

21. 4. Spurious Tuples • Bad designs for a relational database may result in erroneous results for certain JOIN operations • The "lossless join" property is used to guarantee meaningful results for join operations • GUIDELINE 4: • Design relation schemas so that they can be joined with equality conditions on attributes that are either primary keys or foreign keys in a way that guarantees that no spurious tuples are generated. Module 6

22. Spurious Tuples • There are two important properties of decompositions: • Non-additive or losslessness of the corresponding join • Preservation of the functional dependencies. • Note that: • Property (a) is extremely important and cannot be sacrificed. • Property (b) is less stringent and may be sacrificed. Module 6

23. Summary and Discussion of Design Guidelines Problems pointed out: • Anomalies cause redundant work to be done during • Insertion • Modification • Deletion • Waste of storage space due to nulls and difficulty of performing aggregation operations and joins due to null values • Generation of invalid and spurious data during joins on improperly related base relations. Module 6

24. Functional dependencies • Functional dependencies (FDs) • Is a constraint between two sets of attributes from the database. • Assumption • The entire database is a single universal relation schema R={A1,A2…An} • Where A1,A2 … are the attributes. Module 6

25. Definition • FDs are: • used to specify formal measures of the "goodness" of relational designs • keys that are used to define normal forms for relations • constraints that are derived from the meaning and interrelationships of the data attributes • A set of attributes X functionallydetermines a set of attributes Y if the value of X determines a unique value for Y Module 6

26. Functional Dependencies • A functional dependency, X -> Y holds if whenever two tuples have the same value for X, they must have the same value for Y • For any two tuples t1 and t2 in any relation instance r(R): If t1[X]=t2[X], then t1[Y]=t2[Y] • X -> Y in R specifies a constraint on all relation instances r(R) • This means that the values of the Y component of a tuple in r depend on, or are determined by, the values of the X component • The values of the X component functionally determines the values of Y component. • FDs are derived from the real-world constraints on the attributes • The main use of FD is to describe R by specifying constraints on its attributes that must hold at all times. Module 6

27. Lakes of the world Continent->Name Name ->Length Module 6

28. Graphical representation of Functional Dependencies Module 6

29. Examples of FD constraints • Social security number uniquely determines employee name • SSN -> ENAME • Project number uniquely determines project name and location • PNUMBER -> {PNAME, PLOCATION} • Employee ssn and project number uniquely determines the hours per week that the employee works on the project • {SSN, PNUMBER} -> HOURS Module 6

30. Examples of FD constraints • A FD is a property of the attributes in the schema R, not of a particular legal relation state r of R. • It must be defined explicitly by someone who knows the semantics of the attributes of R. • The constraint must hold on every relation instance r(R) • If K is a key of R, then K functionally determines all attributes in R • (since we never have two distinct tuples with t1[K]=t2[K]) Module 6

31. Satisfies algorithm • Why it is used? To determine whether a relation r satisfies or does not satisfy a given functional dependency A  B. • How it works? • Sort the tuples of the relation r on the A attributes so that tuples with equal values under A are next to each other • Check that tuples with equal values under attributes A also have equal values under B • If it meets the condition 2 then the output of the algorithm is true else it is false Module 6

32. Relation state of TEACH TEACHER -> COURSE • TEACH TEXT -> COURSE TEACHER COURSE TEXT Module 6

33. Drawbacks of Satifies algorithm • Using this algorithm is tedious and time consuming • So inference axioms are used. Module 6

34. Inference Rules for Functional Dependencies • F is the set of functional dependencies that are specified on relation schema R. • Schema designers specifies the most obvious FDs. • The other dependencies can be inferred or deduced from FDs in F. Module 6

35. Example of Closure • Department has one manager (DEPT_NO -> MGR_SSN) • Manager has a unique phone number (MGR_SSN->MGR_PHONE) then these two dependencies together imply that (DEPT_NO->MGR_PHONE) • This defines a concept called as closure that includes all possible dependencies that can be inferred from the given set F. • The set of all dependencies that include F as well as all dependencies that can be inferred from F is called the closure of F denoted by (F+) Module 6

36. Example F={SSN  {ENAME, BDATE, ADDRESS, DNUMBER}, DNUMBER  {DNAME, DMGRSSN}} • The inferred functional dependencies are • SSN  {DNAME, DMGRSSN} • SSN  SSN • DNUMBER  DNAME • To determine a systematic way to infer dependencies, a set of inference rules has to be discovered that can be used to infer new dependencies from a given set of dependencies. This is denoted by F|=X Y Module 6

37. Inference Rules for FDs • Given a set of FDs F, we can infer additional FDs that hold whenever the FDs in F hold • Armstrong's inference rules: • IR1. (Reflexive) If Y subset-of X, then X -> Y • IR2. (Augmentation) If X -> Y, then XZ -> YZ • (Notation: XZ stands for X U Z) • IR3. (Transitive) If X -> Y and Y -> Z, then X -> Z • IR1, IR2, IR3 form a sound and complete set of inference rules • By sound we mean,any dependency that we can infer from F by using IR1 through IR3 satisfies the dependencies in F(or) [if axioms are correctly applied they cannot derive false dependencies] • By complete we mean that by using IR1 through IR3 repeatedly to infer dependencies until no more dependencies can be inferred results in complete set of all possible dependencies that can be inferred from F Module 6

38. Inference Rules for FDs • Some additional inference rules that are useful: • Decomposition: If X -> YZ, then X -> Y and X -> Z • Union or additive: If X -> Y and X -> Z, then X -> YZ • Psuedotransitive: If X -> Y and WY -> Z, then WX -> Z • The last three inference rules, as well as any other inference rules, can be deduced from IR1, IR2, and IR3 (completeness property) Module 6

39. Examples • Given the set F={AB,CX, BXZ} derive ACZ using the inference axioms • Given F={AB, CD} with C subset of B show that F|=AD Module 6

40. Redundant functional dependencies • Given a set F of FDs, a FD AB of F is said to be redundant with respect to the FDs of F iff AB can be derived from the set of FDs F-{AB} • Redundant FDs are extra and unnecessary and can be safely removed from the set F. • Eliminating redundant FDs allows us to minimize the set of FDs. Module 6

41. Equivalence of Sets of Functional Dependencies For a given set F of FDs the set F+ may contain a large number of FDs. It is desirable to find sets that contain smaller number of FDs than F and still generate all the FDs of F+. Sets of FDs that satisfy this condition are said to be equivalent sets. • A set of FD F is said to cover another set of FDs E if every FD in E is also in F+. If every dependency in E can be inferred from F; alternatively E is covered by F • Two sets of FDs E and F are equivalent if E+ = F+. Hence, equivalence means that every FD in E can be inferred from F, and every FD in F can be inferred from E; E is equivalent to F if both the conditions E covers F and F covers E hold. Module 6

42. Minimal Functional Dependencies (minimal cover ) • is useful in eliminating unnecessary functional dependencies. • Also called as Irreducibe Set of F • F is transformed such that each FD in it that has more than one attribute in the RHS is reduced to a set of FDs that have only one attribute on the RHS. Module 6

43. Minimal cover • every RHS of each dependency is a single attribute; • for no X -> A in F is the set F - {X -> A} equivalent to F; • for no X -> A in F and proper subset Z of X is F - {X -> A} U {Z -> A} equivalent to F. no redundancies no dependencies may be replaced by a dependency that involves a subset of the left hand side. Module 6

44. Extraneous Attributes • Further reduction of the size of the FDs of F by removing either extraneous left attributes with respect to F or extraneous right attributes with respect to F. • F be a set of FDs over schema R and let A1A2B1B2. • A1 is extraneous iff • FΞF-{A1A2B1B2}U{A2B1B2} Module 6

45. CANONICAL COVER (FC) • Every FD of FC is simple. RHS has one attribute • FC is left-reduced • FC is nonredudant Module 6

46. Problem Given a set F of FDs find a cononical cover for F FC = {XZ, XYWP, XYZWQ, XZR} • FC= {XZ, XYW, XYP, XYZ, XYW, XYQ, XZR} • FC = {XZ, XYW, XYP, XYQ, XZR} Module 6

47. Normal Forms Based on Primary Keys • Normalization of Relations • Practical Use of Normal Forms • Definitions of Keys and Attributes participating in Keys • First Normal Form • Second Normal Form • Third Normal Form Module 6

48. Normalization of Relations • 2NF, 3NF, BCNF based on keys and FDs of a relation schema (relational design by analysis) • 4NF based on keys, multi-valued dependencies : MVDs; 5NF based on keys, join dependencies : JDs (relational design by synthesis) • Additional properties may be needed to ensure a good relational design: lossless join and dependency preservation Module 6

49. Normalization of Relations • Proposed by Codd • Normalization:analysing the given relation based on their FDs and primary keys to achieve the desirable properties of • Minimizing redundancies • Minimizing anomalies • Provides the database designer with • Formal framework for analyzing relation schemas based on keys and FD • Series of normal form tests Normal form refers to the highest normal form condition that it meets and indicates the degree to which it can be normalized Module 6

50. Normalization of Relations • Lossless join or nonadditive join property: it guarantees that the spurious tuple generation problem does not occur with respect to the relation schemas created after decomposition. • Dependency preservation property: it ensures that each functional dependency is represented in some individual relation resulting after decomposition. Module 6