Relational Algebra

1
Relational Algebra
2
Manipulating Databases

• To access information in a database we use a
  queryEx How many customers have the first name
  John?
• Good query writing follows a formal model called
  relational algebra

3
Relational Algebra

• Relational algebra a collection of mathematical
  operations that manipulate tables
• Familiarity with relational algebra helps
  understanding the logic behind complex queries
  and ease the way for writing them

4
Tables and Queries

• Recall a table is a set of rows/records having
  the same number and types of attributes
• When you send a query to the database, it
• Finds the appropriate rows of information in the
  stored tables
• Performs the requested operations on the data
• Represents the results in a new temporary table
• Delivers the table of results to the user

5
Example

• Ex How many customers have the first name
  John?
• The database creates a table containing
• all customers whose first name is
• John and returns the table to the user

6
Basic types of queries

• There are 4 basic types of queries
• A projection operation produces a result table
  with
• Only some of the columns of its input table.
• A selection operation produces a result table
  with
• All of the columns of the input table
• Only those rows of its input table that satisfy
  some criteria.
• A join or product operation produces a result
  table by
• Combining the columns of two input tables.
• A set operation produces a result table by
• Combining rows from one or the other of its input
  tables

7
Projection operation ?

• A projection query selects some of the columns of
  the input table
• project T onto (attribute1, attribute2, )
• Relational algebra form
• ?attribute1, attribute2,...(T)

8
Example

• ?firstName,lastName(Customer)

9
Example

• Notice that the result table has fewer rows
• Duplicate rows have been removed because the
  attributes do not contain a key

10
Storing the temporary results

• We can store the result of a query in a table T
  as follows
• T ? ?attribute1, attribute2,...(T)
• This will create a table T with attributes
• attribute1, attribute2, containing the
• result of the query

11
Selection queries ?

• A selection query selects rows that match a
  selection criteria from a table
• Relational algebra form
• ?ltconditiongt(T)
• Each row is checked to see if it satisfies the
  condition and selected accordingly

12
Example

• ?lastNameDoe(Customer)

13
Complex selection criteria

• The selection criterion can be any boolean
  expression containing operators like and, or, ,
  ?, lt, gt, ?, ?, etc

14
Example

• T? ?ssn376-77-0099 and date lt
  01-mar-2002(TimeCard)

15
Product queries X

• The product query takes two tables and produce a
  table which is the cross product of the two,
  i.e., combines every row of one table with every
  row of other table
• R(A1, A2 , , An) S(B1, B2 , , Bm) Q(A1,
  A2 , , An, B1, B2 , , Bm)
• Relational algebra form
• R ? S

16
Example

• Employee ? TimeCard

17
Product queries

• If two attributes in two tables T and R have the
  same name, we prefix them with the relation name
  T.ltattributegt Ex Employee.ssn, TimeCard.ssn
• Remark. Many of the resulting rows in the
  previous example dont make sense

18
Join queries ?

• In the previous table we are only interested in
  the rows that match rows with Employee.ssn
  TimeCard.ssn
• We are interested in the query
• ?Employee.ssnTimeCrad.ssn(Employee x TimeCard)

19
Join operations

• A join query is a cross product with a
  restriction on the result rows
• The join condition determines which rows match
• Only matching rows are in the result table
• Typical join condition is equality of attributes
• It is called equi-join
• Relational algebra form
• R ?ltconditiongtS

20
Example

• Some rows from the tableEmployee
  ?Employee.ssnTimeCard.ssn TimeCard

21
Natural join

• Frequently, when doing an equi-join, the
  attributes have the same name
• A natural join is an equi-join with an equality
  condition on the common attributes
• Employee ?ssn TimeCard
• Employee TimeCard
• In natural join the common attributes appear once

22
Queries with multiple joins

• Consider the Video-Rental schema, and suppose we
  want to retrieve for every currently-rented
  video, the renters account number, video number,
  rental date, due date, title of the movie, and
  cost

23
Solution

• ? accountId, videoId, dateRented, dateDue,
  title, cost
• ((Rental ?videoId Video) ?movieId
  Movie)

24
Combining operations

• Suppose we want to find the following info. For
  customer with account 113, find all the videos
  that he is renting For each video, find the
  video number, the title of the movie, and the due
  date

25
Solution

• ? videoId, title, dateDue
• ((? accountId113(Rental) ?videoId Video)
  ?movieId Movie)
• Or
• T1 ? accountId113(Rental)
• T2T1 ?videoId Video
• T3 T2 ?movieId Movie
• T4 ? videoId, title, dateDue ( T3)

26
More examples

• List all comedy movies that were rented on
  December 21, 2001. For every movie list the
  customers name, movie title, and date returned

27
Solution

• T1 ? daterentedDecember 21 2001(PreviousRental
  )
• T2T1 ?videoId Video
• T3 ? genrecomedy (Movie)
• T4 T2 ?movieId T3
• T5 T4 ?accountId Customer
• T6 ? firstName, lastName, title, dateReturned (
  T5)

28
Set operations

• Set operations include Union, intersection, and
  difference
• Relational algebra form ?, ?, ?
• Set operations can be applied to any tables with
  the same shape (compatible)
• The same order and type of attributes
• Attribute names do not have to agree

29
Set operations

• If R and S are two compatible tables
• R ? S is the table that contains the set of rows
  that are either in R or in S
• R ? S is the table that contains the set of rows
  that are both in R and S
• R - S is the table that contains the set of rows
  that are in R but not in S

30
Example of ?

• Retrieve all the videos that are currently or
  were previously rented
• EverRented Rental ? PreviousRental

31
Example of ?

• Retrieve the video id of all the videos that are
  currently rented and have been rented at least
  once before
• Veterans? videoId, ( Rental) ? ? videoId, (
  PreviousRental)

32
Example of -

• Retrieve the video id of all the videos that are
  currently rented and have never been rented
  before
• FirstTime? videoId, ( Rental) - ? videoId, (
  PreviousRental)

33
Aggregate functions

• Not all queries can be expressed using the basic
  operations described previously.
• What if we want to compute the average salary of
  all employees?

34
Aggregate functions

• What if we want to count the number of employees
  in each department?
• For such queries, we use aggregate functions.
• Relational algebra form
• ltgrouping attributesgt?ltfunction listgt(T)

35
Aggregate functions

• The function list includes average, sum, count,
  maximum, minimum
• The result of the query will be a table
  containing the results
• The attributes consist of the grouping attributes
  function parameters

36
Examples

• Ex1 compute the average salary of all the
  employees
• ?Average(salary)(Employee)
• The resulting table contains one attribute
  Average_Salary and one value
• Ex2 compute the number of employees in each
  department
• DNO ? Count(ssn)(Employee)
• The resulting table contains two attributes DNO
  and Count_ssn. There is a row for every dept.
  containing the DNO value and the number of
  employees

37
Renaming attributes

• It is sometimes convenient to rename the
  attributes in the resulting relation
• R(DEPTNUM, NUM_EMPL) ? DNO ?Count(ssn)
  (Employee)

38
Recursive operations

• Compute all the employees supervised by
  Pinochio
• Compute all the emplyees supervised by
  Pinochio at level two
• Compute all the employees supervisod by
  Pinochio at any level!!!

39
Answers

• A1
• Pinochio_ssn lt- ? ssn (?fnamepinochio(Employee
  ))
• Result1? ? ssn (Pinochio ?ssnsuperssn Employee)
• A2
• Result2 ?? ssn (Result1 ?ssnsuperssn Employee)
• Result ? Result1 ? Result2
• A3 is not supported by standard relational
  algebra

40
Outer Join

• Left Outer Join
• Ex list the employee names and also the name
  of the department they manage in case it
  exists
• Right Outer Join
• Full Outer Join

41
Examples from (Emp-Dept-Proj schema)

• List everybody who makes more than 30000.
• List names of everybody working for the research
  department.
• List employees with a dependent.
• List employees that have a daughter.
• List employees without dependents.
• List employees working on a project in Houston.
• List all supervisors.
• List names of all managers.
• List names of managers with at least one
  dependent

42
Examples from (Emp-Dept-Proj schema )

• For every project located in Chicago, list the
  project number, the controlling department
  number, the department managers last name,
  address, and birthdate.
• Make a list of project numbers for projects
  involving an employee whose first name is
  Pinochio either as a worker on the project, or
  as a manager of the department that controls the
  project.
• Find the names of all employees who are directly
  supervised by Isaac Newton
• For each department, retrieve the department name
  and average salary of its employees.
• Retrieve the average salary of all female
  employees
• For each project, list the project name and the
  total number of hours spent on the project.