Database Storage & Design Chapter 5 Homework A language that can be used to produce any relation that can be derived

Databas – Part III

6

Chapter 5 Relational Algebra and Relational Calculus

Review Questions

5.1 What is the difference between a procedural and non–procedural language? How would you

classify the relational algebra and relational calculus?

Procedural language: a language that allows user to tell the system what data is needed and

5.2 Explain the following terms:

relationally complete;

closure of relational operations.

The relational algebra is a theoretical language with operations that work on one or more

5.3 Define the five basic relational algebra operations. Define the Join, Intersection, and Division

operations in terms of these five basic operations.

Five basic operations are:

There is also the Join, Intersection, and Division operations:

Databas – Part III

5.4 Discuss the differences between the five Join operations: Theta join, Equijoin, Natural join,

Outer join, and Semijoin. Give examples to illustrate your answer.

Theta join R F S

Produces a relation that contains tuples satisfying the predicate F from

the Cartesian product of R and S.

5.5 Compare and contrast the tuple relational calculus with domain relational calculus. In

particular, discuss the distinction between tuple and domain variables.

5.6 Define the structure of a (well-formed) formula in both the tuple relational calculus and

domain relational calculus.

Tuple relational calculus

A (well-formed) formula is made out of one or more atoms, where an atom has one of the

following forms:

Databas – Part III

8

Domain relational calculus

A (well-formed) formula is made out of one or more atoms, where an atom has one of the

following forms:

5.7 Explain how a relational calculus expression can be unsafe? Illustrate your answer with an

example. Discuss how to ensure that a relational calculus expression is safe.

Exercises

For the following exercises, use the Hotel schema defined at the start of the Exercises at the end of

Chapter 4.

5.8 Describe the relations that would be produced by the following relational algebra operations:

a) hotelNo ( price 50 (Room) )

b) Hotel.hotelNo Room.hotelNo(Hotel Room)

Databas – Part III

9

c) hotelName (Hotel Hotel.hotelNo Room.hotelNo (price 50 (Room)) )

d) Guest (dateTo -Jan-2007 (Booking))

e) Hotel Hotel.hotelNo Room.hotelNo (price 50 (Room)) )

5.9 Provide the equivalent tuple relational calculus and domain relational calculus expressions

for each of the relational algebra queries given in Exercise 4.8.

a) hotelNo ( price 50 (Room) )

b) Hotel.hotelNo Room.hotelNo(Hotel Room)

Databas – Part III

10

c) hotelName (Hotel Hotel.hotelNo Room.hotelNo (price 50 (Room)) )

d) Guest (dateTo -Jan-2007 (Booking))

e) Hotel Hotel.hotelNo Room.hotelNo (price 50 (Room)) )

f) guestName, hotelNo (Booking Booking.guestNo Guest.guestNo Guest)

11

5.10 Describe the relations that would be produced by the following tuple relational calculus

expressions:

(a) {H.hotelName | Hotel(H) H.city

(b) {H.hotelName | Hotel(H) ( R) (Room(R) H.hotelNo R.hotelNo R.price

50)}

(c) {H.hotelName | Hotel(H) ( B) ( G) (Booking(B) Guest(G) H.hotelNo

B.hotelNo B.guestNo = G.guestNo G.guestName

(d) {H.hotelName, G.guestName, B1.dateFrom, B2.dateFrom | Hotel(H) Guest(G)

5.11 Provide the equivalent domain relational calculus and relational algebra expressions for each

of the tuple relational calculus expressions given in Exercise 4.10.

(a) {H.hotelName | Hotel(H) H.city

(b) {H.hotelName | Hotel(H) ( R) (Room(R) H.hotelNo R.hotelNo R.price

50)}

Databas – Part III

12

RA: hotelName (Hotel Hotel.hotelNo Room.hotelNo (price 50 (Room)) )

DRC: {hotelName | ( hNo, cty, gNo, gName, gAddress, hNo1, gNo1, dFrom, dTo,

(d) {H.hotelName, G.guestName, B1.dateFrom, B2.dateFrom | Hotel(H) Guest(G)

DRC: {hotelName, guestName, dateFrom1, dateFrom2 | ( hNo, cty,

gNo, gAddress, hNo1, gNo1, dTo1, rNo1, hNo2, gNo2, dTo2, rNo2)

RA: Booking2(hotelNo, guestNo, dateFrom2, dateTo2, roomNo2)

13

5.12 Generate the relational algebra, tuple relational calculus, and domain relational calculus

expressions for the following queries:

(a) List all hotels.

(b) List all single rooms with a price below £20 per night.

(c) List the names and cities of all guests.

(d) List the price and type of all rooms at the Grosvenor Hotel.

Databas – Part III

14

(e) List all guests currently staying at the Grosvenor Hotel.

(f) List the details of all rooms at the Grosvenor Hotel, including the name of the guest

staying in the room, if the room is occupied.

Databas – Part III

15

(g) List the guest details (guestNo, guestName, and guestAddress) of all guests staying at the

Grosvenor Hotel.

5.13 Using relational algebra, create a view of all rooms in the Grosvenor Hotel, excluding price

details. What would be the advantages of this view?

16

5.14 List all employees.

5.15 List all the details of employees who are female.

5.16 List the names and addresses of all employees who are managers.

5.17 Produce a list of the names and addresses of all employees who work for the IT department.

Databas – Part III

17

5.18 Produce a list of the names of all employees who work on the SCCS project.

5.19 Produce a complete list of all managers who are due to retire this year, in alphabetical order

of surname.

5.20

5.21 Produce a report of the total hours worked by each employee.

5.22 For each project on which more than two employees worked, list the project number, project

name, and the number of employees who work on that project.

5.23 List the total number of employees in each department for those departments with more than

10 employees. Create an appropriate heading for the columns of the results table.

Databas – Part III

18

The following tables form part of a Library database held in an RDBMS:

5.24 List all book titles.

5.25 List all borrower details.

5.26 List all book titles published in the year 2012.

19

5.27 List all copies of book titles that are available for borrowing.

5.28

5.29

20

5.30 List the names of borrowers with overdue books.

5.31 -321-52306-

5.32 -321-52306-

5.33 -321-52306- een borrowed?

5.34

21

5.38 Analyze the RDBMSs that you are currently using. What types of relational languages does

the system provide? For each of the languages provided, what are the equivalent operations

for the eight relational algebra operations defined in Section 5.1?