Relational Algebra

1 / 21

# Relational Algebra - PowerPoint PPT Presentation

Relational Algebra. Dashiell Fryer. What is Relational Algebra?. Relational algebra is a procedural query language. It consists of the select, project, union, set difference, Cartesian product, and rename operations.

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

## Relational Algebra

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

### Relational Algebra

Dashiell Fryer

What is Relational Algebra?
• Relational algebra is a procedural query language.
• It consists of the select, project, union, set difference, Cartesian product, and rename operations.
• Set intersection, division, natural join, and assignment combine the fundamental operations.
Select Operation
• Select operation returns a relation that satisfies the given predicate from the original relation.
• Denoted by lowercase sigma σ.
• Example: σbranch-name = “Perryridge”(loan) from page 89 returns a relation of the tuples from relation loan whose branch is Perryridge.
• =,≠,≤,≥,<,>, Λ,¬,v are legal in the predicate.
Project
• Unary operation
• Denoted by uppercase pi Π.
• Returns a relation with only the specified attributes.
• Example: page 90 Πloan-number, amount (loan) lists all of the loan numbers and the amounts.
Composition of Operations
• Results of relational operations are relations themselves.
• Compositions of operations form a relational-algebra expression.
Union
• Denoted, as in set theory, by .
• Binary operation
• Results in a relation with all of the tuples that appear in either or both of the argument relations.
Union (Cont’d)
• Unions must be between compatible relations
• Both relations must have the same number of attributes.
• Domains of the ith attribute of the first and the ith attribute of the second must be the same for all i.
Set Difference
• Denoted by -
• Binary operation
• R – S produces all tuples in R but not in S
• Relations must be compatible under the same conditions as the union operation.
Cartesian-Product
• Denoted by a cross (×)
• Binary operation
• Combines any two relations
• R × S is the Cartesian-Product of R and S
• Output has the attributes of both relations
Cartesian-Product (Cont’d)
• Repeated attribute names are preceded by the relation they originated from.
• Example: page 93 r= borrower × loan

(borrower.customer-name,

borrower.loan-number, loan.loan-number, loan.branch-name, loan.amount)

Rename
• Unary operation
• Denoted by lowercase Greek letter rho ρ
• Useful for naming the unnamed relations returned from other operations.
Formal Definition
• Relational Algebra acts on either a relation in the database or a constant relation.
• Suppose E1 and E2 are relational-algebra expressions
Formal Definition (Cont’d)
• All relational-algebra expressions are:

E1 E2

E1 – E2

E1 × E2

σP(E1), P is the predicate

ΠS(E1), S is a list of attributes in E1

ρx(E1), x is the new name of E1

• The fundamental operations can be composed to make new, simpler operations.
• Set-Intersection
• Natural-Join
• Division
• Assignment
Set-Intersection
• Denoted by the symbol .
• Results in a relation that contains only the tuples that appear in both relations.
• R  S = R – (R – S)
• Since set-intersection can be written in terms of set-difference, it is not a fundamental operation.
Natural-Join
• Denoted by |x|.
• Binary operation
• Creates a Cartesian-product of the arguments then performs selection to force equality on attributes that appear in both relations
Division
• Denoted by 
• Binary Operation
• Used in queries that include the phrase “for all”.
Division (Cont’d)
• Division is an operation on schema R – S
• A tuple t is in r  s if and only if:
• t is in ΠR – S(r) and
• For every tuple ts in s, there is a tuple tr in r satisfying both of the following:

a. tr[S] = ts[R]

b. tr[R – S] = t

Assignment
• Denoted by the  symbol
• Works like assignment operations in programming languages
• Assignment must always be made to a temporary relation
• Does not result in display to the user
Summary
• Relational Algebra defines a set of operations that operate on relations and result in new tables.
• Operations can be composed