CP363 : Relational Algebra

Relational Algebra is a basic means of manipulating data in a relational database. It consists of a number of operations that provide a formal basis for implementing and optimizing database queries. There are two kinds of operations: unary, which operate on a single table, and binary, which operate on two tables. Its unary operations are:

• reference
• selection
• projection
• renaming

Reference

Refers to an existing relation.

Algebra: R

SQL: SELECT * FROM table

Examples:

SELECT * FROM member

Returns every tuple in the member table. (No particular order is requested or implied.)

Selection

Selects specific tuples from a relation.

Algebra: σ_{<selection condition>}(R)

SQL: SELECT * FROM table WHERE condition

A selection condition may compare:

• attributes with constant values
• attributes with other attributes
• functions applied to attributes with constant values
• functions applied to attributes with other attributes
• functions applied to attributes with functions applied to other attributes

Conditions may use the comparison operators =, <, <=, >, >=, etc. and the Boolean operators AND, OR, NOT, etc.

Examples:

SELECT * FROM member
  WHERE last_name = 'Brown'

Returns all tuples in member where the attribute last_name is equal to the constant value 'Brown'.

SELECT * FROM member
  WHERE YEAR( Birth_Date ) < 1950

Applies the YEAR function to the Birth_Date attribute and compares the result to the constant value 1950.

SELECT * FROM member
  WHERE YEAR( Birth_Date ) < 1950
  AND Institution = 'Waterloo'

Uses the boolean operator AND to return all tuples in member where a member was born before 1950 and works at the University of Waterloo.

SELECT * FROM Loan
  WHERE Date_Returned IS null

Returns all tuples in Loan where Date_Returned does not have a value, i.e. the item has not yet been returned. (Comparisons to null are made with the IS operator.)

SELECT * FROM Loan
  WHERE Date_Returned = Date_Borrowed

Returns all the tuples in Loan where the item was returned on the same day it was borrowed by comparing the two attributes Date_Returned and Date_Borrowed.

SELECT * FROM Loan
  WHERE YEAR( Date_Returned - Date_Borrowed ) >= 1

Returns all tuples from Loan where the item borrowed was out for at least one year.

SELECT * FROM Loan
  WHERE Date_Returned IS null
  AND YEAR( CURRENT DATE - Date_Borrowed ) >= 1

Returns all tuples from Loan where the item borrowed has been out for at least one year and not yet returned.

Projection

Drops attributes from a result set. Attributes are returned in the same order in which they are listed. (The order of the tuples is unchanged!)

Algebra: π_{<attribute list>}(R)

SQL: SELECT attribute list FROM table

Selection and projection may be used together.

Examples:

SELECT last_name, first_name FROM member

Returns only the values of the last_name and first_name attributes in the member table.

Note that although the definition of projection is that no duplicate tuples are returned when only non-key attributes are listed, this is not true for SQL. To return only non-duplicate tuples you must use the DISTINCT clause.

SELECT DISTINCT last_name FROM member

Returns each value of last_name only once.

SELECT last_name FROM member
  WHERE last_name = 'Brown'

Returns the full names of all members whose surname is 'Brown'.

Renaming

Attributes and relations can be renamed. This is often useful in making the results of queries easier to read and understand. Renamed attributes and relations are said to have aliases.

Algebra: ρ_{< new attribute list>}(R) ρ_{S< new attribute list>}(R) ρ_S(R)

SQL: SELECT attribute AS new attribute FROM table SELECT * FROM old table AS new table

SELECT CONCAT(last_name, ', ',  first_name) AS Name FROM member

Returns the member name in the form 'Brown, David' as the attribute Name. The 'CONCAT()' function concatenates string in MySQL. (Most other database use '||' as the concatenation operator.

SELECT * FROM member AS M

member is temporarily renamed to M.

(See the description of Cross Product for an example of when a result set can be renamed in SQL.)

Symbol Reference