Data Analysis for Database Design, 3rd Edition

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 efficient 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

find 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 significant.

Section 17.4

1. They would probably both be regarded as being defined 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 final 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