Tour Operator Agency Database

Assignment 5: Tour Operator Agency Database

CIS 515

Running Head: Tour Operator Agency Database

1. a) Propose an efficient data structure that may hold the tour operator’s data using a normalization process. Describe each step of the process that will enable you to have a 2nd Normal Form data structure.

First Normalization Process (1NF)

Fig 1. 1NF

For the 1NF, three separate tables have been identified. The tables are Salesperson, Customers and Tours. The following steps were performed to achieve 1NF.

Step 1: The tables are designed to eliminate repeating groups that creates data redundancy.

Step 2: Primary Keys have been identified for each table. Emp_ID is the pk for Salesperson, Cust_ID for Customers and Tour_ID for Tours.

Step 3: As shown in the figure, the partial dependencies were identified once the primary keys were determined in step 2.

Running Head: Tour Operator Agency Database

Second Normalization Process (2NF)

Step 1: The 1NF is studied to examine composite primary key and attributes involved in partial dependencies.

Step 2: New tables for Salesperson, Customers and Tours have been created to remove partial dependencies as shown in Fig 1.

Table: Salesperson

Table: Customers

Running Head: Tour Operator Agency Database

Table: Tours

1. b) Create naming conventions for each entity and attributes.

Entity: Salesperson

Attributes: Emp_ID, Emp_FN, Emp_LN, Tours_sold, Cust_ID

Entity: Customers

Attributes: Cust_ID, Cust_FN, Cust_LN, Cust_Address, Tour_ID, No of person in tour, Total Amount Paid, Sold_Date

Entity: Tours

Attributes: Tour_ID, Cost per person

1. c) Conclude your data model design with an Entity Relationship Model (ERM) that will visually represent the relationships between the tables.

Running Head: Tour Operator Agency Database

Fig. ERM

2. Construct a query that can be used on a report for determining how many days the customer’s invoice will require payment if total amount due is within 45 days. Provide a copy of your working code as part of the paper.

SQL> Select Cust_ID, B. Tour ID, sum(Total amount paid) from Customers A, Tours B where A. Tour ID=B. Tour ID and TRUNC(sysdate)- TRUNC(Sold_Date)<=45 Group By Cust_ID, B. Tour ID;

3. a) Construct a trigger that will increase the field that holds the total number of tours sold per salesperson by an increment of one (1).

Running Head: Tour Operator Agency Database

Create or Replace Trigger Trg_Tours

After insert or update of Tours Sold on Salesperson

For each row

Begin

Update Salesperson set Tours Sold = Tours Sold + 1

Where Emp_ID = New Emp_ID

End;

/

3. b) Create a query that can produce results that show the quantity of customers each salesperson has sold tours to. 

SQL> Select Emp_ID, Emp_FN, Count (B.Cust_ID) from Salesperson A, Customers B where B.Cust_ID=B.Cust_ID Group by Emp_ID, Emp_FN;

4. Support the reasoning behind using stored procedures within the database as an optimization process for the database transactions.

Running Head: Tour Operator Agency Database

Stored procedures offer various benefits to a database. Some of the reasoning behind using it within database to optimize process for database transactions are as follows:

– It helps to bring uniform logic across multiple programs.

– Due to the fact scripts are kept in a single location, the task of updating dependencies becomes simple.

– Business rules shouldn’t change from program to program. Rules have to remain consistent across the board. Since the stored procedures reside in a single location, the business rules remain consistent and do not get to potentially change from one program to another.

– Because the stored procedures stay in executable form, performance is quick. This helps to speed up network performance, reduce network traffic and faster responses.

– Stored procedures allow shared logic across programs which eliminates the need for repetitive coding and helps to save time as well as effort.

– If business rules change which calls for making changes to stored procedures, then only they need to be updated and not the applications that run the stored procedures.

Running Head: Tour Operator Agency Database

References

Coronel, C. & Morris S. (2017, 2015). Database systems design, implementation and management (12th ed.), Cengage Learning, Boston, MA