Write an SQL statement to create a table room with the attributes (roomNo,hotelNo,hotelName,address,city,type,price,floor,guestNo), specify that

the attribute roomNo cannot be null,

the attributes roomNo and hotelNo together form the primary key,

the attribute hotelNo is a foreign key from another table hotel,

the attribute guestNo is a foreign key from another table guest,

and the attribute floor is a natural number no more than 100.

Continue with the above example. Querying from the room table, write an SQL statement to find out the minimal room price of each hotel in Sydney.

You should put your answers to these questions.

SAD data flow diagram. Continue with the use case analysis done in the last week. Create DFDs for the final project. You need to include at least 3 activities/use cases from different roles of users:

student e.g., enrol in a unit

teaching staff e.g., produce the class list

managing staff e.g., schedule the units

You need to at least create the context diagram, level 0 diagram and several.

You should put your answers to the above questions.

(SQL constraints, aggregates, select.

Drop all tables. Then, recreate the room table with the attributes (roomNo,hotelNo,hotalName,type,price,guestNo), and specify that

roomNo must not be null,

hotelNo must not be null,

the price is between $0 and $99,999,

the default value of the type is standard,

the type of rooms must be standard or single or double or master or president,

(note: do not specify the primary key as well as the foreign key.)

Alter the room table to make (roomNo,hotelNo) to be the primary key.

Alter the room table to make guestNo to be the foreign key referring to the same attribute guest table. What happened? Why?

Create the guest table with the attributes (guestNo,guestName,gender,email,nationality), and specify that

guestNo is the primary key,

guestName and email forms a candidate key,

gender must be either male or female.

Redo step c). What happened?

Insert the following record into guest table, what happened? Why?

Insert into guest values (12345, James Bond, jbond@security.gov.uk,man, United Kingdom);

Insert the following record into guest table.

Insert into guest values (12345, James Bond, jbond@security.gov.uk,male, United Kingdom);

Insert the following record into guest table, what happened? Why?

Insert into guest values (54321, James Bond, jbond@security.gov.uk,male, Great Britain);

Insert the following record into guest table, what happened? Why?

Insert into guest values (null, Jason Borne, jborne@security.gov,male, USA);

Insert the following records into guest table. Why they do not violate the constraints?

Insert into guest values (54321, Super Man, Null,male, Mars);

Insert into guest (guestNo,guestName,nationality) values (11111,Lady Gaga,USA);

Insert the following record into room table, what happened? Why?

Insert into room values (101, 987654,Hilton, President,150000, 11111);

Insert the following record into room table, what happened? Why?

Insert into room values (101, 987654,Hilton, President,10000, 12321);

Insert the following record into room table, what happened? Why?

Insert into room values (101, 987654,Hilton, President,10000, null);

Insert the following record into room table, whats the value for the type column? Why?

Insert into room (roomNo,hotelNo,hotelName,price,guestNo) values (222, 987654,'Hilton',300, 12345);

Inset the following records to the room table so that the records are as follows:

Count the number of rooms in the hotel Hilton.

Count the number of distinct roomNo in the room table.

Select the average price, minimal price and maximal price of the hotel Hilton.

Calculate the total revenue of the hotel Home.

Calculate the total amount paid by the guest 12345.

If we try to select a hotel with an average price below 250, can we use the following statement? Why?

Select hotelName from room where avg(price)

Find out the minimal price for every hotel.

Calculate the revenue for all hotels that have at least one room occupied by some guest.

Find out the hotelName and the total amount of money paid by a single guest.

II. (1 mark) SQL select from multiple tables.

a) Again, insert some records into the room and the guest tables respectively

as follows:

Page 4

b) Select all rooms and guests such that the guest has booked the room

(using where clause).

c) Select all rooms and guests such that the guest has booked the room

(using join clause).

d) Select all rooms and guests such that the guest has booked the room

(using variables).

e) Select all room prices booked by James Bond (using where clause).

f) Select all room prices booked by James Bond (using join clause).

g) Select all room prices booked by James Bond (using variables).

h) Select all guest numbers and names in the hotel Hilton. If we use the

following statement

Select guestNo, guestName

From room, guest

Where room.guestNo=guest.guestNo And hotelName=Hilton;

What will happen? Why? What is the correct statement?

i) Select all rooms booked by male with the price over 500 and ordered by

prices, but only show the room number and hotel name.

j) Select the average price of rooms booked by someone, group by gender.

New Query Options Results Messages questio roomNo New Query Options Results Messages questio roomNo