Chapter 4 Hotel hotelno hotelname city Room roomno

List all hotels • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, Type, price)

• List all hotels. Project Hotel over (hotelno, hotelname) giving T 1 Or

List all single rooms with a price below $20 per night • Hotel (hotelno,

• List all single rooms with a price below $20 per night. Select

List the names and addresses of all guests • Hotel (hotelno, hotelname, city) •

• List the names and addresses of all guests. Project Guest over (guestname,

List the price and type of all rooms at Grosvenor Hotel • Hotel (hotelno,

• List the price and type of all rooms at Grosvenor Hotel. Room

List all guest currently staying at the Grosvenor Hotel • Hotel (hotelno, hotelname, city)

• List all guest currently staying at the Grosvenor Hotel join Booking where

List the details of all rooms at the Grosvenor Hotel, including the name of

• Hotel join Room where Hotel. hotelno = Room. hotelno giving T 6

• Hotel join Booking where Hotel. hotelno = Booking. hotelno giving T 1

• T 5 right outer join T 8 where T 5. roomno =

List the guest details (guestno, guestname, and guestaddress) of all guests staying at the

Create a view of all rooms in the Grosvenor Hotel, excluding price details. State

Create a view of all rooms in the Grosvenor Hotel, excluding price details. Room

Slides: 19

Download presentation

Chapter 4 • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, Type, price) • Booking (hotelno, guestno, datefrom, dateto, roomno) • Guest (guestno, guestname, guestaddress)

List all hotels • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, Type, price) • Booking (hotelno, guestno, datefrom, dateto, roomno) • Guest (guestno, guestname, guestaddress)

• List all hotels. Project Hotel over (hotelno, hotelname) giving T 1 Or Project Hotel over (hotelno, hotelname, city) giving T 1

List all single rooms with a price below $20 per night • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, Type, price) • Booking (hotelno, guestno, datefrom, dateto, roomno) • Guest (guestno, guestname, guestaddress)

• List all single rooms with a price below $20 per night. Select Room where price<20 and type=‘single’ giving T 1

List the names and addresses of all guests • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, Type, price) • Booking (hotelno, guestno, datefrom, dateto, roomno) • Guest (guestno, guestname, guestaddress)

• List the names and addresses of all guests. Project Guest over (guestname, guestaddress) giving T 1

List the price and type of all rooms at Grosvenor Hotel • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, type, price) • Booking (hotelno, guestno, datefrom, dateto, roomno) • Guest (guestno, guestname, guestaddress)

• List the price and type of all rooms at Grosvenor Hotel. Room join Hotel where Room. hotelno = Hotel. hotelno giving T 1 Select T 1 where hotelname=‘Grosvenor’ giving T 2 Project T 2 over (type, price) giving T 3

List all guest currently staying at the Grosvenor Hotel • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, Type, price) • Booking (hotelno, guestno, datefrom, dateto, roomno) • Guest (guestno, guestname, guestaddress)

• List all guest currently staying at the Grosvenor Hotel join Booking where Hotel. hotelno = Booking. hotelno giving T 1 join Guest where T 1. guestno = Guest. guestno giving T 2 Select T 2 where hotelname=‘Grosvenor’ and datefrom <= ‘systemdate’ and dateto >= ‘systemddate’ giving T 3 Project T 3 over (Guest. guestno, Guest. guestname) giving T 4

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 • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, Type, price) • Booking (hotelno, guestno, datefrom, dateto, roomno) • Guest (guestno, guestname, guestaddress)

• Hotel join Room where Hotel. hotelno = Room. hotelno giving T 6 • Select T 6 where hotelname=‘Grosvenor’ giving T 7 • Project T 7 over (roomno, hotelno, type, price) giving T 8

• Hotel join Booking where Hotel. hotelno = Booking. hotelno giving T 1 • T 1 join Guest where T 1. guestno = Guest. guestno giving T 2 • Select T 2 where hotelname=‘Grosvenor’ and datefrom <= ‘systemdate’ and dateto >= ‘systemdate’ giving T 4 • Project T 4 over (roomno, guestname) giving T 5

• T 5 right outer join T 8 where T 5. roomno = T 8. roomno giving T 9

List the guest details (guestno, guestname, and guestaddress) of all guests staying at the Grosvenor Hotel • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, Type, price) • Booking (hotelno, guestno, datefrom, dateto, roomno) • Guest (guestno, guestname, guestaddress)

• Hotel join Booking where Hotel. hotelno = Booking. hotelno giving T 1 • Select T 1 where hotelname=‘Grosvenor’ and datefrom <= ‘systemdate’ and dateto >= ‘systemdate’ giving T 2 • Guest semijoin T 2 where Guest. guestno = T 2. guestno giving T 3

Create a view of all rooms in the Grosvenor Hotel, excluding price details. State advantages. • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, Type, price) • Booking (hotelno, guestno, datefrom, dateto, roomno) • Guest (guestno, guestname, guestaddress)

Create a view of all rooms in the Grosvenor Hotel, excluding price details. Room join Hotel where Room. hotelno = Hotel. hotelno giving T 1 Select T 1 where hotelname=‘Grosvenor’ giving T 2 Project T 2 over (roomno, type) giving T 3 • Advantages: security, user friendly, performance