ICT285 - Databases - Computer Science Assignment Help

Assignment Task

What you need to do:
Create an ERD for this database that you use as the basis of your implementation.
A one or two paragraph explanation as to the changes you have made to the ERD based on your feedback from Assignment 1 or because of having to support the transactions and views below.
Create a data dictionary that lists at least each of the tables, the columns, their domains and any other constraints that apply.
Implement the database in Oracle SQLPlus

Provide all the SQL statements that are required for the following transactions to be executed:
Transaction 01:
The FBN001 route is from Perth to Singapore and has an estimated time of departure of 1100 and estimated time of arrival of 1600. On 20th June 2021, the airplane FH-FBT will be flying the route FBN001. It is a Boeing 767 with a capacity of 350 seats.

Transaction 02:
Record the fact that FBN001 on 20th June 2021 will have the following crew:
Pilot: Martha McGee
Co-Pilot: Dorothy McDonald
Engineer: Albert Tharp
Head Steward: Kathy Kelly
Steward: Aubrey Ornellas

Transaction 03:
Make a reservation for John Smith on Flight FBN001 on 20th June 2021. FBN001 flies from Perth to Singapore and has an estimated time of departure of 1100 and estimated time of arrival of 1600. He pays for his reservation with cash.

Transaction 04:
Record that FBN001 on 20th June 2021 left Perth at 1105 and arrived in Singapore at 1555.

Transaction 05:
On 21st June 2021, FH-FBT had a scheduled maintenance at the Melbourne Airport. The maintenance was supervised by Laurence Schreiner
Provide VIEWS for the following (views should be named as ViewA, ViewB etc) (20 marks):
ViewA:
All flight reservations made by John Smith including, for those flights that have flown, the duration of the flight
ViewB:
Number of unreserved/available seats on FBN001 on 20th June 2021.
ViewC:
Total hours flown EVER, for crew of the FBN001 on 20th June 2021.
ViewD:
Total hours flown EVER, for the Pilot of the FBN001 on 20th June 2021, broken down by that person’s role (i.e., how many hours as pilot, how many as co-pilot etc?)
ViewE:
Maintenance history for FH-FBT including the date and location of maintenance episode, whether or not the maintenance was scheduled or not, and the name and phone number of the supervising employee.

This ICT285 - Computer Science Assignment has been solved by our Computer Science experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.