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

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 when they are fired. Moreover, traitors, and internal nuts may also decide to do harm to a company database.

Thus, Woodland Retreats is required to restrict number of people allowed to database access to maintain database integrity. Typically, integration of security system is also very critical when database is put on the web because a database may be vulnerable for hackers or non-authorized individual intending to steal sensitive data from the database if adequate security systems are not put in place. A non-protected security system is also vulnerable to competitors who may want to steal trade secrets of an organization. Apart from external penetrators,

The project integrates SSL Secure Socket Layer (SSL) as effective database security. The SSL is critical for the protection of internet routers against external penetrators through two-way authentication systems. The SSL also uses the encryption systems using public key encryption. The encryption system protects data from being viewed by an external penetrator by changing data into unreadable form. Only users with pair of public and private key…