Ch4

Relational Algebra

The purposes of Database System:

Store data efficiently
Access data efficiently. That is, answer a query in efficient way.

Query languages: Allow manipulation and retrieval of data from a database.

Two mathematical Query Languages form the basis for “real” languages (e.g. SQL), and for implementation:

Relational Algebra - a set of procedures, more operational, very useful for representing execution plans.
                                 Specify what I want to get and how do I get it.   (e.g. Find all people who live in Memphis.)
Relational Calculus - a set of logic. It is predicate calculus.
                                  It is a declarative language (Like Lisp, Prolog) which only specifies what kind data you want.
                 Let users describe what they want, rather than how to compute it. (Non operational, declarative.)
Note:   Users will never write either Relational Algebra or Relational Calculus, but Database System will convert SQL to relational algebra. That is, Relational Algebra is for internal access. So, we still need to learn it.
We need clear, unambiguous languages for expressing processing logic. Relational Algebra helps clarify relational manipulation and establishes a foundation on which to learn SQL.

Relation is a set of tuples. Thus relational algebra has "set operators" (Cartesian product, ×, intersection, union, complement, difference) and selection, projection, join.

Click and see definitions of Operators of Relational Algebra.

Click the following to see examples:

Selections
Projections
Joins

Division is a short cut of nested difference:

A / B same as

Example: Page 110 of the textbook

A	sno	pno	B1	pno	A/B1	sno
	s1	p1		p2		s1
	s1	p2				s2
	s1	p3	B2	pno		s3
	s1	p4		p2		s4
	s2	p1		p4
	s2	p2			A/B2	sno
	s3	p2	B3	pno		s1
	s4	p2		p1		s4
	s4	p4		p2
				p4	A/B3	sno
						s1

Click here to see examples of Algebra Queries from pages 110 to 116 of the textbook.

Relational Calculus
        It is a declarative language (Like Lisp, Prolog) which describe what they want, rather than how to compute it.
        Data independent
        Let the system to find best way to do it.
        There are in two flavors: Tuple relational calculus (TRC) and Domain relational calculus (DRC).
                  Calculus has variables, constants, comparison ops, logical connectives and quantifiers.
                  TRC: Variables range over (i.e., get bound to) tuples.
                  DRC: Variables range over domain elements (= field values).
        Both TRC and DRC are simple subsets of first-order logic (e.g. for all, there exists...).

Click here to see examples.