Tour Operator Agency Database

Strayer University
Strategic Planning for DBase Systems
CIS515

February 13, 2016

Tour Operator Agency Database
The International Tour Agency is currently exhibiting signs of massive growth. The expected growth this year will be about 50%. The organization started as a small company and although the growth is exciting they are able to see that this will impact the way they are used to doing business. Their database is completely unnormalized which makes entering data easy but running reports and acquiring up to the minute data next to impossible. The most important step is to identify how the data system should work for the end users. After analyzing business needs and identifying the direction that the organization wanted to go in, analyzing the data becomes the next logical step.
The agency’s tables are set up with their own naming conventions that also need to be reviewed and updated. It will need to be simple and accurately reflect the needs of the organization. It needs to be consistent and should not be changed. Plurals should not be used and special characters are not the best option. It can become tedious writing code therefore the more logical and simple it is, the less likely it is for coding mistakes to take place. Now, concentration can be focused on obtaining results rather than sifting through material for errors and inconsistencies. The end users and how they use terminology in their sector should also be addressed. The point is to create a system that works for them. A good option in creating a naming convention would be to avoid using any type of special characters and to try to avoid mixing character types if at all possible at least for table names. (Sarkuni, 2014)
The current structure is set up with a table name of Salespersons and attributes named employee id, first name, last name and “Tours sold” field. There are already certain items that stand out and need to be changed. The capitalization, quotes, and pluralization of the table name and fields should be changed. The other table stores customer data and tours sold. The attributes are customer name, address, city, state, zip code, tour(s) selected, number of persons in tour, and total amount paid. In this table there is redundant data, no Primary Key and spaces in the naming convention. The final portion to be evaluated was a rate sheet that included tours offered and the cost per person. Once the naming convention was updated to ensure compliance with the organization and the database, then the normalization could begin.

In moving the table to second normal form certain aspects of the table have changed like Salespersons to Employees and attributes are reassigned. It is important to design the database in a way that makes the organization scalable in every department. All employees are included this way. The customers were placed in their own chart and the removed attributes were moved to additional charts that also needed more review. The tables to begin with were named Employees, Customers, Orders, and Tours. The pluralization was later removed in the next form. The assigned attributes for Employee were as follows: emp_id, F_name, L_name, phone, address, city, state, and zipcode. The Customer table’s attributes were as follows: cust_id, F_name, L_name, phone, address, city, state, and zipcode. The Tour tables attributes were as follows: tour_id, season, location, amenity, RatePerPerson, and TourDate. The Order tables attributes were order_id, emp_id, cust_id, tour_id, sale_dt,,and TourDate.
While building the tables and inserting the values it may be important to note that they may need to be edited a few more times in order to obtain optimum performance. Next, in order to use the database and ensure it is viable, running a few queries will remove doubts. In the first query we break down a couple of customer orders into daily increments for easier payments. We need to select the paid from orders and divide it into the 45 days requested. The query will show what needs to be paid daily. With additional data requested from multiple table we can also review which customers would be paying and the number of people traveling. This is an excellent way to review if the tables were set up properly and make additional adjustments.

Here is a good example of showing identifying information and slowly building up the query requested. The naming scheme makes sense for the end users, and the reports are quick and efficient.

There will also be times when things go inexplicably wrong and you just need to work on it some more. There is something faulty that needs to be revisited.
In this case the query ended up being deceptively simple, while the simple trigger revealed a flaw. Building a database well takes practice and a lot of it.
Stored procedures are used within the database to enable information to move quickly without taking up the amount of room storing each individual digit would require. It takes up less room, is more efficient, and doesn’t require breaking down the tables in order to use it.The triggers or stored procedures may run applications on different programming schemas, or just keep a basic count of incoming visitors, but either way it is an exciting and an important part of the database and knowing how to implement the procedures is important in todays technological environment.