大连理工大学数据库-6-Formal_Relational_Query_Languages

23
Reame, and therefore to refer to, the results of relational-algebra expressions. Allows us to refer to a relation by more than one name. Given a relational-algebra expression E, the expression x (E) returns the result of expression E under the name x.
Note: The term select corresponds to what we refer to in SQL as where
7
Project Operation()

The project operation(投影运算) on r list some attributes and their values in relation r the duplicate rows(重复行) are removed from the result, since relations are sets Relation r:
Select * From R,S
19
Composition of Operations

Can build expressions using multiple operations. Example: A=C(r s)
A=C(r s)
rs
20
Composition of Operations
<attribute> op <attribute> or <constant> where op is one of: =, , >, . <.
6
Try…

Find the instructors in Physics with a salary greater than $90,000 dept_name=“Physics” salary>90000(instructor)

SQL statement
r Union s
13
Try…

Find all courses taught in the Fall 2009 semester, or in the Spring 2010 semester, or in both.
course_id ( semester=“Fall” Λ year=2009 (section)) course_id ( semester=“Spring” Λ year=2010 (section))
r Except s
16
Try…

Find all courses taught in the Fall 2009 semester, but not in the Spring 2010 semester. course_id ( semester=“Fall” Λ year=2009 (section)) − course_id ( semester=“Spring” Λ year=2010 (section))

name, course_id (instructor.ID =teaches.ID (dept_name =“Physics” (instructor × teaches)))

name, course_id (instructor.ID =teaches.ID ((dept_name =“Physics”(instructor)) × teaches))

Set differences must be taken between compatible relations.
R and s must have the same arity attribute domains of r and s must be compatible

SQL statement
relations as input and produce a new limited relation as output

Three types of operations/operators on relations
fundamental operations(基本运算)
additive operations(附加运算)

Find the name of all instructors in the Physics department
11
Union Operation()

Union operation on relation r and s is defined as r s = {t | t r or t s}

21
Try…

Find the names of all instructors in the Physics department together with the course id of all courses they taught.
22
Try…

Find the names of all instructors in the Physics department together with the course id of all courses they taught.
Relations r, s:

r s:
12
Union Operation()

For r s to be valid.
r, s must have the same arity (same number of
attributes) The attribute domains must be compatible (example: 2nd column of r deals with the same type of values as does the 2nd column of s)


For relation r(R) and s(S), r s is a relation whose schema is R’ , which is the concatenation(串联) of R and S R (A1, A2, …, Am), S(B1, B2, …, Bn) R’ (A1, A2, …, Am, B1, B2, …, Bn) Assume that attributes of r(R) and s(S) are disjoint. (That is, R S = ). If attributes of r(R) and s(S) are not disjoint, then renaming must be used. SQL Statement:
9
Try…

To eliminate(去掉) the dept_name attribute of instructor. ID, name, salary (instructor)
Note: The term project corresponds to what we refer to in SQL as select
17
Cartesian-Product Operation(x)

Cartesian-product operation on relation r and s is defined as: r s = {t | t = t1 t2 and t1 r and t2 s }
rs
18
Cartesian-Product Operation(x)
extended operations(扩展运算)
3
Fundamental Operations

Six basic operators
select:
project:
union: set difference: –
Unary operations(一元运算)
Binary operations
A,C (r)
8
Project Operation()

Notation： A1, A2, …, Ak (r): where A1, A2 are attribute names and r is a relation name

The result is defined as the relation of k columns obtained by erasing the columns that are not listed.


A=B ^ D > 5 (r)
5
Select Operation()

Notation: p(r)
p is called the selection predicate

Defined as:

p(r) = {t | t r and p(t)}
Where p is a formula in propositional calculus (命题演算) consisting of terms connected by : (and), (or), (not) Each term is one of:
24


Rename Operation()

If a relational-algebra expression E has arity n, then
(二元运算)
Cartesian product: x
rename:
Unary operations
4
Select Operation()

The select operation on r (R) selects tuples that satisfy a given predicate, resulting in a subset of r Relation r
10
Composition of Relational Operations

Several relational algebra operations can be composed together into a relational algebra expression.
Explain the meaning of following statement: name ( dept_name=“Physics” (instructor))

A typical SQL query has the form select A1, A2, ..., An from r1, r2, ..., rm where P Equivalent to the relational algebra expression A1, A2, ..., An( P (r1 r2 rm))

Domain Relational Calculus (域关系演算)
2
Relational Algebra

The relational algebra is procedural(过程化) query language
a set of operations, take one or two limited
14
Set-Difference Operation(–)

Set difference operation on relation r and s is defined as r – s = {t | t r and t s }
Relations r, s:


r – s:
15
Set-Difference Operation(–)
Principles of Database Systems
6 Formal Relational Query Languages
Formal Relational Query Languages

Relational Algebra
(关系代数)
Tuple Relational Calculus(元组关系演算)