Relational Algebra

Relational Query Languages

Languages for describing queries on a relational database

Structured Query Language (SQL)

• Predominant application-level query language
• Declarative

Relational Algebra

• Intermediate language used within DBMS
• Procedural

Relational Algebra Operations

Below are fundamental operations that are "complete".  That is, this set of operations alone can define any retrieval.

• Select
• Project
• Rename
• Union
• Set Difference
• Cartesian Product

Convenient, natural additions to the set of operations makes

• Set Intersection
• Natural Join
• Division
• Assignment

Select Operation

Choose a subset of tuples from a relation based on some criteria, results in another relation called a "result set"

Notation uses lower case sigma:

σ_condition(relation)

Project Operation

• Produce a subset of attributes from a relation
• Unselected columns are eliminated
• Duplicate rows are eliminated
• Result is a relation

Notation is lower case pi

π_{attribute- list}(relation)

Set Operations

Union: r ∪ s ? a row is in the result set if it is a row from r or from s; or the set of all tuples that belong to either r or s.

[Intersection: r ∩ s ? a row is in the result set if it is a row from both r and from s.]

Set Difference: r - s ? the set of all tuples in r that are not in s

Both operations require that the sets r and s are union compatible:

• Both r and s have same number of columns
• Domains of attributes match or are compatible in sequence in both r and s
• The names may not necessarily match up; helpful if they do, however.

Union compatible relations can be combined using union, intersection ∩, and set difference.

Cartesian Product

If R and S are two relations, R × S is the set of all concatenated tuples , where x is a tuple in R and y is a tuple in S

• (R and S need not be union compatible)

R × S is expensive to compute:

• The sum of the number of attributes of R and S is the number of attributes of the Cartesian product
• The product of the sizes of R and S in the number of rows

Cartesian Product

R			S			R X S
a	b		c	d		a	b	c	d
xl	x2		y1	y2		x1	x2	y1	y2
x3	x4		y3	y4		x1	x2	y3	y4
						x3	x4	y1	y2
						x3	x4	y3	y4

Renaming

Result of an expression evaluation is a relation, called the result set.

Attributes of relation must have distinct names. This is not guaranteed with Cartesian product. Suppose in the previous example attributes a and c have the same name. 

Renaming operator tidies this up. To assign the names A₁, A₂,… A_n to the attributes of the n column relation produced by

expression expr, use the form expr [A₁, A₂, … A_n]

Join

This is a derived operation, i.e., it is based on the basic operations of the relational algebra.  It is a convenience operation because it is done so much.

A (general or theta θ) join of R and S is the expression 
R  _{join-condition} S

where join-condition is a conjunction of terms A_i oper B_i in which A_i is an attribute of R and B_i is an attribute of S and oper is one of =, <, >, ≤, ≥, ≠

The meaning is essentially:

σ_{join.-condition}(R X S)

where join-condition in both case are the same, except for possible renamings of attributes.

Join and Renaming

Problem: R and S might have attributes with the same name – in which case the Cartesian product is not defined

Solution:

• Rename attributes prior to forming the product and use new names in join-condition?.
• Common attribute names are qualified with relation names in the result of the join

Join

This is a derived operation, i.e., it is based on the basic operations of the relational algebra.  It is a convenience operation because it is done so much.

A (general or theta θ) join of R and S is the expression 
R  _{join-condition} S

where join-condition is a conjunction of terms A_i oper B_i in which A_i is an attribute of R and B_i is an attribute of S and oper is one of =, <, >, ≤, ≥, ≠

The meaning is essentially:

σ_{join.-condition}(R X S)

where join-condition in both case are the same, except for possible renamings of attributes.

Join and Renaming

Problem: R and S might have attributes with the same name – in which case the Cartesian product is not defined

Solution:

• Rename attributes prior to forming the product and use new names in join-condition?.
• Common attribute names are qualified with relation names in the result of the join

Division

Goal: Produce the tuples in one relation, r, that match all tuples in another relation, s

• 
• r (A1, …An, B1, …Bm)
• s (B1 …Bm)
• r/s, with attributes A1, …An, is the set of all tuples such that for every tuple in s, is in r

Can be expressed in terms of projection, set difference, and cross-product

Refer to:
http://jcsites.juniata.edu/faculty/rhodes/dbms/relalg.htm

Cartesian product 笛卡尔积

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26477398/viewspace-2144973/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26477398/viewspace-2144973/