Question

Database Management System

Complete the following exercises in Connolly & Begg:

4.8 The following tables from part of a database held in a relational DBMS

Hotel (hotelNo, hotelName, city)

Room (roomNo, hotelNo, type, price)

Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Guest (guestNo, guestName, guestAddress)

a) Identify the foreign keys in this schema.

b) Explain how the entity integrity rule and the referential integrity rule apply to these relations.

5.8 Describe the relations that would be produced by the following relational algebra operations. In your description, make clear what attribute your relations have, and explain what each relation represents in the real world.

a) π_hotelNo(σ_{price > 50}(Room))

b) σ_{Hotel.hotelno = Room.hotelNo}(Hotel × Room)

c) π_hotelName(Hotel ⨝ _{Hotel.hotelNo = Room.hotelNo}(σ_{price > 50}(Room)))

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

Book (ISBN, title, edition, year)

BookCopy (copyNo, ISBN, available)

Borrower (borrowerNo, borrowerName, borrowerAddress)

BookLoan (copyNo, dateOut, dateDue, borrowerNo)

Formulate the following queries in relational algebra:

5.24 List all book titles

5.25 List all book titles published in the year 2012.

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

5.30 List the names of borrowers with overdue book.s

Answer 1

4.8

a. Foreign keys

Hotel ----- none

Room ----- hotelNo

Booking ----- hotelNo, guestNo, roomNo

Guest ----- none

b. Entity integrity rule and the referential integrity rule

The primary keys of Tables Hotel(hotelNo), Room(roomNo), Guest(guestNo) are unique and not null. This satisfies entity integrity rule.

The referential integrity rule deals with foreign keys of the table . The primary key attribute in a table should match the foreign key attribute value in other table which is satisfied in the tables.

5.8

a)Display hotelNo whose price is greater than 50

b) Display all hotels where rooms are available.

c) Display all hotels names where rooms are available and of price greater than 50.

5.24

_title(Book)

5.25

_title( _{year = 2012} ( Book))

5.27

_title( Book BookCopy Borrower )

5.30

_borrowerName( _{dateDue < dateOut} (BookLoan Borrower ))

Do ask if any doubt. Please upvote.