## Relational Algebra

### 4.1 Background

• Considered: structural properties and constraints
• Want to consider: foundations of database operations

### Types of Operations

• Query: mapping of a database into a relation
• Update: mapping between databases

### Origin of Operations

• From set theory
• Specifically developed for relational databases

### The Approach

• Relational algebra:
• A procedural language
• Based on algebraic concepts

• Algebra:
• Studies quantities
• Refers to relations and properties by symbols

### 4.2 Set Theory Operations

• Union (A B)
• Intersection (A B)
• Difference (A - B)
• Defined only for relations over identical attributes
Student
 Name Number Ben 3412 Dan 1234 Nel 2341
Instructor
 Name Number Don 3412 Nel 2341
 Name Num Don 3412 Ron 4123
Student Instructor
 Name Number Ben 3412 Dan 1234 Don 3412 Nel 2341
Student Instructor
 Name Number Nel 2341
Student - Instructor
 Name Number Ben 3412 Dan 1234

The above expressions are undefined, if ‘Administrator’ replaces ‘Instructor’

### 4.3 Renaming (ρS(B1,...,Bn)(R))

• The relation name from R to S: ρS(R)
• The attributes to B1,...,Bn: ρ(B1,...,Bn)(R)
• Both: ρS(B1,...,Bn)(R)

### Use

Facilitates set operations.
 Name Number Don 3412 Ron 4123
 Name Number Ben 3412 Dan 1234 Nel 2341 Don 3412 Ron 4123

### 4.4 Assignments (S(B1,...,Bn) ← R)

The renaming operation under alternative notation

vs.

Student Temp

### 4.5 Selection σ<selection condition>(R)

The condition must be a propositional formula

Clauses
of the operations {=,<,,>,,} on attributes
Boolean operations
AND, OR, NOT
Student
 Name Number Sex Ben 3412 M Dan 1234 M Nel 2341 F
σ(Name>“BEN”) AND (Number<2000)(Student)
 Name Number Sex Dan 1234 M

An attribute with unordered domain allows only the comparison operations =,.

### 4.6 Projection (π<attribute list>(R))

• The selection operation chooses tuples (rows of tables)
• The projection operation chooses attributes (columns of tables)
Student
 Name Number Sex Ben 3412 M Dan 1234 M Nel 2341 F
πName, Number(Student)
 Name Number Ben 3412 Dan 1234 Nel 2341

Identical tuples collapse into a single tuple

### 4.7 Cross Join (R × S)

Also called Cartesian product and cross product. Combines the attributes of the given relations, in all possible ways.

R(A1,...,An) × S(B1,...,Bm) = Q(A1,...,An,B1,...,Bm)

Instructor
 Name Number Don 3412 Nel 2341
 Adm Num Dean 3412 Secretary 4123
 Name Number Adm Num Don 3412 Dean 3412 Don 3412 Secretary 4123 Nel 2341 Dean 3412 Nel 2341 Secretary 4123

### Application

• Combine related information with the cross join operation
Instructor × Administrator  Name Number Adm Num Don 3412 Dean 3412 Don 3412 Secretary 4123 Nel 2341 Dean 3412 Nel 2341 Secretary 4123

• Extract tuples with the selection operation
Temp σNumber = Num (Instructor × Administrator)  Name Number Adm Num Don 3412 Dean 3412

• Weed out attributes with the projection operation

### 4.8 Join (R<join condition>S)

R(A1,...,An)<condition>S(B1,...,Bm) = Q(A1,...,An,B1,...,Bm)

Instructor
 Name Number Don 3412 Nel 2341
 Adm Num Dean 3412 Secretary 4123
 Name Number Adm Num Don 3412 Dean 3412

• Cross product operation restricted to combinations which satisfy the stated conditions
• Cross product includes all possible combinations of tuples
• Join = cross product + selection

• Called equi-join if only relational operations ‘=’ are in use.

### Application

Variant of the previous example:

### 4.9 Natural Join (RS)

• Natural-join = equi-join + projection
• The join checks for equality of values of common attributes
• The projection collapses together the common attributes
Instructor  Name Number Don 3412 Nel 2341

• When the operands have no attribute in common, the natural join operations reduces to the join cross (i.e., Cartesian product) operation

### 4.10 Outer Joins

• A join operation is complete, if all the tuples of the operands contribute to the result.
• Tuples not participating in the result are said to be dangling
• Outer join operations are variants of the join operations in which the dangling tuples are appended with NULL fields. They can be categorized into left, right, and full outer joins.
Instructor ][ Administrator  Name Number Adm Don 3412 Dean Nel 2341 NULL NULL 4123 Secretary
Instructor ] Administrator  Name Number Adm Don 3412 Dean Nel 2341 NULL

### 4.11 Division (R ÷ S)

R(Z) ÷ S(X) equals maximal T(Z - X) which satisfies T(Z - X) × S(X) R(Z)

R
 A B a 1 a 2 a 3 b 2 b 3 b 4 c 1 c 2
S1
 B 1 2
R÷S1
 A a c

Visited
 Name City Ben Columbus Dan Cincinnati Dan Columbus Nel Cleveland Nel Columbus
city visited by every one?

### 4.12 A Complete Set of Operations

• A subset of the relational algebra operations is complete, if it can express any relational algebra operation
• {σ,π,,-,×} is complete for relational algebra
• The extra operations are provided for convenience
• The outer join operations, and the following operations, can’t be expressed in terms of the basic relational algebra operations

Example: A B = A B - ((A - B) (B - A))

### 4.13 Global Aggregate Functions

Aggregate Functions (<function list>(R))
Mathematical functions on collections of values from the database: SUM, AVERAGE, MAXIMUM, COUNT
Student
 Name Number Sex Ben 3412 M Dan 1234 M Nel 2341 F
COUNT(Student)
 Count 3
Grouping (<grouping attributes><function list>(R))
of tuples in a relation by the value of some of their attributes
Sex COUNT, SUM(Number)(Student)
 Sex Count SUM M 2 4646 F 1 2341

Note: ℱ denotes the character script-F

### 4.14 Assignment #2

Due: Mon, Feb 2

Problems 6.16, 6.22 (a–d, f), and 5.17 (5th ed) / 5.16 (4th ed) from the textbook (pages 214, 217, 169 5th ed; 186, 188, 147 4th ed)

Notes

• If you submit your homework electronically, use the departmental submit utility: submit XXX lab2 filename. XXX represents ‘c670aa’ for students of the 3:30 section, and ‘c670ab’ for students of the 5:30 section.

• Files are restricted to 1,048,576 bytes
• The submit program issues error messages to the student’s CSE email account

Reference: Ch. 5.3, 6.1–6.5 in textbook.