3. Formal Relational Query Languages

Question 1

What are the two types of Relational Query Languages?

Answer 1

• Relational algebra (procedural), consists of a set of operations that take one or two relations as input and produce a new relation as their result.
• Relational calculus (declarative), describes the desired information without giving a specific procedure for obtaining that information.
• Note that they are logically equivalent and can express the same queries (Codd’s theorem)

Question 2

What is the basis of a relational algebra query?

Answer 2

It explains how to obtain the result

e.g course_id (semester=“Fall” Λ year=2014)

Question 3

What is the basis of a relational calculus query?

Answer 3

It explains what is required

e.g {t | t in instructor Λ t[salary]>25000 }

Question 4

What are the 6 fundamental relational algebra operations?

Answer 4

• Select (unary)
• Project (unary)
• Rename (unary)
• Union (binary)
• Set difference (binary)
• Cartesian product (binary)

Question 5

What 2 operations can you derive from the fundamental relational algebra operations?

Answer 5

• Set intersection (binary)

* Natural join (binary)

Question 6

What is Select?

Answer 6

Selection of tuples from a relation
• σ p(r)
• p = selection predicate, condition for selection
σ p(r) = {t | t ε r p(t)}

Question 7

What is Project?

Answer 7

Selection of attributes from a relation
• π A1, A2, … Ak (r)
• r = relation name
π staff_id, name, salary (instructor)

Question 8

What is Union?

Answer 8

Tuples that appear in r or s or both
• r ∪ s
• r ∪ s={t|t ε r Λ t ε s}

Question 9

What 2 conditions have to be valid for Union?

Answer 9

1. r, s must have same arity (same number of attributes)

2. The attribute domains must be compatible

Question 10

What is Set difference?

Answer 10

Tuples that appear in r but not in s
• r – s
• Same conditions as Union

Question 11

What is Cartesian product?

Answer 11

Combine two relations: each tuple in r is joined with each tuple in s
• r x s
• r x s = {t q | t ε r Λ q ε s}

Question 12

What is Set intersection?

Answer 12

Tuples that appear in both r and s
• r ∩ s
• r ∩ s = { t | t ε r Λ t ε s }

Question 13

What is Natural join?

Answer 13

Combine two relations by matching values of common attributes in r and s
• r ⨝ s