Question

I need help with this please, I'm very confused.

Given the following relational model database schema:

GUEST (email, address, phone_no, name) (phone_no) is a candidate key.

Email and address are long strings, phone_no and name are also strings.

TYPE (code, description)
The type code is a numeric code and the description a length varying string.

AMENITY (code, description)
The amenity code is a numeric code and the description a length varying string.

CITY (city_id, name, state, country)
The city identification is a four-character alphanumeric string. Name and country are required strings while state if present is a four-character string.

HOTEL (hotel_name, check_in_time, check_out_time, city) (city) FK CITY Hotel names are up to 512 characters. Check in and check out times are time values.

HOTEL_PHONES (hotel_name, phone) (hotel_name) FK HOTEL The hotel phone are strings long enough to represent an international phone number.

ROOM (hotel_name, room_number, floor, room_type) (hotel_name) FK HOTEL

Room numbers are integer values as well as the floor number.

(room_type) FK TYPE

HOTEL_AMENITIES (hotel_name, amenity_code) (hotel_name) FK HOTEL (amenity_code) FK AMENITY

STAY (email, hotel_name, room_number, start_date, ccard_type, ccard_number, total_amount) (email) FK GUEST

(hotel_name, room_number) FK ROOM

Start date is a date without time. The credit card type is a five-character string and the number a 16-character string (fixed length). The total amount represents the total paid by the guest in the stay, represented by a numeric format that allow any value from $0.00 to $99,999.99.

Questions

1. 1) Write the DDL statements to create the relations in a DBMS. For datatypes follow the ones available for MySQL server v5.7. See reference link below. [30 marks]

2. 2) Write the insert statements to add 3 guests, 1 hotel with 3 rooms and one stay for each guest in the available hotel. [10 marks]

3. 3) Write the SQL statement(s) to delete the guest with email [email protected]. [5 marks]

4. 4) Write an update statement to increase by 10% all the stays with start date between

   January 1st, 2018 and July 1st, 2019. [5 marks]

5. 5) Answer the following queries using SQL:

   1. List all hotels in Denver, CO, USA. [5 marks]

   2. List the phone numbers for hotels in Roma, Italy. [5 marks]

   3. List hotel name, room number and type description for all hotels. [5 marks]

   4. List hotel name, check in time and amenities description for all hotels. [5 marks]

   5. List the names and city name for hotels that have at least one room with type “Double Suite Room”. [10 marks]

   6. List the guest names, stay date, for guests that stayed in hotels in New York, NY, USA. [10 marks]

   7. List the guest names, stay date and paid amount, for guests that stayed in hotels in New York, NY, USA or San Francisco, CA, USA during July 2018. [10 marks]

Answer 1

1.

CREATE TABLE GUEST
(email VARCHAR[20] NOT NULL,
address VARCHAR[50],
phone_no VARCHAR[15],
name VARCHAR[20]
);

2.

CREATE TABLE TYPE
(
code VARCHAR[20],
description VARCHAR[100]
);

3,

CREATE TABLE AMENITY
(
code VARCHAR[20],
description VARCHAR[100]
);

4.

CREATE TABLE CITY
(
city_ID INT,
name VARCHAR[20],
country VARCHAR[20]
);
5.

CREATE TABLE HOTEL
(

hotelname VARCHAR[20],
check_in_time TIME,
check_out_time TIME,
city VARCHAR[20]
);

6.

CREATE TABLE HOTEL
(
hotel_name VARCHAR[20],
phone VARCHAR[20]
);

7.

CREATE TABLE ROOM
(
hotel_name VARCHAR[20],
room_no INT,
floor INT,
room_type VARCHAR[20]
);

8.

CREATE TABLE HOTEL_AMENITIES
(
hotel_name VARCHAR[20],
amenitycode INT
);

9.

CREATE TABLE STAY
(
email VARCHAR[20],
  hotel_name VARCHAR[20],
room_no INT,
start_date DATE,
ccard_type VARCHAR(5),
ccard_no VARCHAR(16),
totol_amt FLOAT

);

10.

CREATE TABLE FK_GUST
(
  hotel_name VARCHAR[20],
room_no INT
);

2

INSERT INTO GUEST VALUES('[email protected]','new york',7578341087,'guest1');

3   

DELETE FROM GUEST WHERE email='[email protected]';