Database System - 3rd theory class : Formal Relational Query Languages (1) 20/03/16

Formal Relational Query Languages (形式化查询语言)
Query Language : language in which users request information from the database
Categories of languages :

1. procedural (过程性)
   Relational Algebra (关系代数)
2. non-procedural (非过程性)
   Turple Kelational (元组关系演算) / Domain Relational Calculus (域关系演算)

" Pure language "

Relational Algebra → Procedural language
operations : Union (并) / Intersection (交) / Defference (差) / Cartesian Product (笛卡尔积) / Project (投影) / Select (选择) / Join (连接) / Division (除)

Campatibility (相容性) 👇
relation R&S are campatible when : (1) R , S must have same arity (同元的)
(2) The attribute domains must be compatible

• Union
  Notation : R ∪ S
  Defined as : R ∪ S = { t | t ∈ R v t ∈ S } R , S should be compatible
  R ∪ S = S ∪ R

• Difference
  Notation : R - S
  Defined as : R - S = { t | t ∈ R v t 不属于 S } R , S should be compatible

• Intersection
  Notation : R ∩ S
  Defined as : R ∩ S = { t | t ∈ R ^ t ∈ S } R , S should be compatible
  R ∩ S = S ∩ R = R - ( R - S ) = S - ( S -R )

• Cartesian Product
  Notation : R X S
  Defined as : R X S = { t q | t ∈ R ^ q ∈ S } R , S are disjoint ( R ∩ S = 空集 )
  degree : R - n , S - m , R X S - n X m

• Select
  Notation :
  p is called the selection predicate (选择谓词)
  Where pis a formula in propositional calculus （命题演算） consisting of terms connected by : Each term is one of: attribute opattribute or constant

• Project
  Notation :
  where A1 , A2 , … , Akare 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

• Join
  Notation :
  Defined as :
  R ( A1 , A2 , … , An ) , A ∈ { A1 , A2 , … , An }
  S ( B1 , B2 , … , Bm ) , B ∈ { B1 , B2 , … , Bm }
  t ∈ R , s ∈ S
  A and B are compatible
  θ ∈ { ≥ , ＞ , ≤ ,＜, = , <> }
  Join usually used with Select and Project together

• Rename
  Notation : ρ
  Rename a relation to another with a different name.
  Duplicate a relation and give a new name.
  R1 → R2 , and only the relation names are different for R1 and R2
  Query : Select all course No.s which both “2015030101” and “2015040101”

• Equal-Join
  Notation :
  Defined as :
  R ( A1 , A2 , … , An ) , A ∈ { A1 , A2 , … , An }
  S ( B1 , B2 , … , Bm ) , B ∈ { B1 , B2 , … , Bm }
  t ∈ R , s ∈ S
  A and B are compatible
  Equal-Join is a special case of Join

• Natural-Join
  Notation:
  Defined as:
  R and S have one same attribute or a group of same attributes.
  Duplicated columns should be deleted in the result relation.
  Natural-Join is a special case of Equal-Join