USA: +1(669)289-8683 
Sign in
Papers_1/Assignment1_samplescript (1).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);
/
Papers_1/Assignment1_samplescript (2).sql
/*==============================================================*/
/* DBMS name: ORACLE Version 10g */
/* Created on: 29/09/2013 4:48:31 PM */
/*==============================================================*/
alter table CONDUCTTEST
drop constraint FK_CONDUCTT_CONDUCTTE_AIRPLANE;
alter table CONDUCTTEST
drop constraint FK_CONDUCTT_CONDUCTTE_TESTINFO;
alter table CONDUCTTEST
drop constraint FK_CONDUCTT_CONDUCTTE_SENIOR_T;
alter table CONDUCTTEST
drop constraint FK_CONDUCTT_CONDUCTTE_TECHNICI;
alter table EXPERTISES
drop constraint FK_EXPERTIS_HASEXPERT_TECHNICI;
alter table FLIGHTDAYS
drop constraint FK_FLIGHTDA_FLIESON_FLIGHT;
alter table SERVES
drop constraint FK_SERVES_SERVES_FLIGHTDA;
alter table SERVES
drop constraint FK_SERVES_SERVES2_AIRPLANE;
alter table SERVICEBY
drop constraint FK_SERVICEB_SERVICEDB_SERVICES;
alter table SERVICEBY
drop constraint FK_SERVICEB_SERVICES2_TECHNICI;
alter table SERVICES
drop constraint FK_SERVICES_SERVICES_FLIGHTDAY;
alter table SERVICES
drop constraint FK_SERVICES_SERVICES3_SENIOR_T;
alter table TECHNICIAN
drop constraint FK_TECHNICI_EMPLOYEET_EMPLOYEE;
alter table TECHNICIAN
drop constraint FK_TECHNICI_SUPERVISE_SENIOR_T;
alter table TRAFFIC_CONTROLLER
drop constraint FK_TRAFFIC__EMPLOYEET_EMPLOYEE;
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 EXPERTISES cascade constraints;
drop table FLIGHT cascade constraints;
drop table FLIGHTDAYS cascade constraints;
drop table SENIOR_TECHNICIAN cascade constraints;
drop index SERVES2_FK;
drop index SERVES_FK;
drop table SERVES cascade constraints;
drop table SERVICEBY cascade constraints;
drop index SERVICES3_FK;
/*drop index SERVICES2_FK;
*/
/*drop index SERVICES_FK;
*/
drop table SERVICES 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 INTEGER not null,
MODEL VARCHAR2(15) not null,
CAPACITY INTEGER not null,
WEIGHT INTEGER not null,
DATEOFMANUFACTURE DATE not null,
constraint PK_AIRPLANE primary key (REGNO)
);
/*==============================================================*/
/* Table: CONDUCTTEST */
/*==============================================================*/
create table CONDUCTTEST (
TESTNO INTEGER not null
constraint CKC_TESTNO_CONDUCTT check (TESTNO >= 1000),
REGNO INTEGER not null,
EMPID INTEGER not null
constraint CKC_EMPID_CONDUCTT check (EMPID >= 1000),
SEN_EMPID INTEGER not null
constraint CKC_SEN_EMPID_CONDUCTT check (SEN_EMPID >= 1000),
DATETEST DATE not null,
SCORETEST INTEGER not null,
HOURSTEST INTEGER not null,
constraint PK_CONDUCTTEST primary key (TESTNO, REGNO, DATETEST)
);
/*==============================================================*/
/* Index: CONDUCTTEST_FK */
/*==============================================================*/
create index CONDUCTTEST_FK on CONDUCTTEST (
REGNO ASC
);
/*==============================================================*/
/* Index: CONDUCTTEST2_FK */
/*==============================================================*/
create index CONDUCTTEST2_FK on CONDUCTTEST (
TESTNO ASC
);
/*==============================================================*/
/* Index: CONDUCTTEST3_FK */
/*==============================================================*/
create index CONDUCTTEST3_FK on CONDUCTTEST (
SEN_EMPID ASC
);
/*==============================================================*/
/* Index: CONDUCTTEST4_FK */
/*==============================================================*/
create index CONDUCTTEST4_FK on CONDUCTTEST (
EMPID ASC
);
/*==============================================================*/
/* Table: EMPLOYEE */
/*==============================================================*/
create table EMPLOYEE (
EMPID INTEGER not null
constraint CKC_EMPID_EMPLOYEE check (EMPID >= 1000),
UNIONMEMBERID VARCHAR2(15) not null,
FIRSTNAME VARCHAR2(20) not null,
LASTNAME VARCHAR2(20) not null,
STREET VARCHAR2(30) not null,
SUBURB VARCHAR2(30) not null,
STATE CHAR(3) not null,
POSTCODE VARCHAR2(10) not null,
PHONENUMBER VARCHAR2(15),
constraint PK_EMPLOYEE primary key (EMPID)
);
/*==============================================================*/
/* Table: EXPERTISES */
/*==============================================================*/
create table EXPERTISES (
EMPID INTEGER not null,
EXPERTISE VARCHAR2(15) not null,
constraint PK_EXPERTISES primary key (EMPID, EXPERTISE)
);
/*==============================================================*/
/* Table: FLIGHT */
/*==============================================================*/
create table FLIGHT (
FLIGHTNO VARCHAR2(10) not null,
ROUTE VARCHAR2(60) not null,
DESTINATION VARCHAR2(15) not null,
constraint PK_FLIGHT primary key (FLIGHTNO)
);
/*==============================================================*/
/* Table: FLIGHTDAYS */
/*==============================================================*/
create table FLIGHTDAYS (
FLIGHTNO VARCHAR2(10) not null,
DAY DATE not null,
constraint PK_FLIGHTDAYS primary key (FLIGHTNO, DAY)
);
/*==============================================================*/
/* Table: SENIOR_TECHNICIAN */
/*==============================================================*/
create table SENIOR_TECHNICIAN (
EMPID INTEGER not null
constraint CKC_EMPID_SENIOR_T check (EMPID >= 1000),
constraint PK_SENIOR_TECHNICIAN primary key (EMPID)
);
/*==============================================================*/
/* Table: SERVES */
/*==============================================================*/
create table SERVES (
FLIGHTNO VARCHAR2(10) not null,
DAY DATE not null,
REGNO INTEGER not null,
constraint PK_SERVES primary key (FLIGHTNO, DAY, REGNO)
);
/*==============================================================*/
/* Index: SERVES_FK */
/*==============================================================*/
create index SERVES_FK on SERVES (
FLIGHTNO ASC,
DAY ASC
);
/*==============================================================*/
/* Index: SERVES2_FK */
/*==============================================================*/
create index SERVES2_FK on SERVES (
REGNO ASC
);
/*==============================================================*/
/* Table: SERVICEBY */
/*==============================================================*/
create table SERVICEBY (
FLIGHTNO VARCHAR2(10) not null,
DATEOFSERVICE DATE not null,
EMPID INTEGER not null,
constraint PK_SERVICEBY primary key (FLIGHTNO, DATEOFSERVICE, EMPID)
);
/*==============================================================*/
/* Table: SERVICES */
/*==============================================================*/
create table SERVICES (
FLIGHTNO VARCHAR2(10) not null,
DATEOFSERVICE DATE not null,
SEN_EMPID INTEGER not null
constraint CKC_SEN_EMPID_SERVICES check (SEN_EMPID >= 1000),
TIMEOFSERVICE TIMESTAMP not null,
constraint PK_SERVICES primary key (FLIGHTNO, DATEOFSERVICE)
);
/*==============================================================*/
/* Index: SERVICES_FK */
/*==============================================================*/
/*create index SERVICES_FK on SERVICES (
FLIGHTNO, DATEOFSERVICE ASC
);
*/
/*==============================================================*/
/* Index: SERVICES2_FK */
/*==============================================================*/
/*create index SERVICES2_FK on SERVICES (
EMPID ASC
);
*/
/*==============================================================*/
/* Index: SERVICES3_FK */
/*==============================================================*/
create index SERVICES3_FK on SERVICES (
SEN_EMPID ASC
);
/*==============================================================*/
/* Table: TECHNICIAN */
/*==============================================================*/
create table TECHNICIAN (
EMPID INTEGER not null
constraint CKC_EMPID_TECHNICI check (EMPID >= 1000),
SEN_EMPID INTEGER not null
constraint CKC_SEN_EMPID_TECHNICI check (SEN_EMPID >= 1000),
SALARY INTEGER not null,
constraint PK_TECHNICIAN primary key (EMPID)
);
/*==============================================================*/
/* Index: SUPERVISE_FK */
/*==============================================================*/
create index SUPERVISE_FK on TECHNICIAN (
SEN_EMPID ASC
);
/*==============================================================*/
/* Table: TESTINFO */
/*==============================================================*/
create table TESTINFO (
TESTNO INTEGER not null
constraint CKC_TESTNO_TESTINFO check (TESTNO >= 1000),
TESTNAME VARCHAR2(40) not null,
MAXSCORE INTEGER not null,
constraint PK_TESTINFO primary key (TESTNO)
);
/*==============================================================*/
/* Table: TRAFFIC_CONTROLLER */
/*==============================================================*/
create table TRAFFIC_CONTROLLER (
EMPID INTEGER not null
constraint CKC_EMPID_TRAFFIC_ check (EMPID >= 1000),
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_TESTINFO foreign key (TESTNO)
references TESTINFO (TESTNO);
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_TECHNICI foreign key (EMPID)
references TECHNICIAN (EMPID);
alter table EXPERTISES
add constraint FK_EXPERTIS_HASEXPERT_TECHNICI foreign key (EMPID)
references TECHNICIAN (EMPID);
alter table FLIGHTDAYS
add constraint FK_FLIGHTDA_FLIESON_FLIGHT foreign key (FLIGHTNO)
references FLIGHT (FLIGHTNO);
alter table SERVES
add constraint FK_SERVES_SERVES_FLIGHTDA foreign key (FLIGHTNO, DAY)
references FLIGHTDAYS (FLIGHTNO, DAY);
alter table SERVES
add constraint FK_SERVES_SERVES2_AIRPLANE foreign key (REGNO)
references AIRPLANE (REGNO);
alter table SERVICEBY
add constraint FK_SERVICEB_SERVICEDB_SERVICES foreign key (FLIGHTNO, DATEOFSERVICE)
references SERVICES (FLIGHTNO, DATEOFSERVICE);
alter table SERVICEBY
add constraint FK_SERVICEB_SERVICES2_TECHNICI foreign key (EMPID)
references TECHNICIAN (EMPID);
alter table SERVICES
add constraint FK_SERVICES_SERVICES_FLIGHTDAY foreign key (FLIGHTNO, DATEOFSERVICE)
references FLIGHTDAYS (FLIGHTNO, DAY);
alter table SERVICES
add constraint FK_SERVICES_SERVICES3_SENIOR_T foreign key (SEN_EMPID)
references SENIOR_TECHNICIAN (EMPID);
alter table TECHNICIAN
add constraint FK_TECHNICI_EMPLOYEET_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__EMPLOYEET_EMPLOYEE foreign key (EMPID)
references EMPLOYEE (EMPID);
Papers_1/ISYS224_AssignmentTwo_2013 (1)
MACQUARIE UNIVERSITY
Faculty of Science
Department of Computing
ISYS224 Database Systems
2013 – Semester 2
https://ilearn.mq.edu.au/
Assignment 2 — Relational Database Design &
Implementation; Database Programming (worth 20%)
Due: 6 pm, Wednesday, 30 October, 2013 (Week 12)
Please Print Clearly In CAPITALS
Surname
First Name
Student ID
Signature
Plagiarism Office Use Only:
Plagiarism is where you use (part of) the work of another
person and present it as your own. This is akin to stealing
and is not permitted. If you are caught encouraging,
assisting or committing plagiarism, you may receive
negative marks for the work and possibly be suspended
from the unit.
The University’s academic honesty policy is at:
http://www.mq.edu.au/policy/docs/academic_h
onesty/policy.html
It is in your interests to familiarise yourself with these rules.
Important Note:
You are required to submit an electronic copy as well as
a hard copy of your assignment by the due date.
Mark Max
Task 1 /3
Task 2 /9
Task 3 /3
Task 4 /5
Total /20
Page 2
ISYS 224 Database Systems
Assignment 2 — Relational Database Design & Implementation;
Database Programming (PL/SQL)
Due: 6 pm, Wednesday, 30 October, 2013 (Week 12)
Worth 20% of Course Mark
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
• Study the relevant chapters from the prescribed textbooks and go over the relevant tutorial
and practical exercises so far.
• 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 ).
Page 3
The EER diagram for Task One from the Diagnostic Assignment:
2 Task Specifications
Task 1 [3 marks]
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.
m
m
m
Note: conductTest is a relationship among Airplane, Technician, Senior_Technician, and TestInfo with attributes: data, score, and hours.
service is a relationship among Flight, Technician, and Senior_Technician with attributes: date, and time
Inheritance_1
Inheritance_2
Inheritance_3
service
date, time
supervise
date
conductTest
1
m 1
m
date
score
hours
serves
Airplane
RegNo
model
capacity
weight
dateOfManufacture
Integer
RegNo
Flight
flightNo
route
destination
{day}
flightNo
testNo
testName
maxScore
testNo Technician expertises salary Senior_Technician Traffic_Controller dateOfExam Employee empID unionMemeberID name address phoneNumber empID Page 4 Task 2 [9 marks] 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 (3 marks). 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). 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. [3 marks] 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. [5 marks] 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 ). Page 5 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 . You are required to submit an electronic copy as well as a hard copy. • Electronic Submission – You must submit all the work in two documents named Assignment2 and Assignment2.sql. The electronic submission site is on ilearn (Moodle) https://ilearn.mq.edu.au/ under Assignment 2 –> Assignment 2 Submission. • Hard copy submission – Your assignment must also be submitted in hard copy in the assignment boxes marked ISYS224 at E6A ground floor next to the computer labs. Note that hand-drawn diagrams or hand-written materials are not acceptable. You must attach an assignment cover sheet to your hardcopy submission (the first page of this document). • Deadline for submission – 6 pm, Wednesday, 30 October, 2013 (Week 12). Late submissions will not be accepted. Notes: If you changed your file after submission, you may re-submit your assignment again before the due date. If you make errors in the submission process, you may get zero marks. The submission date is taken to be the date of electronic submission regardless of when the hardcopy was submitted within the allowed time. If you have experienced serious and unavoidable disruption from completing any unit requirements, then you may apply for Special Consideration. More details are available from: http://www.mq.edu.au/policy/docs/special_consideration/procedure.html 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.
• procedure available_airplanes(. . .);
