Total Length: 3480 words ( 12 double-spaced pages)
Total Sources: 6
Page 1 of 12
Database Design for Spa/Hotel Booking System
The WR (Woodlands Retreat) is a medium sized organization currently intending to build a database system to integrate modern IT (information systems) in its business operation to enhance its dynamic and innovative business operations. Although, the Woodlands Retreat has an IT system, however, the current systems are not capable of keeping book records as well as not capable of managing all data for the company new added facilities.
Objective of this project is to build a relational database for the Woodlands Retreat using a SQL database methodology. The project provides the important assumption, and business constraints related to the project.
: Assumptions and Business Constraints
The document provides the assumption and business constraints on the baseline up which the project will be accepted. The methodology used to complete the project is SQL methodology. Moreover, the database will be designed to enforce the restriction and business rule in order to prevent double booking and over scheduling as well as enhancing effective administration.
The project provides the following assumption for the database design:
Stakeholders will not modify the project scope,
Availability of a hardware and software platform,
The hardware will be functional to enhance effective implementation of the database.
The project should be completed by the specified date and must be in line with the specified database technology.
The entire document used to design the database will be in English, and translation could be implemented only after the acceptance of English version document.
Moreover, the project must follow the following constraints in the database design.
The database will be designed in such as way that it will:
allow only one guest or group to book a venue at a time, mandate guests to pay a deposit for each booking before transaction is approved, only allow guests to book an event or venue for maximum 5 days a week, only allow a restaurant to have no more than 6 different menus.
Allow a minimum of two nights stay per booking and maximum of 7 nights per booking,
Allow "a maximum of 10 spa sessions per booking."
Additionally, the employee ID should be unique and birthday information must be in a format such as MM/DD/YEAR. (Hernandez, 2003). Moreover, each customer should have his or her unique ID different from the ID of other customer.
Moreover, a guest could only make a reservation for a period and events could only be done for private function such as birthday parties, weddings or corporate functions. Staff could be assigned specific tasks required by the guests. The client is required to have a single invoice when checking out and the system will keep tracks of staff hours in order to generate rotas.
2: Design of Enhanced Entity Relation Diagram
This section provides the EERD (Enhanced Entity Relation Diagram) for the database of the Woodlands Retreat. Enhanced entity relation model is a conceptual data model used to design a database system. The EER model is used to create an accurate database system by using database schema to simplify more complex database application system. (Stephens, & Plew, 2001). Typically, the EER model includes superclasses, primary keys, aggregation and composition in the entity relation diagram. The EER model also includes specialization, and generalization in the database design. (Navathe, 2011).
Using the ERR model, the project provides the enhance entity relations diagram for Woodlands Retreat. Identification of all entities is very critical for the enhanced entity relation diagram. Fig 1 provides the enhanced entity relation diagram for the Woodlands Retreat. The entities for the Woodlands Retreat Database are as follows:
Woodland Retreat
Clients
Accommodation
Restaurants
Moonlight
Gym facilities
Booking
Gold Suites
Employee
Payment
Rooms, and Events.
Fig 1: Enhanced Entity Relation Diagram for the Woodlands Retreat
3:Physical Design Tables
Column
Primary
Key
Foreign
Key
Alternate
Key
Data type & size
Allowable
value
Description
Derived
Data
Notes/
assumpt
Wooodland_Retreat
Wooodland_Retreat
Hotel
Address
VARCHAR (20)
10
Woodland retreat info
Address
Complete
Client
Client_ID
Client_Name
Email_ID
VARCHAR (20)
12
Set of Customer ID
E-mail ID
Complete
Employee
Employee_ID
Job_Title
Email_ID
VARCHAR (20)
8
Set of Employee ID
Email_ID
Complete
Booking
Booking _ID
Booking_reference
Customer_ID
VARCHAR (20)
7
Set of Booking Info
Customer_ID
Complete
Accommodation
Accommodation_ID
Accomodation_reference
Customer_ID
VARCHAR (20)
9
Set of Accommodation Info
Customer_ID
Complete
Restaurants
Restaurants_ID
Restaurants_reference
Wooodland_Retreat
VARCHAR (20)
11
Set of Restaurants
Wooodland_Retreat
Complete
Room
Room_ID
Room_Reference
Wooodland_Retreat
VARCHAR (20)
15
Set of Room
Wooodland_Retreat
Complete
Event
Event_ID
Event_Reference
Wooodland_Retreat
VARCHAR (20
13
Set of Event
Wooodland_Retreat
Complete
Gold Suite
Gold_Suite_ID
Gold_Suite _Reference
Wooodland_Retreat
VARCHAR (20
12 Set of Gold_Suite
Wooodland_Retreat
Complete
Restaurants
Restaurants_id
Restaurants_reference
Wooodland_Retreat
VARCHAR (20
13
Set of restaurants
Wooodland_Retreat
Complete
Menu
Menu_id
Menu_reference
Wooodland_Retreat
VARCHAR (20
12
Set of menu
Wooodland_Retreat
Complete
4: Physical Index Design of the Woodlands Retreat
Index is an object used to speed data retrieval process from the database system.For example, when searching for client name, index will assist the management to retrieve clients using their names. In the database index, the clients name will be stored alphabetically, and the row in the index will reveal the corresponding rows in the table.
Typically, index is used to increase the performances of data retrieval from the relational database. Moreover, index is used to enforce value uniqueness. Essentially, database spends lots of time to find data, thus, index assists in finding data as fast as possible. A crucial aspect of database management is index and is used to speed information and data search. Different types of indexes are:
B-Tree indexes,
Reverse-key indexes,
Bit-map indexes,
Hash indexes,
Index-Organized Tables.
Generally, indexes translate key values into a row ID. Moreover, index reduces the cost of obtaining data from the database. The paper analyses these index one by one to choose the best index to be used for the proposal.
B-Tree Index stores key values sequentially in branch block, and root block. Harsh-cluster index uses algorithms technique to determine the data to read. On the other hand, Bitmap index use a bit (0 or 1) to search key value.
The project selects B-Tree Indexes and Index-Organized Tables for the index strategy, and index-organized table stores index in B-Tree index structure. The B-Tree Indexes are supported by Oracle. Moreover, B-Tree allows searches in sequential access. Typically, B-Tree assists in optimizing systems to read and write a large block of data. Moreover, B-Tree assists in sorting data in a sequential traversing and provides an elegant recursive algorithm. Additionally, B-Tree minimizes wastes using an arbitrary insertions and deletions of numbers. Unlike Bitmap index that could only provide performance improvement on relatively few values, however, B-Tree index is able to provide performance search with large values. Moreover, maintenance of B-Tree index is cost effective than Bitmap indexes because maintenance of Bitmap is more expensive than maintenance of B-Tree. Thus, B-tree indexes are the best compared to other type of index types.
The index-organized tables (IOT) assist in store columns in logical primary key order. Benefits of index-organized table are that IOT index store columns in tables and allow faster access to primary keys. Moreover, IOT places all indexes in logical rowids.
Question 5: Database Queries for the Woodlands Retreat
This section provides the queries for the database design for the Woodland Retreats. The project creates the Table WOODLAND RETREATS, CLIENT, EMPLOYEE, BOOKING, EVENT, SUITES and SPA FACILITIES.
and PAYMENTS. The SQL queries for the tables are presented in Appendix 1 and Appendix 2 provides SQL output screen for the database of Woodlands Retreat.
The project creates CLIENT table because the client is very important for the database of the Woodlands Retreat. The clients' details are very important in the database because the clients order for the booking of the restaurants, events and company facilities. By storing the client's details, the company will be able track loyal customer and understand major attributes of loyal customers. The company will also be able to use clients' information for marketing campaign.
Moreover, the project creates EMPLOYEE table because employees are the important stakeholders of the company. Employees are responsible for recording the booking, events and record payments made by clients. The database also creates BOOKING table because clients will need to make booking for the hotel rooms and other facilities such as events, gym activities, and spa-treatments.
The project also creates table for EVENT because the events are important business activities of the Woodland Retreats. The company offers different events such as corporate functions, wedding and birthday parties, which generate important revenue for the company.
Finally, the project creates PAYMENT table because the company will need to records all the payment collected from customer. The payment data in the database will assist the company to identify the business activities that generate more revenue.
6: Database Security System
Database security is very critical to protect Woodland database from being corrupted.
Woodland Retreats might face both internal external threats on its database. Woodland might face internal threats from internal penetrators who may decide to do harm to a database. For example, disgruntled employees may decide to do harm to a database….....