- Staffs have to find holidays that are suitable for the customer, then photocopy the files and then give it to the customer to take away with them. This is too long.
- If a customer requests available flights for a certain country, every flight available is looked through and the flights to the certain destination are given to the customer. This takes too long.
- Each letter is written up to be sent to customers which is inefficient
I have spoken to Michael Long and he has given me these desires:
- The details of the customers, destinations, hotels and bookings need to be able to be accessed, searched, edited, created and deleted easily and quickly.
- The customers born before 01/01/1955 needs to be known to send them special offers
- He wants to enter a location name and see all the customers he has in that location
- He wants a list of all the flights which shows the airline and any other relevant information
- He needs to be able to print a destination, hotel and customers list
- An easier and more efficient way of sending letters need to be made
- The database must have some sort of security system to stop unauthorised people from accessing the data
- The system must be easy to use
- He shouldn’t need to write out the customer, hotel and destination details every time there is a booking
- The system should have the company name and logo on it
- The database should have a consistent and professional design
- He needs to access all the flights available for a certain country quickly
- He needs to access all the hotels in a certain country quickly
- His signature needs to be on the letters to the customers
Possible ways of tackling the problem:
One way of solving the problem is making a spreadsheet based on the things the travel agency needs by using a software package. The spreadsheet could include all the customers’ details on one sheet and then all the destinations on another sheet and so on. This would minimise the time taken to book flights because everything is on the computer. The staff wouldn’t need to go looking through the cabinets because everything would be in front of them. However, the spreadsheet wouldn’t be able to run queries to allow the staff to see certain types of information. For example, if the staff wanted to see destinations in a certain price range, they would have to scroll down and look for it individually. If all the destinations were given in ascending price order, then the staff will just have to scroll down until they reach the minimum price and take all the destinations up to the maximum price. Also, the spreadsheet will not be linked so bookings will be made by typing in the cells. The spreadsheet is a reusable solution because all the data can be deleted and sorted when ever it is needed to. This will keep the system always up to date and available.
Another way of tackling the problem is by creating a database using a software package. In a relational database, when data on one table is changed, the same data on other tables and forms is also changed, therefore, they are reusable. The database could be set up to store information on destinations and customers etc. and be able to search selected ones. The end user, the staff of Mr Long’s business, will also be able to run queries to pull up certain pieces of information. They can be adjusted so that wanted information will come up; therefore, the queries are a reusable aspect. They will also be able to add, delete and sort out all the records. This solution can be used over and over again unlike hand written files that have to be thrown away and written up again whenever there is a new change. The data in the database will just have to be altered whenever there is a change and have the changes saved so the tables are reusable.
Alternatively, Mr Long could hire a new member of staff who will be in charge of sorting out the files. The person must be qualified and dedicated to their job and very organised to keep all the files in order. However, this wouldn’t really solve the problem because the business will still be using the old system and just hiring another person to take care of it. This is just wasting money. Also, it doesn’t solve the problem of the filing cabinets taking up space because they will still be there as the files still need to be stored there.
Solution:
I have chosen to make a database using a software package to solve the problems Lee’s Holidays currently faces. This is because spreadsheets are more suitable for solving problems that involve calculations using formulas which isn’t needed with this business; therefore, making a spreadsheet isn’t the best solution. What is needed is a system that will keep everything in order and be able to be accessed easily and quickly. This is the reason why I think creating a database using Microsoft Access is more suitable for the business. The user would want to use this method because it has many benefits over the other methods.
I will also need to do mail merged letters for Mr Long to send to his customers for promotional offers and customer loyalty. To do this, I will use Microsoft word.
With Microsoft Access, I will be able to store all of the data into one database. This will save space unlike all of the cabinets Mr Long’s business currently uses. Also, whenever certain information is needed, the user can just run a query to get want they want. Backup copies of the system can easily be made so in the case of the system crashing or something wrong with the computer, he can retrieve the data from the backup files.
Performance Criteria:
The database system:
- Hotels table must include the fields: Hotel ID, Name, Address, Country and Hotel Grade
- Needs to be able to search, sort, edit, delete, create and access records data quickly
- Customers table must include the fields: Customer ID, First Name, Surname, Address, Telephone, DOB and Area
- Needs to be able to store data in 4 different tables – Customers table, Hotels table, Destinations table and Bookings table
- Tables need to be linked so that it is a relational database
- Destinations form must include the fields: Destination ID, Destination, Airline, and Airport
- Needs to be able to make queries and specify the criteria for the queries and alter the criteria
- Bookings table must include the fields: Bookings ID, Departure Date, Departure Time, Arrival Date, Arrival Time, Customer ID, Destination ID and Hotel ID
- Must be efficient and able to be reused over many years
- Bookings form needs to be a sub-form within a form
- Forms must have buttons for ease with navigation and operation
- Needs to be able to make reports
- Needs to have a validation on the Customers table for the DOB field – not allowing future dates to be inputted to minimise mistakes
- Needs to have a validation on the Bookings table for the Departure Date field – not allowing past dates to be inputted to minimise mistakes
- Need to have automated processes wherever possible.
The mail merge letters:
- Need to include the date
- Need to include name of business, address, owner, telephone number and fax number
- Needs to include the fields: Customer ID, First Name, Surname, Address, Area and Telephone.
- Must be formal
- Must include Mr. Long’s signature
- Need to include the business logo
Form of Output:
The form of output will be on screen viewing because the staff will need to look at the information on the screen about customer details, destinations etc. Also, print outs of destinations the customer is interested in will need to be made so the customer can take them away and decide which one they want to go to. Furthermore, booking confirmation will also need to be printed out and given to customers. Additionally, mail merged letters will need to be made.
Information to be output:
- Mail merged letters
- Details of customers (Address, name etc.)
- Destinations available with details (Airline companies etc.)
- Hotels available with details (Country, grade etc.)
- Bookings
- Print out of reports
Data needed to produce output:
I will go through each piece of information in the filing cabinet and put it into the database system that I am going to make. I will put it into tables as I go along.
- Address from the holiday guide
- Addresses of hotels from brochures/internet
- Descriptions of holidays from the travel brochures the business uses
- Details of the current customers Lee’s Holidays have in their filing cabinets.
- The company logo from Mr. Long
Testing:
I will need to test the database to see if it works properly or not and if it doesn’t; I will need to make a few adjustments. The buttons will need to be checked to see if they work. The validation should work with no problems. The mail merge letters should also give the correct information. All the results of the query need to be merged into the letters.