Computer Science Assignment – Database Programming
The objective of this assignment is to:
(1) Enhance your understanding of Relational Database Design & Implementation, and
(2) Understand & practice with PL/SQL in particular database interaction, functions & procedures, check constraints, triggers and packages.
The assignment will consist of four (4) tasks.
— Task one will involve the extension of the relational model from assignment 1 and the development of the code for maintaining the consistency of the database.
— Task two will involve the development of several functions and procedures which will be encapsulated in a package with a separate specification (header) and a body (implementation).
— Task three will involve developing a database trigger.
— Task four will involve testing the code developed as part of tasks one to three. You may want to insert a sufficient number of rows into the tables so that you can demonstrate the workings of all the code components.
Methodology
** Using your solutions to assignments 1 & 2, create the DDL/SQL script file to create the tables and attributes (dropping them all beforehand of course).
** Write the code for Task one in a separate SQL script and test it to check for errors.
** Write the package specification and the package body in two separate SQL scripts.
** Write the SQL script file to insert the records (at least five records per table) into the relevant tables so that you can test the functions and procedures provided in the package above.
** Refer to the Oracle 11g documentation files when necessary.
** Compile all the separate SQL scripts together into a single script (Assignment2.sql).
** Place the test results into your report (Assignment2.doc).
The EER diagram for Task One from the Diagnostic Assignment:
Note: conductTest is a relationship among Airplane, Technician, Senior_Technician and Testinfo with attributes: data, score, and hours. And service is a relationship among Flight, Technician, and Senior_ Technician with attribute date and time.
2 Task Specifications
Task 1
You are required to add another attribute called NoofPassengers to each flight to record the number of passengers booked on that flight. You are also required to ensure the consistency requirement that NoofPassengers does not exceed the capacity of the airplane that serves that flight.
Discuss how you could maintain the consistency of the database. This discussion should go in the report. Then revise your DDL script from assignment 1 to add NoofPassengers to the relevant flight table as well as implementing the necessary SQL and/or PL/SQL code to maintain the consistency of the database. Place the revised DDL statements and the code in your SQL script.
Task 2
Write the package body for the following package specification. Place the package specification and the package body in your SQL script. The marks for each procedure are of equal value.
create or replace package assignment2
is
procedure open_flight(. . .);
procedure schedule_test(. . .);
procedure available_airplanes(. . .);
end assignment2;
/
The detailed description of each procedure is provided below:
** procedure open_flight(. . .)
This procedure takes the flight information as parameters in order to open a new flight to serve a particular route and destination on a particular day. It also assigns a particular airplane to the flight. You will need to ensure that there is only one flight serving the same route and destination on the same day, and also that the airplane is available on that day.
**procedure schedule_test(. . .);
This procedure schedules a particular test for a particular airplane on a particular date. You will need to ensure that the airplane is available on that day for the test. When a test is scheduled, the test score should be NULL. Note that in this procedure you do not need to deal with the assignment of technicians for the test; you only do this if it is necessary in your design (e.g., because of referential integrity).
**procedure available_airplanes(. . .);
This procedure will print out, using a cursor, all the airplanes that are available on a particular day satisfying a given capacity constraint. The airplanes must have passed all the scheduled tests within the last three months. Passing a test will require obtaining at least 85% of the maximum score.
Task 3.
Write a trigger called check_flight_safety with the following specification: It will fire when the score for a scheduled test for a particular airplane is updated. If the test score is less than 85% of the maximum score, this means that the airplane is not safe to fly and all the scheduled flights for the airplane must be cancelled. Place the trigger code in your SQL script.
create or replace trigger check_flight_safety
after update on . . .
for each statement
begin
. . .
end;
/
Task 4.
This task involves testing all the code developed in Tasks 1, 2 & 3 above. You can extend your script from assignment 1 for this purpose by creating meaningful scenarios for testing the trigger, and all the functions and procedures in this assignment. When testing an update operation on a table, show all the records in the table before and after the update. Place all the test code in your SQL script. All the test results are to be placed into your report (Assignment2.doc).
3 Submissions
What to submit
Your submission consists of one SQL file named Assignment2.sql (DDL scripts, all the SQL and
PL/SQL code and test cases) and one file called Assignment2.doc.
Marking Notes
Your submitted file Assignment2.sql may be executed in Oracle during the marking process and also manually marked for clarity.
Please note that:
** You may use as much or as little SQL within functions and procedures as you wish provided that you do what is required for each part of the question. You may also implement any additional PL/SQL objects as you wish.
** Any SQL and/or PL/SQL statements and/or blocks containing syntax errors are not acceptable. If you have any syntax errors in your code, you may get zero marks for that part.
