Tour Operator Agency Database

Assignment 5: Tour Operator Agency Database

Introduction

The normalization process is used to design databases in which it is easy to organize and manage data while ensuring the accuracy of data throughout the database.It is a technique where a set of guidelines called normal forms, are used to optimally design a database to reduce redundant data.

The advantage of having a highly normalized data schema is that information is stored in one place and one place only, reducing the possibility of inconsistent data. Additional benefits to a database include it provides greater overall database organization, reduction of redundant data, data consistency within the database, a more flexible database design that can handle database security more effectively.

The purpose of this proposal is to develop a more efficient data structure of a multinational tour operator agency’s current reservation processing system that is limited to business intelligence and reporting functionalities. A conceptual design is presented to develop a database that will leverage the agency’s data sources enabling them to forecast and project tour sales more appropriately using the normalization process. Additionally, each step of the process that enables the ability to have a 2nd Normal Form data structure will be described. Included is an entity relationship diagram that depicts the structure of the new database.

Data Structure of Tour Operator’s Data Using a Normalization Process

Experiencing organizational growth can create planning and forecasting problems without the essential tools in place to manage the growth. Having a database that will allow appropriate tracking and modeling will enhance operations effectiveness. Positioning the company to maximize the benefit of business intelligence while minimizing downtime during transition to the new system will require planning and efficient design practice.

Presently, the database does not utilize normalization in which existing data has not been broken down logically into smaller, more manageable tables. Furthermore, data should not be redundant, meaning that the duplication of data should be kept to a minimum.

Normal forms, an integral concept involved in the process of database normalization. Normal form is a way of measuring the levels, or depth, to which a database has been normalized. A database’s level of normalization is determined by the normal form. There are commonly used normal forms in the normalization process which include the first normal form (NF1), the second normal form (NF2) and the third normal form (NF3). Of the three normal forms, each subsequent normal form depends on normalization steps taken in the previous normal formal.

In the first normalization (NF) process the objective is to divide the base data into logical units called tables. When each table has been designed, each record should be unique and identified by a primary key. “A database is in first normal form if it satisfies the following conditions contains only atomic values and no repeating groups. An atomic value is a value that cannot be divided. A repeating group means that a table contains two or more columns that are closely related” (Database Normalization, 2014).

The first step in the process is to bring the database to the first normalization form (NF!), using the naming convention process. This identification process includes naming the object in a way that makes the object unique and distinguishable from other objects in the database making data easier to retrieve and find. Descriptive table names are especially important for users querying the database. “The use of a proper naming convention will improve the data model’s ability to facilitate communication among the designer, application programmer, and the end user. In fact, a proper naming convention can go a long way toward making your model self-documenting.”

Each entity will be defined and identified by a primary key for each table. Each entity naming convention will be in upper-case letter as follows EMPLOYEE, CUSTOMER, TOUR.

The naming convention of attributes for Employee table are EMP_ID, EMP_FirstName, EMP_LastName, Tours_soldtable The tours sold field will be updated manually. The naming convention of attributes for Customer data CUST_ID, CUST_NAME, CUST _ADDRESS, CUST _CITY, CUST _STATE, CUST _ZIP CODE, NO_OF_PEOPLE, TOTAL_AMOUNT_PAID. The Naming convention of attributes for Tour are TOUR_ID and

TOUR_COSTPERPERSON. Tour rates differ every three months depending on the tourist season.

In next step is the second normalization form (NF2), where data that is only partly dependent on the primary key is entered into another table. The second normal form is a result of the first normal form being further broken into more specific units. Non-key fields depend on primary key when primary key is a single field. Maintain the primary key and foreign key relationship between the tables and also remove the subsets of data which will be applied to multiple rows in the table.

The relationship describes the reference from one entity instance to one or more of another entity instance. The relationship name should therefore include the target entity text so that it is clear from the relationship name who the relationship is to. The relationship between the Sales_Person to Customer Entity are as follows One sales person can have one or many customers One tour can have one or many customers.

Entity Relationship Model (ERM)

Query to Determine Days an Invoice Will Require Payment if Due Within 45 days

The following query will determine which customer’s invoices will require full payment within forty-five days:

Select CUST_ID, b.TOUR_ID, sum (TOT_AMT_PAID) From Customer a, Tour b Where a.Tour_ID=b.TOUR_ID and Trunc (SYSDATE)-Trunc (TOUR_SOLD_DATE) <=45 Group by CUST_ID, b.TOUR_ID, sum;

SQL> select * from Tour;

TOUR_ COST_PER_PERSON

—– —————

11 199

11 199

11 199

22 100

33 75

44 125

55 150

SQL> select * from Sales_Person;

EMP_I EMP_FIRST_NAME EMP_LAST_NAME CUST_ TOURS_SOLD

—– ——————– ——————– —– ———-

101 Adam Jones 121 1

102 Chris Geraghty 131 1

103 Ken Keary 141 1

CUST_ CUST_NAME STREET_ADDR CITY

—– ——————– ————————- ——————–

STATE ZIP TOURS_SELECTED NO_OF_PEOPLE TOT_AMT_PAID TOUR_

——————– ———- ————– ———— ———— —–

TOUR_SOLD

———

121 Tom #1024 Dallas

Texas 75035 11 1 199 11

10-APR-14

131 Shane #3021 New York

New York 36778 22 2 200 22

15-APR-14

CUST_ CUST_NAME STREET_ADDR CITY

—– ——————– ————————- ——————–

STATE ZIP TOURS_SELECTED NO_OF_PEOPLE TOT_AMT_PAID TOUR_

——————– ———- ————– ———— ———— —–

TOUR_SOLD

———

141 Rena #1290 San Jose

CA 32910 33 1 75 33

01-MAY-14

Trigger Increasing Field of Tours Sold When Full Reservation Payment Has Been Received

TRIGGER AND ITS DISPLAY:

SQL> CREATE OR REPLACE TRIGGER TRG_TR

2 AFTER INSERT or UPDATE of TOURS_SOLD ON Sales_Person

3 FOR EACH ROW

4 BEGIN

5 update Sales_Person SET TOURS_SOLD = TOURS_SOLD + 1

7 WHERE EMP_ID=: NEW.EMP_ID

8 END;

9 /

Display of the output

NAME CUST_ID NUM_PEOPLE EMP_ID

—– ——————– ——————– —– ———-

Adam 3 121

Chris 5 131

Ken 3 141

And the trigger increased the 1 in Sales_Person tours_sold.

NAMETOURS_SOLD EMP_ID

Jones 1 101

Geraghty 1 102

Keary 2 103

Query That Produces Quantity of Tours Each Salesperson Has Sold

SQL> select EMP_ID, EMP_FIRST_NAME, Count (b.CUST_ID) From Sales_Person a, Customer b Where b.CUST_ID=b.CUST_ID Group by EMP_ID, EMP_FIRST_NAME;

Stored Procedures as an Optimization Process for Database Transactions

A stored procedure is compiled code which can be called from or within T-SQL statements as well as from client applications. SQL Server runs the code in the procedure and then returns the results to the calling application. These procedures are used to enforce the level of consistency in applications.

Stored procedures runs on the SQL Server, using the power of the server and reducing the load on the client. Network traffic is reduced drastically by using the stored procedures. Once the stored procedure is run it remains in the memory and can be reused without overloading the overhead into the memory.

They can enhance security and give users only the permission to execute them. Control, security and efficiency procedure which runs on databases servers. We can avoid repetitions of the SQL statements or code. Complied code is available for reuse

There are numerous reasons for using stored procedures include SQL Server has already parsed, optimized, and compiled stored procedures, so they run quickly without needing to repeat these steps each time.

References

Ambysoft Inc. (2013). Introduction to Data Normalization: A Database “Best” Practice Retrieved from: agiledata.org/essays/dataNormalization.html

Coronel, C., Morris, S., Rob, P. (2013). Database Systems: Design, Implementation, and Management, 10th ed. Chapter 8 – 9.

ERDs (2013). Developing Entity Relationship Diagrams (ERDs). Retrieved from http://users.csc.calpoly.edu/~jdalbey/205/Lectures/HOWTO-ERD.html

IBM (2005). Building a Relational Data Model. Retrieved from: http://publib.boulder.ibm.com/infocenter/idshelp/v10/advanced/printjsp?printType=sub/TopicsPrint&linkId=Id=t4_0_1_1

Plew, R. & Stephens R. (2003). The Database Normalization Process. Retrieved from: http://www.informit.com/articles/article.aspx?p=30646

Reilly, M. (2000). SQL Server Pro: Retrieved from: http://sqlmag.com/stored-procedures/all-about-stored-procedures

Wambler, S. (2013) Introduction to Data Normalization: A Database “Best” Practice. Retrieved from: http://www.agiledata.org/essays/dataModeling101.html