Sure Guru Holidays: A Case Brief for a Travel Agent System

The System Requirements

Sure Guru Holidays is a new travel agent starting up in the UK with one branch already open and plans to open several more across the UK. They specialise in allowing the holidaymakers to choose their flights, hotels and tours to create a tailor-made personalised holiday. Holidaymakers can book a separate flight, hotel package and any of the tours they would like to take.

Sure Guru Holidays also offer package deals with pre-defined flights and hotels and these include all the tours for that particular holiday. Sure Guru Holidays differentiate between the two in their office as Package and Bespoke. Sure Guru Holidays can offer much cheaper holidays than other providers, as they do not have to organise the details of the holiday (see below).

Sure Guru Holidays requires a system that records all the details about the Customers (Holidaymakers), the Flights available, the Hotels they use (and their capacity), and the Tours that are available.

Name

Address

Contact information

Holiday dates

Hotel name

Location

Address

Facilities

Capacity

Number of rooms available

Types of rooms

Room costs

Flight number

Destination airport

Time of departure

Time of arrival

Make of airplane

Total number of seats

Available number of seats

Flight cost

Tour name

Location

Start time

End time

Tour details

Capacity

Number of spaces available

Tour cost
 

These are the key data that Sure Guru Holidays have provided and said they need, but it does not include additional entities and/or attributes that you may discover that are required.

With the Flights, Hotels and Tours it is not Sure Guru Holidays’ responsibility to book the actual room or actual seats – that is for the Airlines, Hotels and Tour Guides to do. Therefore all Sure Guru Holidays needs to do is ensure that the airline / hotel / tour operator knows the number of guests they are expecting from each booking. They must also ensure that no hotel or flight has more guests than they have capacity for and no tour is overbooked.

All of Sure Guru Holiday’s bookings are weekly. Holidaymakers can book more than one week but they cannot book part of a week. Obviously flights must be booked to coincide with the check in and check out times.

Once a customer has confirmed their details and paid Sure Guru Holidays the company sends out an email confirmation to the customer along with E-Tickets and vouchers for the hotel and tours. Currently Sure Guru Holidays requires a private system to link all their staff to a central database. However, the manager is keen to potentially develop the system further into a public Web-based system where customers can use a portal to search for their own holiday and book it online. However initially the focus is on creating a robust and secure private system.

Holiday bookings for customers (in whatever format the customer requires)

Checking flight details

Booking customers onto flights

Sending information through to hotels and tour guides

Recording payments made by customers

Checking & confirming prices

Daily, weekly and monthly sales figures

Turnover

Holidaymaker preferences

Marketing

However, as this is a new system the management, who have given the initial requirements, are willing to be guided by the analyst /designer of this new system. They are also aware that they have not provided a full requirements list (particularly the non-functional requirements) and expect to provide more details.

In this scenario you will have to make assumptions about these requirements (you may want to discuss these with your tutor) and these should be detailed in your report.

The management have specifically stated that they do not need the system to record any information about their staff. The system should record that customers have made payment for their holidays but it does Not hold payment information such as credit card numbers.

1a. Draw an Entity Relationship Model for the Sure Guru Holiday case study above. Explain any assumptions you have made.

The ER diagram, list of assumptions, Data dictionary showing entity, attributes, data type, data size, keys and constraints (40%)

b.Show all the DDL used in the creation of all the tables.

A copy of the final generated DDL script (no need to hand in any intermediate scripts you may have generated). Highlight any constraints you have added to the tables.

Include the ER Diagram in the main body of your document and DDL script.

Comment on any changes you made to the ER Diagram before mapping the final DDL script. Discuss why you had to make these changes (1 page maximum) (15%)

c. Create and store appropriate test data in the database. Note, use full 4 digit years for any date fields (i.e., ’01-JAN-2018’ rather than ’01-JAN-18’)  (5%)

2. Populate a working database for the Sure Guru Holiday case study. Write FIVE SQL queries that reflect the needs of the business. These queries should include some of the more complex SQL syntax that will involve combining two or more tables together.