ER DIAGRAM AND RELATIONAL SCHEMA FOR A SMALL BUSINESS

  100% Pass and No Plagiarism Guaranteed

ER DIAGRAM AND RELATIONAL SCHEMA FOR A SMALL BUSINESS

Overview

You are to create an ER diagram, and a relational schema for a small business. You need to provide adiscussion of normalisation including the normal form that each entity is in, a list of relationships with all table names, primary and foreign keys, and attributes indicating their type and purpose.

Learning Outcomes Assessed

The following course learning outcomes are assessed by completing this assessment:
• K4. design a relational database for a provided scenario utilising tools and techniques including ER diagrams, relation models and normalisation
• K5.describe relational algebra and its relationship to Structured Query Language (SQL);
• A1.design and implement a relational database using a database management system;

Project Specification

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 put together from offers by airlines, hotel chains/resorts and pre-existing holiday packages. They keep prices low by dealing directly with the airlines, hotel chains and global travel agencies, where they can source and combine surplus accommodation and flights. When a FHTS customer wishes to book business flights and accommodation, it is always done by the respective employees of that Australian company themselves who book online. This model is used to reduce administration costs. Once the online booking is made, FHTS guarantees that a booking will be finalised withthe travel details to the respective customer(s) within 30 minutes of that booking. The companies that book with FHTS, don’t require quotes, nor wish to check their bookings, as it is known that FHTS sources the cheapest accommodation and flights, and an overall Service Level Agreement legally binds them to that. Therefore, only an invoice is generated to the respective company of the employees once the booking is finalised. The MINIMUM entities you are expected to have are listed below. No normalisation has been undertaken on these entities, so there may be many to many relationships that are not resolved. 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.

Information and business rules gathered about the current business activities are as follows:

• 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. 
• Also aFHTS customercan be a frequent flyer, if they travel a great deal, or a VIP if they belong to a high volume company.If the FHTS customer has either a VIP status, or is a frequent flyer then a discount will apply. The frequent flyer discount is calculated by any airline. The VIP status discount is calculated by any of theaccommodation companies. Neither discount can be applied to any included holiday packages.
• There are three types of VIP status, these include Gold, Silver and Bronze. This depends on the company, also some companies don’t have a VIP status at all.
• A customer can make many travel bookings with FHTS and it is possible that a number of customers (employees from one company) can be part of one booking. Customer details are always taken at each booking. The VIP status discount applies to all customers listed on the booking, but the frequent flyer discount is applied individually, for the respective holder.
• Each booking needs a unique identification, customer details, package details, invoice details and the details of the FHTS staff who handled the booking. Also each booking contains details of the airlines, destinations and any sourced holiday packages used to compile the discount business travel arrangements.
• A completed booking will automatically generate one invoice to the company of the customers who made the booking. The invoice will include the costs for each customer listed in the booking.i.e. airline, accommodation or alternative holiday packages. It will also include a 5% commission for FHTS and the overall travel costs.
• There are many staff working at FHTS who take the bookings from the customers. However, only one staff member is allocated to each booking. If a staff member becomes sick then their supervisor takes over bookings, until the staff member returns.
• 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. If the supervisor leaves, then it is just a matter of updating staff details with a new supervisor’s details at any stage.
• A staff member needs a unique ID, name, phone number, email address, their supervisor name and supervisor ID and an attribute to state if there has been supervisor intervention for a respective booking.“Yes” or “No” is all that is needed.
• The customers may need to use none or many airlines; may need to fly to none or many destinations, depending on whether one or more holiday travel packages were used for all or part of the business travel package. It is possible that one or more flights and/or accommodation are needed in conjunction with any included holiday packages, or not.
• If one or more holiday travel packages are included into the business travel package, each are seen as separate entities on the invoice and each always include only one destination, one return and one accommodation site and would be sourced from an outside database. The entity can be considered as just a holiday package entity, with travel agency ID, travel agency name, date of package issued, date of package expired, departing date, return date
o The flight details are linked to the Airline entity
o The accommodation details are linked to the Destination entity
• An airline would include Airline ID, Airline name, flight number, departing date and time, landing date and time, cabin class
• One or more frequent flyer discounts may be used fornone or many flights. (Depending on the number of customers on the booking, and on the inclusion of a holiday package).
• A destination would have a destination ID, destination name, destination address, post code, email address, date of arrival, date of departure, VIP discount code, VIPdiscount amount.

Marking Criteria/Rubric