Edinburgh Airport officials are concerned about complaints received by the service department and are about to do an overhaul of the service maintenance system at the airport. The general manager has decided that the information needs to be stored in a DBMS and has engaged you as the designer of the new system. You are required to gather all the relevant technical and personnel information, which will allow you to construct this new system.

The airports technical department maintain all the aircraft that are based at this airport. The information system you create will need to store all the relevant details along with the staff which service and maintain these aircraft.

The aircraft based at Edinburgh are normally identified by an international registration number. The registration number consists of a two letter code identifying the owning airline and a three letter unique id within the airline fleet. For example, ?BA-237? would be a valid aircraft registration number. ?BA? is the code for the British Airlines fleet of aircraft and ?237? identifies one particular aircraft of the BA fleet.

Each aircraft is of a specific type of aircraft which the airport must be licensed to accommodate. Each type of aircraft has a unique model name (e.g. ?A320-100? or ?B747-400?) and is produced by a specific maker (e.g., ?Airbus? or ?Boeing?). The model name typically contains some indication of the maker (e.g. all model names used by Boeing start with a ?B?), a model number (such as 747 or 320) and a sub-model code that allows us to identify the aircraft model. For example, a ?B747-400 ER? is the extended range version of the latest version of the Boeing 747, as is ?A320-100? a specific variant of an Airbus A320.

The airport also wishes to keep track of a number of technical details of each type of aircraft such as:

• maximum cruising range (in km),
• length, span and height, of aircraft
• whether it is a ?Jet?, a ?Prop? or a ?Turboprop? plane.
• All aircraft are strictly airliners or some sort of commercial aircraft
• Note also that each type of aircraft has a fixed number of engines

As part of this technical specification, the airport wants also to keep track of both the emission category and the noise class of each type of aircraft. There are five emission 2 categories, from highest (cat 1) to lowest (cat 5). The noise class is expressed as a numeric value ranging from 1 for the loudest aircrafts up to 5 for the most quiet noise class. Class 0 is used for propeller driven aircrafts (?Prop? or ?Turboprop? planes).

The aircraft themselves are owned by only a single airline carrier (so no joint ownership). The system will need to keep track of details such as: contact phone, fax numbers of every airline, host internet website. For each airline they will have a home country where it is flying from. Airlines are identified by their unique name.

With regard to the Maintenance service department: the system must concentrate on the technicians alone. They are uniquely identified by their employee id. You need to consider the storage of details such as: the name (both given and family name), employee id, address, phone number, and salary for each technician, as well as their login. Each technician is an expert on one or more types of aircraft, and his or her expertise may overlap with that of other technicians. This information about technicians must also be recorded.

The actual testing done on the aircraft should also need to be stored and details such as the following is important: keep track of each time a given aircraft is tested by a given technician with regard to a certain test specification. You can determine some of the relevant attributes that might be important

Naturally NO CASE STUDY can capture all the details of the operations of the organisation; you are permitted to make any assumptions.

Task 1

Business Case

Task 1A. Discuss why a relational database would be suitable for the organisation in the case study and provide (3) three reasons for this recommendation.

Task 1B. Discuss using (3) three advantages that a database management system provides to an organisation. Note: Make these relevant to the organisation in the case study.

Requirements Definition

Read the case study carefully, and decide what are the important (key) features of the system. Construct a basic prototype that will display the following:

Task 1C. Initial screen designs that can be shown to the client - this allows for the input of information to satisfy the user requirements. Submit a hardcopy that has a set of screen shots that illustrate the screen designs.

Task 1D. List typical reports that would be likely features of the database - this requires you to assume the role of the user/manager and list three (3) likely reports he/she might need to do their job. Submit a hardcopy which shows the report, its main features, and then populate it with some likely test data. Submit a hardcopy that has a set of screen shots that illustrate the screen designs.

Note:

A suitable package to do the above task (A to D) would be Microsoft Word or any similar product.

Task 2

Development

Use database development strategy to decide the main elements of the database - this will include nominating the:

Fields (also the primary keys and foreign keys)

Tables

Task 2A. Create a Data Dictionary - which contains the following: table, field type, field size and field description

Note:

A suitable package to do Task 2B would be Microsoft Word or any similar product.

Database Design

Develop an initial prototype of this case study system, therefore in your design phase you and your team should create the following appropriate diagrams. Use the MySQL Workbench (or equivalent tool set) to create the following:

Task 2B. An Entity - Relationship diagram.

Task 2C. Normalised Schema - normalise to 3rd normal form. Provide a discussion that shows how you schema passes each of the normal forms.

Note:

A suitable package to do Task 2B would be Gliffy, EDraw, Microsoft Visio or any similar product.

Task 3

Implementation of a Relational Database

Task 3A. Use the final normalised ER - Diagram to create the physical database. Use MySQL server, Oracle Express or an equivalent relational database to create the system. Provide a screen shot of the final system that displays the entire set of tables.

Populate the Database

Task 3B. Enter six (6) records into the system that you can use to display to the client that the system can operate. Provide screenshots of the final system that displays (3) three of the tables with the mock data inserted.

User Help Guide

Task 3C. In order to complete the assignment, you need to provide a user manual that will describe how to use the system. Screenshots of the main menu and navigation between the sub-menus along with accurate descriptions is an important feature of this section of the assignment.

Note: You may use previous screenshots (forms, reports and data screens) to create this manual.

Improvement

Task 3D. Provide a lessons learned log - that indicates what you found important and any improvements you wish to make for the next version of the database.

Note:

A suitable package to do Task 3A, Task 3B would be MySQL community server or any similar product.

A suitable package to do Task 3C, Task 3D would be Microsoft Word or any similar product.