4004CMD Working with Data Applied Core Assessment – Individual Report Assignment | CU

Assignment Task

Write a 2500 (maximum) word report on database design and its implementation based on the scenario below (100 marks). The report should use an appropriate structure; cover page, contents page, references where appropriate (correctly cited), diagrams should be large and clear, appropriate section headings to clearly identify which part you are answering. The report should look professional and be written in a clear and concise manner.

“Phoenix Air" is a relatively new company that book flights for people. Until now they have used a spreadsheet to keep a record of bookings, passengers, flight and aircraft details. They would like you to create a database to store this information. This will help them to grow their business, for example; keeping track of bookings, targeted marketing, printing ticket information and generating invoices. The amount of data that will be stored means that a Relational Database would be the best solution for this scenario.

There are three separate tasks for this Coursework, with multiple parts to each:

1.  TASK 1 – Design, implement and query a Relational Database
2. TASK 2 – Data Visualisation on the un-normalised dataset
3.  TASK 3 – A reflection on the report

NB Both tasks use the same dataset, but I have provided them in TWO separate excel files as they are formatted slightly differently. Both datasets can be found in the section for “Applied Core Assessment” on the Assessment page on Aula.

TASK 1 – Relational Database: Design, Creation and Querying

Dataset to use for Normalisation
This spreadsheet contains relevant information about the dataset which you must use when answering the following questions. Please note that code MUST be written in the report with screenshots showing successful execution of the commands; code must not be included as a screenshot as it will not be marked.
A)    With specific reference to the dataset, explain why it should be converted into a relational database and what are the advantages of doing so. You should give examples from the data that demonstrate the point you are trying to make (e.g. do not just write about “deletion anomalies”, show specific examples within the dataset).

B)    Database Security: Research and write a short paragraph identifying TWO security concerns with the use of Relational Databases. This should include what the issues are and how to address them.
 C)    Demonstrate the detailed processes of implementing the relational database:

i.    Show each stage of normalisation; You should include the table schema at each stage of normalisation, what the rules are for each stage of normalisation, what the changes are that you made to meet the rules, and the justification for making them. Make sure any keys you apply to the table are clearly defined at every stage.

ii.    Produce a “Physical” Entity-Relationship Diagram of the final DB schema (the final schema is the table design at the end of 3NF) using Crow’s Foot notation.

iii.    Write SQL commands to create the tables for your final DB schema. You should populate each of them with the relevant data and include proof of successful creation of the database (screenshots of each table with data inserted). You should include the code (SQL Statements) you use to CREATE each table. You do not need to include the code for the INSERT statements; you can either choose to insert the data using SQL or import from a CSV file. I do not need to know which you chose – but you should include evidence of the tables populated with data (screenshot).

D)    Write some SQL code for the following queries together with a screenshot of the results and a brief explanation of key elements of the code:

i.    Write a query to output the Passenger contact details along with a column that contains “Yes” if they are a member of the “Frequent Flyer Loyalty Scheme” and “No” if they are not. You should output the column headings as specified here: ‘Customer Name’, Telephone Number’, ‘Email Address’, ’Member’.
For Example:

Customer Name    | Telephone Number    | Email Address    | Member
Charles Lindbergh    | 0121 543182    | clindbergh@gmaiy.com
| YES Usha Sundaram    | 0123 654263    | sundaram@gsnail.com
| NO

ii.    Find which passenger that has flown the greatest number of times. Output their name, contact details and their total number of flights. You should only output 1 row with the answer in.

iii.    Write code to output a report of how long (in total) each aircraft (identified by RegNo) has spent in the air. (number of total number of minutes travelled). Output the Aircraft’s RegNo, Code, Aircraft Type and last maintenance date. The report should be ordered by the airplane that has spent the longest amount of time in the air to the one that has spent the least.

iv.  Phoenix Air noticed there was an error on their original spreadsheet - it had omitted flight data for passengers flying with them in the past. As a gesture of goodwill, they have decided to increase everyone’s current total miles flown by 25% which means the passenger may fall into a higher loyalty band, and therefore the data may need to be updated (by the team at Phoenix Air). What you have been asked to do is write a report that outputs each passenger name, the current total of Km’s travelled, current Loyalty Band level, the total number of Kilometre’s they have travelled PLUS 25%, and which band of the loyalty scheme they should be in with the increase (this may still be the same one they are currently in or they may be in the next bracket). This report will be used by Phoenix Air to see what further updates may be required – you DO NOT NEED TO UPDATE THE DATA just produce the report. For example; Orville Wright is currently a Bronze member, with the increase they will become a Silver member:

Customer Name  | Current KM | Loyalty Level | Updated KM | New Loyalty Level
Orville Wright | 13152    | BRONZE    | 16440    | SILVER

TASK 2 – Data Visualisation

This dataset should be used for TASK 2: Phoenix Air Dataset for Visualisation

This dataset is different to the one used in TASK 1 (no lookup table required). Further explanation is given in the spreadsheet about the dataset. Use this dataset to do the following:

1. You need to create 4 graphs; each one should be of a different type. Each graph should illustrate appropriate information from the dataset, and you should justify your choices. You must demonstrate their implementation in Python. Both the code (in-text) and the output graph (screenshot) itself should be included in the report. For each of the graphs, you must include an explanation of any conclusions demonstrated.

TASK 3 – Reflection

Write a short reflective paragraph about this coursework. You should consider things like;

1. What you found easy and/or difficult
2. How you would do things differently in future
3. What you have learned on this module
4. How the skills learnt will benefit your chosen degree

Assessed Module Learning Outcomes

The Learning Outcomes for this module align to the marking criteria which can be found at the end of this brief. Ensure you understand the marking criteria to ensure successful achievement of the assessment task. The following module learning outcomes are assessed in this task:
1.    Create efficient database designs using techniques such as normalisation and Entity-Relationship (ER) diagrams for a range of applications.
2.    Create, populate and perform basic access approaches on a database using an appropriate tool, for example SQL.
3.    Identify security threats to databases and apply appropriate control measures.
4.    Analyse different types of data using tools such as R or Python.