Computer Science Assignment - Database Programming
AmazingExpert
questions.zipAss2-Descriptor.docx
1 Problem Description
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.
image1.jpeg
sample-script-.sql
/*==============================================================*/ /* DBMS name: ORACLE Version 11g */ /* Created on: 3/10/2013 6:03:53 PM */ /*==============================================================*/ alter table CONDUCTTEST drop constraint FK_CONDUCTT_CONDUCTTE_AIRPLANE; alter table CONDUCTTEST drop constraint FK_CONDUCTT_CONDUCTTE_SENIOR_T; alter table CONDUCTTEST drop constraint FK_CONDUCTT_CONDUCTTE_TESTINFO; alter table CONDUCTTEST drop constraint FK_CONDUCTT_CONDUCTTE_TECHNICI; alter table SENIOR_TECHNICIAN drop constraint FK_SENIOR_T_INHERITAN_TECHNICI; alter table SERVES drop constraint FK_SERVES_SERVES_FLIGHT; alter table SERVES drop constraint FK_SERVES_SERVES2_AIRPLANE; alter table SERVICE drop constraint FK_SERVICE_SERVICE_TECHNICI; alter table SERVICE drop constraint FK_SERVICE_SERVICE2_FLIGHT; alter table SERVICE drop constraint FK_SERVICE_SERVICE3_SENIOR_T; alter table TECHNICIAN drop constraint FK_TECHNICI_INHERITAN_EMPLOYEE; alter table TECHNICIAN drop constraint FK_TECHNICI_SUPERVISE_SENIOR_T; alter table TRAFFIC_CONTROLLER drop constraint FK_TRAFFIC__INHERITAN_EMPLOYEE; alter table FLIGHTDAY drop constraint FK_FLIGHTDAY; drop table AIRPLANE cascade constraints; drop index CONDUCTTEST4_FK; drop index CONDUCTTEST3_FK; drop index CONDUCTTEST2_FK; drop index CONDUCTTEST_FK; drop table CONDUCTTEST cascade constraints; drop table EMPLOYEE cascade constraints; drop table FLIGHT cascade constraints; drop table FLIGHTDAY cascade constraints; drop table SENIOR_TECHNICIAN cascade constraints; drop index SERVES2_FK; drop index SERVES_FK; drop table SERVES cascade constraints; drop index SERVICE3_FK; drop index SERVICE2_FK; drop index SERVICE_FK; drop table SERVICE cascade constraints; drop index SUPERVISE_FK; drop table TECHNICIAN cascade constraints; drop table TESTINFO cascade constraints; drop table TRAFFIC_CONTROLLER cascade constraints; /*==============================================================*/ /* Table: AIRPLANE */ /*==============================================================*/ create table AIRPLANE ( REGNO VARCHAR2(10) not null, MODEL VARCHAR2(30), CAPACITY NUMBER, WEIGHT NUMBER, DATEOFMANUFACTURE DATE, constraint PK_AIRPLANE primary key (REGNO) ); /*==============================================================*/ /* Table: CONDUCTTEST */ /*==============================================================*/ create table CONDUCTTEST ( REGNO VARCHAR2(10) not null, SEN_EMPID VARCHAR2(10) not null, TESTNO VARCHAR2(10) not null, EMPID VARCHAR2(10) not null, Test_DATE DATE, SCORE NUMBER, HOURS INTEGER, constraint PK_CONDUCTTEST primary key (REGNO, SEN_EMPID, TESTNO, EMPID) ); /*==============================================================*/ /* Index: CONDUCTTEST_FK */ /*==============================================================*/ create index CONDUCTTEST_FK on CONDUCTTEST ( REGNO ASC ); /*==============================================================*/ /* Index: CONDUCTTEST2_FK */ /*==============================================================*/ create index CONDUCTTEST2_FK on CONDUCTTEST ( SEN_EMPID ASC ); /*==============================================================*/ /* Index: CONDUCTTEST3_FK */ /*==============================================================*/ create index CONDUCTTEST3_FK on CONDUCTTEST ( TESTNO ASC ); /*==============================================================*/ /* Index: CONDUCTTEST4_FK */ /*==============================================================*/ create index CONDUCTTEST4_FK on CONDUCTTEST ( EMPID ASC ); /*==============================================================*/ /* Table: EMPLOYEE */ /*==============================================================*/ create table EMPLOYEE ( EMPID VARCHAR2(10) not null, UNIONMEMBERID VARCHAR2(10), NAME VARCHAR2(20), ADDRESS VARCHAR2(40), PHONENUMBER VARCHAR2(12), constraint PK_EMPLOYEE primary key (EMPID) ); /*==============================================================*/ /* Table: FLIGHT */ /*==============================================================*/ create table FLIGHT ( FLIGHTNO VARCHAR2(6) not null, ROUTE VARCHAR2(25), DESTINATION VARCHAR2(25), constraint PK_FLIGHT primary key (FLIGHTNO) ); /*==============================================================*/ /* Table: FLIGHT */ /*==============================================================*/ create table FLIGHTDAY ( FLIGHTNO VARCHAR2(6) not null, FLIGHT_DAY VARCHAR2(15), constraint PK_FLIGHTDAY primary key (FLIGHTNO, FLIGHT_DAY) ); /*==============================================================*/ /* Table: SENIOR_TECHNICIAN */ /*==============================================================*/ create table SENIOR_TECHNICIAN ( EMPID VARCHAR2(10) not null, constraint PK_SENIOR_TECHNICIAN primary key (EMPID) ); /*==============================================================*/ /* Table: SERVES */ /*==============================================================*/ create table SERVES ( FLIGHTNO VARCHAR2(6) not null, REGNO VARCHAR2(10) not null, FLIGHT_DATE DATE not null, constraint PK_SERVES primary key (FLIGHTNO, REGNO, FLIGHT_DATE) ); /*==============================================================*/ /* Index: SERVES_FK */ /*==============================================================*/ create index SERVES_FK on SERVES ( FLIGHTNO ASC ); /*==============================================================*/ /* Index: SERVES2_FK */ /*==============================================================*/ create index SERVES2_FK on SERVES ( REGNO ASC ); /*==============================================================*/ /* Table: SERVICE */ /*==============================================================*/ create table SERVICE ( EMPID VARCHAR2(10) not null, FLIGHTNO VARCHAR2(6) not null, SEN_EMPID VARCHAR2(10) not null, SERVICE_DATE DATE, TIME DATE, constraint PK_SERVICE primary key (EMPID, FLIGHTNO, SEN_EMPID) ); /*==============================================================*/ /* Index: SERVICE_FK */ /*==============================================================*/ create index SERVICE_FK on SERVICE ( EMPID ASC ); /*==============================================================*/ /* Index: SERVICE2_FK */ /*==============================================================*/ create index SERVICE2_FK on SERVICE ( FLIGHTNO ASC ); /*==============================================================*/ /* Index: SERVICE3_FK */ /*==============================================================*/ create index SERVICE3_FK on SERVICE ( SEN_EMPID ASC ); /*==============================================================*/ /* Table: TECHNICIAN */ /*==============================================================*/ create table TECHNICIAN ( EMPID VARCHAR2(10) not null, SEN_EMPID VARCHAR2(10), EXPERTISES VARCHAR2(30), SALARY NUMBER, constraint PK_TECHNICIAN primary key (EMPID) ); /*==============================================================*/ /* Index: SUPERVISE_FK */ /*==============================================================*/ create index SUPERVISE_FK on TECHNICIAN ( SEN_EMPID ASC ); /*==============================================================*/ /* Table: TESTINFO */ /*==============================================================*/ create table TESTINFO ( TESTNO VARCHAR2(10) not null, TESTNAME VARCHAR2(20), MAXSCORE NUMBER, constraint PK_TESTINFO primary key (TESTNO) ); /*==============================================================*/ /* Table: TRAFFIC_CONTROLLER */ /*==============================================================*/ create table TRAFFIC_CONTROLLER ( EMPID VARCHAR2(10) not null, DATEOFEXAM DATE, constraint PK_TRAFFIC_CONTROLLER primary key (EMPID) ); alter table CONDUCTTEST add constraint FK_CONDUCTT_CONDUCTTE_AIRPLANE foreign key (REGNO) references AIRPLANE (REGNO); alter table CONDUCTTEST add constraint FK_CONDUCTT_CONDUCTTE_SENIOR_T foreign key (SEN_EMPID) references SENIOR_TECHNICIAN (EMPID); alter table CONDUCTTEST add constraint FK_CONDUCTT_CONDUCTTE_TESTINFO foreign key (TESTNO) references TESTINFO (TESTNO); alter table CONDUCTTEST add constraint FK_CONDUCTT_CONDUCTTE_TECHNICI foreign key (EMPID) references TECHNICIAN (EMPID); alter table SENIOR_TECHNICIAN add constraint FK_SENIOR_T_INHERITAN_TECHNICI foreign key (EMPID) references TECHNICIAN (EMPID); alter table FLIGHTDAY add constraint FK_FLIGHTDAY foreign key (FLIGHTNO) references FLIGHT (FLIGHTNO); alter table SERVES add constraint FK_SERVES_SERVES_FLIGHT foreign key (FLIGHTNO) references FLIGHT (FLIGHTNO); alter table SERVES add constraint FK_SERVES_SERVES2_AIRPLANE foreign key (REGNO) references AIRPLANE (REGNO); alter table SERVICE add constraint FK_SERVICE_SERVICE_TECHNICI foreign key (EMPID) references TECHNICIAN (EMPID); alter table SERVICE add constraint FK_SERVICE_SERVICE2_FLIGHT foreign key (FLIGHTNO) references FLIGHT (FLIGHTNO); alter table SERVICE add constraint FK_SERVICE_SERVICE3_SENIOR_T foreign key (SEN_EMPID) references SENIOR_TECHNICIAN (EMPID); alter table TECHNICIAN add constraint FK_TECHNICI_INHERITAN_EMPLOYEE foreign key (EMPID) references EMPLOYEE (EMPID); alter table TECHNICIAN add constraint FK_TECHNICI_SUPERVISE_SENIOR_T foreign key (SEN_EMPID) references SENIOR_TECHNICIAN (EMPID); alter table TRAFFIC_CONTROLLER add constraint FK_TRAFFIC__INHERITAN_EMPLOYEE foreign key (EMPID) references EMPLOYEE (EMPID); /
