Including the departure city and time for staff member

Database Design Assignment

Semester 2, 2015

Instructions:

Write a report (one report for each group) with your solutions to all the tasks. On the cover sheet clearly list all members of your group. Create a PDF file for the report, check that it can be opened and submit it using TurnItIn. Do not protect the PDF. You must use the ER symbols learned in the course.

Task 1: Conceptual design (50 marks)

Draw an EER or ER diagram for the conceptual design of the database. List all of your assumptions.

If you choose to use non-3NF tables, provide the reasons why.

Assign appropriate data types and lengths to all attributes.

Task 4: SQL Script files (20 marks)
Make an SQL script file (and name it for example airline.sql)that contain SQL statements to create the tables, insert sample data (minimum 5 records for each table), and do the queries (1), (5), (8), (9) and (10). You need to make sure your script files work correctly.

Create a transcript of the execution of all your solutions (and name it for example airline.lst). IMPORTANT: In the transcript include the question number in SQL statements using SQLPlus comments (‘‐‐') for example: SELECT * FROM employee E ‐‐ Q5
Copy and pastethe content of the transcript file to your report to be submitted to TurnItIn. Keep a copy of your solutions (airline.sql) and of your transcript file (airline.lst) as you might be required to present them in an interview.

2. The airline has pilots, flight attendants as well as other staff. For each member of staff it keeps data about his / her staff number (which is a unique identifier), the staff name, date of birth, home address, and date joined the airline. In addition, previous work experience including company, position, start and finish dates are also recorded.

3. For each staff member, an emergency contact person, including name, postal address, phone, email, and relationship with the staff, must be recorded.

4

8. The system must be able to make the following queries:

(5) For each city (given the city name), produce a list of the flights scheduled to arrive or depart within the next 24 hours. The list should include the flight number, the time of arrival or departure in ascending order of time.

(6) Given flight number, list origination city, stop-over city (if applicable), and destination city as well as the departure and/or arrival times at these cities.

(11) For a staff member, list the details of the emergency contact person.

(12) For a given date, list the airplanes scheduled for flights and those not scheduled for flights.

Task 1:

•You have recognised every entity (and represented it as an entity or as an attribute as appropriate).

•Correct primary keys represented for each entity participation and cardinality constraints are represented for each relationship (either of the two cardinality notations taught in the lectures is acceptable, as long as no ambiguity arises).

•Partial keys are correctly represented for each weak entity.

•You have correctly represented every attribute of each entity and relation. •You have chosen a correct way to map every entity, relation and attribute.

Task 3:

•All the SQL commands for inserting sample data (at 5 rows per table) are included. •All the SQL commands for doing the five queries are included.

•You have included the complete execution results of all your commands from the transcript.