Hotel Assignment

Topics: Database normalization, Relational model, Data modeling Pages: 4 (732 words) Published: March 18, 2013
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...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • “the Traditional International Assignment Is No Longer the Preferred Choice for Multinational Corporations (Mncs) in Staffing Their Global...
  • Hotel Reservation and Billing System Essay
  • Essay about PT 1420 Assignment 3
  • Assignment Ethics Essay
  • Week 5 Assignments New Essay
  • 5 Written Assignment 5 Unit 5001V1 Revision 1 Essay
  • hotel reserve Essay
  • Essay about Hotel Reservation

Become a StudyMode Member

Sign Up - It's Free