Spotify Page 01, 02, 03, 04

Spotify Page 01spotify Page 02spotify Page 03spotify Page 04spotify Pa

Spotify Page 01spotify Page 02spotify Page 03spotify Page 04spotify Pa

Spotify_Page_01 Spotify_Page_02 Spotify_Page_03 Spotify_Page_04 Spotify_Page_05 Spotify_Page_06 Spotify_Page_07 Spotify_Page_08 Spotify_Page_09 Spotify_Page_10 Spotify_Page_11 Spotify_Page_12 Spotify_Page_13 Spotify_Page_14 Spotify_Page_15 Spotify_Page_16 Spotify_Page_17 Spotify_Page_18 Spotify_Page_19 Assignment 5: Tour Operator Agency Database 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: 1. Design a data model that will conform to the following criteria: 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. b. Create naming conventions for each entity and attributes. c. 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 with which you are familiar. Note: The graphically depicted solution is not included in the required page length. 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. 3. Using the salesperson table described in the summary above, complete the following: 3. Construct a trigger that will increase the field that holds the total number of tours sold per salesperson by an increment of one (1). 3. Create a query that can produce results that show the quantity of customers each salesperson has sold tours to. 1. 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 any chart or drawing tool with which you are familiar. The completed diagrams / charts must be imported into the Word document before the paper is submitted.

Paper For Above instruction

The rapid expansion of the tour operator agency serving the North American market necessitates a comprehensive overhaul of its existing data management system. As a specialist in database management, my primary goal is to design a robust and efficient database model that enhances business intelligence capabilities, reduces redundancy, and supports predictive analytics for future sales. This paper outlines a strategic approach, focusing on normalization principles, creation of an Entity Relationship Model (ERM), and implementation of SQL queries and triggers to optimize the system.

Designing the Data Model with Normalization

The initial step involves examining the current data structure, which exhibits redundancy and lacks normalization, particularly apparent in how customer and sales data are stored. To create an efficient second normal form (2NF) structure, I adopt a stepwise normalization process. First, I identify all functional dependencies within each table, then decompose the tables accordingly.

The original sales table, which includes employee id, first and last names, and "Tours sold"—manually updated—indicates partial dependency; specifically, employee details are redundantly stored for each sale. I separate employee information into a distinct "Salespersons" entity, with employee id as the primary key. The "Tours Sold" field, which aggregates total sales per salesperson, is better stored as a derived value or calculated dynamically to prevent redundancy.

Next, the customer data table contains customer details and multiple entries for each tour booked, leading to duplication. To achieve normalization, I segment customer information into a "Customers" table with unique customer identifiers, and a "Tour Bookings" associative table that links customers to specific tours, including details like date, number of persons, and total amount paid per booking.

The "Tour" table, which acts as a rate sheet with varying prices seasonally, remains primarily stable but requires attributes such as "Tour ID," "Tour Name," and "Cost per Person." To incorporate seasonal rate changes, I introduce a "Tour Rates" table with effective date ranges, enabling dynamic rate tracking.

This process distills the database into multiple normalized tables: Salespersons, Customers, Tours, Tour Rates, and Tour Bookings, each with primary keys and foreign key relationships that minimize redundancy, ensure data integrity, and conform to second normal form.

Naming Conventions and Entity Relationship Model

For clarity and consistency, I propose the following naming conventions:

  • Entities: Use singular nouns in PascalCase, e.g., Salesperson, Customer, Tour, TourRate, TourBooking
  • Attributes: Use camelCase, e.g., employeeId, firstName, totalAmountPaid
  • Foreign keys: Append "_id" to attribute names, e.g., salesperson_id, customer_id, tour_id

The ER diagram visually depicts relationships:

  • Salesperson (1) to TourBooking (N): A salesperson can have many bookings, but each booking is linked to one salesperson.
  • Customer (1) to TourBooking (N): Customers can have multiple bookings; each booking is for one customer.
  • Tour (1) to TourRate (N): Tours can have many rate entries, varying over different seasons.
  • Tour (1) to TourBooking (N): Each booking involves one tour, but a tour can be part of many bookings.

SQL Query for Payment Terms

To determine the number of days until a customer's invoice is due when the total amount due is within 45 days, I craft the following query:



SELECT customerName, invoiceDate, DATEDIFF(day, invoiceDate, GETDATE()) AS daysUntilDue


FROM CustomerInvoices


WHERE totalAmountDue 

AND DATEDIFF(day, invoiceDate, GETDATE()) BETWEEN 0 AND 45;

This query calculates the difference in days between the invoice date and the current date, filtering for invoices due within 45 days.

Triggers and Query for Salesperson Data

A trigger to increment the "toursSold" field upon each new sale is essential for maintaining accurate salesperson metrics. The trigger, implemented in PL/SQL, looks like:



CREATE OR REPLACE TRIGGER incrementToursSold


AFTER INSERT ON TourBooking


FOR EACH ROW


BEGIN


UPDATE Salesperson


SET toursSold = toursSold + 1


WHERE salesperson_id = :NEW.salesperson_id;


END;

Furthermore, to assess the quantity of customers each salesperson has sold tours to, the following query is used:



SELECT s.employeeId, s.firstName, s.lastName, COUNT(DISTINCT b.customer_id) AS customerCount


FROM Salesperson s


JOIN TourBooking b ON s.employeeId = b.salesperson_id


GROUP BY s.employeeId, s.firstName, s.lastName;

Supporting the use of stored procedures, these functions encapsulate complex operations, improve code reuse, and optimize transaction handling by reducing redundant parsing and execution overhead. Stored procedures also enhance security by restricting direct table access and centralizing business logic, leading to more maintainable and efficient database management.

Conclusion

The proposed normalization process and data model design significantly improve the tour operator's database system by reducing redundancy, ensuring data integrity, and increasing flexibility for future changes, such as seasonal rate adjustments. Implementing appropriate SQL queries, triggers, and stored procedures further enhances operational efficiency and reporting accuracy, equipping the agency to handle continued growth and improving its ability to forecast and project sales effectively.

References

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

Related Assignments