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
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 A1, A2,… An to the attributes of the n column relation produced by
expression expr, use the form expr [A1, A2, … An]
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 Ai oper Bi in which Ai is an attribute of R and Bi 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 Ai oper Bi in which Ai is an attribute of R and Bi 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/