Tour Operator Agency Database

This paper inspects the life cycle of a database from the beginning stages through the different steps it takes to achieve the last stage for upkeep and advancement. Utilizing a travel office/agency organization’s current information that won’t meet the future needs and development of the business, a reasonable outline is introduced to add to a database that will give the usefulness that will bolster future development and move existing information to the new framework. Included is an entity-relationship chart that delineates the structure of the new database.

Extending operations by 50% will prompt an effective income model. Encountering this development can make arranging and estimating issues without the devices set up to deal with this development. Having a database that will permit tracking that is fitting and modeling will help in the operations efficiency. The organization’s current information and tables won’t bolster these operations effortlessly. Situating the organization to boost the advantage of business insight while minimizing downtime; a move to the new framework will require arrangement and productive outline design. To fulfill this database life cycle methodology will be utilized.

The first stage of the database life cycle is to investigate the organization’s present circumstance, to pick up an exhaustive comprehension of current operations and characterize degree and limits to address the issues sought. As of now the information structure does not incline towards standardization and would keep a few types of business knowledge for having the capacity to be acquired. When a firm comprehension has been built up, a reasonable outline ought to be made to give a manual for laying out a standardized information structure that can yield the coveted business knowledge. Amid this stage, consideration will likewise be set on relocating existing information into the new outline to have the capacity to influence verifiable data for operations that will help in forecasting solutions.

The conceptual configuration stage will be a basic stride in illustrating a productive standardized database. This will incorporate the conventional naming procedure to build up every substance within the table and database can take out transitive conditions by framing interesting keys within the database. The following stage will be the logical outline/design, this will guarantee information approvals and suitable security and referential integrity is actualized all through the database. This will transform the design from to a software dependent model from a software-independent model. The last stage for the outline procedure will be the physical configuration. This is characterizing information storage, characterize uprightness and efforts to establish security, and ensure the application of performance measures.
After all outline stages have been appropriately tended to, the following step is the loading and implementation. This involves selecting the most suitable database management system (DBMS) and the real formation of the tables. Scripts ought to be composed to load existing information into the new tables, amid this stage, any transformation of information ought to be delivered to keep repetitive information from being stacked into the database. When information is stacked into the database, the testing stage and any calibrating is carried out to ensure initial information burden is in a state of harmony and the sum total information has been confirmed. The database executive ought to perform testing on structure and survey prior to turning it over for end client assessment and testing. Any modifications and changes ought to be tended during this period, and after that, the database and programming application is currently ready for the generation environment.

This will database will permit the organization to proceed with the development rate and oversee effectively the information put away to deliver compelling reports and business insight. With the new database, viable questions can be executed to figure out which clients are the most common customers and when reservations are booked most. This will upgrade the capacity to adequately oversee and estimate for extra development and proper staffing. Furthermore, execution audit information can likewise be obtained by assessing the salespersons that are making the most reservations and getting the last installments for reservations. At this stage the database life cycle framework is thought to be in a support and development stage. As data and business knowledge is acquired, extra demand for improvements or adjustments will be presented by the organization to influence more conceivable outcomes from data got from the information.
Entity-Relationship Model

 

Query construction
This query to determine how many days the customer’s invoice will require payment if total due is within 45 days will return the Customer Name, Date of Reservation, Total Reservation Cost, Sum of payments received to date, Days remaining to pay 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]));

Visual Basic module (Triggers)
Calculate the sum of Pmt Amount based on specified key and if amount totals tbl Reservations. Total Cost then update tbl Employee. Total Tours 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
Query Construction
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]));

Leave a Reply