top-rated free essay

Hotel Assignment

By dajLouis1 Mar 18, 2013 732 Words
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]

Cite This Document

Related Documents

  • Hotel Reservation and Billing System

    ...Major System Features and Functions The system will automate many of the manual procedures that are being used by management, front desk, and room services at the Hotel Computer Science. After preliminary analysis of the requirement specifications a system with the following functions will best satisfy the needs of the hotel management and st...

    Read More
  • “the Traditional International Assignment Is No Longer the Preferred Choice for Multinational Corporations (Mncs) in Staffing Their Global Operations.” Discuss This Statement and Outline and Critique Two Alternatives to Traditional Internatio...

    ...• The explosive growth of the e-business/knowledge sectors, coupled with rapid developments in communication technology and the evolution of business relationships to include more international joint ventures, have both enabled and fuelled a greater need for movement of individuals across the globe. • In the wake of globalisation wherein k...

    Read More
  • Assignment Ethics

    ...[pic] ASSIGNMENT SUBMISSION AND ASSESSMENT _________________________________________________________________________ OUMM3203 PROFESSIONAL ETHICS (PLEASE EDIT WHERE NECESSARY-THIS IS ONLY SUGGESTIONS FOR THE ASSIGNMENT’S GENERAL INSTRUCTIONS) _________________________________________________________________________ INSTRUCTIONS TO S...

    Read More
  • PT 1420 Assignment 3

    ... Short answers 5. What two things must you normally specify in a variable declaration? You must specify the variable type and an identifier. int n; // type - int, identifier - n 6. What value is stored in uninitialized variables? is a variable that is declared but is not set to a definite known value before it is used. Algorithm workbenc...

    Read More
  • Week 5 Assignments New

    ...PT1420 - Unit 5 Homework and Lab Assignment Unit 5 Assignment 1: Homework 1.) Design an if-then statement that assigns 20 to the variable y and assigns 40 to the variable z if the variable x is greater than 100. (Simple if statement) If x > 100 then Y=20 Z=40 End if 2.) Design an if-then statement that assigns 0 to the variable ...

    Read More
  • 5 Written Assignment 5 Unit 5001V1 Revision 1

    ... Chartered Management Institute Approved Centre Level 5 Extended Diploma in Management and Leadership Written Assessment – 5 Unit 5001V1 Instruction Sheet Assignment: Unit 5001V1 – Personal development as a manager and leader Level: 5 Lecturer: ...

    Read More
  • Hotel Reservation

    ...anticipate the time of completion. By a clearly defined scope, specified time will be assigning to develop each module in the application. From here can conclude that, virtual tour on hotel room needing more time in completion. By well-organized task and duration, the application will develop on time. PARADIGM Waterfall paradigm is importan...

    Read More
  • hotel reserve

    ...HOTEL PAYMENT SYSTEM FOR GRAND VILLA HOTEL A Thesis Presented to the Faculty of the College of Computer Studies, AMA Computer College Pasig Campus In Partial Fulfilment of the Requirements for the Degree of Bachelor of Science in Information Technology Gab,Espiritu.F Alarcon, Isaro A. School year/Trimester: 2013 – 2014 / S...

    Read More

Discover the Best Free Essays on StudyMode

Conquer writer's block once and for all.

High Quality Essays

Our library contains thousands of carefully selected free research papers and essays.

Popular Topics

No matter the topic you're researching, chances are we have it covered.