Lecture Four - The Relation Operatiors

Readings:
  • Required: Connolly and Begg, Sections 3.1.4

Operators for Relation Data Model

The Query Operations provide us with facilities with which to access and retrieve data from one or more tables. Each operation operates on entire tables and also produces a table as its result.

The Relational Operations can be distinguished into: set theoretic operationsand specific relational operations. a complete list of relational operations, most of which we will discuss later. As we will see, in SQL most query operations are specified using the SELECT command.

Specific Relational Operations

The basic relational operations are Projection, Selection, and Join. The first two are unary operations, whereas the last one is binary.

Projection

The Projection operation, , selects the attributes specified on an attribute_list from a table r, while discarding the rest.

attribute_list(r)

As a simple example, consider the following is a projection operation and an equivalent SQL statement that generates a table with three columns: MemNo, Lname, and PhoneNumber.

MemNo,Lname,PhoneNumber(MEMBER)

SELECT MemNo, Lname and PhoneNumber

FROM MEMBER;

Selection

The selection operation, , selects some rows in a table r that satisfy a selection condition (alias predicate).

selection_condition(r) A selection condition is an expression similar to one we use when defining an if statement, or a loop condition, in any programming language.

In SQL, a selection condition is specified in a WHERE clause. Here is a simple example of a selection condition and its equivalent SQL statement that involves only one attribute.

Fname='John'(MEMBER)

SELECT *

FROM MEMBER

WHERE Fname='John';

Join

The Join operation combines two tables into one. (Why?) Consider the tables r and s with degrees r and s, respectively. The join of tables r and s produces a table whose first r columns are from table r and whose last s attributes are from table s. To be combined, the rows from each table must satisfy a joining condition.

r joining_condition s

A joining condition is a selection condition with which involves comparing attribute values in two tables. The most common joining conditions contain only equalities(equi-join).

SELECT r.*, s.*

FROM r, s

WHERE joining condition;

We can use the names of the tables to qualify the names of attributes, e.g., r.A, r.B, s.A, s.B, r.*, and s.*. This is particularly useful in situations in which the tables have the same name for some of their attributes.

Example One: Consider the following join operation and its equivalent SQL statement that lists the membership numbers and last names of all the members who currently have a borrowed book.

MEMBER MEMBER.MemNo=BOOK.BorrowerMemNo BOOK

SELECT MemNo, Lname

FROM MEMBER, BOOK

WHERE MEMBER.MemNo = BOOK.BorrowerMemNo;

Example Two: Let us refine the above statement to list only those members who have borrowed the book with call number QA76.9.D26C66. This can be easily done by specifying a selection condition along with the joining condition.

SELECT MemNo, Lname

FROM MEMBER, BOOK

WHERE MemNo = BorrowerMemNo

ANDCallNumber = 'QA76.9.D26C66';

Set Theoretic Operations

Union, Difference

Let us first consider the tables r and s. We assume that these tables are union compatible which means that they have the same degree (i.e., same no. of attributes) and the same domains of corresponding attributes.

Table r
A / B / C
a / b / c
d / a / f
c / b / d
Table s
D / E / F
b / g / a
d / a / f
Union

r U s is a table whose tuples are members of r or s or both. In accordance with set theory, the resulting table should not contain any duplicates.

Table r U s
A / B / C
a / b / c
d / a / f
c / b / d
b / g / a

The resulting table has no obvious names for its columns. The convention is that in such cases the resulting table inherits the same attribute names from the first table in operation.

Difference

r - s is a table whose tuples are members of r but not of s.

Table r - s
A / B / C
a / b / c
c / b / d

SQL supports both the operations UNION and EXCEPT (difference). UNION eliminates duplicate rows in the resulting table. However, we are often interested in seeing duplicate rows, and for this reason SQL provides the option UNION ALL, which retains duplicates. The statement

( SELECT MemNo

FROM MEMBER, BOOK

WHERE MemNo = BorrowerMemNo AND

CallNumber = 'QA76.9.D26C66'

)

UNION

( SELECT MemNo

FROM MEMBER, BOOK

WHERE MemNo = BorrowerMemNo AND

CallNumber = 'QA76.9.D7E53'

);

The eight relational operators.

  • RESTRICT
  • PROJECT
  • JOIN
  • INTERSECT
  • UNION
  • DIFFERENCE
  • PRODUCT
  • DIVIDE

RESTRICT

Based on a relation X, Restrict returns a relation Y which consists of all tuples from X that satisfy a specified condition. <O>

Product / Desc. / Price
PR01 / Cooker / 1000
PR02 / T.V. / 9000
PR03 / CD-ROM / 600
Product / Desc. / Price
PR01 / Cooker / 1000
PR02 / T.V. / 9000
PR03 / CD-ROM / 600
Product / Desc. /

Price

PR02 / T.V. / 9000
Product / Desc. / Price
PR01 / Cooker / 1000
PR03 / CD-ROM / 600

PROJECT

Based on a relation X, Project returns a relation Y which consists of all tuples that remain the tuples/sub-tuples in X after specified attributes of X have been eliminated.

Product / Description / Price
PR01 / Cooker / 1000
PR02 / T.V. / 9000
PR03 / CD-ROM / 600
Description
Cooker
T.V.
CD-ROM
Product / Description
PR01 / Cooker
PR03 / CD-ROM

PRODUCT

Based on two relation X and Y, Product returns a relation Z which consists of all possible mathcing pairs of tuple from X and Y.

Product
T.V.
Radio
Fridge
Salesman / Product
John / T.V.
John / Radio
John / Fridge
Peter / T.V.
Peter / Radio
Peter / Fridge
Salesman
John
Peter


UNION

Based on two relations X and Y, Union returns a relation Z which consists of all tuples appearing in either, or both of X and Y.

Member
Jeffrey
Peter
Tommy
Mary
Member
John
Peter
May
Jeffrey
Tommy
Mary
Member
John
Peter
May


INTERSECT

Based on two relations X and Y, Intersect returns a relation Z which consists of all tuples appearing in both of X and Y.

Member
Jeffrey
Peter
Tommy
Mary
Member
Peter

Member

John
Peter
May


DIFFERENCE

Based on two relations X and Y, Difference returns a relation Z which consists of all tuples appearing in the X and not Y.

Member
Jeffrey
Peter
Tommy
Mary
Member
John
May
Member
John
Peter
May


JOIN

Giving two relations X and Y, based on the common attribute(s) of X and Y, Join returns a relation Z which consists of all tuples that are combinations X and Y. The common value for the common attribute(s) appears just once in Z.

There are different kinds of JOIN:

Natural Join: / Link tables by selecting only the rows with common values in their common attribute(s).
Outer-Join: / The unmatched pairs would be retained and the values for the unmatched attribute(s) would be left vacant or null.
Student / I.D. / Subject
John / 99001 / TC001
John / 99001 / TC002
May / 99003 / TC001
I.D. / Subject
99001 / TC001
99001 / TC002
99003 / TC001
Student / I.D.
John / 99001
Peter / 99002
May / 99003


Student / I.D. / Subject
John / 99001 / TC001
John / 99001 / TC002
Peter / 99002
May / 99003 / TC001
I.D. / Subject
99001 / TC001
99001 / TC002
99003 / TC001
Student / I.D.
John / 99001
Peter / 99002
May / 99003


DIVIDE

Takes two relations X and Y, let X be binary and Z be unary, X divided by Y returns a relation Z which consists of all values of one attribute of X that match all values in Y.

Book
DB Sys.
Intro. to DB.
Member
Mak
Student / Book
Judy / DB Sys.
Judy / C++
Mak / Intro. to DB.
Mak / DB Sys.
Tom / DB Sys.
David / Data Comm.


* Discussion of Exercise 04

(Prepared by BR-cheung)3-1/22