Assignment 1: Tour Operator Agency Database

Assignment 1: Tour Operator Agency Database

A multinational tour operator agency has experienced a 50% business growth in the North American market due to social media, leading to an expanded operation requiring enhanced data management strategies. Their current reservation processing system has a limited data repository lacking normalization and business intelligence capabilities. The existing database includes a table for salespersons with manual "...Tours sold" updates, a customer data table with multiple bookings per customer, and a tour rate sheet with seasonal variations.

The assignment involves designing a normalized data model in Second Normal Form (2NF), establishing naming conventions, creating an entity-relationship model (ERM) illustrating relationships, constructing specific SQL queries for invoicing and sales analysis, developing triggers and stored procedures to optimize database operations, and justifying the use of stored procedures for transaction efficiency.

Paper For Above instruction

The design and implementation of an efficient, normalized database system are essential for the tour operator agency to improve data consistency, reduce redundancy, and enhance business intelligence capabilities. To achieve these goals, the data model must be carefully structured following normalization principles, with clear naming conventions and accurate relationship mapping.

Designing a Normalized Data Model

The initial step involves analyzing the existing tables to identify redundancy and partial dependencies. The goal is to establish a data model in Second Normal Form (2NF), which eliminates partial dependencies on the primary key. Consider the key entities involved: salespersons, customers, tours, and sales transactions.

For example, the current salesperson table includes employee id, first name, last name, and Tours sold, which is manually updated. To normalize, we should decouple the "Tours sold" field into a separate table that records each sale transaction, linking back to the salesperson via a foreign key. This ensures that any changes to the number of tours sold are automatically reflected without redundant data entry.

Similarly, the customer table should be split. Since customers can book multiple tours, instead of duplicating customer information for each booking, a customer table should be linked to a booking or reservation table that records each individual booking, including tour details and payment amounts.

The tour rate sheet should be maintained as a separate entity, with its own table, to accommodate seasonal variations. This table will include the tour name, season, and cost per person, enabling dynamic updates without affecting other data entities.

Following these steps, the primary entities and their relationships can be established in a normalized schema, reducing data redundancy and improving data integrity. The finalized model aligns with the principles of 2NF, where all non-key attributes depend solely on the primary key or candidate keys.

Entity Naming Conventions

Clear and consistent naming conventions facilitate understanding and maintenance. For entities, use singular nouns prefixed with context, such as tblSalesperson, tblCustomer, tblTour, tblSaleTransaction, and tblTourRate.

Attributes should be named descriptively using camel case or underscores, e.g., employeeId, firstName, totalAmountPaid. Primary keys are named with an "Id" suffix, while foreign keys reference primary key names with an "Id" suffix as well, such as salespersonId, customerId.

Entity Relationship Model (ERM)

The ERM visually represents the relationships among entities. The tblSalesperson has a one-to-many relationship with tblSaleTransaction, as one salesperson can handle multiple transactions. The tblCustomer also has a one-to-many relationship with tblSaleTransaction, as each customer can make multiple bookings. The tblTour is related to tblTourRate via the season attribute. The tblSaleTransaction links customers, salespersons, and tours, capturing the sales details.

Diagrams can be created using Microsoft Visio or open-source tools like Dia, illustrating entities as boxes with attribute lists, and relationships as connecting lines, annotated with cardinalities.

SQL Query for Payment Due within 45 Days

To determine how many days an invoice will require payment if due within 45 days, a SQL query can use the DATEDIFF function to calculate the difference between invoice date and due date (Invoice date + 45 days). The following SQL demonstrates this:

SELECT 

InvNumber, 


INVDate, 


DATEADD(day, 45, INVDate) AS DueDate, 


DATEDIFF(day, INVDate, DATEADD(day, 45, INVDate)) AS DaysToDue


FROM 


Invoice


WHERE 


DATEDIFF(day, INVDate, GETDATE())

This query lists all invoices due within 45 days, showing due dates and days remaining.

Salesperson Table Triggers and Queries

A trigger is required to increment the "Tours sold" count each time a sale is recorded. An example in SQL Server syntax:

CREATE TRIGGER trgIncrementToursSold

ON tblSaleTransaction


AFTER INSERT


AS


BEGIN


UPDATE s


SET ToursSold = ToursSold + 1


FROM tblSalesperson s


INNER JOIN inserted i ON s.employeeId = i.salespersonId;


END;

A query to count how many customers each salesperson sold tours to would join the salesperson and sale transaction tables:

SELECT 

s.firstName + ' ' + s.lastName AS SalespersonName,


COUNT(DISTINCT t.customerId) AS NumberOfCustomers


FROM 


tblSalesperson s


JOIN 


tblSaleTransaction t ON s.employeeId = t.salespersonId


GROUP BY 


s.firstName, s.lastName;

Justification for Stored Procedures

Stored procedures are vital for optimizing database performance by encapsulating complex, frequently executed operations. They reduce network traffic by executing multiple statements server-side, enforce security by controlling access to underlying data, and improve consistency through standardized execution. For example, a stored procedure for processing a new sale can include all relevant updates—adding sale records, updating salesperson's tours sold count, and logging transactions—all within a single call, ensuring atomicity and data integrity.

Using stored procedures aligns with best practices for scalable, maintainable systems, especially when handling critical transactions like booking or payments in a tour operation environment. They simplify application development, promote code reuse, and facilitate performance tuning by leveraging database engine optimizations.

Conclusion

Implementing an optimized, normalized database design enhances the tour operator agency’s data consistency and operational efficiency. Clear naming conventions, comprehensive ER diagrams, and advanced SQL features such as triggers and stored procedures contribute to a robust system capable of supporting growth and improved business intelligence. This strategic approach ensures data integrity while enabling scalable growth and improved reporting capabilities.

References

  • Casteel, S. (2020). Database systems: Design, implementation, & management (13th ed.). Pearson.
  • Database systems (6th ed.). Pearson.
    • Fundamentals of database systems (7th ed.). Addison-Wesley. Database system concepts (6th ed.). McGraw-Hill. SQL fundamentals: Extensive techniques for enterprise data management. Sybex. Database: Principles, programming, and performance (2nd ed.). Morgan Kaufmann. Database management systems: Design, implementation, & management. Wiley. Database system concepts (7th global ed.). McGraw-Hill Education. Relational database design principles. Wiley.
  • Tanenbaum, A. S., & Milojicic, D. (2017). Distributed systems: Principles and paradigms. Pearson.

Related Assignments