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.
In Assignment 01, you designed a
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 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
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 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).
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
