Hotel Assignment

School of Arts & Sciences Department of Computers & Technology
COMP 420 – Database Systems Assignment 3

Holiday Hotel has a database system that records data on the hotel rooms, and the guests that use the rooms. A portion of the database schema is given below. Guest personal data are recorded in the Guest table. The room is registered in the name of one guest, even if there are multiple persons sharing the room. Rooms are of various types (double bed, suite, etc.). The cost of the room is dependent on the roomtype. The room cost is calculated as the cost per night times the number of nights (CheckOutDate – CheckInDate). When a new registration is added, the CheckOutDate is left Null and on check out, the CheckOutDate is updated.

GUEST (GuestId, GuestName, Address, Phone)

ROOM (RoomId, RoomTypeId, AvailableStatus)

ROOMTYPE (RoomTypeId, RoomType, CostPerNight)

REGISTRATION (RegId, RoomId, GuestId, CheckInDate, CheckOutDate)

1. For this assignment, draw a detailed ER diagram using UML notation. Write all attributes in the ERD using the format -> attribute: type. Make reasonable assumptions about data types. For the Registration table, explain all constraints that are necessary on the table. [25 marks]

2. Guest 1234 registers and checks in Room R25 on 14-Jan-09 (RegId = 9005).

Carefully explain the referential integrity checks that the DBMS would perform when the insert statement is executed. Explain the entity integrity check and the uniqueness check on the primary key that the DBMS would perform before this insert statement is executed successfully. Be very specific with your answers; explain exactly what tables/columns would be checked and what would happen if the check is successful/unsuccessful. [15 marks]

3. Define the following related concepts. Use an example(s) to convey your message and include a diagram. a. Parent/child entities b. Identifying & non identifying relationships c. Weak / strong entities These do not have to be explained in any order. Only ensure that they are all included in your description. Marks will be given for clarity and organization for this question. [18 marks] 4. Explain the concept of a view in database systems. Give one advantage of using views in database applications. [6 marks]

5. Differentiate between a candidate key and the primary key for a given relation? Also include the explanation of the concept of foreign keys although it would be expected that it would have been discussed in previous questions. [6 marks]

6. Consider the instance of the Students relation shown in the table above.

SID | NAME | LOGIN | AGE | GPA | 50000 | Dave | dave@cs | 19 | 3.3 | 53666 | Jones | jones@cs | 18 | 3.4 | 53688 | Smith | smith@ee | 18 | 3.2 | 53650 | Smith | smith@math | 19 | 3.8 | 53831 | Madayan | madayan@music | 11 | 1.8 | 53832 | Guldu | guldu@music | 12 | 2.0 |
(© Ramakrishnan & Gehrke, 2002) 7. a. Give an example of an attribute (or set of attributes) that you can deduce is not a candidate key, based on this instance being legal? Thoroughly explain your logic for full marks. b. Is there any example of an attribute (or set of attributes) that you can deduce is a candidate key, based on this instance being legal? Thoroughly explain your logic for full marks.

[10 marks]

8. Question 8, 9, & 10 are based on the following table Emp_SSN | Emp_Name | Emp_Rank | Hire_Date | Dept_Code | Dept_Name | Emp_Office | Adv_ID | Adv_Name | Adv_DOB | 078-05-1120 | Thompson Cummings | Professor | 7/1/1990 | CT | Comp. and Tech. | D-1 | 3567 | Ron | 2/23/1985 | 078-05-1120 | Thompson Cummings | Professor | 7/1/1990 | CT | Comp. and Tech. | D-2 | 1480 | Kern | 1/01/1970 | 101-99-2468 | Michael Roberts | Asst. Professor | 8/21/2005 | AH | Arts and Humanities | G-12 | 2811 | Nick | 11/30/1983 | 123-45-6789 | Aleksandr Myllari | Assoc. Professor | 1/13/2012 | BUS | Business | B-4 | 7140 | Shamaul | 12/12/1982 | 123-45-6789 | Aleksandr Myllari | Assoc. Professor | 1/13/2012 | BUS | Business | B-5 | 3334 | Antonio | 1/06/1988 | 789-34-5432 | Keston Bhola | Asst. Professor | 7/1/2010 | CT | Comp. and Tech. | D-2 | 4567 | Karla | 2/28/1987 |

9. Given the following table choose an appropriate primary key. Explain the reasoning behind you choice. [4 marks]

10. Explain all functional dependencies that exist. [15 marks]

11. Normalize the above up to the third normal form (3NF). Explain each the logic for each normal form in the context of the above table. [15 marks]