Database

HIT234 Database Management Assignment 2

School of Engineering and Information Technology

ASSESSMENT COVER SHEET

Student ID and Name	Awash Siwakoti Basanta Sapkota Sakar Shrestha
Assessment Title	Assignment 2
Unit Number and Title	HIT234 Database concepts
Lecturer/Tutor	Mamoun Alazab
Date Submitted	19/05/2019

Office use only

KEEP A COPY

Please be sure to make a copy of your work. If you have submitted assessment work electronically make sure you have a backup copy.

PLAGIARISM

Plagiarism is the presentation of the work of another without acknowledgement. Students may use a limited amount of information and ideas expressed by others, but this use must be identified by appropriate referencing.

CONSEQUENCES OF PLAGIARISM

Plagiarism is misconduct as defined under the Student Conduct By-Laws. The penalties associated with plagiarism are designed to impose sanctions on offenders that reflect the seriousness of the University’s commitment to academic integrity.

I declare that all material in this assessment is my own work except where there is a clear acknowledgement and reference to the work of others. I have read the University’s Academic and Scientific Misconduct Policy and understand its implications. *

http://www.cdu.edu.au/governance/documents/AcademicandScientificMisconductPolicyv1.03Jan2011.pdf.

Signed: Awash Siwakoti Date 19/05/2019

Basanta Sapkota

Sakar Shrestha

* By submitting this assignment and cover sheet electronically, in whatever form you are deemed to have made the declaration set out above.

HOTEL DATABASE MANAGEMENT SYSTEM

Part A:

Describe the situation. Your description should be brief and relatively informal (maximum length 1 A4 page)

Scenario:

"The owner of a Hilton hotel would like to have a hotel management system built for the hotel. The hotel is located in major cities and tourist attraction areas of Darwin, Australia.

The owner hired an expert in database concept to design the database for his hotel. The Hotel is identified by the Hotel Code, Hotel Name, Hotel Number, Location, Employee ID and Guest ID. In the hotel, employees are hired to perform various duties. So, there are number of employees in the hotel. So, the attributes of the employee's include Employee ID, Name, Date of Birth, Phone and Room category. Employees are tasked to manage rooms based on the Room category attribute assigned by the hotel to the employees.

In the hotel, guests make reservations for rooms of their choice. The reservations of the guest are also recorded in the directory which can be identified as Reservation No, Guest ID, Hotel Code, Room Type and Room ID. The attributes of the Guest are Guest ID, Reservation No, Name, Phone, Address and Room ID. According to the guest choice, they are placed in the different categories of rooms based on Room ID, Room Type, Guest ID, Reservation No and Room category. So, guest are billed according to the type of room they have reserved which can be identified by Invoice No, Guest ID, Room Charge and Miscellaneous Charges.

Business Rules/Assumption

1. One guest makes one reservation.

2. Hotel have many reservations.

3. Hotel can have many guests.

4. Many guests can occupy one room.

5. One room can have more than one guest.

6. One employee manages one room.

7. One room can hold one reservation.

8. Many Employees work in hotel.

9. Guests can make reservation in hotel through which rooms are occupied by guests.

10. Many employees work in hotel who are then assigned by hotel to manage one room.

11. Guests have many links.

12. Guests pay bill for the room they are occupying.

ER DIAGRAM (Using Chen and Crow feet Notation):

Part B: Map the ER diagram to 3NF & Normalisation

Data Schema:

Translate your ER Diagram into a schema of normalised table.

HOTEL

Hotel Code

Employee ID

Hotel Name

Location

Hotel Num

Employee

Employee ID

Name

DOB

Phone

Room Category

Room

Room ID

Room Type

Guest ID

Reservation Num

Room Category

Reservation

Reservation Num

Guest ID

Hotel Code

Room Type

Room ID

Guest

Guest ID

Reservation Num

Name

Phone

Address

Room

Bill

Invoice Num

Guest ID

Room charge

Misc. Charge

Normalization

HOTEL

Hotel Code

Employee ID

Hotel Name

Location

Hotel Num

Employee

Employee ID

Name

DOB

Phone

Room Category

Room

Room ID

Room Type

Guest ID

Reservation Num

Room Category

Reservation

Reservation Num

Guest ID

Hotel Code

Room Type

Room ID

Guest

Guest ID

Reservation Num

Name

Phone

Address

Room

Bill

Invoice Num

Guest ID

Room charge

Misc. Charge

Functional dependencies

Employee ID- (Name, DOB, PHONE)

(NAME, DOB)- Employee ID

Reservation Num- (Guest ID, Room type)

Guest ID- (Room ID)

(Name, phone, Address)- Guest ID

Bill- (Invoice Num, Guest Id)

Invoice Num- (Bill, Guest id)

3NF

· Should be in Second Normal form.

· It should not have any transitive dependency.

HOTEL

Hotel Code

Employee ID

Employee

Employee ID

Name

Room Category

Room

Room ID

Guest ID

Reservation Num

Reservation

Reservation Num

Guest ID

Hotel Code

Room Type

Room ID

Guest

Guest ID

Reservation Num

Room

Bill

Invoice Num

Guest ID

Sakar Shrestha Basanta Sapkota

Awash Siwakoti