Review

1 / 23

# Review - PowerPoint PPT Presentation

Review. Exam Su 3:30PM - 6:30PM 2010/12/12 Room C9000. Algebra. Relational Algebra Operations. Basic operations Selection ( ) Selects a subset of rows from relation. Projection ( ) Deletes unwanted columns from relation.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Review

Exam

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

### Algebra

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.

### Functional Dependency

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!

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

• 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

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
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)

Examples
• Relation: ABCDE
• Dependencies:
• Decompose to?
Examples
• Relation: ABCD
• Dependencies:
• What is the candidate key?
Examples
• Relation: ABCD
• Dependencies:
• What is the candidate key?
Examples
• Relation: ABCD
• Dependencies:
• What is the candidate key?
Examples
• Relation: ABCD
• Dependencies:
• What is the candidate key?
Examples
• Suppose you are given a relation R(A,B,C,D)
• Dependencies
• What is the candidate key?
• Decomposed into ACD and BC. Good?
Examples
• Suppose you are given a relation R(A,B,C,D)
• Dependencies
• What is the candidate key?
• Decomposed into ABC and AD. Good?