Systems and Database Design Assignment: Database Design & Implementation (Airline)
The assignment consists of two tasks. The first task, Database Design, requires a word processed document in PDF format detailing the design of your database. The second task, Implementation, is a collection of SQL scripts which create and populate the database designed in the first task, and then query the data it contains. A small amount of marks are dedicated to presentation, notation and formatting.
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.
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, and 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.
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.
Filename: create.sql
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.
Make sure this script can be run multiple times without resulting in any errors (hint: drop the database if it exists before trying to create it). You can use/adapt the code at the start of the creation scripts of the sample databases available in the unit materials to implement this.
You will need to follow an appropriate creation order when creating your tables - you cannot create a table with a foreign key constraint that refers to a table which does not yet exist.
Once you have created your database, it is recommended that you use SSMS to create an ER diagram and use this to verify that your implementation matches your design. This can be done by right clicking on the "Database Diagrams" folder of the database in the Object Explorer in SSMS.
Following the SQL statements to create your database and its tables, you must include statements to populate the database with sufficient test data. You are only required to populate the database with enough data to make sure that all views and queries return meaningful results. You can start working on your views and queries and write INSERT statements as needed for testing as you go.
Filename: views.sql
Flight Instance View
Create a view which shows the following details of all flight instances:
-All of the columns in the flight instance table.
-The departure airport code, arrival airport code and distance of the flight.
-The full name of the pilot, co-pilot and flight service manager.
- Concatenate the first name and last name into one column, e.g. "Joe Bloggs".
-The model number of the plane.
-A column with an alias of "expected_attendants", which will contain the number of attendants which are expected to be on board based upon the capacity of the plane.
- The column should calculate this by adding together the plane's first class capacity and economy capacity and dividing the total by 100.
Creating this view will require joining the flight instance table to the flight table, the plane table, the attendant table, and the pilot table (twice) - a total of 5 joins. This view serves as a very convenient replacement for the flight instance table, as it includes the relevant details from numerous other tables - use this view in queries that need this data!
Filename: queries.sql
Write SELECT statements to complete the following queries. If you do not understand or are not sure about exactly what a query requires, contact your lecturer or tutor.
Query 1 - Plane Finder
Write a query that selects the registration number, model number, range, and total passenger capacity (first class plus economy) of all planes which have a total capacity of at least 400 and a range of at least 14000kms. Order the results by range, in descending order.
Query 2 - Flight Instance Descriptions
Write a query that concatenates various pieces of flight instance information to form a single column that describes all flight instances in the following way:
"The [departure time] instance of flight [flight number] from [departure airport code] to [arrival airport code] takes [travel time in hours] hours."
Query 3 - Departing Flight Information
Write a query that selects the flight number, arrival airport code, departure time minus one hour and model number of all upcoming flights (departure time in the future). Give the columns aliases of "Flight Number", "Destination", "Boarding Time" and "Plane". Order the results by departure time. Using the Flight Instance View in this query is recommended.
Query 4 - Flight Statistics
Write a query that selects the flight number, number of flight instances and total distance travelled of all flights. Only flight instances that have already occurred (i.e. ones before the current date) should be included, and your results do not need to include flights that have had no instances. Give all columns appropriate aliases, and order the results by the total distance in descending order. Using the Flight Instance View in this query is recommended.
Query 5 - Attendant Comparison
Write a query that selects the ID number, full name, and number of years worked for all attendants as well as the ID number, full name and number of years worked of their mentor, if they have one. Attendants with no mentor should appear in the results, but will have NULL for the mentor details.
Query 6 - Pilot Selection
Write a query that selects the pilot ID number and full name of all pilots who are qualified to fly plane used in flight instance 3. Use a subquery to determine the model of plane for flight instance 3.
Query 7 - International Flights
Write a query that selects the flight number, the name of the country that the departure airport is in and the name of the country that the arrival airport is in for all international flights - i.e. All flights where the departure airport and arrival airport are in a different country. This will involve multiple joins with the same table (use table aliases to make this possible).
Query 8 - Understaffed Flight Instances
Write a query that selects the flight instance ID, flight number, departure time, expected number of attendants and actual number of attendants for any flight instances where the actual number of attendants is less than the expected number. See Page 8 (views.sql) for details regarding the expected number of attendants, and use the information in your database to determine the number of attendants actually rostered onto the flight instance. This query will involve using COUNT, GROUP BY and HAVING, and using the Flight Instance View in this query is recommended.
Attachment:- Database Assignment.rar