6. Write an SQL statement to add a column floor to the room table. You should put your answers to these questions. staffID name 12345 James 54321 Gaga 09876 Super Page 3

(2 marks) Practices: I. (1 mark) SAD use case analysis. Conduct use case analysis for the final project. Consider the users from the following 3 different categories. a) student b) teaching staff c) managing staff You need to analyse at least one use case for each category of users listed above. You should put your answers to the above questions. II. (1 mark) SQL where condition, order by, select, alter. a) Again, work on the room table created in the last week with the records as follows:

b) Select the distinct value of guestNo in the room relation. c) Select the rooms not in the hotel Hilton. d) Select the rooms not in the hotel Hilton and whose number is not 333. e) Select the rooms not in the hotel Hilton or whose number is not 333. f) Select the rooms in the hotel Hilton and order the results by prices. g) Select the rooms in the hotel Hilton and order the results by prices in a descendent order. Page 4 h) Select the rooms with a price greater than $100, and order the results firstly by hotel name in an ascendant order, and secondly by price in a descendent order. i) Add a column floor to the room table. j) Drop the column floor in the room table. k) Drop the column hotelNo in the room table. What happened? Why? l) Change the data type of the column price into some numerical data type with decimals. m) Construct a new relation guest with the columns (guestNo, guestname, address) without setting guestNo as the primary key. n) Drop the column guestNo and re-add it. o) Make guestNo as the primary key of the guest table. What happened? Why? p) Modify the column guestNo so that it cannot be null. Re-do step o). q) Drop the column guestNo and re-add it. What happened? Why? r) For the room table, add a constraint stating that guestNo is a foreign key from the guest number. What happened? Why? s) Insert the following values to the guest table. INSERT INTO guest VALUES (12345,'James Bond','London'); INSERT INTO guest VALUES (54321,'Lady Gaga',null); INSERT INTO guest VALUES (11111,'Super Man','Mars'); t) For the room table, add a constraint stating that guestNo is a foreign key from the guest table. u) Delete the guest James Bond from the guest table. What happened? Why? Note: for comparison purposes, youd better keep all records of your SQL statements (simply save them in a separate .txt or .doc file). Page 5 Resources: You might find some of the following resources useful: 1. Wikipedia http://www.wikipedia.org/ 2. W3C school SQL tutorials http://www.w3schools.com/sql/

New Query Options Results Messages questio roomNo