Relational Algebra - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Relational Algebra - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Relational Algebra

What is Relational Algebra?

• Relational Algebra is formal description of how

relational database operates.

• It is a procedural query language, i.e. user must

define both “how” and “what” to retrieve.

• It consists of a set of operators that consume either

one or two relations as input. An operator produces

one relation as its output.

Terminology

• Relation - a set of tuples.
• Tuple - a collection of attributes which describe some real world entity.
• Attribute - a real world role played by a named domain.
• Domain - a set of atomic values.
• Set - a mathematical definition for a collection of objects which contains no duplicates.

Algebra Operations

• Unary Operations - operate on one relation. These include select, project and rename operators.
• Binary Operations - operate on pairs of relations. These include union, set difference, division, cartesian product, equality join, natural join, join and semi-join operators.

Select Operator Example

Name Age Weight Harry 34 80 Sally 28 64 George 29 70 Helena 54 54 Peter 34 80 Name Age Weight Harry 34 80 Helena 54 54 Peter 34 80 Name Age Weight Helena 54 54 Person б Age≥34 (Person) б Age=Weight (Person)

Project Operator

• Project ( ∏ ) retrieves a column. Duplication is not

permitted.

• e.g., name of employees:

name

(Employee)

e.g., name of employees earning more than 80,000:

name

(б

Salary>80,

(Employee))

Project Operator Example

Name Age Salary Harry 34 80, Sally 28 90, George 29 70, Helena 54 54, Peter 34 40, Name Sally Name Age Salary Sally 28 90, Employee б Salary>80,000 (Employee) ∏ (^) name (б Salary>80,000 (Employee))

Cartesian Product

• In mathematics, it is a set of all pairs of elements (x, y)

that can be constructed from given sets, X and Y,

such that x belongs to X and y to Y.

• It defines a relation that is the concatenation of

every tuple of relation R with every tuple of relation

S.

Rename Operator

• In relational algebra, a rename is a unary operation written as

a / b

( R ) where:

a and b are attribute names

R is a relation

• The result is identical to R except that the b field in all tuples is

renamed to an a field.

• Example, rename operator changes the name of its input

table to its subscript,

• ρ employee

(Emp)

• Changes the name of Emp table to employee

Rename Operator Example

Name Salary Harry 80, Sally 90, George 70, Helena 54, Peter 40, EmployeeName Salary Harry 80, Sally 90, George 70, Helena 54, Peter 40, Employee ρ (^) EmployeeName / Name ( Employee )

Union Operator Example

FN LN Susan Yao Ramesh Shah Barbara Jones Amy Ford Jimmy Wang FN LN John Smith Ricardo Brown Susan Yao Francis Johnson Ramesh Shah FN LN Susan Yao Ramesh Shah Barbara Jones Amy Ford Jimmy Wang John Smith Ricardo Brown Francis Johnson Student Professor Student U Professor

Intersection Operator

• Denoted as ∩. For relations R and S, intersection is R ∩ S.
  • Defines a relation consisting of the set of all tuples that are

in both R and S.

• R and S must be union-compatible.
• Expressed using basic operations:

R ∩ S = R – (R – S)

Set Difference Operator

• For relations R and S,

Set difference R - S, defines a relation consisting of

the tuples that are in relation R, but not in S.

Set difference S – R, defines a relation consisting of

the tuples that are in relation S, but not in R.

Set Difference Operator Example FN LN Susan Yao Ramesh Shah Barbara Jones Amy Ford Jimmy Wang FN LN John Smith Ricardo Brown Susan Yao Francis Johnson Ramesh Shah FN LN Barbara Jones Amy Ford Jimmy Wang Student Professor Student - Professor FN LN John Smith Ricardo Brown Francis Johnson Professor - Student