Substituting for T2 in the last statement gives:
TR = Column of [Join of T1, Telephone on roomNo]
T2
under extensionNo.
Now substitute for T1:
TR = Column of [Join of {Rows of Room where capacity = 1}
T1
, Telephone on
roomNo]
T2
under extensionNo.
The square and curly brackets, and subscripts T1 and T2, have been inserted in order
to make the correspondence between the nested and unnested versions clearer.
Normally, the nested version would be written simply as:
TR = Column of (Join of (Rows of Room where capacity = 1), Telephone on
roomNo) under extensionNo.
We will see in the following chapter that it is easier for a database management
system to optimise its query processing performance if the query is expressed as a single
statement.
Questions
1. Is it likely to be more efﬁcient to do a join before selecting out the rows required, or
to select rows before doing a join? (1 min)
2. (a) With reference to the Eats, Keeper and Supervision tables shown in the
questions for section 17.3, write a sequence of unnested relational algebra operations to
ﬁnd the foods eaten by animals supervised by keepers called Morris.
(b) Write a single, nested relational algebra statement that answers the part (a)
query.
\$VVLJQPHQW
1. Prove that a table list in a relational algebra statement need never contain more
than two tables.
\$QVZHUSRLQWHUV
Section 17.3
1. (a) TR (b) Same as Eats, but with
columns reversed.
(c) TR (d) TR
208 Data Analysis for Database Design
keeperName
Morris
Dennis
Darwin
keeperNo animalName
101 zebra
animalName food
camel buns
camel hay
(e) TR
(f) T1 TR
2. (a) T1 = Rows of Eats where animalName = 'camel'.
TR = Column of T1 under food.
T1 TR
(b) Similar to 2(a), but the table occurrences for T1 and TR will be null.
(c) T1 = Rows of Keeper where keeperName = 'Morris'.
TR = Column of T1 under keeperNo.
T1 TR
3. (a) TR
(b) T1
TR
Relational algebra 209
animalName food
camel hay
keeperNo animalName
100 snake
103 snake
keeperNo
100
103
animalName food
camel buns
camel hay
food
buns
hay
keeperNo keeperName
100 Morris
103 Morris
keeperNo
100
103
keeperNo animalName keeperNo keeperName
100 camel 100 Morris
100 snake 100 Morris
100 lion 100 Morris
101 zebra 101 Dennis
103 snake 103 Morris
animalName food keeperNo animalName
camel buns 100 camel
zebra hay 101 zebra
snake people 100 snake
snake people 103 snake
camel hay 100 camel
snake mice 100 snake
snake mice 103 snake
lion people 100 lion
keeperNo food
100 buns
101 hay
100 people
103 people
100 hay
100 mice
103 mice
4. (a) T1 = Rows of Keeper where keeperName = 'Morris'.
T2 = Join of T1, Supervision on keeperNo.
TR = Column of T2 under animalName.
T1 T2
TR
(b) T1 = Rows of Eats where food = 'hay'.
T2 = Join of T1, Supervision on animalName.
TR = Column of T2 under keeperNo.
T1 T2
TR
(c) T1 = Rows of Eats where food = 'people'.
T2 = Column of T1 under animalName.
T3 = Join of T2, Supervision on animalName.
T4 = Column of T3 under keeperNo.
T5 = Join of T4, Keeper on keeperNo.
TR = Column of T5 under keeperName.
T1 T2
T3 T4
210 Data Analysis for Database Design
keeperNo keeperName
100 Morris
103 Morris
keeperNo keeperName animalName
100 Morris camel
100 Morris snake
100 Morris lion
103 Morris snake
animalName
camel
snake
lion
keeperNo
101
100
animalName food
zebra hay
camel hay
animalName food keeperNo
zebra hay 101
camel hay 100
animalName food
snake people
lion people
animalName keeperNo
snake 100
snake 103
lion 100
animalName
snake
lion
keeperNo
100
103
T5 TR
This solution demonstrates the use of column operations to minimise the amount of
data carried through into the joins.
5. The contents of T1 and T2 will be the same, but the order of rows may differ. (The
order of rows in a table is immaterial, in that it conveys no information, though as a
matter of convenience, a user would expect to be able to specify ordering of the output.)
The general rule is that the sequence in which the table names are written in a join
statement is not signiﬁcant.
Section 17.4
1. They would probably both be regarded as being deﬁned on the domain of all
possible dates (this domain could have a cut-off point at the date the company started
business). There would be a difference in their ranges if deliveryDate could be in the
future, but orderDate could not. Even so, it would make sense to ask questions like,
‘For which orders is the deliveryDate equal to the orderDate?’ Incidentally, assuming
that there is a table type Order (orderNo, orderDate, deliveryDate), this query could
be answered by a Rows operation of the form:
TR = Rows of Order where orderDate = deliveryDate.
2. The result would be a list of all partNo(s) that are both major and minor parts. Final
assemblies and components would be excluded from the list.
Section 17.5.3
1. TR
The result table represents all current employees who are not on the list of retired
employees.
Section 17.6
1. TR = Division of Assignment by ProductGroup.
Section 17.7
1. T1 = Product of Doctor, Patient.
T2 = Rows of T1 where Doctor.doctorNo = Patient.doctorNo.
TR = Columns of T2 under Doctor.doctorNo, doctorName, patientNo,
patientName.
The ﬁnal statement (TR = . . .) could have referred to Patient.doctorNo instead of
Doctor.doctorNo.
Relational algebra 211
keeperNo keeperName
100 Morris
103 Morris
keeperName
Morris
employeeNo employeeName
E32 Wroe
E54 Mistry
E63 Morris

Get Data Analysis for Database Design, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.