Guide IV
Chapter 5 Relational Algebra and Relational Calculus
5.1 Choose any four relational algebra operators and explain how each functions.
For example: Select produces a horizontal subset of a relation. Project produces a vertical subset
5.2 Given two relations R and S, where R contains N1 tuples and S contains N2 tuples (N2 > N1 > 0),
give the minimum and maximum cardinality for the result relation for each of the following
relational algebra expressions and in each case state any assumptions about the schemas that are
required to make the expression meaningful:
(a) R S
(b) R S
(c) R S
(d) R x S
(e) a = 1(R)
(f) a(R)
Answer is shown in table below.
Expression Min Max Assumptions
5.3 A relational database contains details about journeys from Paisley to a variety of destinations and
contains the following relations:
Operator (opCode, opName)
Journey (opCode, destinationCode, price)
Destination (destinationCode, destinationName, distance)
Each operator is assigned a unique code (opCode) and the relation operator records the
association between this code and the operator s name (opName). Each destination has a unique
code (destinationCode) and the relation destination records the association between this code
and the destination name (destinationName), and the distance of the destination from Paisley.
Guide IV
The relation Journey records the price of an adult fare from Paisley to the given destination by
as specified operator, several operators may operate over the same route.
Formulate the following queries using relational algebra, tuple relational calculus, and domain
relational calculus (the answers to these queries in SQL are given in the next section):
(a) List the details of journeys less than £100.
(b) List the names of all destinations.
(c) Find the names of all destinations within 20 miles.
(d) List the names of all operators with at least one journey priced at under £5.
(e)
Guide IV
(f) List the names of all destinations that do not have any operators.
5.4 The following tables form part of a database held in a Relational Database Management System:
Employee (empID, fName, lName, address, DOB, sex, position, deptNo)
Department (deptNo, deptName, mgrEmpID)
(1) List all employees.
(2) List all the details of employees who are female.
(3) List the names and addresses of all employees who are Managers.
(4)
(5)
Guide IV
5.5 The following tables form part of a database held in a Relational Database Management System:
Employee (empNo, eName, salary, position)
Aircraft (aircraftNo, aName, aModel, flyingRange)
(1) List all Boeing aircraft.
(2) List all Boeing 737 aircraft.
(3) List the employee numbers of pilots certified for Boeing aircraft.
(4) List the names of pilots certified for Boeing aircraft.
RA: eName (( aName (Aircraft) 3 aircraftNo Certified) 3 empNo Employee)
TRC: {E.eName | Employee(E) (( C) Certified(C) (E.empNo = C.empNo)
(5) List the aircraft that can fly nonstop from Glasgow to New York (flyingRange >
flightDistance).
(6) List the employee numbers of employees who have the highest salary.
RA: To answer this query, we first find all employees who do not have the
Guide IV
(7) List the employee numbers of employees who have the second highest salary.
RA: To answer this query, we proceed as above and first find all employees who
(8) List the employee numbers of employees who are certified for exactly three aircraft.
RA: To answer this query, we first find the employees who are certified for at
least three aircraft, then find the employees who are certified for at least four
Guide IV
DRC: {C1empNo | ( C1aircraftNo)Certified(C1empNo, C1aircraftNo))