QUESTION 4 [25 Marks]

1. Referring to Figure 1, write statement to create the Hotel table using the integrity enhancement features of SQL.

(8 Marks)

2. Write SQL statement using the Data Defination Language (DDL) or Data Manipulation Language (DML) with the following questions:

1. Create an INSERT statement to add the tuple of data to the HOTEL table from the following sample data. (7 Marks)

Hotel Number	Hotel Name	City
H001	Sunshine Bay	Tanjung Karang
H002	Mayang Sari	Port Dickson
H003	Corus Jumbo	Air Keroh
H004	Eagle Ranch	Johor Bharu

2. Change the hotel name of H003 to Classy Jumbo. (2 Marks)
3. Modify the City of H001 to Kuala Kubu (2 Marks)
4. Delete Eagle Ranch from HOTEL table (2 Marks)

3. Create a view containing the hotel name and the names of the guests staying at the hotel.

(2 Marks)

4. Give the users Manager and Director full access to these views, with the privilege to pass the access on to other users.

The following tables form part of a database held in a relational DBMS:+ (hotelNo, hotelName, city) (roomNo, hotelNo, type, price)+ Hotel Room Booking Guest where Hotel Room forms Booking dateFrom) Guest (hotelNo, guestNo, dateFrom, dateTo, roomNo) < (guestNo, guestName, guestAddress) contains hotel details and hotelNo is the primary key;+ contains room details for each hotel and (roomNo, hotelNo) the primary key; contains details of bookings and (hotelNo, guestNo, forms the primary key; contains guest details and guestNo is the primary key. Figure 1: SCHEMA