Assignment on tour operator agency database

Assignment Help PL-SQL Programming
Reference no: EM13723966

Tour Operator Agency Database:

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:

Part 1: Design a data model that will conform to the following criteria:

Question 1: 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 in Microsoft Word or Visio, or an open source alternative such as Dia. Note: The graphically depicted solution is not included in the required page length.
Question 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.

Part 2: Using the salesperson table described in the summary above, complete the following:

Question 1: Construct a trigger that will increase the field that holds the total number of tours sold per salesperson by an increment of one (1).
Question 2: Create a query that can produce results that show the quantity of customers each salesperson has sold tours to.

Question 3: 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. .
  • Include a cover page containing the title of 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 Excel, Visio, MS Project, or one of their equivalents such as Open Project, Dia, and OpenOffice. The completed diagrams / charts must be imported into the Word document before the paper is submitted.

Reference no: EM13723966

Questions Cloud

Assume semi-annual coupon payments : What is the yield to maturity of a 23 year bond that pays a coupon rate of 8.25% per year and has $1,000 par value and is currently priced at $1,298.05. Assume semi-annual coupon payments. Round the answer to two decimal places in percentage form.
Complete the final phase of new product launch : Complete the final phase of your New Product Launch Marketing plan. Using the sample Marketing Plan in Marketing and Management
Evaluation of business operations : You are the human resources (HR) manager for a public organization and you are conducting a New Hire Orientation session for all new employees. Last week, you analyzed the organization, including its strengths and weaknesses, as well as its missio..
Black water corp just issued zero-coupon bonds : Black water Corp just issued zero-coupon bonds with a par value of $1,000. The bond has a maturity of 25 years and a yield to maturity of 8.29%, compounded semi-annually. What is the current price of the bond?
Assignment on tour operator agency database : Part 1: Design a data model that will conform to the following criteria: Question 1: 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 yo..
Key components of new product launch marketing plan : Pitch your Learning Team's new product to the class using a Microsoft PowerPoint presentation of 10-15 slides. Include the following key components from your New Product Launch Marketing Plan: Executive summary and Situational analysis
Changing global environment : HR professionals play a key role in helping organizations respond to evolving trends, comply with federal and state regulations, and manage workplace flexibility. As an HR professional it is important for you to demonstrate your understanding of ..
Total portfolio is exactly as risky as the market : You own a portfolio equally invested in a risk-free asset and two stocks. If one of the stocks has a beta of 1.35, and the total portfolio is exactly as risky as the market, what must the beta be for the other stock in your portfolio?
Can you add in some examples of the work : Can you add in some examples of his work or things in his stylie of work? On the last slide you say he wore a silk knotted about neck, can you show that or the patched clothing?

Reviews

Write a Review

PL-SQL Programming Questions & Answers

  Write a pl/sql block and declare a variable v_sal

Write a PL/SQL block and declare a variable  v_sal  of the type NUMBER. Include the following statement in the Execution section:

  Run the lab_03_01.sql script

Run the lab_03_01.sql script in the attached file to create the SAL_HISTORY table. Display the structure of the SAL_HISTORY table.

  Database systems1 what is an sql injection attack explain

database systems1. what is an sql injection attack? explain how it works and what precautions must be taken to prevent

  How does the order of joins in an sql statement

How does the order of joins in an SQL statement affect the performance of the join and what can the DBA do to determine the preferred order of joins for an SQL statement that includes the join of at least three tables?

  Create a pivottable on a new worksheet using the data

Use the data on the Christensen worksheet in the range A1:J19 to create a PivotChart on a new sheet. Use the Type, Issue Price, and Est. Value fields and the default locations. Find the average issue price and average estimated value.

  Delete all takes tuples corresponding to any section of any

Delete all takes tuples corresponding to any section of any course with the word “database” as a part of the title; ignore case when matching the word with the title.

  Create student sorting table

Student.txt in the attachment contains the roster of a class. Each student object has the following fields: student id, last name, first name, academic level.Create a RosterClass to store the student database in vector.

  Write a pl-sql block

Write a PL/SQ block - Select the average salary of all employees in department number 20.

  Write programme in oracle to make employee table

List the employee_id and employee_name of the employees form the l_employees table with the top 4 credit limits.

  Write queries in sql and relational algebra

Write given queries in SQL and relational algebra: Determine cities from which one can fly to Toronto with one change of planes, and using same type of the aircraft for both legs of trip.

  Write a select statement that returns these columns

Write a SELECT statement that returns these columns from the Products table: The date_added column A column that uses the CAST function to return the date_added column with its date only (year, month, and day)

  Pretend that you are on the boards

Pretend that you are on the boards of the American National Standards Institute (ANSI) and the International Organization for Standards (ISO), two of the organizations who standardized SQL

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd