Database Optimization: Tour Operator Agency Case Study Report

This report presents an enhanced database management strategy for a multinational tour operator agency, addressing the need for improved data handling and long-term scalability. The strategy focuses on database normalization to eliminate data redundancies and ensure logical relationships between entities. The normalization process involves three stages: 1NF, 2NF, and 3NF, applied to the initial un-normalized relations (Salesperson, Customer, and Tour). The report details the steps taken to achieve 3NF, including the elimination of repeating groups, partial dependencies, and transitive dependencies. The final relational model incorporates a camelCase naming convention and includes entities such as Salesperson, Customer, Tour, TourSales, and Payment. An entity-relationship model visually represents the database structure. The report also provides SQL queries for specific tasks, such as determining invoice payment timelines and salesperson performance, and justifies the use of stored procedures for enhanced performance, reduced network traffic, and improved security compared to embedding SQL code directly in applications. The report concludes by referencing sources that support the proposed database management strategy.

COVER PAGE

Contents
Introduction.................................................................................................................................................3
1 Enhanced database management strategy.................................................................................................3
Normalization process.............................................................................................................................3
Naming Convention.................................................................................................................................5
Entity Relationship Model.......................................................................................................................6
2 Queries.....................................................................................................................................................7
3 Reasoning behind stored procedures........................................................................................................7
References...................................................................................................................................................8
Figure 1: Entity relationship model.............................................................................................................6

Introduction
With the current growth experienced by the multinational tour operator agency, there is need for the
company to rethink its data management not only to solve the short term problems they are experiencing
but also for the long term (Brunelli, 2010). Thus there is need to improve and restructure the current
database to make sure the database that will be achieved will be able to handle data on a daily basis and
store the data eliminating any possible redundancies in the data (White, 2010). This data accumulated for
a long time will be very helpful to the company as it will be useful in conducting other operations like
business intelligence which will help derive meaning from the data thus facilitate better decision making
(Whitehorn, 2010).
1 Enhanced database management strategy
The current database has a lot of redundancies in the data because of lack of normalization. Presence of
multivalued attributes results to a lot of redundancies as more data is stored overtime. Normalization aims
to improve the database structure by reorganizing the data so that it meets the following requirements;
 No data redundancy in one or multiple tables. This is ensured by making sure that all the data that
is related is stored in one table.
 There should exist logical relationships between the entities making up the database.
Normalization of the existing database will take place in three stages;
 1NF- This stage is called First Normal Form and it’s the stage at which the relations or entities in
UNF (Un-normalized Form) are normalized to 1NF by eliminating all the repeating groups in
any all the relations.
 2NF- This stage of normalization is called Second Normal Form and involves taking the relations
that were achieved in normalization to 1NF and then removing any partial dependencies. Partial
dependency occurs when there exists a composite primary key and one of the non-key attribute is

dependent on one of the primary keys making up the composite primary key. Elimination of
partial dependencies involves creating a parent table with all attributes that are partially
dependent on one of the primary keys.
 3NF- This stage of normalization is called Third Normal Form and involves taking the relations
achieved in 2NF and then removing any transitive dependencies in all the relations. Transitive
dependencies occur in a relation when an attribute is functionally dependent on another attribute
that is not the primary key attribute.
Although there exists other stages of normalization like BCNF, 4NF and 5NF normalization to 3NF is
enough to create a fully functioning database because the other later stages of normalization are usually
not necessary.
Normalization process
To carry out the normalization process, we must derive all the entities and their attributes from the case
study provided. All these entities are in their un-normalized form (UNF) thus the normalization process
can be applied by following the three stages to achieve relations in 3NF.
According to the case study the relations in UNF are;
 Salesperson (employeeID, firstname, lastname, toursSold)
 Customer( customerName, addres, city, state, zipCode, tour, numberOfPersonsInTour,
totalAMountPaid)
 Tour ( tourName, rate)
The first step of normalization is normalization to 1NF where each of the entity listed above is evaluated
to identify any multivalued attributes.
 Salesperson (employeeID, firstname, lastname, toursSold)

This relation contains a multivalued attribute which is tours sold. Since an employee can sell one
or more tours it means that the tours sold attribute will have repeating groups for those employees
that have sold more than one tour thus to normalize to 1NF this is eliminated to form.
o salesperson (employeeID, firstName, lastName, totalTours)
The totalTours attribute will be used the number of tours sold by the salesperson where
by a trigger will be implemented such that every time a tour sale is recorded under a
certain salesperson, the totalTours column will be incremented by 1.
 Customer( customerName, address, city, state, zipCode, numberOfPersonsInTour,
totalAMountPaid)
This entity contains repeating groups because if a customer makes more than one booking then
the tours field becomes multivalued. Thus eliminating the repeating groups will lead to formation
of the following relation.
o Customer (customerName, address, city, state, zipCode)
o Tour (numberOfPersonsInTour, totalAmountPaid)
This relation contains
 Tour ( tourName, rate)
This relation is already in 1NF because it contains no repeating groups.
The next step is normalization to 2NF. The entities achieved in 1NF are normalized by evaluating partial
dependencies.
 Salesperson (employeeID, firstName, lastName, totalTours)
This relation is already in 2NF since there is no partial dependency
 Customer( customerName, address, city, state, zipCode, numberOfPersonsInTour,
totalAMountPaid)

THe customerName attribute is a composite attribute which can form firstname and lastName.
numberOfPersonsInTour determines the totalAMountPaid thus the two attributes are partially
dependent on each other. Eliminating this partial dependency leaves you with;
o Customer( firstName, lastName, address, city, state, zipCode)
o CustomerTours (NumberOfPersonsInTour, totalAMountPaid)
 Tour (TourName, rate)
This relation is already in 2NF because there are no repeating groups.
The final step is normalization of the entities achieved in 2NF to 3NF.
 salesperson (employeeID, firstName, lastName, totalTours)- This relation is already in 3NF
because there are no transitive dependencies in the relation.
 Customer( customerName, address, city, state, zipCode)
In this relation we can introduce a unique primary key customerID because two customers can
have the same name.
Customer (customerID, firstname, lastname, address, city, state, zipCode)
 Tour (TourName, rate)
In this relation we introduce a unique primary key tourID because two tours can have the same
name.
Tour (TourID, TourName, rate)
 For the customerTours relation which has the NumberOfPersonsInTour and totalAmountPaid, all
these attributes can be achieved by introducing a TourSales table which will be used to record
which customer purchased which tour and which salesperson sold the tour to the customer.
TourSales (saleID, customerID, tourID, customerID, employeeID, paymentDate)
 Because the customer can pay for the tour within a period of 45 days we can introduce a
payments table in assumption that a customer can make multiple payments.

Payment (paymentID, saleID, amount).
Naming Convention
Based on the normalization process above, the final relational model with camelCase naming convention
is;
 Salesperson (employeeID, firstName, lastName, tours)
 Customer ( customerID, firstName, lastName, address, city, state, zipCode)
 Tour (TourID, TourName, rate)
 TourSales (saleID, TourID, customerID, employeeID, paymentDate)
 Payment (PaymentID, SaleID, amount)

Entity Relationship Model

Figure 1: Entity relationship model
2 Queries
 Query to determine how many days the customer’s invoice will require payment.
select customer.customerID, sysdate-tour_sales.paymentDate from tour_sales inner join customer
on tour_sales.customerID=customer.customerID where customer.customerID=1;
 Using salesperson table;
1. Trigger
CREATE TRIGGER salesperson_increment
AFTER INSERT
ON tour_sales
FOR EACH ROW
DECLARE
BEGIN
UPDATE salesperson SET tourSold=tourSold+1 where
employeeID=:new.employeeID;
END;
2. Query to show quantity of customers salesperson has sold to.
select sp.employeeID,sp.lastname,count(ts.customerID) from salesperson sp inner join
tour_sales ts on ts.employeeID=sp.employeeID group by sp.employeeID,sp.lastname;

3 Reasoning behind stored procedures
Use of stored procedures instead of writing SQL code in the program code has many benefits which
demonstrate why it is better to use stored procedures instead of SQL on application codes. Some of these
benefits are (Patton, 2005);
 Stored procedures enhance performance through faster execution- Stored procedures run on the
database server they are built and optimized thus the database executes stored procedures faster
than application codes by use of mechanisms like caching.
 Stored procedures allow reduction in network traffic- Application codes require sending a lot of
request back and forth from the database for processing in the application and this can be
achieved in the database server using a stored procedure thus minimizing the number of
transaction significantly which in turn reduces the network traffic by a huge margin.
 Use of stored procedures can be used to enhance security- By use of grants and privileges a stored
procedures can ensure more security compared to SQL running on application codes.
References
Brunelli, M. (2010). Creating a database strategy that can adapt to changing business needs. Retrieved
from https://searchdatamanagement.techtarget.com/news/2240025285/Creating-a-database-
strategy-that-can-adapt-to-changing-business-needs
Patton, T. (2005). Determine when to use stored procedures vs. SQL in the code. Retrieved from
https://www.techrepublic.com/article/determine-when-to-use-stored-procedures-vs-sql-in-the-
code/

White, C. (2013). Technology Innovations for Enhanced Data Management | IBM Big Data & Analytics
Hub. Retrieved from http://www.ibmbigdatahub.com/blog/technology-innovations-enhanced-
data-management
Whitehorn, M. (2010). Database planning tips for a successful data management strategy. Retrieved from
https://searchdatamanagement.techtarget.com/news/2240025464/Database-planning-tips-for-a-
successful-data-management-strategy