Tour Operator Agency Database

Assignment 5 – Tour Operator Agency Database

CIS 515 – Strategic Planning for Database Systems

Tour Operator Agency Database

This task development by 50 percent, will prompt an adequate income to demonstrate (ERDS, 2013). Encountering this development can make arranging and determining issues without the instruments set up to deal with this development (ERDS, 2013). Having a database that will permit proper following and displaying will help in the activities efficiencies (ERDS, 2013). The organization’s current information and tables won’t bolster these activities effortlessly (ERDS, 2013). Situating is the organization to expand the advantage of business knowledge (ERDS, 2013). While limiting downtime amid the change to the new frameworks will require arranging and active structure practice (ERDS, 2013). To achieve this database lifecycle approach will be utilized (ERDS, 2013).

The main period of the database life cycle is to investigate the organization’s present circumstance; to pick up an intensive comprehension of continuous tasks and characterize degree and limits to address the issues wanted (ERDS, 2013). Presently, the information structure does not lean toward standardization and would keep a few types of business knowledge from having the capacity to be acquired (ERDS, 2013). When a firm understanding has built up a reasonable plan ought to be made to give a manual for spreading out a standardized information structure that can yield the coveted business knowledge (ERDS, 2013). Amid this stage, thought will likewise be put on moving existing information into the new plan to have the capacity to use recorded data for activities that will help in estimating arrangements (ERDS, 2013).

The applied plan stage will be a fundamental advance in sketching out a productive standardized database (HR Data Manager, 2013). This will incorporate the naming tradition procedure to build up every substance inside the table and database can wipe out transitive conditions by shaping extraordinary keys inside the database (HR Data Manager, 2013). The following stage will be the consistent plan; this will guarantee information approvals, and proper security and referential uprightness are actualizing all through the database (HR Data Manager, 2013). This will interpret the product autonomous theoretical model into a subordinate product model (HR Data Manager, 2013). The last stage for the structure procedure will be the physical plan (HR Data Manager, 2013). This is when characterizing information stockpiling associations, distinguish respectability and safety efforts, and decide execution measures are connected (HR Data Manager, 2013).

After all plan stages have sufficiently tended to, the subsequent step is the execution and stacking (HR Data Manager, 2013). This involves choosing the appropriate database administration framework (DBMS) and the natural formation of the tables (HR Data Manager, 2013). Contents ought to be composed to stack existing information into the new tables, amid this stage any transformation or de-duplication of information ought to be routed to keep repetitive information from being piled into the database (HR Data Manager, 2013). When information is accumulated into the database the testing stage and any adjusting to ensure first information stack is in a state of harmony, and the total of what information has been checked (HR Data Manager, 2013). The database head ought to perform testing on structure and survey before turning it over for end-client assessment and testing (HR Data Manager, 2013). Any corrections and changes ought to be tended to as of now at that point, and after that, the database and programming application are currently prepared for the creation condition (HR Data Manager, 2013).

This will enable the organization to proceed with the development rate and oversee effectively the information put away to create convincing reports and business insight (Wambler, 2013). With the new database, valuable questions can be executed to figure out which clients are reserving the most and at what times of the year are more reservations made (Wambler, 2013). This will upgrade the capacity to viably oversee and gauge for additional development and proper staffing (Wambler, 2013). Moreover, execution audit information can likewise be gotten however assessing the business people that are making the most reservations and accepting last installments for appointments (Wambler, 2013). This will take into account the proper planning of more capable salespersons to be accessible amid the anticipated pinnacle seasons (Wambler, 2013).

Now the database life cycle the framework is viewed as in support and advancement organize (Wambler, 2013). As data and business knowledge is getting the extra demand for improvements or alterations will be put together by the organization to use more potential outcomes from data acquired from the information (Wambler, 2013).

Entity Relationship Model (ERM)

Representing Travel Agency CRM and Sales

Query to determine how many days the customer’s invoice will require payment if total due is within 45 days.

This query will return the Customer Name, Date of Reservation, Total Reservation Cost, Sum of payments received to date, Days remaining to pay the full balance.

SELECT tblCRMCustomer.CustomerName, tblReservations.ReservationDate, tblReservations.TotalCost, tblReservations.PmtDueDate, Sum(tblPayments.PmtAmount) AS SumOfPmtAmount, Date$()-[tblReservations]![PmtDueDate] AS DayToPay

FROM (tblCRMCustomer INNER JOIN tblPayments ON tblCRMCustomer.CustomerID = tblPayments.CustomerID) INNER JOIN tblReservations ON (tblPayments.ReservationID = tblReservations.ReservationID) AND (tblPayments.CustomerID = tblReservations.CustomerID) AND (tblCRMCustomer.CustomerID = tblReservations.CustomerID)

GROUP BY tblCRMCustomer.CustomerName, tblReservations.ReservationDate, tblReservations.TotalCost, tblReservations.PmtDueDate, Date$()-[tblReservations]![PmtDueDate]

HAVING (((Sum(tblPayments.PmtAmount))<[tblReservations]![TotalCost]));

Trigger that will increase the field of tours sold by a salesperson (employee) by one each time a full payment has received for a reservation.

The relational database used in my example is Microsoft Access. Microsoft Access does not support triggers, to accomplish this I would create a code a Visual Basic module to execute an update on the tblPayments. Calculate the sum of PmtAmount based on a specified key and if amount totals tblReservations.TotalCost then update tblEmployee.TotalTours by 1. If I were using Microsoft SQL, it would be as follows.

CREATE TRIGGER dbo.tblReservations

ON dbo.tblEmployee

IF EXISTS(SELECT * FROM tblReservations Where TotalCost = TotalPayments)

BEGIN

CASE

WHEN EXISTS(Update tblEmployee SET TotalTours = TotalTours + 1

END

END

Construct query that produces results that show the quantity of customers each salesperson has sold tours to

SELECT tblEmployee.EmployeeNumber, tblCRMCustomer.CustomerName, Sum(tblPayments.PmtAmount) AS SumOfPmtAmount

FROM (tblTours INNER JOIN (tblEmployee INNER JOIN (tblCRMCustomer INNER JOIN tblReservations ON tblCRMCustomer.CustomerID = tblReservations.CustomerID) ON tblEmployee.EmployeeID = tblReservations.EmployeeID) ON tblTours.TourID = tblReservations.TourID) INNER JOIN tblPayments ON (tblPayments.ReservationID = tblReservations.ReservationID) AND (tblPayments.CustomerID = tblReservations.CustomerID) AND (tblCRMCustomer.CustomerID = tblPayments.CustomerID)

GROUP BY tblEmployee.EmployeeNumber, tblCRMCustomer.CustomerName, tblReservations.TotalCost

HAVING (((Sum(tblPayments.PmtAmount))=[tblReservations]![TotalCost]));

References

ERDs (2013). Developing Entity Relationship Diagrams (ERDs). Retrieved from http://users.csc.calpoly.edu/~jdalbey/205/Lectures/HOWTO-ERD.html

HR Data Manager. (2013). HR Data Manager – Human Resources Management System. Retrieved from http://www.hrdatamanager.com/Support/tabid/58/Default.aspx

Wambler, S. (2013) Data Modeling 101. Retrieved from http://www.agiledata.org/essays/dataModeling101.html