The Strayer Oracle Server May Be Used To Test And Com 470451
The Strayer Oracle Server May Be Used To Test And Compile the Sql Que
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 with which you are familiar.
- 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.
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
Introduction
The rapid expansion of the tour operator’s business necessitates a comprehensive overhaul of its existing database system. As business volume increases, redundancies and inefficient data structures hinder accurate reporting and decision-making. Therefore, implementing a normalized relational database model is fundamental to optimize data integrity, reduce redundancy, and facilitate better business intelligence. This paper presents a detailed approach to redesigning the tour operator’s database, integrating normalization principles to achieve a second normal form (2NF), and constructing relevant SQL queries and triggers to support operational and analytical needs.
Current System and Challenges
The present system relies on unnormalized tables storing diverse data points—salespersons, customer data, and tour rates—in a manner that leads to data duplication. For example, customers who book multiple tours appear multiple times, resulting in redundant storage and complicating data updates or retrievals. The manual updating of the "Tours sold" field further introduces inaccuracies and inefficiencies. Without normalization, the system cannot optimally support business intelligence, forecasting, or reporting functions.
Designing an Efficient Data Model Using Normalization
Normalization is a systematic approach to organizing data to minimize redundancy and dependency. The process involves decomposing tables into smaller, well-structured tables adhering to specific normal forms. The goal is to reach at least Second Normal Form (2NF), which ensures that every non-key attribute is fully functionally dependent on the primary key.
Step 1: Identifying Entities and Attributes
The primary entities include:
- Salespersons (SalespersonID, FirstName, LastName, ToursSold)
- Customers (CustomerID, CustomerName, Address, City, State, ZipCode)
- Tours (TourID, TourName, CostPerPerson, Season)
- Bookings (BookingID, CustomerID, TourID, NumberOfPersons, TotalAmount, BookingDate)
Step 2: Removing Partial Dependencies for 2NF
In the original schema:
- The "ToursSold" in the Salesperson table is manually maintained and redundant across records.
- Customer data may be duplicated when customers place multiple bookings.
- Tour rate information is embedded, leading to duplicated data and potential inconsistency.
By decomposing:
- Create a Salesperson table with unique IDs.
- Create a Customer table with unique IDs.
- Create a Tour table with dynamic rate information, including seasonal variations.
- Create a Booking table linking Customers and Tours, with associated details.
Step 3: Establishing Naming Conventions
Adopt consistent and descriptive naming:
- Tables: [tbl_Salespersons], [tbl_Customers], [tbl_Tours], [tbl_Bookings]
- Attributes: [SoldToursCount], [CustomerName], [TourName], [Rate], [NumberOfPersons], [TotalAmount], [BookingDate]
Step 4: Entity Relationship Model (ERM)
Visualizing relationships:
- One-to-many relationship between Salespersons and Bookings (a salesperson may handle multiple bookings).
- One-to-many relationship between Customers and Bookings.
- Many-to-one relationship between Bookings and Tours.
- The ER diagram illustrates the connections, with foreign keys linking tables.
SQL Query for Payment Timing
To determine how many days the invoice will require payment if due within 45 days:
```sql
SELECT
CustomerID,
TotalAmount,
BookingDate,
ADD_DAYS(BookingDate, 45) AS PaymentDueDate,
CASE WHEN TRUNC(SYSDATE)
TRUNC(ADD_DAYS(BookingDate, 45) - SYSDATE)
ELSE
0
END AS DaysRemaining
FROM
tbl_Bookings
WHERE
TotalAmount IS NOT NULL;
```
This query calculates the payment due date and days remaining based on the booking date.
Triggers and Stored Procedures
Trigger to Increment Tours Sold
```sql
CREATE OR REPLACE TRIGGER trg_increment_ToursSold
AFTER INSERT ON tbl_Bookings
FOR EACH ROW
BEGIN
UPDATE tbl_Salespersons
SET ToursSold = ToursSold + 1
WHERE EmployeeID = (SELECT EmployeeID FROM tbl_Salespersons WHERE EmployeeID = :NEW.SalespersonID);
END;
```
This trigger updates the ToursSold counter whenever a new booking is recorded.
Query for Number of Customers per Salesperson
```sql
SELECT
sp.EmployeeID,
sp.FirstName,
sp.LastName,
COUNT(DISTINCT b.CustomerID) AS CustomerCount
FROM
tbl_Salespersons sp
JOIN
tbl_Bookings b ON sp.EmployeeID = b.SalespersonID
GROUP BY
sp.EmployeeID, sp.FirstName, sp.LastName;
```
Reasoning for Using Stored Procedures
Stored procedures encapsulate complex operations within the database, leading to improved performance by reducing network traffic, enforcing data consistency, and allowing precompiled execution paths. They streamline transaction processing, enable security by controlling direct table access, and facilitate code reuse across applications, solidifying their role as essential tools for database optimization.
Conclusion
Implementing a normalized relational database schema significantly enhances data integrity, reduces redundancy, and boosts reporting capabilities essential for forecasting and decision-making. The proposed ER model and SQL implementations align with best practices to support the tour operator's expanding business needs. Properly designed, the database will be more scalable, manageable, and capable of delivering timely, accurate insights necessary for strategic growth.