Create a database for flying high travel services

Assignment Help Database Management System
Reference no: EM131182299

You have been commissioned to create a database for Flying High travel services (FHTS). FHTS is a new and specialised travel agency who cater for Australian companies with high travel needs. Their market niche is inlow cost business travel packages byairlines. They keep their prices low by dealing directly with the airlines, who provide combined cheap flights and accommodation.

When a FHTS customer wishes to book business flights with accommodation, it is always done by the respective employees of that Australian company, who book online. This model is used to reduce administration costs.

When a booking is made, a FHTS staff member sources flights by airlines that contain the dates and destinations specified by the customers. Accommodation will always be included automatically in the final destination, this is provided by the airline. It may not always be the same accommodation, but is always of high quality to satisfy the customer's comfort.

The following business rules and information gathered about the current business activities will allow you to derive entities. Your submission is not expected to have many to many relationships left unresolved. You may add entities or attributes as you see fit. Assumptions can be made toinclude further entities and their relationships, but referential integrity and normalisation processes must be adhered to. Reasons should be given for any relation that is not maintained in 3NF.

- A company (a business client of FHTS) is referenced by a unique company number, it has a company name, phone, fax, and email contact.

- A customer of FHTS is an employee of a company who is requiring the business travel services of FHTS. Each customer can only belong to one company. A customer will have a unique customer ID, first name, last name, mobile phone number, address, post code and email address. These details are needed in case travel arrangements change and they need to be notified.

- A customer may also be a VIP of FHTS. The level of the VIP status is dependent on the number of years and the amount of money that the customer has spent with FHTS. There are three levels of VIP status, these include Gold, Silver and Bronze. A customer VIP status will give a discount to the customer booking.

- A customercanalso be a frequent flyer. As a frequent flyer, a customer may receive a discount on their flights.All airlines that FHTS deal with, recognise their customer's frequent flyer status. The amount of a frequent flyer discount is only dependent on the amount of frequent flyer points they have accumulated.

- Also a frequent flyer will accumulate more points based on the number flying kilometres they accumulate. The number of kilometres travelled by a flight needs to be recorded.

- A customer can make many travel bookings with FHTS,but only one customer is allowed to be on a booking.FHTS records the date of a travel booking.

- There are many staff working at FHTS who take many bookings from the customers. However, only one staff member is allocated to each booking.

- A number of staff members are supervisors who supervise one or more other staff members. However, each staff member is supervised by only one supervisor.

- A staff member needs a unique ID, first name, last name, phone number, date of birth, start date, tax file number, home address, email address and a Supervisor ID.

- A booking also needs an attribute to state if there has been supervisor intervention for a respective booking."Yes" or "No" is all that is required.

- A booking may contain oneor more flights, by one or more airlines. Eachflight has only one location departure and destination.

- A flight details include a unique ID, flight number, departure location ID and location name, departure date and time, destination location ID and location name, destination date and time.

- Each flight has cabin class fares. Cabin class fares are directly associated with the cabin classes. The cabin classes are first, business and economy. A customer may have differentclass faresfor each booking i.e. fordifferent flights, but only one class fare for eachflight.

- A cabin class fareis set by each Airline. As FHTS deals with the Airlines directly, all class fares remain fixed across all Airlines. However the overall flight fare is determined by the cabin class fare and an additional flight fee charge. FHTS stores the flight fees.

- No flights will have stop overs, all flights will have final endlocations (destinations). A location will have one or many flights that can be either departure or destination flights.

- A location may have one or many accommodation sites. However, a customer will only be allocated one accommodation site at their travel location destination.

- Alocation will need at least, a unique ID, name and the number of days for the stay.

- Information regarding an accommodation site is kept including, unique ID, name,address details,email address, date of arrival and date of departure.

Your submission should include:

- An ER Diagram with all entity names, attribute names, primary and foreign keys, relationships, cardinality and participation indicated. All many to many relationships should be resolved.

- A discussion of normalisation including the normal form that each entity is in and why that is optimal. Also a discussion of how normalisation was achieved for that entity.

- A list of relationships with all table names, attributes, primary and foreign keys indicated as per the conventions given in the lecture slides (ie entity/table names in capitals, attributes as proper nouns, primary key underlined and foreign keys in italics.)

- A database schema indicating the type and purpose of all attributes.

Reference no: EM131182299

Questions Cloud

Select a suitable throat size : A cast venturi meter is to be used to meter the flow of 15°C water through a 10-cm pipe. For a maximum differential pressure of 76 cm H2O and a nominal 0.5-m3/min flow rate, select a suitable throat size.
Isolate the potentiometer from the remaining circuit : A potentiometer is used to measure angular position ? of the wheel. A voltage follower is used to isolate the potentiometer from the remaining circuit.
Find the level of production that will maximize revenue : A rancher has divided a plot of ground into an L shaped region. The larger section to serve as a corral and the smaller as a training area (see Figure 9) If 480 feet of fencing is available, determine the dimensions x and y which maximizes the tot..
Determine the maximum voltage : Determine the maximum voltage that can be applied to the potentiometer; Determine the value of series resistor Rs in order to protect the potentiometer if Vs=15V; If the resolution required is 0.010, determine the value for amplifier gain;
Create a database for flying high travel services : When a FHTS customer wishes to book business flights with accommodation, it is always done by the respective employees of that Australian company, who book online. This model is used to reduce administration costs.
What are the risks of enacting or not enacting the policy : What are the risks of enacting or not enacting the policy? How will the lives of people who are affected by the health issue change if this policy is enacted?
Explain what changes in research resulted : Utilizing the internet, or your textbook, choose one example of unethical research: Summarize the research. Explain why the research was unethical. Explain what changes (if any) in research resulted
Concrete block of mass : The system in Fig. 12-36 is in equilibrium. A concrete block of mass 225 kg hangs from the end of the uniform strut of mass 45.0 kg. For angles  = 30.0° and θ = 45.0°, find (a) the tension T in the cable and the (b) horizontal and (c) vertical com..
Price proposal form-measure of distance : Price Proposal Form, Measure of Distance - Is the distance just measured for transportation by road, or does this include distance via marine transportation also?

Reviews

Write a Review

 

Database Management System Questions & Answers

  Draw a fully-labeled erd that includes entities

Draw a fully-labeled ERD that includes entities, labeled relationships (indicate whether each relationship is mandatory or optional), primary keys, foreign keys, and cardinalities. Follow the format (Crow's Foot notation) and naming conventions.

  Order results by customers last name in ascending order

Only demonstrate those customers that have the word/partial word "mail" somewhere in the EmailAddress. Order the results by the customer's last name in ascending order.

  Discuss cloud computing and saas

Your team has decided to discuss 1) cloud computing, 2) SaaS (software as a service), 3) web, social media, and big data, and 4) real-time data. Create a PowerPoint with a minimum of 5-7 slides to present how data warehousing is headed in these fo..

  Data mining

DATA MINING-Business and Management Scenario assignment-Data Warehouse Reports. This is Part Three of the three-part assignment. For this week, you will complete the following: Resource: Business and Management Scenario assignment, Document data ware..

  Discuss three different ways to create an oracle database

Discuss three different ways to create an Oracle Database. What are the advantages/disadvantages of each method. What is the difference between control files and redo logs. What is a datafile. What is a tablespace

  Prepare table which is in first normal form

Prepare the example of table which is in first normal form but not in second normal form and example of a table which is in second normal form but not in third normal form.

  Define calculations and format the table

internship at Mountain View Realty, a real estate firm that focuses in the North Utah County area. The previous intern developed a spreadsheet listing houses listed and sold during the last several months. She included addresses, location, list pr..

  Design a database for dgthot

You are to design a database for DGTHOT (Dax's Good Times House of Tutoring). The company offers its tutoring services to both individuals and companies.

  Create a database for use by the employees

The vegetable database: You have to create a database for use by the employees of vegetables.com, an online store which sells many different vegetables, including onions, tomatoes, carrots, potatoes, squash, eggplants and zuchini

  Design relational database using entity-relationship diagram

Design a Relational Database using Entity-Relationship Diagram (ER-D) and design a Relational Database by Mapping Entity-Relationship Diagram (ER-D) into Relational Models.

  Explain how to develop a project charter

Using the above scenario, develop a project charter. Include the problem statement, justification for the project, and how the project will be measured for success.

  A county wishes to create a database

A county wishes to create a database to control its local libraries. Each library has a number of employees, one of whom is designated as the manager of the library and is responsible for supervising employees and the general day-to-day management..

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