Department of Computers & Technology
COMP 420 – Database Systems
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.
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.
3. Define the following related concepts. Use an example(s) to convey your message and include a diagram. a. Parent/child entities