Database Design and Implementation

Task 1 - Database Design

Your first task is to design a database for the scenario detailed on the following pages. Your final database design should comprise of approximately 10 entities.

State any assumptions you have made regarding your database design at the beginning of the database design document. Do not make any assumptions that significantly change the structure of the scenario, as this may make Task 2 of the assignment difficult. Only make assumptions that influence your database design. If you are unsure about an assumption you wish to make, ask your tutor.

Once you feel you have identified the entities, attributes and relationships of the scenario in sufficient depth, you are required to create a logical ER diagram and a corresponding physical ER diagram (5 marks) to depict your database. Adhere to the distinctions between logical and physical ER diagrams covered in Lecture 3. It is recommended that you draw your diagrams on paper first, in order to find a layout that is clear and can be created in an electronic format.

Lastly, create a data dictionary (7 marks), with an entry for each entity in your database. The entries should list the name of the entity, a description of its purpose, a list of attributes (columns), important information about the attributes (e.g. data type, null/not null, identity, default values...), and details of any constraints applied to attributes. List the entries in your data dictionary in an appropriate table creation order that can be used to create the database. Include any additional information, if any, that may be needed to implement the database. Remember, a data dictionary should contain all the information needed to implement a database. Use the data dictionary in Lecture 4 and the data dictionary of the "company" example database (Module 5) as examples. Some marks are also awarded for presentation and notation.

Your complete database design should consist of a list of assumptions, logical and physical ER diagrams and a data dictionary. This should be in the form of a single PDF document. Make sure that your assignment includes the unit code, assignment number/name, year and semester and your name and student number on the first page.

Scenario Details

You are required to design and create a database for an airline. The database must contain details of the airline's planes, flights, flight instances and staff, as well as supporting data as detailed below.

- Details of the planes owned by the airline must be stored. This must include the registration number of the plane, the year it was built, its first class and economy passenger capacities, and a foreign key identifying the model of the plane.

- Details of plane models must be stored. This must include the model number, manufacturer name, range (how many kilometres it can travel without refuelling) and cruise speed.

- Details of flights must be stored. This must include a flight number, a foreign key identifying the airport the flight departs from, a foreign key identifying the airport the flight arrives at, and the distance between the airports in kilometres.
- Note: A flight is a route that the airline offers - e.g. Flight QF574 is a Perth to Sydney route offered by Qantas.

- Details of flight instances must be stored. This must include a flight instance ID, a foreign key identifying the plane making the flight, a foreign key identifying the flight that is being flown, the date/time that the flight leaves, and date/time that the flight arrives.
- Note: A flight instance is a specific occurrence of a flight - e.g. The 2016-07-04 QF574 flight at 05:30.

- Details of airports must be stored. This must include the airport code (e.g. "PER" for Perth), the name of the airport, a contact phone number, its latitude and longitude, and a foreign key identifying the country that the airport is in.

- A list of countries must be stored. This must simply contain the two letter country code (e.g. "AU") and the name of the country.

- Details of pilots must be stored. This must include a pilot ID, their first name, last name, date of birth and number of hours flown.
- The database must record which models of plane each pilot is qualified to fly. Each pilot must be qualified to fly at least one model of plane.
- The database must record which pilots are aboard each flight instance. Each flight instance requires exactly two pilots (a pilot and co-pilot).

- Details of flight attendants must be stored. This must include an attendant ID, their first name, last name, date of birth and hire date.
- For training purposes, some flight attendants mentor other flight attendants. The database must record each flight attendant's mentor, if they have one.
- The database must record which attendants are aboard each flight instance. A flight instance can have many attendants on board.
- The database must record which attendant has been designated the flight service manager (FSM) of each flight instance. Each flight instance must have one FSM.

General Information and Guidelines
The information above describes all of the entities, attributes and relationships required in the database design. Some minor details, such as the cardinality of some relationships, have been omitted. It is up to you to make (and state) any assumptions you need in order to complete the database design. If you are uncertain about any part of the scenario described above, seek clarification from your tutor.

Many of the entities in this scenario contain an identifying attribute that is suitable to be used as the entity's primary key, such as plane registration numbers, model numbers, flight numbers, airport codes, etc. These values will not be auto-incrementing. For entities that do not have a meaningful attribute to use as the primary key, use auto-incrementing integers - for example, flight instance ID numbers, and the ID numbers of staff should be auto-incrementing integers. A compound primary key may be suitable for certain intermediary entities.

Be sure to specify the most appropriate data type (and length, where applicable) for each attribute in your data dictionary. Note that when you are storing a date/time (for example the departure time or arrival time of a flight instance), it should be stored as a single column - do not split the date and time into two columns unless there is a very good and necessary reason to do so.

Read the scenario details several times to ensure that your database design incorporates all the elements described. If you desire feedback on your work in progress, send it to your tutor.

Additional Requirements

Task 2 - Implementation

Once your database has been designed, it is time to implement it in a DBMS, populate the database, and then manipulate the data via queries. The deliverables of this task are three files containing SQL statements. We will be using Microsoft SQL Server 2008 R2 or above - your SQL scripts must run in the same environment used in the unit/labs.

Create your scripts as three ".sql" files, with the filenames listed in the following headings. Templates for the script files are provided with this assignment brief - please use them. Format your code for readability, and use comments for headings and to provide further detail or information about your code if needed.

Database Creation & Population Script
Produce a script to create the database you designed in Task 1 (incorporating any changes you have made since then). Be sure to give your columns the same data types, properties and constraints specified in your data dictionary, and be sure to name tables and columns consistently. Include any logical and correct default values and any check or unique constraints that you feel are appropriate.

Attachment:- Assignment_162.rar