CS 425 – Database Organization - 3 - Chapter 3 ：Formal Relational Query Language (1)

1、Relational Query Language

① Expressive power of a query language

• What queries can be expressed in this language?

② Procedural versus non-procedural, or declarative
③ “Pure” languages:

• Relational algebra
• Tuple relational calculus
• Domain relational calculus

④ The above 3 pure languages are equivalent in computing power
⑤ Relational algebra:

• Algebra of relations -> set of operators that take relations as input and produce relations as output
• -> composable: the output of evaluating an expression in relational algebra can be used as input to another relational algebra expression

⑥Now: First introduction to operators of the relational algebra

2、Relational Algebra

① Procedural language
② Six basic operators

• select: σ
• project: π
• union: ∪
• set difference: –
• Cartesian product: x
• rename: ρ

③ The operators take one or two relations as inputs and produce a new relation as a result.

• composable

3、Select operation
① Notation: σ ρ(r )
② ρ is called the selection predicate‘
③ Defined as:

④ Where ρ is a formula in propositional calculus consisting of terms connected by :
<1> ∧ (and), ∨ (or), ¬ (not)
<2>Each term is one of:
<attribute> op <attribute> or <constant>
<3>where op is one of: =, ≠, >, ≥，<，≤
⑤ Example

4、Select Operation - Example

σ dept_name=“Physics”(instructor )

5、Project Operation

① Notation:

πA 1,A 2,......, 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
④ Duplicate rows removed from result, since relations are sets
⑤ Let A be a subset of the attributes of relation r then:

⑥ Example: To eliminate the dept_name attribute of instructor

6、Project Operation - Example


7、Union operation
① Notation: r ∪ s
② Defined as: