Database Management Systems

c Database Management Systems - Prof. Holowczak - Week 6 Database Management Systems - Prof. Holowczak

Zicklin School of Business - Baruch College
City University of New York

Week 6

What You'll Learn This Week

Relational Algebra
For Next Week

Elmasri/Navathe (3^rd) ed. Kroenke (7^th ed.) McFadden (5^th ed.)
Chapter 7 Chapter 8 Handouts on relational algebra

Elmasri/Navathe (3^rd) ed.	Kroenke (7^th ed.)	McFadden (5^th ed.)
Chapter 7	Chapter 8	Handouts on relational algebra

Relational Algebra

We now turn to a more rigorous treatment of the relational model.

Relational Algebra is a collection of operations on Relations
Relations are operands and the result of an operation is another relation.
Two main sets of relational operators:
1. Set theory operations:
  Union, Intersection, Difference and Cartesian product.
2. Specific Relational Operations:
  Selection, Projection, Join, Division

Set Theoretic Operations

Consider the following relations R and S
R

First	Last	Age
Bill	Smith	22
Sally	Green	28
Mary	Keen	23
Tony	Jones	32

First	Last	Age
Forrest	Gump	36
Sally	Green	28
DonJuan	DeMarco	27

Union: R S
Result: Relation with tuples from R and S with duplicates removed.
Difference: R - S
Result: Relation with tuples from R but not from S
Intersection: R S
Result: Relation with tuples that appear in both R and S.

R S

First Last Age
Bill Smith 22
Sally Green 28
Mary Keen 23
Tony Jones 32
Forrest Gump 36
DonJuan DeMarco 27

R - S

First Last Age
Bill Smith 22
Mary Keen 23
Tony Jones 32

R S

First Last Age
Sally Green 28

First	Last	Age
Sally	Green	28

Union Compatible Relations

Attributes of relations need not be identical to perform union, intersection and difference operations.
However, they must have the same number of attributes or degree and the domains for corresponding attributes must be identical.
Domain is the datatype and size of an attribute.
The degree of relation R is the number of attributes it contains.
Definition: Two relations R and S are union compatible if and only if they have the same degree and the domains of the corresponding attributes are the same.
Some additional properties:
- Union, Intersection and difference operators may only be applied to Union Compatible relations.
- Union and Intersection are commutative operations
  R S = S R
  R S = S R
- Difference operation is NOT commutative.
  R - S not equal S - R
- The resulting relations may not have meaningful names for the attributes. Convention is to use the attribute names from the first relation.

Cartesian Product

Produce all combinations of tuples from two relations.

R

First Last Age
Bill Smith 22
Mary Keen 23
Tony Jones 32

S

Dinner Dessert
Steak Ice Cream
Lobster Cheesecake

Dinner	Dessert
Steak	Ice Cream
Lobster	Cheesecake

R X S

First Last Age Dinner Dessert
Bill Smith 22 Steak Ice Cream
Bill Smith 22 Lobster Cheesecake
Mary Keen 23 Steak Ice Cream
Mary Keen 23 Lobster Cheesecake
Tony Jones 32 Steak Ice Cream
Tony Jones 32 Lobster Cheesecake

First	Last	Age	Dinner	Dessert
Bill	Smith	22	Steak	Ice Cream
Bill	Smith	22	Lobster	Cheesecake
Mary	Keen	23	Steak	Ice Cream
Mary	Keen	23	Lobster	Cheesecake
Tony	Jones	32	Steak	Ice Cream
Tony	Jones	32	Lobster	Cheesecake

Selection and Projection

Selection Operator

Selection and Projection are unary operators.
The selection operator is sigma:
The selection operation acts like a filter on a relation by returning only a certain number of tuples.
The resulting relation will have the same degree as the original relation.
The resulting relation may have fewer tuples than the original relation.
The tuples to be returned are dependent on a condition that is part of the selection operator.
C (R) Returns only those tuples in R that satisfy condition C
A condition C can be made up of any combination of comparison or logical operators that operate on the attributes of R.
- Comparison operators:
- Logical operators:

Use the Truth tables (memorize these) for logical expressions:

	T	F
T	T	F
F	F	F

	T	F
T	T	T
F	T	F

	T	F
	F	T

Selection Examples

Assume the following relation EMP has the following tuples:

Name Office Dept Rank
Smith 400 CS Assistant
Jones 220 Econ Adjunct
Green 160 Econ Assistant
Brown 420 CS Associate
Smith 500 Fin Associate

Name	Office	Dept	Rank
Smith	400	CS	Assistant
Jones	220	Econ	Adjunct
Green	160	Econ	Assistant
Brown	420	CS	Associate
Smith	500	Fin	Associate

Select only those Employees in the CS department:
_{Dept = 'CS'} (EMP)
Result:

Name Office Dept Rank
Smith 400 CS Assistant
Brown 420 CS Associate
Select only those Employees with last name Smith who are assistant professors:
_{Name = 'Smith' Rank = 'Assistant'} (EMP)
Result:

Name Office Dept Rank
Smith 400 CS Assistant
Select only those Employees who are either Associate Professors or in the Economics department:
_{Rank = 'Assistant' Dept = 'Econ'} (EMP)
Result:

Name Office Dept Rank
Smith 400 CS Assistant
Jones 220 Econ Adjunct
Green 160 Econ Assistant
Smith 500 Fin Associate
Select only those Employees who are not in the CS department or Adjuncts:
_{(Rank = 'Adjunct' Dept = 'CS')} (EMP)
Result:

Name Office Dept Rank
Green 160 Econ Assistant
Smith 500 Fin Associate

Name	Office	Dept	Rank
Smith	400	CS	Assistant
Brown	420	CS	Associate

Name	Office	Dept	Rank
Smith	400	CS	Assistant

Name	Office	Dept	Rank
Smith	400	CS	Assistant
Jones	220	Econ	Adjunct
Green	160	Econ	Assistant
Smith	500	Fin	Associate

Name	Office	Dept	Rank
Green	160	Econ	Assistant
Smith	500	Fin	Associate

Projection Operator

Projection is also a Unary operator.
The Projection operator is pi:
Projection limits the attributes that will be returned from the original relation.
The general syntax is: _attributes R
Where attributes is the list of attributes to be displayed and R is the relation.
The resulting relation will have the same number of tuples as the original relation.
The degree of the resulting relation may be equal to or less than that of the original relation.

Projection Examples

Assume the same EMP relation above is used.

Project only the names and departments of the employees:
_{name, dept} (EMP)
Results:

Name Dept
Smith CS
Jones Econ
Green Econ
Brown CS
Smith Fin

Name	Dept
Smith	CS
Jones	Econ
Green	Econ
Brown	CS
Smith	Fin

Combining Selection and Projection

The selection and projection operators can be combined to perform both operations.
Show the names of all employees working in the CS department:
_name ( _{Dept = 'CS'} (EMP) )
Results:

Name
Smith
Brown
Show the name and rank of those Employees who are not in the CS department or Adjuncts:
_{name, rank} ( _{(Rank = 'Adjunct' Dept = 'CS')} (EMP) )
Result:

Name Rank
Green Assistant
Smith Associate

Name
Smith
Brown

Name	Rank
Green	Assistant
Smith	Associate

For Next Week

Check for formal assignments
Chapters to Read

Elmasri/Navathe Kroenke McFadden
Chapter 7 on Relational Algebra Chapter 8 Handouts on relational algebra
Turn in E-R Diagram for group projects.
Start reviewing for the mid-term exam.

Elmasri/Navathe	Kroenke	McFadden
Chapter 7 on Relational Algebra	Chapter 8	Handouts on relational algebra

[Last Week]

[Home]

[Next Week]

File: week6.html Date: Wed Jan 24 10:21:06 EST 2001
All materials Copyright, 1997-2001 Richard Holowczak

Zicklin School of Business - Baruch College City University of New York