Database Design for SPA Hotel Booking System

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 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 can decrypt and read the data.

The Woodland Retreats should also use certificates for the secured database.

"Part of setting up a secure Web site is obtaining an electronic site credential called a certificate from a trusted third-party company called a certifying authority. A certifying authority will validate that you are a legitimate organization before providing you with a certificate. Your users' communication sessions then access your certificate as an initial authentication step as they contact your database over the Web. SSL also validates data integrity at both ends of the communication." (Navathe, 2011. P. 403)..

Thus, Woodland Retreats must integrate security systems in the database to protect a database from both internal and external penetrators. The project will integrate security systems to the database of Woodland Retreats against both internal and external penetrators.

The project will integrate security provisions such as SSL and certificates to protect the database system from external party penetrators. To protect the database system against internal penetrators, Woodlands Retreat should define category of people who would have access to the database, and the level of access of each user should be different. For example, the level of access given to the company DBA (database administrator) should be different from the access given to ordinary company employee. The company should only grant the level access to each employee to perform the necessary job functions. Typically, the company should allow only trusted employee to have access to sensitive data such as SSN (social security number), credit card information, bank information and other classified sensitive information.

More importantly, the Woodlands Retreat should make a policy for database users to use a combination of a database user ID and strong password in order to have access to the data in the database. The passwords should include combination of alphabets, numeric, and special characters. The database should lock account of a user after a number of failed login.

The company should also use database privileges to control access to the database. For example, the company should use system privileges to control the tasks performing within the scope of the database. Typically, the company should specify a user responsible to have a privilege of creating table, altering the structure of a table, and manipulate user accounts. Moreover, the company should define users allowed to retrieve data, and manipulate data from the database. Thus, the paper suggests that the system privileges should be granted to database administrator while the object privileges should be granted to object owners.

The paper also suggests that the Woodlands Retreat should employ technical and management staff who will be in charge of the database security system. The technical staff should include network administrators, system administrator and database administrators. The company should set up three basic security management for the database systems, which include:

System level management,

Database level management, and Application level management.

The Woodlands Retreat should also employ system administrator who will manage host computer for the database. The system administrator should be in charge of protecting the physical data file from accidental delete or overwritten. Moreover, both system administrator and database administrator must control access to physical data file. Typically, the core program of the database should be controlled by one administrator account.

Physical security is also vital for database system. The Woodlands Retreat should restrict access to computer room and the company should use good locks for the computer rooms to deter unauthorized access. If there are sensitive data stored in the database, the company should employ an armed guard who will restrict access to the computer room. Moreover, the database export file should be thoroughly secured and the files should have backups in case of accidental loss.

Woodlands Retreat should also protect database from external penetrators especially from hackers who may put database file at risks. Hackers could filter network packet to have access to classified data or password. The company should install network firewall, intrusion detection system (IDS) and instruction prevention system (IPS) to enhance maximum security of the database system.

The network firewall is a defensive mechanism against hackers. Properly installed firewall will protect the Woodlands Retreat network against external penetrators. The network firewall filters outgoing and incoming TCP/IP data packets, and the network router should be design in such a way to integrate allowed and disallowed IP addresses. (Connolly & Carolyn, 2010).

The company could also use facilities such as biometric authentication of user using fingerprint method. Security cards could also be included in the database system to enhance login authentication.

Woodland Retreats should also use the following proactive measures to protect the database and prevent security breaches:

Regularly change the administrator passwords,

Frequently change users password,

Discouraging users to share passwords,

Removing inactive user accounts,

Removing user accounts of non-employee,

Performing random monitoring of all database activities,

Regularly perform database auditing.

7: Architecture

This section discusses the possibility of upgrading and updating the Woodlands Retreat database to web-based data based (WBDB) technology. A web-based database generally relies on internet server to function. The company will utilize passwords to have access to database through web browser. Typically, by upgrading to web-based database, Woodlands Retreat will be able to update the company prices for its services as well as providing maintenance of user and client details. (Caprita, & Mazilescu, 2005). Typically, WBDB involves different technologies such as Web browser, Web Server, JAVA, and application server.

This paper suggests that Woodlands Retreat may upgrade to web-based database technology using two-tier WBDB architecture. The minimal configuration for the two-tier architecture involves lightweight application with technologies that include JBDC, XML and SQL.

The company can also use database architecture such as JavaScript client-side scripts and PHP server-side scripts. (Hellerstein, Stonebraker, & Hamilton, 2007). The system will run on MySQL database server, Windows Apache Web Server with PHP installed. The project has chosen PHP because it could run on different operating systems, which include, Microsoft Windows, Linux and UNIX versions. The strengths of the new system are as follows:

High performance;

Portability;

Low cost;

Interfaces to different database systems;

Built-in libraries to be used for many Web tasks;

Ease method of learning and use;

Source code availability.

Despite the benefits that the company could derive from the web-based database system, the security issue is the major challenges in managing web-based database. Hackers generally attack database systems of organizations based on the assumption that skilled database security experts are scarce. Typically, hackers could attack web-based database of a company remotely using several techniques such as network filtering and network sniffing. Since web-based database systems require internet connection to function, penetrators could attack organizational network system remotely. The Woodland Retreats need to integrate effective security system when upgrading to web-based database system. The company should exclude some countries such as Asia and Latin America from having accessing to the company's IP address because these countries are marked as high risked countries.