RECENT ASSIGNMENT

Assignment 1
Overview
You are to create an ER diagram, and a relational schema for a small business. You need to provide a discussion 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.
Timelines and Expectations
Percentage Value of Task: 20%
Due: 5pm Friday the 29 august, 2016 (week 7)
Minimum time expectation: 12 hours
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 in low cost business travel packages by airlines. 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 to include 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 customer can also 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 one or more flights, by one or more airlines. Each flight 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 different class fares for each booking i.e. for different flights, but only one class fare for each flight.
• A cabin class fare is 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 end locations (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.
• A location 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.
Submission
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.
The assignment is to be submitted via the Assignment 1 submission box in Moodle. This is to be found in the Assessments section of the course Moodle shell.

Marking Criteria/Rubric
Assessment Criteria and Marking Overview Tasks Marks
1. Presentation
• Cover page indicating student name and number and tutor name. 2
• Page numbers included in report 2
• Index giving page numbers of various sections 2
• Overall presentation of the report 2
• Full APA referencing of all materials used and full disclosure of assistance from all sources including tutors and other students. 2 10
2. E-R diagram
• Completeness of diagram 12
• Correct notation and convention used 8
• All assumptions clearly noted 8
• Primary and foreign keys 10
• Resolution of many to many relationships 12 50
3. Normalization
• All entities and relationship in appropriate normal form 10
• Discussion of normalisation for all entities and relationships 5
• Appropriate interpretation of each normal form, arguments for leaving the schema in the normal form you consider optimal. 5 20
4. Conversion of E-R diagram to relational schema
• Correct standards, conventions and notation used 2
• Primary keys used 2
• Foreign keys correctly identified including parent entity 6
• Schema is a correct translation of the E-R diagram submitted with appropriate tables, columns, primary keys, and foreign keys. 6
• Types and restrictions on attributes given 4 20
Total 100