Review
Download
1 / 23

Review - PowerPoint PPT Presentation


  • 72 Views
  • Uploaded on

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.

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

PowerPoint Slideshow about ' Review' - tad-higgins


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

  • Exam

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

    • Room C9000



Relational algebra operations
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 dependencies fds
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!


Example contd

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


Reasoning about fds contd
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


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


Examples1
Examples

  • Relation: ABCDE

  • Dependencies:

  • Decompose to?


Examples2
Examples

  • Relation: ABCD

  • Dependencies:

  • What is the candidate key?


Examples3
Examples

  • Relation: ABCD

  • Dependencies:

  • What is the candidate key?


Examples4
Examples

  • Relation: ABCD

  • Dependencies:

  • What is the candidate key?


Examples5
Examples

  • Relation: ABCD

  • Dependencies:

  • What is the candidate key?


Examples6
Examples

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

  • Dependencies

  • What is the candidate key?

  • Decomposed into ACD and BC. Good?


Examples7
Examples

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

  • Dependencies

  • What is the candidate key?

  • Decomposed into ABC and AD. Good?


ad