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

• 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

• 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!

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

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)

• Relation: ABCDE

• Dependencies:

• Decompose to?

• Relation: ABCD

• Dependencies:

• What is the candidate key?

• Relation: ABCD

• Dependencies:

• What is the candidate key?

• Relation: ABCD

• Dependencies:

• What is the candidate key?

• Relation: ABCD

• Dependencies:

• What is the candidate key?

• Suppose you are given a relation R(A,B,C,D)

• Dependencies

• What is the candidate key?

• Decomposed into ACD and BC. Good?

• Suppose you are given a relation R(A,B,C,D)

• Dependencies

• What is the candidate key?

• Decomposed into ABC and AD. Good?