Enhanced Database Management Strategy for Multinational Tour Operator Agency

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)