In Assignment 01, you designed a database for Terrific Airlines. You are now expected to implement the database. There have been some minor changes in the requirements from Assignment 01 needed to support the transactions and views listed below. You will need to incorporate these changes and any changes you have made because of the feedback you received on Assignment 01. What you need to do:1. Create an ERD for this database that you use as the basis of your implementation. (15 marks)2. 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. (5 marks)3. Create a data dictionary that lists at least each of the tables, the columns, their domains and any other constraints that apply. (15 marks)4. Implement the database in Oracle SQLPlus on arion.murdoch.edu.au (20 marks)a. All tables should be created as per your ERD; the marker will check your ERD against your tables.b. All integrity constraints should be created and appropriately named.c. All columns should be of an appropriate domain/size.d. All tables should be populated with sample data that will allow the marker to test that your database fulfils the application requirements as specified and support the transactions and views listed below.e. SELECT, UPDATE and DELETE permissions should be GRANTED on all database objects (particularly tables and views) to the user MARKERTL. This is most important. If you do not grant this permission, the marker will not be able to mark all or part of your assignment. 5. Provide all the SQL statements that are required for the following transactions to be executed (25 marks):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 McGeeCo-Pilot: Dorothy McDonaldEngineer: Albert TharpHead Steward: Kathy KellySteward: Aubrey OrnellasTransaction 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 6. 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 flightViewB: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. Please note the following about the marking of this assignment:1. The marker will view your documentation and then match your documentation to your implementation. This means for example, that tables, columns and constraints should be named in your database as they are in your documentation. Relationships defined in your ERD should be defined in your database. 2. The marker will view the sample data in your tables. 3. The marker will check that the data as per the transactions in 5 above have been included. 4. The marker will execute each of the views created for 6 above. 5. AGAIN, please ensure that you GRANT the appropriate privileges on all relevant objects (tables and views) to the user MARKERTL. If you do not do this, the marker will not be able to mark part of your assignment (and you may be awarded 0 for this section). RouteFlightRouteNeSeriaNoOriginModelAerivaTimeCapacityMaintenanceDateDestinationMaintenance_RecordCrewMaintenancetoEmployeelDPlaneNoJobTteDescriptionNamePhoneDateRoleResponsbleEmployeeReservationCustomerCredit CardFirstNameNumberReservationNoExpiryDateFlightNoLastNameDatePhoneFarePayment

Introduction Data Dictionary: A data dictionary is a tabular format representation of data model…

Answered: What you need to do: 1. Create an ERD…

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

In Assignment 01, you designed a database for Terrific Airlines. You are now expected to implement the database. There have been some minor changes in the requirements from Assignment 01 needed to support the transactions and views listed below. You will need to incorporate these changes and any changes you have made because of the feedback you received on Assignment 01.

What you need to do:

1. Create an ERD for this database that you use as the basis of your implementation. (15 marks)

2. 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. (5 marks)

3. Create a data dictionary that lists at least each of the tables, the columns, their domains and any other constraints that apply. (15 marks)

4. Implement the database in Oracle SQLPlus on arion.murdoch.edu.au (20 marks)

a. All tables should be created as per your ERD; the marker will check your ERD against your tables.

b. All integrity constraints should be created and appropriately named.

c. All columns should be of an appropriate domain/size.

d. All tables should be populated with sample data that will allow the marker to test that your database fulfils the application requirements as specified and support the transactions and views listed below.

e. SELECT, UPDATE and DELETE permissions should be GRANTED on all database objects (particularly tables and views) to the user MARKERTL. This is most important. If you do not grant this permission, the marker will not be able to mark all or part of your assignment.

5. Provide all the SQL statements that are required for the following transactions to be executed (25 marks):

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 20^th 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 20^th 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 20^th 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 20^th June 2021 left Perth at 1105 and arrived in Singapore at 1555.

Transaction 05:

On 21^st June 2021, FH-FBT had a scheduled maintenance at the Melbourne Airport. The maintenance was supervised by Laurence Schreiner

6. 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 20^th June 2021.

ViewC:

Total hours flown EVER, for crew of the FBN001 on 20^th June 2021.

ViewD:

Total hours flown EVER, for the Pilot of the FBN001 on 20^th 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.

Please note the following about the marking of this assignment:

1. The marker will view your documentation and then match your documentation to your implementation. This means for example, that tables, columns and constraints should be named in your database as they are in your documentation. Relationships defined in your ERD should be defined in your database.

2. The marker will view the sample data in your tables.

3. The marker will check that the data as per the transactions in 5 above have been included.

4. The marker will execute each of the views created for 6 above.

5. AGAIN, please ensure that you GRANT the appropriate privileges on all relevant objects (tables and views) to the user MARKERTL. If you do not do this, the marker will not be able to mark part of your assignment (and you may be awarded 0 for this section).