Chapter14-FunctionalDependencies.pptx

To customer accounting may cause this update to be

Info icon This preview shows pages 10–19. Sign up to view the full content.

to “Customer-Accounting” may cause this update to be made for all 100 employees working on project P1 Functional Dependencies and Normalization 10
Image of page 10

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

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 Functional Dependencies and Normalization 11
Image of page 11
Example of a 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 Functional Dependencies and Normalization 12
Image of page 12

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Two Relations with Update Anomalies Functional Dependencies and Normalization 13
Image of page 13
Sample states for EMP_DEPT and EMP_PROJ Functional Dependencies and Normalization 14 Sample states for EMP_DEPT and EMP_PROJ resulting from applying NATURAL JOIN to the relations in Figure 14.2. These may be stored as base relations for performance reasons.
Image of page 14

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Generation of Spurious Tuples – avoid at any cost 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: The relations should be designed to satisfy the lossless join condition. No spurious tuples should be generated by doing a natural-join of any relations Functional Dependencies and Normalization 15
Image of page 15
Spurious Tuples There are two important properties of decompositions: a) Non-additive or losslessness of the corresponding join b) 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. (See Chapter 15) Functional Dependencies and Normalization 16
Image of page 16

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Functional Dependencies Functional dependencies (FDs) Are used to specify formal measures of the "goodness" of relational designs And keys are used to define normal forms for relations Are constraints that are derived from the meaning and interrelationships of the data attributes A set of attributes X functionally determines a set of attributes Y if the value of X determines a unique value for Y Functional Dependencies and Normalization 17
Image of page 17
Defining Functional Dependencies 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) Written as X Y; can be displayed graphically on a relation schema as in Figures. ( denoted by the arrow: ).
Image of page 18

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Image of page 19
This is the end of the preview. Sign up to access the rest of the document.
  • Fall '09
  • SUNANHAN
  • Database normalization, BCNF

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern