COMP1212 Anomalies and Dependencies

1. COMP1212Anomalies and Dependencies Dr. Mabruk Ali

2. 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.

3. Redundant Information in Tuples and Update Anomalies Mixing attributes of multiple entities may cause problems Information is stored redundantly wasting storage Problems with update anomalies Insertion anomalies Deletion anomalies Modification anomalies

4. Data redundancy Values stored repetitively in relations (esp. poorly designed relations) Potential for anomalous data to be stored This relation associates employees with projects. Assume no nulls are allowed. Slide 4

5. Update anomalies Each person’s salary is repeated for each project they are involved with. What does this imply when we need to increase someone’s salary? 37 35 37 35 Only one value updated: ANOMALY Both values updated: OK 50 48 Slide 5

6. If a project ends (i.e., is deleted), what happens to the data for employees on that project? Delete anomalies What happens to (Brown, 20)? ANOMALY Delete project Alpha Slide 6

7. Insert anomalies What happens when we hire a new person? (remember, no nulls allowed) Where do we store (Johnson, 36) until then? ANOMALY Johnson hasn’t yet been assigned to a project, but no nulls allowed Slide 7

8. The solution: Normalisation Breaking up the relation eliminates the worst of the redundancy Slide 8

9. Functional Dependencies(FD) An important concept associated with normalization. Functional dependency describes the relationship between attributes. For example, if A and B are attributes of 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. An alternative way to describe the relationship between attributes A and B is to say that “A functionally determines B”. A Called (the Determinant) B Called (the dependent)

10. Characteristics of FDs Determinants should have the minimal number of attributes necessary to maintain the functional dependency with the attribute(s) on the right hand-side. This requirement is called full functional dependency.

11. Identifying FDs Identifying all functional dependencies between a set of attributes is relatively simple if the meaning of each attribute and the relationships between the attributes are well understood. This information should be provided by the enterprise in the form of discussions with users and/or documentation such as the users’ requirements specification.

12. Identifying FDs (Cont) However, if the users are unavailable for consultation and/or the documentation is incomplete then depending on the database application it may be necessary for the database designer to use their common sense and/or experience to provide the missing information.

13. Examples of FD constraints (1) social security number determines employee name SSN -> ENAME project number determines project name and location PNUMBER -> {PNAME, PLOCATION} employee ssn and project number determines the hours per week that the employee works on the project {SSN, PNUMBER} -> HOURS

14. Types of functional dependency Full Partial Transitive

15. Full Functional Dependency Full functional dependency indicates that if A and B are attributes of a relation. B is fully functionally dependent on A, if B is functionally dependent on A, but not on any proper subset of A. A functional dependency A → B is a partially dependency if there is some attribute that can be removed from A and yet the dependency still holds. A  B == LHS  RHS

16. Example of Full FD Example: R(Year, Course_code, Course_coordinator) year + course_code  course_coordinator (i.e., course_coordinator determined by combination of both a particular year and a course_code) If we remove either Year or Course code from the left hand side (LHS) (the determinant), the dependency is no longer exist. Year Course_coordinator Course_code Slide 16

17. Partial functional dependency Subset of left hand side determines right hand side “extra” attributes on LHS are unnecessary Student Name Invoice Date R1(StudentId, StudentName,DateOfBirth) R2(InvoiceNumber, InvoiceDate, InvoiceTotal) Invoice Number Student ID Date of Birth Invoice Total Slide 17

18. Now Full functional dependency left hand side determines right hand side No “extra” attributes on LHS are unnecessary Date of Birth Invoice Total Invoice Number Student ID Slide 18

19. Transitive dependency Transitive dependency part number determines supplier number supplier number determines supplier name therefore, part number alone also determines supplier name Ideally should not exist within the same relation Part number Part number Supplier number Supplier name Supplier name Slide 19

20. Transitive Dependency It is important to recognize a transitive dependency because its existence in a relation can potentially cause update anomalies. Transitive dependency describes 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).

21. MVD & JD Normal Forms will be discussed next lecture. The fourth normal form makes use of a new kind of dependency, called a multivalued dependency (MVD); MVDs are a generalization of FDs. The fifth normal form makes use of an­other new kind of dependency, called a join dependency (JD); JDs are a generalization of MVDs, just as MVDs are a generalization of FDs.

22. The End Lecture 05 - ER to Relation Mapping