Hotel Resevation System - Database Project

Hotel Reservation system

Phase (1): Requirements Analysis Phase (2): Entity Relationship Diagram (ERD)

Presented By: Ahmed Mostafa Abdel-aal Amr Mestkawy Ramy Hefnawy Zakaria Mohamed badr Senousy

Supervised By: Dr. Ayman Taha

Hotel Reservation system Description

Hotel Reservation System (HRS) online system to manage business process cycle of hotel management. Hotel Reservation System contains five modu les rooms’ management, customer reservation management, supplies management, accounting Module and employee management modules. HRS allow users to perform the following functionalities: employees’ check-ins, check-outs, reservations, employee information management, and product and supplier information management. Reporting will facilitate to management level to review and check work progress, review status of rooms and cash management. Objectives Main objectives of System to manage the following activities:

1. 2. 3. 4. 5. 6. 7. 8.

Rooms status / service cycle Admission of a new customer Assign a room according to customer’s reservations Reservations confirmation (Check In / Check out) Suppliers and Products management Employee’s managements Payables / Receivables management (balance) Generate Reports

Scope of work The scope of project to build online hotel reservation system that can maintain room’s reservations, employee’s details, products details, accounts payable and receivable. In this project, we will keep track of information of employees working in the hotel. Functional Requirements: Room’s management Module: This module provides the management facility of rooms. It would allow to: add a new room edit an existing room View room’s status   

Reservation Module: This module allows the employee to manage reservations. It would allow to: make a new reservation (check in, assuming that the customer makes the reservation at the same time he/she checks in) Check out a customer Find all reservations by a customer 

 

Employee Management Module: This module allows to manage the employee information. It would allow to: Password verification add a new employee edit an existing employee Different views for receptionist and manager    

Accounting Module: This module keeps track of all the accounting information regarding the hotel. It would consist of: Generate customer invoice Calculate daily customers payment Generate supplier invoices Calculate total payables    

Supplies Management Module: This module keeps track of inventory. It would allow to: add a new supplier edit an existing supplier add a new product edit an existing product    

Non – Functional Requirements:

Some other objectives are: • No data duplication • No Paper Work Required • Time Efficient • Cost Efficient • Automatic data validation • User friendly environment • Data security and reliability • Fast data insertion & retrieval Users/Roles of the System Managers: The manager would have special privileges. He will be able to: add a new employee edit an existing employee  

 Receptionist: The receptionist will have access to the Room management module, Product management module and Reservation module. He will be able to perform the following operations:      

add a new room edit an existing room add a new product edit an existing product check in a customer check out a customer

Room Services: The room service would be responsible to maintain rooms. Forms and Reports Forms        

Employee Details (add and edit) Supplier Details (add and edit) Rooms Details (add and edit) Products Details (add and edit) Suppliers Details (add and edit) Reservation/Check in / Check out Receivable Details Payable Details

Reports      

Balance Statement Reports Customer Statement Employee Details Supplier Details Rooms Status Reservation/Check in / Check out History

Tools: 1- ASP. Net 2- C# 3- SQL Server 

 Entity Relationship Diagram

Tables Description

Table Name: CUSTOMER Primary Key: Customer ID Purpose: To store the customer details. Column Name Customer ID  Name Address Phone Cell

Data Type and Domain int (4) varchar (100) varchar (100) int(10) int(11)

 Null Constraint Not Null Not Null Null Null Null

Key Constraint Primary Key

Table Name: EMPLOYEE Primary Key: Employee ID Purpose: To store the employee detail. Column Name Employee ID  Name Manager ID Address Phone

Data Type and Domain int (4) varchar (100) int (4) varchar (100) int(10)

 Null Constraint Not Null Not Null Not Null Not Null Not Null

Key Constraint Primary Key

Application based Constraint

Foreign Key

Table Name: SUPPLIER Primary Key: Supplier ID Purpose: To store the information of each supplier. Column Name Supplier ID  Name Address Phone

Data Type and Domain int (4) varchar (100) varchar (100) int(10)

 Null Constraint Not Null Not Null Null Null

Key Constraint Primary Key

Application based Constraint

Table Name: PRODUCT Primary Key: Product ID Purpose: To store the information of each product.

Product ID  Name Supplier ID

Data Type and Domain int (4) varchar (100) varchar (100)

 Null Constraint Not Null Not Null Not Null

Price

int(10)

Not Null

Description

varchar (100)

Null

Quantity

long(8)

Not Null

Column Name

Key Constraint Primary Key

Application based Constraint

Foreign Key Price not less then 0 or greater then 100,000 Quantity not less then 0 or greater then 100,000

Table Name: ROOM Primary Key: Room ID Purpose: To store the information of each room. Column Name Room ID Manager ID Price Maintenance ID

Data Type and Domain int (4) int (4) int (10)

 Null Constraint Not Null Not Null Not Null

Key Constraint Primary Key Foreign Key

int (4)

Not Null

Foreign Key

Application based Constraint

Table Name: ROOM-TYPE Primary Key: Room ID, Room Type Purpose: To store the room type against each room id. Column Name Room ID Room Type

Data Type and Domain int (4) varchar (100)

 Null Constraint Not Null Not Null

Key Constraint Primary Key Primary Key

Application based Constraint

Table Name: RESERVATION Primary Key: Customer ID, Room ID Purpose: To store the reservation information.

Customer ID Room ID Employee ID

Data Type and Domain int (4) int (4) int (4)

 Null Constraint Not Null Not Null Not Null

Start Date

Date

Not Null

End Date

Date

Not Null

Charges

int(10)

Not Null

Column Name

Key Constraint Primary Key Primary Key Foreign Key

Application based Constraint

Date must be in DD-MM-YYYY format Date must be in DD-MM-YYYY format Charges not less then 0

Table Name: Accounts Payable Primary Key: ID Purpose: To keep a record of the accounts payable.

ID Amount

Data Type and Domain int (4) int (10)

 Null Constraint Not Null Not Null

Date

Date

Not Null

EmployeeID

int (4)

Not Null

Column Name

Key Constraint Primary Key

Application based Constraint

Amount not less than 0 Date must be in DD-MM-YYYY format Foreign Key

Table Name: Accounts Receivable Primary Key: ID Purpose: To keep a record of the accounts receivable.

ID Amount

Data Type and Domain int (4) int (10)

 Null Constraint Not Null Not Null

Date

Date

Not Null

Column Name

Key Constraint Primary Key

Application based Constraint

Amount not less than 0 Date must be in DD-MM-YYYY format

Table Name: SUPPLIER-PRODUCT Primary Key: Transaction ID, Product ID Purpose: To keep a record of which supplier supplies a product.

Transaction ID Product ID Supplier ID

Data Type and Domain int (4) int (4) int (4)

 Null Constraint Not Null Not Null Not Null

Date

Date

Not Null

Amount

int(10)

Not Null

Column Name

Key Constraint Primary Key Primary Key Foreign Key

Application based Constraint

Date must be in DD-MM-YYYY format Charges not less then 0

Table Name: ROOM-PRODUCT Primary Key: Room ID, Product ID Purpose: To keep a record of which product is used in which room. Column Name Room ID Product ID

Data Type and Domain int (4) int (4)

 Null Constraint Not Null Not Null

Key Constraint Primary Key Primary Key

Application based Constraint