1 / 23

Comprehensive Review of Relational Algebra Operations and Functional Dependencies in Database Systems

This document provides a detailed examination of fundamental relational algebra operations and functional dependencies in database systems. Key operations, including selection, projection, and set differences, are discussed in relation to their application in manipulating relations. Additionally, functional dependencies (FDs) are defined with examples illustrating candidate keys and the implications of dependencies on data integrity. Common problems like update, insertion, and deletion anomalies are explored, alongside the principles of decomposition and minimal covers to ensure lossless joins and the preservation of dependencies.

kane-dale
Télécharger la présentation

Comprehensive Review of Relational Algebra Operations and Functional Dependencies in Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Review

  2. Exam • Su 3:30PM - 6:30PM 2010/12/12 • Room C9000

  3. Algebra

  4. Relational Algebra Operations • Basic operations • Selection ( ) Selects a subset of rows from relation. • Projection( ) Deletes unwanted columns from relation. • Cartesian product( ) Combinetwo relations. • Set-difference ( ) Tuples in relation 1, but not in relation 2. • Union( ) Tuples in relation 1 or in relation 2.

  5. Functional Dependency

  6. Functional Dependencies (FDs) • A functional dependency XY holds over relation R if, for every allowable instance r of R: • given two tuples in r, if the X values agree, then the Y values must also agree. (X and Y are sets of attributes.) • 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! • K is a candidate key for R means that KR • However, KR does not require K to be minimal!

  7. Wages Example (Contd.) • Problems due to R W : • Update anomaly: Can we change W in just the 1st tuple of SNLRWH? • Insertion anomaly: What if we want to insert an employee and don’t know the hourly wage for his rating? • Deletion anomaly: If we delete all employees with rating 5, we lose the information about the wage for rating 5! Hourly_Emps2

  8. Reasoning About FDs (Contd.) • Couple of additional rules (that follow from AA): • Union: If X  Y and X  Z, then X  YZ • Decomposition: If X  YZ, then X  Y and X  Z • Example: Contracts(cid,sid,jid,did,pid,qty,value), and: • C is the key: C  CSJDPQV • Project purchases a part using single contract: JPC • Dept purchases at most a part from a supplier:SD P • JP  C, C  CSJDPQV imply JP  CSJDPQV • SD  P implies SDJ  JP • SDJ  JP, JP  CSJDPQV imply SDJ  CSJDPQV

  9. Suppose that relation R contains attributes A1 ... An. A decompositionof R consists of replacing R by two or more relations such that: • Each new relation scheme contains a subset of the attributes of R (and no attributes that do not appear in R), and • Every attribute of R appears as an attribute of one of the new relations. • e.g., A  B, ABCD  E, EF  GH, ACDF  EG has the following minimal cover: • A  B, ACD  E, EF  G and EF  H

  10. Examples Does A B hold? Don’t know Does BC  A hold? Does not hold (first two tuples) Does B  C hold? Does not hold (last 2 tuples)

  11. Examples • Relation: ABCDE • Dependencies: • A  B • C  D • Decompose to?

  12. Examples • Relation: ABCD • Dependencies: • CD • CA • BC • What is the candidate key? • B

  13. Examples • Relation: ABCD • Dependencies: • BC • DA • What is the candidate key? • BD

  14. Examples • Relation: ABCD • Dependencies: • ABCD • DA • What is the candidate key? • ABC • BCD

  15. Examples • Relation: ABCD • Dependencies: • ABC • ABD • CA • DB • What is the candidate key? • AB • BC • CD • AD

  16. Examples • Suppose you are given a relation R(A,B,C,D) • Dependencies • ABC • CA • CD • What is the candidate key? • AB • BC • Decomposed into ACD and BC. Good? • Lossless. Can join back to ABCD. • Dependency ABC is not preserved.

  17. Examples • Suppose you are given a relation R(A,B,C,D) • Dependencies • ABC • CAD • What is the candidate key? • A • C • Decomposed into ABC and AD. Good? • No. A is key, so no need to decompose. • Dependency CAD is not preserved.

More Related