Tour Operator Agency Database Due Week 6 And Worth 90 Points

Tour Operator Agency Database Due Week 6 and Worth 90 Poi

The Strayer Oracle Server may be used to test and compile the SQL Queries developed for this assignment. Your instructor will provide you with login credentials to a Strayer University maintained Oracle server. A multinational tour operator agency has gained new business growth in the North American market through the use of social media. Its operation has expanded by 50% within six months and the agency requires an enhanced data management strategy to sustain their business operations. Their existing data repository for its reservation processing system is limited in business intelligence and reporting functionalities.

The tour operator seeks a database management specialist to assist them in leveraging their data sources to enable them to forecast and project tour sales appropriately. Imagine that you have been hired to fulfill their need of enhancing the data repository for their current reservation processing system. Upon reviewing the system, you find that the data structure holds redundant data and that this structure lacks normalization. The database has the following characteristics: A table that stores all the salespersons. The table holds their employee id, first name, last name and “Tours sold” field. The “Tours sold” field is updated manually. A table that stores tour customer data and tours sold. The table holds customer name, address, city, state, zip code, tour(s) selected, number of persons in tour, and total amount paid. The current structure will show the customer more than once if the customer books multiple tours. A tour table that is used as a tour rate sheet which holds the tours offered and the cost per person. Tour rates vary every three (3) months depending on the tourist season.

Write a three to four (3-4) page paper in which you propose an enhanced database management strategy. Your proposal should include the following: Design a data model that will conform to the following criteria: 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. Create naming conventions for each entity and attributes. Conclude your data model design with an Entity Relationship Model (ERM) that will visually represent the relationships between the tables. You may make use of graphical tools in Microsoft Word or Visio, or an open source alternative such as Dia. Note: The graphically depicted solution is not included in the required page length. 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.

Using the salesperson table described in the summary above, complete the following: Construct a trigger that will increase the field that holds the total number of tours sold per salesperson by an increment of one (1). Create a query that can produce results that show the quantity of customers each salesperson has sold tours to. Support the reasoning behind using stored procedures within the database as an optimization process for the database transactions. Your assignment must follow these formatting requirements: Be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides; citations and references must follow APA or school-specific format. Check with your professor for any additional instructions.

Include a cover page containing the title of the assignment, the student’s name, the professor’s name, the course title, and the date. The cover page and the reference page are not included in the required assignment page length. Include charts or diagrams created in Excel, Visio, MS Project, or one of their equivalents such as Open Project, Dia, and OpenOffice. The completed diagrams / charts must be imported into the Word document before the paper is submitted.

Paper For Above instruction

The rapid expansion of a multinational tour operator agency into the North American market has necessitated a comprehensive overhaul of its existing database management strategy. As the organization experiences growth through social media marketing, the increase in sales emphasizes the need for a more robust, normalized, and efficient data repository capable of supporting advanced business intelligence, accurate reporting, and effective forecasting. This paper proposes a strategic data modeling approach, including normalization, entity-relationship modeling, and SQL component implementation to elevate the database infrastructure to meet current and future operational demands.

Designing the Data Model: Normalization Process

The initial step in enhancing the tour operator’s database involves applying normalization principles to eliminate redundancy, improve data integrity, and facilitate efficient data retrieval. Starting from the existing unnormalized tables—namely Salespersons, Customer Tours, and Tour Rate sheet—the normalization process involves successive steps:

  1. First Normal Form (1NF): Ensure each table contains only atomic (indivisible) data. For example, the Customer Tours table presently stores multiple tours in a single field, which violates 1NF. To resolve this, separate the tours into individual records in a junction table, establishing a one-to-many relationship between customers and tours.
  2. Second Normal Form (2NF): Achieve 1NF and remove partial dependencies; that is, attributes should depend entirely on the primary key. For example, in the Customer table, address details are dependent on the customer, not the tour, so they should be shifted into a separate Customer entity. Similarly, the Tours offered and their rates are stored in a Tour Rates table, distinct from Customer data.
  3. Third Normal Form (3NF): Remove transitive dependencies by ensuring non-key attributes are only dependent on the primary key. For instance, if the total amount paid depends on the number of persons and tour rate, then these dependencies are modeled correctly during normalization, ensuring no redundant data storage.

The resulting data structure comprises entities such as Customers, Tours, TourRates, Salespersons, and Bookings, with appropriate foreign key relationships to enforce referential integrity. Naming conventions follow a systematic approach: Entities named with singular nouns (e.g., Customer, Tour), attributes in lowercase with underscores (e.g., customer_id, first_name). This standardized approach enhances clarity and maintainability.

Entity Relationship Model (ERM)

The ER diagram visually maps the relationships among entities. The core entities include:

  • Customer: Stores customer information. Connected to Bookings via a one-to-many relationship.
  • Tour: Details of each tour, including seasonal rates. Connected to Bookings to log customer enrollments.
  • TourRates: Stores rate sheets that change quarterly, linked to Tour entity via a temporal attribute.
  • Salesperson: Responsible for selling tours; linked to Bookings through a sales relationship. Contains a “tours_sold” attribute, which will be updated via triggers.

The ER diagram reflects these relationships with appropriate cardinalities, emphasizing normalized data flow and referential integrity. Graphical tools such as Visio aid in creating an accurate ER model presented visually in the final report.

Constructing Operational SQL Queries

A critical part of the database strategy involves operational queries. One such query calculates the days until invoice payment for amounts due within 45 days:

SELECT customer_name,

total_amount_paid,


SYSDATE + 45 AS payment_due_date


FROM CustomerPayments


WHERE total_amount_paid IS NOT NULL


AND invoice_date BETWEEN SYSDATE - (invoice_age_days) AND SYSDATE


AND (SYSDATE - invoice_date)

This query helps the finance department track overdue payments and forecast cash flow, aligning with the agency's financial planning strategies.

Implementing SQL Triggers and Stored Procedures

The salespersons table is augmented with a trigger to automatically update the “tours_sold” count when a new booking occurs:

CREATE OR REPLACE TRIGGER trg_increment_tours_sold

BEFORE INSERT ON Bookings


FOR EACH ROW


DECLARE


v_salesperson_id NUMBER;


BEGIN


v_salesperson_id := :NEW.salesperson_id;


UPDATE Salespersons


SET Tours_sold = Tours_sold + 1


WHERE employee_id = v_salesperson_id;


END;

Similarly, a query to determine the number of customers each salesperson has sold tours to can be constructed:

SELECT s.employee_id, s.first_name, s.last_name, COUNT(DISTINCT b.customer_id) AS customer_count

FROM Salespersons s


JOIN Bookings b ON s.employee_id = b.salesperson_id


GROUP BY s.employee_id, s.first_name, s.last_name;

To optimize database transactions, stored procedures consolidate repeated logic, reduce server load, and improve security by encapsulating complex SQL statements, thereby streamlining application interactions and ensuring lightweight, manageable code execution.

Conclusion

The proposed data model restructuring, normalization process, and implementation of relational and procedural database components foster a scalable, efficient, and integrity-driven database environment. Such a system supports the tour operator agency’s growth by enabling sophisticated reporting, forecasting, and operational automation, vital for sustaining competitive advantage in a dynamic industry.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.

Related Assignments