1 / 10

# CS 157B Spring 2008 - PowerPoint PPT Presentation

CS 157B Spring 2008. Prof. Sin Min Lee Presented by Li Ch’i Ooi. Manipulating Information with the Relational Algebra [Ch. 6.1]. Relation is a set of tuples and that each tuple in a relation has the same number and types of attributes. Relational algebra includes : Selection Operators

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

## PowerPoint Slideshow about ' CS 157B Spring 2008' - peggy

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

### CS 157B Spring 2008

Prof. Sin Min Lee

Presented by Li Ch’iOoi

• Relation is a set of tuples and that each tuple in a relation has the same number and types of attributes. Relational algebra includes :

• Selection Operators

• Projection Operators

• Set Operators

• Join and product Operations

• Reduce the number of tuples in a set by selecting those that satisfy some criteria.

• Example : lastName = ‘Doe’ (Customer) [ Select from Customer where lastName = ‘Doe’ ]

Customer

• Reduce the size of each tuple in a set by eliminating specific attributes.

• Example : lastName, firstNAme (Customer) [ project customer onto (lastName, firstName) ]

Customer

Set Operators (  -)

• Manipulate two similar sets of tuples by combining or comparing.

• Example : Rental  PreviousRental

Rental PreviousRental

Set Operators (  -) ...con’t

• The union of two relations is a relation that contains the set of each tuple that is in at least one of the input relations.

• Partial result of the Rental  PreviousRental

Set Operators (  -) ...con’t

• The intersection of two relations is the set of all tuples that occur in both input relations.

• The intersection of the relations Rental  PreviousRental in the previous example will return an empty set.

• Another example would be the intersection between the video IDs of the two tables.

•  videoId (Rental)   videoId (PrevioutsRental) = Videotapes that are currently rented as well as those that have been rented before.

• The set of all videotapes that have been rented previously but are not currently rented is expressed as follows:

 videoId (PreviousRental) -  videoId (Rental)

• Increase the size of each tuple by adding attributes

• The Cartesian product produces a tuple of the new realtion for each combination of one tuple from the left operand and one tuple from the right operand. Example : Employee  TimeCard

Employee TimeCard

Join and Product Operations () ...con’t

• The result of this operation has 30 tuples because there are 5 Employee and 6 TimeCard.

• Partial result of Cartesian product Employee  TimeCard

Join and Product Operations () ...con’t

• A selection of those tuples where Employee.ssn equals TimeCard.ssn can be expressed by :

Employee.ssn = TimeCard.ssn (Employee  TimeCard)

• This type of product is called a join. The join operation puts together related objects from two relations.

• A Natural Join however is defined so that the shared attribute appears only once in the output table.

• Ref. textbook Table 6.6 [natural join] vs Table 6.7 [join]