FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
This is an example solution for assignment 2 of the FIT1004/FIT2010 unit (Databases), semester 1, 2009, Monash University. Note that this is an example
solution, not a model solution. In particular, there is no claim that this solution would get full marks, or that it is based on the marking scheme. The
assignment has many different correct solutions. Students may consult this example in order to learn how the assignment may be approached by an
experienced software engineer, but not to draw any conclusions about what design features are “required” or “wrong”.
Note that no output is shown for the answers provided in this sample solution. The interested reader may obtain the output by copying and executing the
statements shown here into any suitable database client software.
Contents
Contents................................................................................................................................................................................................ 1
Case study............................................................................................................................................................................................. 2
Task Q1.a ............................................................................................................................................................................................ 13
Task Q1.b ............................................................................................................................................................................................ 18
Task Q2.I............................................................................................................................................................................................. 23
Task Q2.II ........................................................................................................................................................................................... 24
Task Q2.III .......................................................................................................................................................................................... 24
Task Q2.IV........................................................................................................................................................................................... 25
Task Q2.V ............................................................................................................................................................................................ 25
Task Q2.VI........................................................................................................................................................................................... 26
Task Q2.VII.......................................................................................................................................................................................... 26
Task Q2.VIII ........................................................................................................................................................................................ 27
Task Q2.IX........................................................................................................................................................................................... 28
Task Q2.X ............................................................................................................................................................................................ 28
Task Q2.XI........................................................................................................................................................................................... 29
Task Q2.XII.......................................................................................................................................................................................... 31
Task Q3.I............................................................................................................................................................................................. 32
Task Q3.II ........................................................................................................................................................................................... 33
Task Q4.I............................................................................................................................................................................................. 36
Task Q4.II ........................................................................................................................................................................................... 37
Task Q5.I............................................................................................................................................................................................. 39
Task Q5.II ........................................................................................................................................................................................... 40
page 1 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Case study
After reviewing all the designs, defining the scope and further deliberations with management of Moving Muscles Ltd (MMLtd), the design of the
database has been finalised. In this assignment you will use a database model as depicted by the following ERD:
page 2 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
For this assignment you will populate these tables with appropriate test data and write the SQL queries and triggers specified below. You should also
ensure that any activities you carry out in the database conform to the requirements of the ERD displayed above.
The schema file for creating this model is available in the file MovMuscleAss2.sql - this file creates the above tables, creates suitable sequences for
use as numeric primary key values and populates several of the tables - you should read this schema carefully so that you are aware of the meaning
of the various attributes. You must not alter the schema file in any manner, it must be used as supplied. Penalties will apply to queries that use
subqueries and views unnecessarily. In handling dates, default date format must not be assumed, you must make use of the to_date and to_char
functions in all appropriate situations.
You may need to rerun the schema, especially when you have been experimenting with your solutions and may have corrupted the database
unintentionally. If you suspect that there might be such problems, simply rerun the schema. The schema includes the appropriate drop commands at
the head of the file.
Listing MovMuscleAss2.sql:
---------
FIT1004 Database
Assignment 2 Semester 1 2009 Schema File
Target: Oracle
Author: L Smith
Generated from Power*Architect and hand edited to add check clause and remove schema reference
-- Change to set echo on if wish commands to be echoed during execution
set echo off
--- Place DROP commands at head of schema file
-DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
TABLE PURPOSE_LIST PURGE;
TABLE JOB PURGE;
TABLE TRUCK_CONTAINER PURGE;
TABLE TRUCK PURGE;
TABLE TRUCK_TYPE PURGE;
TABLE DRIVER PURGE;
TABLE EMPLOYEE PURGE;
TABLE TRUCK_CLASS PURGE;
TABLE CONTAINER PURGE;
TABLE CONTAINER_MODEL PURGE;
TABLE CONTAINER_FUNCTION PURGE;
TABLE QUOTE PURGE;
TABLE PURPOSE_DESC PURGE;
TABLE CUSTOMER PURGE;
TABLE JOB_TYPE PURGE;
SEQUENCE CUST_NO_SEQ;
page 3 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
DROP SEQUENCE QUOTE_NUM_SEQ;
--- Create Table
: 'CONTAINER_FUNCTION'
-- cf_code
: Code for container function
-- cf_desc
: Description of container function
-CREATE TABLE CONTAINER_FUNCTION (
CF_CODE NUMBER(3,0) NOT NULL,
CF_DESC VARCHAR2(100) NOT NULL,
CONSTRAINT CONTAINER_FUNCTION_PK PRIMARY KEY (CF_CODE)
);
-- Create Table
-- job_code
-- job_description
--
: 'JOB_TYPE'
: Code for employee roles (PK)
: Description of roles (eg. Manager, Clerk, Mechanic, Driver)
CREATE TABLE JOB_TYPE (
JOB_CODE NUMBER(2,0) NOT NULL,
JOB_DESCRIPTION VARCHAR2(100) NOT NULL,
CONSTRAINT JOB_TYPE_PK PRIMARY KEY (JOB_CODE)
);
------------
Create Table
cust_no
cust_gname
cust_fname
cust_bname
cust_contact_no
cust_street
cust_suburb
cust_state
cust_pcode
:
:
:
:
:
:
:
:
:
:
'CUSTOMER'
Customer number (PK)
Customer given name
Customer family name
Customer business name (if applicable)
Customer phone contact number
Customer street address
Customer suburb
Customer state
Customer post code
CREATE TABLE CUSTOMER (
CUST_NO NUMBER(6,0) NOT NULL,
CUST_GNAME VARCHAR2(50) NOT NULL,
CUST_FNAME VARCHAR2(50) NOT NULL,
CUST_BNAME VARCHAR2(100),
CUST_CONTACT_NO CHAR(10) NOT NULL,
CUST_STREET VARCHAR2(100) NOT NULL,
CUST_SUBURB VARCHAR2(100) NOT NULL,
CUST_STATE VARCHAR2(3) NOT NULL,
CUST_PCODE CHAR(4) NOT NULL,
CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUST_NO),
CONSTRAINT CUSTOMER_CUST_STATE_CHK CHECK (CUST_STATE IN ('VIC', 'NT', 'NSW', 'WA', 'QLD', 'SA', 'TAS', 'ACT'))
);
------
Create Table
EMP_NUMB
EMP_GNAME
EMP_FNAME
:
:
:
:
'EMPLOYEE'
Employee number (PK)
Employee given name
Employee family name
page 4 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
------
EMP_CONTACT_NO
EMP_SALARY
EMP_TAXFILE_NO
EMP_CURRENT
JOB_CODE
:
:
:
:
:
Employee emergency phone contact number
Employee salary
Employee tax file number
Indicates if employee is currently employed (Y or N)
Employee job code (employee role)
CREATE TABLE EMPLOYEE (
EMP_NUMB NUMBER(3,0) NOT NULL,
EMP_GNAME VARCHAR2(50) NOT NULL,
EMP_FNAME VARCHAR2(50) NOT NULL,
EMP_CONTACT_NO CHAR(10) NOT NULL,
EMP_SALARY NUMBER(9,2) NOT NULL,
EMP_TAXFILE_NO CHAR(10) NOT NULL,
EMP_CURRENT CHAR(1) NOT NULL,
JOB_CODE NUMBER(2,0) NOT NULL,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EMP_NUMB),
CONSTRAINT EMP_CURRENT_CHK CHECK (EMP_CURRENT IN ('Y','N'))
);
--- Create Table
-- purpose_no
-- purpose_desc
: 'PURPOSE_DESC'
: Moving purpose number (PK)
: Purpose description
CREATE TABLE PURPOSE_DESC (
PURPOSE_NO CHAR(3) NOT NULL,
PURPOSE_DESC VARCHAR2(100) NOT NULL,
CONSTRAINT PURPOSE_DESC_PK PRIMARY KEY (PURPOSE_NO)
);
-------------
Create Table
:
quote_num
:
quote_date
:
job_start_date
:
job_enddate
:
quote_hire_charge:
quote_fulfilled
purpose_no
cust_no
'QUOTE'
Quote number (PK)
Date and time quote prepared
Job start date
Job end date
Qouted hire charge
- Customers are warned if they do not confirm the job at quote time, that the hire charge
may change due to unavailabilty of truck/container combinations
: Quote converted to job (Y or N)
: Moving purpose number (FK)
: Customer number (FK)
CREATE TABLE QUOTE (
QUOTE_NUM NUMBER(7,0) NOT NULL,
QUOTE_DATE DATE NOT NULL,
JOB_START_DATE DATE NOT NULL,
JOB_END_DATE DATE NOT NULL,
QUOTE_HIRE_CHARGE NUMBER(6,2) NOT NULL,
QUOTE_FULFILLED CHAR(1) DEFAULT 'N' NOT NULL,
PURPOSE_NO CHAR(3) NOT NULL,
CUST_NO NUMBER(6,0) NOT NULL,
CONSTRAINT QUOTE_PK PRIMARY KEY (QUOTE_NUM),
CONSTRAINT QUOTE_FULFILLED_CHK CHECK (QUOTE_FULFILLED in ('Y','N')),
CONSTRAINT JOB_DATES_CHK CHECK (JOB_START_DATE <= JOB_END_DATE)
page 5 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
);
--------
Create Table
cm_model_no
cm_width
cm_length
cm_load_capacity
cf_code
:
:
:
:
:
:
'CONTAINER_MODEL'
Container model number (PK)
Container model width
Container model length
Container model load capacity
Container function code (FK)
CREATE TABLE CONTAINER_MODEL (
CM_MODEL_NO CHAR(8) NOT NULL,
CM_WIDTH NUMBER(4,2) NOT NULL,
CM_LENGTH NUMBER(4,2) NOT NULL,
CM_LOAD_CAPACITY NUMBER(5,0) NOT NULL,
CF_CODE NUMBER(3,0) NOT NULL,
CONSTRAINT CONTAINER_MODEL_PK PRIMARY KEY (CM_MODEL_NO)
);
--- Create Table
-- c_code
-- cm_model_no
: 'CONTAINER'
: Container code (PK)
: Container model number (FK)
CREATE TABLE CONTAINER (
C_CODE CHAR(5) NOT NULL,
CM_MODEL_NO CHAR(8) NOT NULL,
CONSTRAINT CONTAINER_PK PRIMARY KEY (C_CODE)
);
--- Create Table
: 'TRUCK_CLASS'
-- tc_class
: Truck class (PK)
-- tc_driver_hire_rate : Driver hire rate for this truck class
CREATE TABLE TRUCK_CLASS (
TC_CLASS NUMBER(1,0) NOT NULL,
TC_DRIVER_HIRE_RATE NUMBER(5,2) NOT NULL,
CONSTRAINT TRUCK_CLASS_PK PRIMARY KEY (TC_CLASS)
);
------
Create Table
emp_numb
emp_licence_no
tc_class
:
:
:
:
'DRIVER'
Driver employee number (PK, FK)
Drivers licence number
Highest truck class this driver can drive
CREATE TABLE DRIVER (
EMP_NUMB NUMBER(3,0) NOT NULL,
EMP_LICENCE_NO VARCHAR2(20),
TC_CLASS NUMBER(1,0) NOT NULL,
CONSTRAINT DRIVER_PK PRIMARY KEY (EMP_NUMB)
);
page 6 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
-------
Create Table
tt_code
tt_make
tt_model
tc_class
:
:
:
:
:
'TRUCK_TYPE'
Truck type code (PK)
Truck type make
Truck type model
Truck class (FK)
CREATE TABLE TRUCK_TYPE (
TT_CODE NUMBER(3,0) NOT NULL,
TT_MAKE VARCHAR2(100) NOT NULL,
TT_MODEL VARCHAR2(100) NOT NULL,
TC_CLASS NUMBER(1,0) NOT NULL,
CONSTRAINT TRUCK_TYPE_PK PRIMARY KEY (TT_CODE)
);
-------
Create Table
: 'TRUCK'
truck_rego_no
: Truck rego number (PK)
truck_kms
: Current kms travelled by truck (updated at close of job)
truck_lastservicekms : Kms at last service
tt_code
: Truck type code (FK)
CREATE TABLE TRUCK (
TRUCK_REGO_NO CHAR(9) NOT NULL,
TRUCK_KMS NUMBER(7,1) NOT NULL,
TRUCK_LASTSERVICEKMS NUMBER(7,1) NOT NULL,
TT_CODE NUMBER(3,0) NOT NULL,
CONSTRAINT TRUCK_PK PRIMARY KEY (TRUCK_REGO_NO)
);
------
Create Table
truck_rego_no
c_code
tc_hirerate
:
:
:
:
'TRUCK_CONTAINER'
Truck rego number (PK,FK)
Container code (PK,FK)
Hire rate for this combination of truck and container
CREATE TABLE TRUCK_CONTAINER (
TRUCK_REGO_NO CHAR(9) NOT NULL,
C_CODE CHAR(5) NOT NULL,
TC_HIRERATE NUMBER(6,2) NOT NULL,
CONSTRAINT TRUCK_CONTAINER_PK PRIMARY KEY (TRUCK_REGO_NO, C_CODE)
);
-------------
Create Table
job_quote_num
job_meet_street
job_meet_suburb
job_meet_state
job_meet_pcode
job_hire_charge
:
:
:
:
:
:
:
'JOB'
Job number (PK FK)
Job street pickup
Job suburb pickup
Job state pickup
Job postcode pickup
Actual hire charge for this job (may vary from quote_hire_charge eg. delayed confirmation
of quote may requiring different truck rig due to unavailability of quoted rig)
job_payment_made : Job payment made (Y, N)
emp_numb
: Driver assigned (FK)
quote_num
: Quote job based on (FK)
page 7 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
-- truck_rego_no
-- c_code
: Truck assigned (FK)
: Container assigned (FK)
CREATE TABLE JOB (
JOB_QUOTE_NUM NUMBER(7,0) NOT NULL,
JOB_MEET_STREET VARCHAR2(100),
JOB_MEET_SUBURB VARCHAR2(100),
JOB_MEET_STATE VARCHAR2(3),
JOB_MEET_PCODE CHAR(4),
JOB_HIRE_CHARGE NUMBER(6,2) NOT NULL,
JOB_PAYMENT_MADE CHAR(1) DEFAULT 'N' NOT NULL,
EMP_NUMB NUMBER(3,0) NOT NULL,
TRUCK_REGO_NO CHAR(9) NOT NULL,
C_CODE CHAR(5) NOT NULL,
CONSTRAINT JOB_PK PRIMARY KEY (JOB_QUOTE_NUM),
CONSTRAINT JOB_PAYMENT_MADE_CHK CHECK (JOB_PAYMENT_MADE in ('Y','N')),
CONSTRAINT JOB_MEET_STATE_CHK CHECK ( JOB_MEET_STATE IN ('VIC', 'NT', 'NSW', 'WA', 'QLD', 'SA', 'TAS', 'ACT'))
);
------
Create Table
truck_rego_no
c_code
purpose_no
:
:
:
:
'PURPOSE_LIST'
Truck rego number (FK)
Container code (FK)
Moving purpose number (FK)
CREATE TABLE PURPOSE_LIST (
TRUCK_REGO_NO CHAR(9) NOT NULL,
C_CODE CHAR(5) NOT NULL,
PURPOSE_NO CHAR(3) NOT NULL,
CONSTRAINT PURPOSE_LIST_PK PRIMARY KEY (TRUCK_REGO_NO, C_CODE, PURPOSE_NO)
);
-- Implement FK rules
ALTER TABLE CONTAINER_MODEL ADD CONSTRAINT CF_CM_FK
FOREIGN KEY (CF_CODE)
REFERENCES CONTAINER_FUNCTION (CF_CODE)
NOT DEFERRABLE;
ALTER TABLE EMPLOYEE ADD CONSTRAINT JT_EMPLOYEE_FK
FOREIGN KEY (JOB_CODE)
REFERENCES JOB_TYPE (JOB_CODE)
NOT DEFERRABLE;
ALTER TABLE QUOTE ADD CONSTRAINT CUSTOMER_QUOTE_FK
FOREIGN KEY (CUST_NO)
REFERENCES CUSTOMER (CUST_NO)
NOT DEFERRABLE;
ALTER TABLE DRIVER ADD CONSTRAINT EMPLOYEE_DRIVER_FK
FOREIGN KEY (EMP_NUMB)
REFERENCES EMPLOYEE (EMP_NUMB)
NOT DEFERRABLE;
ALTER TABLE QUOTE ADD CONSTRAINT PD_QUOTE_FK
page 8 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
FOREIGN KEY (PURPOSE_NO)
REFERENCES PURPOSE_DESC (PURPOSE_NO)
NOT DEFERRABLE;
ALTER TABLE PURPOSE_LIST ADD CONSTRAINT PD_PL_FK
FOREIGN KEY (PURPOSE_NO)
REFERENCES PURPOSE_DESC (PURPOSE_NO)
NOT DEFERRABLE;
ALTER TABLE JOB ADD CONSTRAINT QUOTE_JOB_FK
FOREIGN KEY (JOB_QUOTE_NUM)
REFERENCES QUOTE (QUOTE_NUM)
NOT DEFERRABLE;
ALTER TABLE CONTAINER ADD CONSTRAINT CM_CONTAINER_FK
FOREIGN KEY (CM_MODEL_NO)
REFERENCES CONTAINER_MODEL (CM_MODEL_NO)
NOT DEFERRABLE;
ALTER TABLE TRUCK_CONTAINER ADD CONSTRAINT CONTAINER_TC_FK
FOREIGN KEY (C_CODE)
REFERENCES CONTAINER (C_CODE)
NOT DEFERRABLE;
ALTER TABLE JOB ADD CONSTRAINT CONTAINER_JOB_FK
FOREIGN KEY (C_CODE)
REFERENCES CONTAINER (C_CODE)
NOT DEFERRABLE;
ALTER TABLE TRUCK_TYPE ADD CONSTRAINT TC_TT_FK
FOREIGN KEY (TC_CLASS)
REFERENCES TRUCK_CLASS (TC_CLASS)
NOT DEFERRABLE;
ALTER TABLE DRIVER ADD CONSTRAINT TRUCK_CLASS_DRIVER_FK
FOREIGN KEY (TC_CLASS)
REFERENCES TRUCK_CLASS (TC_CLASS)
NOT DEFERRABLE;
ALTER TABLE JOB ADD CONSTRAINT DRIVER_JOB_FK
FOREIGN KEY (EMP_NUMB)
REFERENCES DRIVER (EMP_NUMB)
NOT DEFERRABLE;
ALTER TABLE TRUCK ADD CONSTRAINT TT_TRUCK_FK
FOREIGN KEY (TT_CODE)
REFERENCES TRUCK_TYPE (TT_CODE)
NOT DEFERRABLE;
ALTER TABLE TRUCK_CONTAINER ADD CONSTRAINT TRUCK_TC_FK
FOREIGN KEY (TRUCK_REGO_NO)
REFERENCES TRUCK (TRUCK_REGO_NO)
NOT DEFERRABLE;
ALTER TABLE JOB ADD CONSTRAINT TRUCK_JOB_FK
page 9 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
FOREIGN KEY (TRUCK_REGO_NO)
REFERENCES TRUCK (TRUCK_REGO_NO)
NOT DEFERRABLE;
ALTER TABLE PURPOSE_LIST ADD CONSTRAINT TC_PL_FK
FOREIGN KEY (TRUCK_REGO_NO, C_CODE)
REFERENCES TRUCK_CONTAINER (TRUCK_REGO_NO, C_CODE)
NOT DEFERRABLE;
ALTER TABLE JOB ADD CONSTRAINT TC_JOB_FK
FOREIGN KEY (TRUCK_REGO_NO, C_CODE)
REFERENCES TRUCK_CONTAINER (TRUCK_REGO_NO, C_CODE)
NOT DEFERRABLE;
-- Create Sequences for use with PK's for
CUSTOMER, QUOTE and JOB
CREATE SEQUENCE cust_no_seq
INCREMENT BY 1
START WITH 100
NOCACHE
NOCYCLE;
CREATE SEQUENCE quote_num_seq
INCREMENT BY 1
START WITH 100
NOCACHE
NOCYCLE;
-- Insert Sample Data
REM INSERTING into TRUCK_CLASS
insert into TRUCK_CLASS (TC_CLASS,TC_DRIVER_HIRE_RATE) values (1,100);
insert into TRUCK_CLASS (TC_CLASS,TC_DRIVER_HIRE_RATE) values (2,150);
insert into TRUCK_CLASS (TC_CLASS,TC_DRIVER_HIRE_RATE) values (3,200);
REM INSERTING into TRUCK_TYPE
insert into TRUCK_TYPE (TT_CODE,TT_MAKE,TT_MODEL,TC_CLASS)
insert into TRUCK_TYPE (TT_CODE,TT_MAKE,TT_MODEL,TC_CLASS)
insert into TRUCK_TYPE (TT_CODE,TT_MAKE,TT_MODEL,TC_CLASS)
insert into TRUCK_TYPE (TT_CODE,TT_MAKE,TT_MODEL,TC_CLASS)
REM INSERTING into TRUCK
insert into TRUCK (TRUCK_REGO_NO,
insert into TRUCK (TRUCK_REGO_NO,
insert into TRUCK (TRUCK_REGO_NO,
insert into TRUCK (TRUCK_REGO_NO,
insert into TRUCK (TRUCK_REGO_NO,
insert into TRUCK (TRUCK_REGO_NO,
insert into TRUCK (TRUCK_REGO_NO,
insert into TRUCK (TRUCK_REGO_NO,
insert into TRUCK (TRUCK_REGO_NO,
insert into TRUCK (TRUCK_REGO_NO,
TRUCK_KMS,
TRUCK_KMS,
TRUCK_KMS,
TRUCK_KMS,
TRUCK_KMS,
TRUCK_KMS,
TRUCK_KMS,
TRUCK_KMS,
TRUCK_KMS,
TRUCK_KMS,
values
values
values
values
TRUCK_LASTSERVICEKMS,
TRUCK_LASTSERVICEKMS,
TRUCK_LASTSERVICEKMS,
TRUCK_LASTSERVICEKMS,
TRUCK_LASTSERVICEKMS,
TRUCK_LASTSERVICEKMS,
TRUCK_LASTSERVICEKMS,
TRUCK_LASTSERVICEKMS,
TRUCK_LASTSERVICEKMS,
TRUCK_LASTSERVICEKMS,
(10,'Isuzu','GigaMax425',3);
(20,'Isuzu','FVM-1400',2);
(30,'Nissan','MK240',1);
(40,'Mack','Load-MaX',3);
TT_CODE)
TT_CODE)
TT_CODE)
TT_CODE)
TT_CODE)
TT_CODE)
TT_CODE)
TT_CODE)
TT_CODE)
TT_CODE)
values
values
values
values
values
values
values
values
values
values
('VIC128234',129056,120040,20);
('VIC291909',10987,0,30);
('VIC100457',150000,140000,10);
('VIC123456',104000,100235,20);
('VIC124789',130560,125000,20);
('VIC200897',54000,42000,30);
('VIC210889',48000,40100,30);
('VIC223456',89000,85124,30);
('VIC224567',23456,0,30);
('VIC100458',134500,105000,10);
REM INSERTING into CONTAINER_FUNCTION
insert into CONTAINER_FUNCTION (CF_CODE, CF_DESC) values (1,'Dry Cargo');
insert into CONTAINER_FUNCTION (CF_CODE, CF_DESC) values (2,'Refrigerated Cargo');
page 10 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
insert into CONTAINER_FUNCTION (CF_CODE, CF_DESC) values (3,'Rugged Terrain');
REM INSERTING into CONTAINER_MODEL
insert into CONTAINER_MODEL (CM_MODEL_NO,
insert into CONTAINER_MODEL (CM_MODEL_NO,
insert into CONTAINER_MODEL (CM_MODEL_NO,
insert into CONTAINER_MODEL (CM_MODEL_NO,
insert into CONTAINER_MODEL (CM_MODEL_NO,
insert into CONTAINER_MODEL (CM_MODEL_NO,
REM INSERTING into CONTAINER
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
insert into CONTAINER (C_CODE,
CM_WIDTH,
CM_WIDTH,
CM_WIDTH,
CM_WIDTH,
CM_WIDTH,
CM_WIDTH,
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
CM_MODEL_NO)
REM INSERTING into PURPOSE_DESC
insert into PURPOSE_DESC (PURPOSE_NO,
insert into PURPOSE_DESC (PURPOSE_NO,
insert into PURPOSE_DESC (PURPOSE_NO,
insert into PURPOSE_DESC (PURPOSE_NO,
insert into PURPOSE_DESC (PURPOSE_NO,
REM INSERTING into JOB_TYPE
insert into JOB_TYPE (JOB_CODE,
insert into JOB_TYPE (JOB_CODE,
insert into JOB_TYPE (JOB_CODE,
insert into JOB_TYPE (JOB_CODE,
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
PURPOSE_DESC)
PURPOSE_DESC)
PURPOSE_DESC)
PURPOSE_DESC)
PURPOSE_DESC)
JOB_DESCRIPTION)
JOB_DESCRIPTION)
JOB_DESCRIPTION)
JOB_DESCRIPTION)
CM_LENGTH,
CM_LENGTH,
CM_LENGTH,
CM_LENGTH,
CM_LENGTH,
CM_LENGTH,
CM_LOAD_CAPACITY,
CM_LOAD_CAPACITY,
CM_LOAD_CAPACITY,
CM_LOAD_CAPACITY,
CM_LOAD_CAPACITY,
CM_LOAD_CAPACITY,
CF_CODE)
CF_CODE)
CF_CODE)
CF_CODE)
CF_CODE)
CF_CODE)
values
values
values
values
values
values
('20ft-Dry',
('40ft-Dry',
('20ft-Ref',
('40ft-Ref',
('20ft-Rug',
('40ft-Rug',
'2.4',
'2.4',
'2.4',
'2.4',
'2.4',
'2.4',
'6.1', '30', 1);
'12.2', '60', 1);
'6.1', '25', 2 );
'12.2', '50', 2);
'6.1', '20', 3);
'12.2', '40', 3);
('DRY01','20ft-Dry');
('DRY02','20ft-Dry');
('DRY04','20ft-Dry');
('DRY05','20ft-Dry');
('DRY08','20ft-Dry');
('DRY09','20ft-Dry');
('DRY13','20ft-Dry');
('REF06','20ft-Ref');
('REF14','20ft-Ref');
('RUG11','20ft-Rug');
('RUG12','40ft-Rug');
('DRY03','40ft-Dry');
('DRY10','40ft-Dry');
('DRY15','40ft-Dry');
('REF07','40ft-Ref');
values
values
values
values
values
values
values
values
values
('P01','Dry Cargo Transportation');
('P02','Refrigerated Cargo Transportation');
('P03','House Shifting');
('P04','Rugged Terrain Transportation');
('P05','Business Shifting');
(10,'Manager');
(20,'Clerk');
(30,'Mechanic');
(40,'Driver');
REM INSERTING into EMPLOYEE
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME, EMP_CONTACT_NO,
'Maddocks','1110001111',150000.00,'003456789','Y',10);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME, EMP_CONTACT_NO,
'DeBono','9452222222',52000.00,'111228907','Y',20);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME, EMP_CONTACT_NO,
'Agas','3333331212',50000.00,'009867341','Y',20);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME, EMP_CONTACT_NO,
'Chen','9702444444',57000.00,'908762451','Y',20);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME, EMP_CONTACT_NO,
'Wilson','5555555555',75000.00,'183461009','Y',30);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME, EMP_CONTACT_NO,
'Matthews','6666666666',62000.00,'007613987','Y',40);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME, EMP_CONTACT_NO,
'Hunt','7777777777',58000.00,'112233785','Y',40);
EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (1,'Marie',
EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (6,'William',
EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (3,'Graham',
EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (9,'Aaron',
EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (10,'Terry',
EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (7,'Ian',
EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (12,'Kim',
page 11 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME,
'Forsythe','8888888888',58000.00,'001297629','Y',40);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME,
'Wiles','9999999999',60000.00,'652062198','Y',40);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME,
'Wong','1010101010',54000.00,'876152073','Y',40);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME,
'Jones','2934567121',58000.00,'056148937','N',40);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME,
'Smith','1110001110',56000.00,'109576142','Y',40);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME,
'Forbes','1234098746',58000.00,'059129573','Y',40);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME,
'Wales','0983541212',60000.00,'439562198','Y',40);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME,
'Wang','8877653421',54000.00,'962306123','Y',40);
insert into EMPLOYEE (EMP_NUMB, EMP_GNAME, EMP_FNAME,
'Smith','8976342765',56000.00,'103928598','Y',40);
REM INSERTING into
insert into DRIVER
insert into DRIVER
insert into DRIVER
insert into DRIVER
insert into DRIVER
insert into DRIVER
insert into DRIVER
insert into DRIVER
insert into DRIVER
insert into DRIVER
insert into DRIVER
DRIVER
(EMP_NUMB,
(EMP_NUMB,
(EMP_NUMB,
(EMP_NUMB,
(EMP_NUMB,
(EMP_NUMB,
(EMP_NUMB,
(EMP_NUMB,
(EMP_NUMB,
(EMP_NUMB,
(EMP_NUMB,
EMP_LICENCE_NO,
EMP_LICENCE_NO,
EMP_LICENCE_NO,
EMP_LICENCE_NO,
EMP_LICENCE_NO,
EMP_LICENCE_NO,
EMP_LICENCE_NO,
EMP_LICENCE_NO,
EMP_LICENCE_NO,
EMP_LICENCE_NO,
EMP_LICENCE_NO,
REM INSERTING into TRUCK_CONTAINER
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
insert into TRUCK_CONTAINER (TRUCK_REGO_NO,
EMP_CONTACT_NO, EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (13,'Sarah',
EMP_CONTACT_NO, EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (15,'Kingsley',
EMP_CONTACT_NO, EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (8,'Andrew',
EMP_CONTACT_NO, EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (16,'Sasha',
EMP_CONTACT_NO, EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (4,'Edward',
EMP_CONTACT_NO, EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (5,'Kate',
EMP_CONTACT_NO, EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (11,'Kingsley',
EMP_CONTACT_NO, EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (14,'Andrew',
EMP_CONTACT_NO, EMP_SALARY, EMP_TAXFILE_NO, EMP_CURRENT, JOB_CODE) values (2,'Roger',
TC_CLASS)
TC_CLASS)
TC_CLASS)
TC_CLASS)
TC_CLASS)
TC_CLASS)
TC_CLASS)
TC_CLASS)
TC_CLASS)
TC_CLASS)
TC_CLASS)
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
values
values
values
values
values
values
values
values
values
values
values
(7,'01768435',3);
(12,'02398442',3);
(13,'14790275',2);
(15,'09873524',2);
(8,'08518529',2);
(16,'09371264',3);
(4,'09349522',2);
(5,'12457890',1);
(11,'04587121',1);
(14,'12087999',1);
(2,'10349510',1);
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
TC_HIRERATE)
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
('VIC200897','DRY01',600);
('VIC210889','DRY02',600);
('VIC223456','DRY04',600);
('VIC224567','DRY05',600);
('VIC291909','DRY08',600);
('VIC124789','DRY09',600);
('VIC128234','DRY13',600);
('VIC123456','REF06',800);
('VIC124789','REF14',800);
('VIC128234','RUG11',650);
('VIC100457','RUG12',850);
('VIC100458','DRY03',800);
('VIC100457','DRY10',800);
('VIC100458','DRY15',800);
('VIC100457','REF07',990);
('VIC100457','DRY13',700);
('VIC100458','DRY09',700);
('VIC123456','DRY08',650);
('VIC124789','DRY05',650);
('VIC128234','DRY04',650);
REM INSERTING into PURPOSE_LIST
page 12 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
PURPOSE_LIST
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
(TRUCK_REGO_NO,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
C_CODE,
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
PURPOSE_NO)
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
('VIC200897','DRY01','P01');
('VIC210889','DRY02','P01');
('VIC223456','DRY04','P01');
('VIC224567','DRY05','P01');
('VIC291909','DRY08','P01');
('VIC124789','DRY09','P01');
('VIC128234','DRY13','P01');
('VIC123456','REF06','P02');
('VIC124789','REF14','P02');
('VIC128234','RUG11','P04');
('VIC100457','RUG12','P04');
('VIC100458','DRY03','P01');
('VIC100457','DRY10','P03');
('VIC100458','DRY15','P05');
('VIC100457','REF07','P02');
('VIC100457','DRY13','P05');
('VIC100458','DRY09','P05');
('VIC123456','DRY08','P03');
('VIC124789','DRY05','P03');
('VIC128234','DRY04','P03');
('VIC200897','DRY01','P03');
('VIC210889','DRY02','P03');
('VIC223456','DRY04','P03');
('VIC224567','DRY05','P03');
('VIC291909','DRY08','P03');
('VIC124789','DRY09','P03');
('VIC128234','DRY13','P03');
commit;
Task Q1.a
Using the supplied schema file create the tables/sequences for MMLtd and the insert the supplied values. This provides a starting point for the
following activities.
Load selected tables with your own additional test data: you should create a single script (mm-load.sql) which will insert, as a minimum, the
following sample data:
•
•
•
10 customers, use a mix of business and private
20 quotes - based on the customers you have added, and
15 jobs - based on the quotes you have added
In loading this data you must not add any further data to the tables which were previously populated by the supplied schema file.
The primary key values for this data must be hard coded values (ie. not make use of sequences) and consist of values below 100 (so as to not
interfere with the selected starting point of the created sequences). Dates should be chosen in the period between the 1st May 2009 and the 30th
page 13 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
June 2009. The script should contain a single COMMIT statement as the last line of the script, ie. all listed actions should be treated as a single
transaction. The data should be structured in such a way that once the script has run and the "commit" SQL command executed, your database must
be in a consistent state.
Design your test data so that you get output for the SQL queries specified below. Queries that are correct and do not produce output using your test
data will lose 50% of the marks allocated, so you should carefully check your test data and ensure it thoroughly validates your SQL queries. (10
marks)
Listing mm-load.sql:
set echo off
-- Inserting customers.
-- We make up names and details at will and add 5 private and 5 business customers:
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (11, 'Matthew', 'Paterson', '1111111111', '1 Blackburn Road', 'Clayton', 'VIC', '3168');
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (12, 'Jessica', 'Jones', '2222222222', '2 Blackburn Road', 'Clayton', 'VIC', '3168');
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (13, 'James', 'Williams', '3333333333', '3 Blackburn Road', 'Clayton', 'VIC', '3168');
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (14, 'Sarah', 'Brown', '4444444444', '4 Blackburn Road', 'Clayton', 'VIC', '3168');
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (15, 'Daniel', 'Wilson', '5555555555', '5 Blackburn Road', 'Clayton', 'VIC', '3168');
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_BNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (16, 'Emily', 'Taylor', 'Taylor University Supplies Ltd.', '6666666666', '6 Blackburn Road', 'Clayton', 'VIC', '3168');
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_BNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (17, 'Joshua', 'Johnson', 'Johnson University Supplies Ltd.', '7777777777', '7 Blackburn Road', 'Clayton', 'VIC', '3168');
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_BNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (18, 'Stephanie', 'White', 'White University Supplies Ltd.', '8888888888', '8 Blackburn Road', 'Clayton', 'VIC', '3168');
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_BNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (19, 'Michael', 'Nguyen', 'Nguyen University Supplies Ltd.', '9999999999', '9 Blackburn Road', 'Clayton', 'VIC', '3168');
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_BNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (20, 'Emma', 'Anderson', 'Anderson University Supplies Ltd.', '1010101010', '10 Blackburn Road', 'Clayton', 'VIC', '3168');
-- Done inserting customers.
-- Inserting quotes.
-- We make up the date details within the specified range. We also make up a purpose number - P03 for private
-- and P05 for business customers. Then, we arbitrary choose a truck/container combination from
page 14 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
-- the PURPOSE_LIST table that suits the respective purpose. This combination is not associated with quotes, but it
-- will be associated with the corresponding jobs later on. We hard-code the QUOTE_HIRE_CHARGE based on the
-- formula (JOB_END_DATE - JOB_START_DATE + 1) * (TRUCK_CONTAINER.TC_HIRERATE + TRUCK_CLASS.TC_DRIVER_HIRE_RATE):
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (11, to_date('01/05/2009', 'dd/mm/yyyy'), to_date('01/06/2009', 'dd/mm/yyyy'), to_date('02/06/2009', 'dd/mm/yyyy'),
2 * (800 + 200), 'Y', 'P03', 11);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (12, to_date('02/05/2009', 'dd/mm/yyyy'), to_date('02/06/2009', 'dd/mm/yyyy'), to_date('03/06/2009', 'dd/mm/yyyy'),
2 * (800 + 200), 'Y', 'P03', 12);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (13, to_date('03/05/2009', 'dd/mm/yyyy'), to_date('03/06/2009', 'dd/mm/yyyy'), to_date('04/06/2009', 'dd/mm/yyyy'),
2 * (800 + 200), 'Y', 'P03', 13);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (14, to_date('04/05/2009', 'dd/mm/yyyy'), to_date('04/06/2009', 'dd/mm/yyyy'), to_date('05/06/2009', 'dd/mm/yyyy'),
2 * (800 + 200), 'Y', 'P03', 14);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (15, to_date('05/05/2009', 'dd/mm/yyyy'), to_date('05/06/2009', 'dd/mm/yyyy'), to_date('06/06/2009', 'dd/mm/yyyy'),
2 * (800 + 200), 'Y', 'P03', 15);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (16, to_date('06/05/2009', 'dd/mm/yyyy'), to_date('06/06/2009', 'dd/mm/yyyy'), to_date('07/06/2009', 'dd/mm/yyyy'),
2 * (800 + 200), 'Y', 'P03', 11);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (17, to_date('07/05/2009', 'dd/mm/yyyy'), to_date('07/06/2009', 'dd/mm/yyyy'), to_date('08/06/2009', 'dd/mm/yyyy'),
2 * (800 + 200), 'Y', 'P03', 12);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (18, to_date('08/05/2009', 'dd/mm/yyyy'), to_date('08/06/2009', 'dd/mm/yyyy'), to_date('09/06/2009', 'dd/mm/yyyy'),
2 * (800 + 200), 'Y', 'P03', 13);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (19, to_date('09/05/2009', 'dd/mm/yyyy'), to_date('09/06/2009', 'dd/mm/yyyy'), to_date('10/06/2009', 'dd/mm/yyyy'),
2 * (800 + 200), 'Y', 'P03', 14);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (20, to_date('10/05/2009', 'dd/mm/yyyy'), to_date('10/06/2009', 'dd/mm/yyyy'), to_date('11/06/2009', 'dd/mm/yyyy'),
2 * (800 + 200), 'Y', 'P03', 15);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (21, to_date('01/05/2009', 'dd/mm/yyyy'), to_date('01/06/2009', 'dd/mm/yyyy'), to_date('02/06/2009', 'dd/mm/yyyy'),
2 * (700 + 200), 'Y', 'P05', 16);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (22, to_date('02/05/2009', 'dd/mm/yyyy'), to_date('02/06/2009', 'dd/mm/yyyy'), to_date('03/06/2009', 'dd/mm/yyyy'),
2 * (700 + 200), 'Y', 'P05', 17);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (23, to_date('03/05/2009', 'dd/mm/yyyy'), to_date('03/06/2009', 'dd/mm/yyyy'), to_date('04/06/2009', 'dd/mm/yyyy'),
page 15 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
2 * (700 + 200), 'Y', 'P05', 18);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (24, to_date('04/05/2009', 'dd/mm/yyyy'), to_date('04/06/2009', 'dd/mm/yyyy'), to_date('05/06/2009', 'dd/mm/yyyy'),
2 * (700 + 200), 'Y', 'P05', 19);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (25, to_date('05/05/2009', 'dd/mm/yyyy'), to_date('05/06/2009', 'dd/mm/yyyy'), to_date('06/06/2009', 'dd/mm/yyyy'),
2 * (700 + 200), 'Y', 'P05', 20);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (26, to_date('06/05/2009', 'dd/mm/yyyy'), to_date('06/06/2009', 'dd/mm/yyyy'), to_date('07/06/2009', 'dd/mm/yyyy'),
2 * (700 + 200), 'N', 'P05', 16);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (27, to_date('07/05/2009', 'dd/mm/yyyy'), to_date('07/06/2009', 'dd/mm/yyyy'), to_date('08/06/2009', 'dd/mm/yyyy'),
2 * (700 + 200), 'N', 'P05', 17);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (28, to_date('08/05/2009', 'dd/mm/yyyy'), to_date('08/06/2009', 'dd/mm/yyyy'), to_date('09/06/2009', 'dd/mm/yyyy'),
2 * (700 + 200), 'N', 'P05', 18);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (29, to_date('09/05/2009', 'dd/mm/yyyy'), to_date('09/06/2009', 'dd/mm/yyyy'), to_date('10/06/2009', 'dd/mm/yyyy'),
2 * (700 + 200), 'N', 'P05', 19);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (30, to_date('10/05/2009', 'dd/mm/yyyy'), to_date('10/06/2009', 'dd/mm/yyyy'), to_date('11/06/2009', 'dd/mm/yyyy'),
2 * (700 + 200), 'N', 'P05', 20);
-- Done inserting quotes.
-------
Inserting jobs.
As before we use an arbitrary chosen truck/container combination from the PURPOSE_LIST table that suits the
respective purpose. We save the truck and container IDs with the job and use the tables TRUCK_CONTAINER
and TRUCK_CLASS to look up the rates. The truck class can be determined on the basis of TRUCK_REGO_NO
using the TRUCK and the TRUCK_TYPE tables. We also use the DRIVER table to arbitrary choose an actual
driver who is permitted to drive a truck of the specified class.
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (11, '1 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (800 + 200), 'Y', 7, 'VIC100457', 'DRY10');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (12, '2 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (800 + 200), 'Y', 7, 'VIC100457', 'DRY10');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (13, '3 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (800 + 200), 'Y', 7, 'VIC100457', 'DRY10');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (14, '4 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (800 + 200), 'Y', 7, 'VIC100457', 'DRY10');
page 16 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (15, '5 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (800 + 200), 'Y', 7, 'VIC100457', 'DRY10');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (16, '1 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (800 + 200), 'N', 7, 'VIC100457', 'DRY10');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (17, '2 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (800 + 200), 'N', 7, 'VIC100457', 'DRY10');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (18, '3 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (800 + 200), 'N', 7, 'VIC100457', 'DRY10');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (19, '4 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (800 + 200), 'N', 7, 'VIC100457', 'DRY10');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (20, '5 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (800 + 200), 'N', 7, 'VIC100457', 'DRY10');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (21, '6 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (700 + 200), 'Y', 7, 'VIC100458', 'DRY09');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (22, '7 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (700 + 200), 'Y', 7, 'VIC100458', 'DRY09');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (23, '8 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (700 + 200), 'Y', 7, 'VIC100458', 'DRY09');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (24, '9 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (700 + 200), 'Y', 7, 'VIC100458', 'DRY09');
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (25, '10 Blackburn Road', 'Clayton', 'VIC', '3168', 2 * (700 + 200), 'Y', 7, 'VIC100458', 'DRY09');
-- Done inserting jobs.
commit;
page 17 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Task Q1.b
For the following tasks, the SQL scripts must correctly manage transactions and use sequences to generate new primary keys for numeric primary
key values (under no circumstances may a new primary key value be hard coded as a number). You should also ensure that any related data
impacted by your script actions are correctly managed. Provide four sql script files (named as indicated) for the following tasks, treat each task as a
single transaction dmt1.sql: A new private customer Cecilia O'Green of 26 Wellington Rd Clayton Victoria (VIC) 3168, phone 990321233 has just called and requested
3 quotes (at this point she has not confirmed she wishes to proceed and book the jobs):
•
•
•
Job start date: 1 June 2009; Job end date: 2 June 2009; for Rugged Terrain Transportation, which is Purpose Number ‘P04’.
Job start date: 6 June 2009; Job end date: 10 June 2009; for Rugged Terrain Transportation, which is Purpose Number ‘P04’.
Job start date: 20 June 2009; Job end date: 20 June 2009; for Business Shifting, which is Purpose Number ‘P05’.
Include this new customer and her quotes into the MMLtd database. You may use any suitable and available truck/container combination to quote
these jobs. There are no part day charges, all charging is based on full days. Customers are charged based on the following formula: number of
days booked *( driver hire rate for truck class + truck_container hire rate). (3 marks)
dmt2.sql: Cecilia has called stating that she has provided some incorrect information about the jobs for which she requested a quote and providing
her quote numbers. For the job that starts on 6 June 2009, the purpose should be for Dry Cargo Transportation (purpose number P01). She has also
asked that for all 3 jobs quoted the start date be moved 3 days later while maintaining the duration previously indicated (as Cecilia knows her quote
numbers, for this task you may hard code the quote numbers you generated in dmt1.sql). These two changes should be carried out using only two
SQL statements. (3 marks)
dmt3.sql: Cecilia, calls again and agrees to the hire rates for the first 2 jobs quoted, she provides the clerk with the relevant quote numbers. While
recording these 2 accepted jobs in the database, you may use any suitable driver employee number, container code and the registration number for
the allocated container and truck. Cecilia will pay for the jobs after the jobs have been completed. Both jobs will meet at the customers address.
Again, as Cecilia knows her quote numbers, for this task you may hard code the quote numbers you generated in dmt1.sql (3 marks)
dmt4.sql: Due to unforeseen circumstances, Cecilia has rung and indicated she needs to cancel the first job which she accepted in (iii) above. MMLtd
agrees to cancel the job without penalty to the customer. Again, as Cecilia knows her quote numbers, for this task you may hard code the quote
number you generated in dmt1.sql (1 mark)
Listing dmt1.sql
-- Creating customer.
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (CUST_NO_SEQ.nextval, 'Cecilia', 'O''Green', '990321233', '26 Wellington Road', 'Clayton', 'VIC', '3168');
page 18 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
-----
Creating qoutes.
Recall that the business requirements description specifies that the processing clerk manually chooses a truck
and the container for the quote. We follow this by choosing a combination for the specified purpose and
by hardcoding the appropriately calculated QUOTE_HIRE_CHARGE.
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (QUOTE_NO_SEQ.nextval, SYSDATE, to_date('01/06/2009', 'dd/mm/yyyy'), to_date('02/06/2009', 'dd/mm/yyyy'),
(650 + 150) * 2, 'N', 'P04', CUST_NO_SEQ.currval);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (QUOTE_NO_SEQ.nextval, SYSDATE, to_date('06/06/2009', 'dd/mm/yyyy'), to_date('10/06/2009', 'dd/mm/yyyy'),
(650 + 150) * 5, 'N', 'P04', CUST_NO_SEQ.currval);
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (QUOTE_NO_SEQ.nextval, SYSDATE, to_date('20/06/2009', 'dd/mm/yyyy'), to_date('20/06/2009', 'dd/mm/yyyy'),
(700 + 200) * 1, 'N', 'P05', CUST_NO_SEQ.currval);
-- Display results.
select * from CUSTOMER where ROWID = (select max(ROWID) from CUSTOMER);
select * from QUOTE where CUST_NO = (select CUST_NO from CUSTOMER where ROWID = (select max(ROWID) from CUSTOMER));
commit;
Listing dmt2.sql
set echo off;
-- The valiable OGreen_CustID holds the CUST_NO of Cecilia O'Green.
-- The value is either supplied by Cecilia on the phone or selected with the following statement:
undef OGreen_CustID
def OGreen_CustID = (select CUST_NO from CUSTOMER where CUST_GNAME = 'Cecilia' and CUST_FNAME = 'O''Green')
-- def OGreen_CustID = 100
-- Again, since Cecilia knows the quote number we could simply hardcode it, but we use the query below
-- to retrieve it in a more flexibme manner:
undef OGreen_Quote2ID
def OGreen_Quote2ID = (select QUOTE_NUM from QUOTE where CUST_NO = &OGreen_CustID and JOB_START_DATE = to_date('06/06/2009', 'dd/mm/yyyy'))
-- def OGreen_Quote2ID = 101
-- Update the purpose number of the quote to P01.
page 19 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
-- Note that as a result of this change, the QUOTE_HIRE_CHARGE must be updated accordingly.
update QUOTE
set PURPOSE_NO = 'P01', QUOTE_HIRE_CHARGE = (800 + 200) * 5
where QUOTE_NUM = &OGreen_Quote2ID;
-- Move each of Cecilia's quotes to 3 days later:
update QUOTE
set JOB_START_DATE = JOB_START_DATE + 3, JOB_END_DATE = JOB_END_DATE + 3
where CUST_NO = &OGreen_CustID;
-- Display results for verification:
select q.*
from QUOTE q, CUSTOMER c
where q.CUST_NO = c.CUST_NO and c.CUST_GNAME = 'Cecilia' and c.CUST_FNAME = 'O''Green';
commit;
Listing dmt3.sql
set echo off;
-- The valiable OGreen_CustID holds the CUST_NO of Cecilia O'Green.
-- The value is either supplied by Cecilia on the phone or selected with the following statement:
undef OGreen_CustID
def OGreen_CustID = (select CUST_NO from CUSTOMER where CUST_GNAME = 'Cecilia' and CUST_FNAME = 'O''Green')
-- def OGreen_CustID = 100
-- As before, Cecilia could supply the quote numbers, be
-- based on Cecilia's customer details and the date when
undef OGreen_Quote1ID, OGreen_Quote2ID
def OGreen_Quote1ID = (select QUOTE_NUM from QUOTE where
def OGreen_Quote2ID = (select QUOTE_NUM from QUOTE where
we would like to be more flexible and select them with appropriate statements
the jobs are to be executed:
CUST_NO = &OGreen_CustID and JOB_START_DATE = to_date('04/06/2009', 'dd/mm/yyyy'))
CUST_NO = &OGreen_CustID and JOB_START_DATE = to_date('09/06/2009', 'dd/mm/yyyy'))
-- When creating jobs from a quote, we need to associate a suitable truck
-- transportation purpose number. Instead of hardcoding this, we create a
-- combination from PURPOSE_LIST. We can select any such combination that
-- absence of further information we arbitrary choose to select the first
drop view OGreen_Quote1_TCCombi;
drop view OGreen_Quote2_TCCombi;
and a container with the job according to the
view that lists a suitable TRUCK_REGO_NO/C_CODE
matches the required PURPOSE_NO and in the
such combination returned by the database:
create view OGreen_Quote1_TCCombi as
page 20 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
(select TRUCK_REGO_NO, C_CODE from PURPOSE_LIST
where PURPOSE_NO = (select PURPOSE_NO from QUOTE where QUOTE_NUM = &OGreen_Quote1ID) and rownum <= 1);
create view OGreen_Quote2_TCCombi as
(select TRUCK_REGO_NO, C_CODE from PURPOSE_LIST
where PURPOSE_NO = (select PURPOSE_NO from QUOTE where QUOTE_NUM = &OGreen_Quote2ID) and rownum <= 1);
-- Now we need to select a driver for each job.
-- We arbitrary select the first driver that has a licence that allows to drive trucks selected into the abovew views:
undef OGreen_Quote1_DriverID, OGreen_Quote2_DriverID
def OGreen_Quote1_DriverID = (select dr.EMP_NUMB from DRIVER dr, TRUCK_TYPE tt, TRUCK t, OGreen_Quote1_TCCombi tcc where tcc.TRUCK_REGO_NO =
t.TRUCK_REGO_NO and t.TT_CODE = tt.TT_CODE and tt.TC_CLASS <= dr.TC_CLASS and rownum <= 1)
def OGreen_Quote2_DriverID = (select dr.EMP_NUMB from DRIVER dr, TRUCK_TYPE tt, TRUCK t, OGreen_Quote2_TCCombi tcc where tcc.TRUCK_REGO_NO =
t.TRUCK_REGO_NO and t.TT_CODE = tt.TT_CODE and tt.TC_CLASS <= dr.TC_CLASS and rownum <= 1)
-- In a similar fashion we need to select the truck/container and the driver hire rates and the duration (in full days)
-- for each of the jobs in order to be able to compute the job hire charge properly:
undef OGreen_Quote1_DriverHR
undef OGreen_Quote1_TruckContHR
undef OGreen_Quote1_Duration
def OGreen_Quote1_DriverHR = (select tc.TC_DRIVER_HIRE_RATE from TRUCK_CLASS tc, TRUCK_TYPE tt, TRUCK t, OGreen_Quote1_TCCombi tcc where
tcc.TRUCK_REGO_NO = t.TRUCK_REGO_NO and t.TT_CODE = tt.TT_CODE and tt.TC_CLASS = tc.TC_CLASS)
def OGreen_Quote1_TruckContHR = (select tc.TC_HIRERATE from truck_container tc, OGreen_Quote1_TCCombi tcc where tc.TRUCK_REGO_NO =
tcc.TRUCK_REGO_NO and tc.C_CODE = tcc.C_CODE)
def OGreen_Quote1_Duration = (select ceil(JOB_END_DATE - JOB_START_DATE) + 1 from QUOTE where QUOTE_NUM = &OGreen_Quote1ID)
undef OGreen_Quote2_DriverHR
undef OGreen_Quote2_TruckContHR
undef OGreen_Quote2_Duration
def OGreen_Quote2_DriverHR = (select tc.TC_DRIVER_HIRE_RATE from TRUCK_CLASS tc, TRUCK_TYPE tt, TRUCK t, OGreen_Quote2_TCCombi tcc where
tcc.TRUCK_REGO_NO = t.TRUCK_REGO_NO and t.TT_CODE = tt.TT_CODE and tt.TC_CLASS = tc.TC_CLASS)
def OGreen_Quote2_TruckContHR = (select tc.TC_HIRERATE from truck_container tc, OGreen_Quote2_TCCombi tcc where tc.TRUCK_REGO_NO =
tcc.TRUCK_REGO_NO and tc.C_CODE = tcc.C_CODE)
def OGreen_Quote2_Duration = (select ceil(JOB_END_DATE - JOB_START_DATE) + 1 from QUOTE where QUOTE_NUM = &OGreen_Quote2ID)
--print 'hello1';
--print &OGreen_Quote1ID;
--print 'hello2';
-- Now we can insert the first job:
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (&OGreen_Quote1ID,
(select CUST_STREET from CUSTOMER where CUST_NO = &OGreen_CustID),
(select CUST_SUBURB from CUSTOMER where CUST_NO = &OGreen_CustID),
(select CUST_STATE from CUSTOMER where CUST_NO = &OGreen_CustID),
(select CUST_PCODE from CUSTOMER where CUST_NO = &OGreen_CustID),
page 21 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
&OGreen_Quote1_Duration * (&OGreen_Quote1_DriverHR + &OGreen_Quote1_TruckContHR),
'N',
&OGreen_Quote1_DriverID,
(select TRUCK_REGO_NO from OGreen_Quote1_TCCombi),
(select C_CODE from OGreen_Quote1_TCCombi));
-- Update the first quote to make it fulfilled:
update QUOTE set QUOTE_FULFILLED = 'Y' where QUOTE_NUM = &OGreen_Quote1ID;
-- Now we can insert the second job:
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (&OGreen_Quote2ID,
(select CUST_STREET from CUSTOMER where CUST_NO = &OGreen_CustID),
(select CUST_SUBURB from CUSTOMER where CUST_NO = &OGreen_CustID),
(select CUST_STATE from CUSTOMER where CUST_NO = &OGreen_CustID),
(select CUST_PCODE from CUSTOMER where CUST_NO = &OGreen_CustID),
&OGreen_Quote2_Duration * (&OGreen_Quote2_DriverHR + &OGreen_Quote2_TruckContHR),
'N',
&OGreen_Quote2_DriverID,
(select TRUCK_REGO_NO from OGreen_Quote2_TCCombi),
(select C_CODE from OGreen_Quote2_TCCombi));
-- Update the second quote to make it fulfilled:
update QUOTE set QUOTE_FULFILLED = 'Y' where QUOTE_NUM = &OGreen_Quote2ID;
-- We do not need these views any longer:
drop view OGreen_Quote1_TCCombi;
drop view OGreen_Quote2_TCCombi;
-- Display the results:
select * from JOB, QUOTE where JOB.JOB_QUOTE_NUM = QUOTE.QUOTE_NUM;
commit;
Listing dmt4.sql
set echo off;
-- As before, the valiable OGreen_CustID holds the CUST_NO of Cecilia O'Green.
-- The value is either supplied by Cecilia on the phone or selected with the following statement:
undef OGreen_CustID
def OGreen_CustID = (select CUST_NO from CUSTOMER where CUST_GNAME = 'Cecilia' and CUST_FNAME = 'O''Green')
-- def OGreen_CustID = 100
page 22 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
-- Yet again, Cecilia could supply the quote number, be we would like to be more flexible and select it with an
-- appropriate statement based on Cecilia's customer details and the date when the job is to be executed:
undef OGreen_Quote1ID
def OGreen_Quote1ID = (select QUOTE_NUM from QUOTE where CUST_NO = &OGreen_CustID and JOB_START_DATE = to_date('04/06/2009', 'dd/mm/yyyy'))
-- Now we delete the job. According to the business case spec, the quote remain in the system and is marked as unfulfilled:
delete from JOB where JOB_QUOTE_NUM = &OGreen_Quote1ID;
update QUOTE set QUOTE_FULFILLED = 'N' where QUOTE_NUM = &OGreen_Quote1ID;
-- To verify the result, we display all of Cecilia's quotes with associated jobs, if any:
select *
from QUOTE q left outer join JOB j on q.QUOTE_NUM = j.JOB_QUOTE_NUM
where q.CUST_NO = &OGreen_CustID;
commit;
Task Q2.I
Display the truck registration number, container code and truck/container combination hire rate for all authorised truck/container combinations. The
registration number and container code should be shown in one column with an appropriate heading. The list should show the most expensive
combinations first. Within each group of combinations of the same hire rate the display should be in truck/container combination order. (2 marks)
Your report should have the form (not all rows shown):
page 23 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Listing qry1.sql
select TRUCK_REGO_NO || ' - ' || C_CODE as "Truck - Container Combination", TC_HIRERATE as "Daily Hire Rate"
from TRUCK_CONTAINER
order by TC_HIRERATE desc, TRUCK_REGO_NO asc, C_CODE asc;
Task Q2.II
Display the full details for all MMLtd customers who live in a particular postcode selected by the user at query execution time (this may be achieved
using a substitution variable for the customer postcode). The output should be in given name within family name order.
A substitution variable may be used in SQL Developer to prompt the user for a value at query execution time by using a place holder such as
&myvalue eg. select * from product where prod_no=&myvalue or select * from product where prod_name = '&myvalue' (in the first
example prod_no is numeric, in the second prod_name is character). The user entered value is then substituted for the placeholder and the query
executed with this value. (3 marks)
Listing qry2.sql
-- To help the user we first display a list of possible postcode choces:
select unique CUST_PCODE as "Available customer postcodes" from CUSTOMER;
-- We can use the following definition for selecting the customres from
-- postcode 3168, or comment it to have the system prompt the user for the postcode:
-- def SearchCustPCode = 3168
-- Now select the customers:
select * from CUSTOMER
where CUST_PCODE = &SearchCustPCode
order by CUST_FNAME asc, CUST_GNAME asc;
Task Q2.III
Display the full details for all types in the MMLtd truck type table which have the word 'MAX' in their model (regardless of case). (3 marks)
Listing qry3.sql
select * from TRUCK_TYPE where lower(TT_MODEL) like '%max%';
page 24 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Task Q2.IV
Display the employee number, full name, contact number, license number and highest truck class of all current drivers who are able to drive trucks
which are class 2 or higher. Order the list such that the employees who are able to drive the higher class trucks are listed first. (4 marks)
Listing qry4.sql
-- On top of the specified requirements, we also order by surname and given name:
select e.EMP_NUMB, e.EMP_GNAME, e.EMP_FNAME, e.EMP_CONTACT_NO, d.EMP_LICENCE_NO, d.TC_CLASS
from EMPLOYEE e, DRIVER d
where e.EMP_NUMB = d.EMP_NUMB and e.EMP_CURRENT = 'Y' and d.TC_CLASS >= 2
order by d.TC_CLASS desc, e.EMP_FNAME asc, e.EMP_GNAME asc;
Task Q2.V
List all jobs which have been completed that have required a duration of more than three days. For each job, list the customer number and name,
the job number, the job start date and end date, if the customer has paid and the duration in days. The display should be ordered with the longest
job duration at the top of the list. Jobs of the same duration should be ordered by job number. The job start and end dates should be displayed in
the form: 01 May 2009 (4 marks)
Listing qry6.sql
--------
We are required to list all jobs that have been completed, however, it is not clear
when a job can be regarded as having been completed. This is an example of an unfortunate
ambiguity as they can appear in real business requirements documents. For the purpose
of this question we assume that any job J that is listed in the database and that has
an end-date strictly before a user-specified date D is regarded as completed at date
D and any date thereafter. According to this, we need to query the user for D; we
call the corresponding variable CompletedDate.
undef CompletedDate_DD
undef CompletedDate_MM
undef CompletedDate_YYYY
-- Uncomment the following 3 lines to prompt for all dates completed before 2010
-- instead of prompting the user:
-- def CompletedDate_DD = '01'
-- def CompletedDate_MM = '01'
-- def CompletedDate_YYYY = '2010'
-- The choice of the example "01 May 2009" that specifies the output date format is ambiguous.
-- E.g., what is expected for 09/06/09: "01 June 2009" or "01 Jun 2009"?
-- In the absence of further information we choose "01 Jun 2009" (it also gives more consise code).
page 25 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
select c.CUST_NO, c.CUST_FNAME, c.CUST_GNAME, j.JOB_QUOTE_NUM,
to_char(q.JOB_START_DATE, 'dd Mon yyyy') as "JOB_START_DATE",
to_char(q.JOB_END_DATE, 'dd Mon yyyy') as "JOB_END_DATE",
j.JOB_PAYMENT_MADE, q.JOB_END_DATE - q.JOB_START_DATE + 1 as "JOB_DURATION_IN_DAYS"
from JOB j, QUOTE q, CUSTOMER c
where j.JOB_QUOTE_NUM = q.QUOTE_NUM and q.CUST_NO = c.CUST_NO
and q.JOB_END_DATE < to_date(&CompletedDate_DD || '/' || &CompletedDate_MM || '/' || &CompletedDate_YYYY, 'dd/mm/yyyy')
and q.JOB_END_DATE - q.JOB_START_DATE + 1 > 3
order by q.JOB_END_DATE - q.JOB_START_DATE desc, j.JOB_QUOTE_NUM asc;
Task Q2.VI
For all purposes listed in the PURPOSE_DESC table, list the number of quotes requiring that purpose. The list should show the purpose number, the
purpose description and the total number of quotes. Note that your query must deal with the situation in which a certain purpose may not as yet
have been required. The display should be in purpose description order. (4 marks)
Listing qry7.sql
select pd.PURPOSE_NO, pd.PURPOSE_DESC, count(distinct q.QUOTE_NUM) as "NUMBER_OF_QUOTES"
from PURPOSE_DESC pd left outer join QUOTE q on pd.PURPOSE_NO = q.PURPOSE_NO
group by pd.PURPOSE_NO, pd.PURPOSE_DESC
order by pd.PURPOSE_DESC asc;
Task Q2.VII
Display the full details for all MMLtd employees - the name details (given name and family name) should be shown in one column called 'Employee
Name', use meaningful labels for the other columns. The output should be displayed in family name within job code order. The licence number and
highest authorised truck class for non drivers should display the text NA. (4 marks)
Your report should have the form (not all rows shown):
page 26 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Listing qry5.sql
select e.EMP_NUMB as "Emp Number", e.EMP_FNAME || ', ' || e.EMP_GNAME as "Name",
e.EMP_CONTACT_NO as "Contact Number", e.EMP_SALARY as "Salary",
e.EMP_TAXFILE_NO as "Tax File Number", e.EMP_CURRENT as "Currently Employed",
e.JOB_CODE as "Job Code",
case when d.EMP_LICENCE_NO is null then 'N/A' else d.EMP_LICENCE_NO end as "Licence Number",
case when d.TC_CLASS is null then 'N/A' else to_char(d.TC_CLASS) end as "Highest Authorised Truck CLass"
from EMPLOYEE e left outer join DRIVER d on e.EMP_NUMB = d.EMP_NUMB
order by e.JOB_CODE asc, e.EMP_FNAME asc, e.EMP_FNAME asc;
Task Q2.VIII
Create a view called TODAYS_JOBS which contains the job number, the customer number, the job end date, the drivers name (as a single column)
and truck/container details (truck rego number and container code) for all jobs which are scheduled to begin tomorrow. (6 marks)
Listing qry8.sql
-- Drop the view in case is exists:
drop view TODAYS_JOBS;
-- If we define the following variable it will be easier to change the query from
-- "select jobst starting tomorrow" to "select jobs starting the day after the given date".
-- Besides, the statement in then much easier to debug, as for most days no jobs actually
-- start *tomorrow*.
undef TodaysDate
def TodaysDate = sysdate
-- This is used for testing as there are actually jobs on the 05/06/09:
-- def TodaysDate = to_date('04/06/2009', 'dd/mm/yyyy')
-- Create the view directly by selecting the required data:
create view TODAYS_JOBS as
select j.JOB_QUOTE_NUM as "JOB_QUOTE_NUM", q.CUST_NO as "CUST_NO", q.JOB_END_DATE as "JOB_END_DATE",
e.EMP_GNAME || ' ' || e.EMP_FNAME as "EMP_NAME", j.TRUCK_REGO_NO as "TRUCK_REGO_NO",
j.C_CODE as "C_CODE"
from JOB j, QUOTE q, EMPLOYEE e
where j.JOB_QUOTE_NUM = q.QUOTE_NUM and e.EMP_NUMB = j.EMP_NUMB
and to_char(&TodaysDate + 1, 'ddmmyyyy') = to_char(q.JOB_START_DATE, 'ddmmyyyy');
-- Display the results for verification:
select * from TODAYS_JOBS;
commit;
page 27 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Task Q2.IX
Report the average number of quotes per customer (be sure to check your answers logic against the ERD's cardinality/connectivity). The resultant
average should be expressed to two decimal points. (6 marks)
Listing qry9.sql
-- Here we are concerned with quotes only (i.e. not with the corresponding jobs).
-- The strategy is to first select a list of all customers with the corresponding
-- number of quotes for each customer (note, we need to include the customers with
-- zero quotes). Once done, we can build an average over the column that states
-- the number of quotes for each customer. Both can be done with a nested query:
select round(avg(CUST_QUOTES_COUNT), 2)
from (select c.CUST_NO, count(distinct q.QUOTE_NUM) as "CUST_QUOTES_COUNT"
from CUSTOMER c left outer join QUOTE q on c.CUST_NO = q.CUST_NO
group by c.CUST_NO);
-- One the logic is understood, we can achieve the same thing with this shorter query:
select round(avg(count(distinct q.QUOTE_NUM)), 2)
from CUSTOMER c left outer join QUOTE q on c.CUST_NO = q.CUST_NO
group by c.CUST_NO;
Task Q2.X
Prepare a single report listing all employees currently employed by MMLtd. The report should be structured as follows (6 marks):
• for each driver display - a string "Licenced Driver", the employee number, employee name and the total number of jobs assigned, and
• for each non-driver display - a string "Non Driver", the employee number, employee name and the job description
This report should be generated from a single SQL statement only, place appropriate headings on each column. Your report should have the form
(not all rows shown):
page 28 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Listing qry10.sql
-- There are at least 3 ways to verify whther an employee is a driver:
-- (1) Check whether JOB_TYPE is 40;
-- (2) Ckeck whether JOB_DESCRIPTION is "driver";
-- (3) Ckeck whether the employee appears in the driver table.
-- All these three possibilities may be prone to data input errors.
-- Here we simply choose the most descriptive approach (number 2).
select case when lower(jt.JOB_DESCRIPTION) = 'driver' then 'Licenced driver' else 'Non-driver' end as "Employee type",
e.EMP_NUMB as "Employee number", e.EMP_GNAME || ' ' || e.EMP_FNAME as "Employee name",
case when lower(jt.JOB_DESCRIPTION) = 'driver'
then (select to_char(count(JOB_QUOTE_NUM)) from JOB where EMP_NUMB = e.EMP_NUMB)
else jt.JOB_DESCRIPTION
end as "Role or Jobs assigned"
from JOB_TYPE jt, EMPLOYEE e
where e.JOB_CODE = jt.JOB_CODE and e.EMP_CURRENT = 'Y'
order by e.EMP_NUMB;
Task Q2.XI
One of the tasks clerks are required to carry out during preparation of a quote involves determining what truck/container combinations can be used
for what purpose and what the total cost for a set number of days hire for a particular combination would be. Prepare a report which will prompt the
user for the number of days the quote is for and then list the following:
• a single column containing the purpose number, the purpose description and the containers load capacity enclosed in [ ] - for example: P01 Dry
Cargo Transportation [30]
• a single column containing the truck registration number, container code and daily truck/container hire rate - for example: VIC200897 DRY01 Daily Hire rate: $600
• the truck class
• the total costs of hire for the required number of days, the heading for this column should indicate the number of days the cost is for. The cost is
calculated according to the formula: Number of days booked * (driver hire rate + truck_container hire rate) all charges are for a full
day, there are no part day charges
Your report should have the form (not all rows shown):
page 29 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
An oracle substitution variable may be reused, without requiring further prompting, by using a double ampersand eg. &&myvalue - this value will only
be prompted for once, and then reused where necessary. To clear a variable set in this manner enter undef myvalue (note the command has no
semi colon after it) (6 marks)
Listing qry11.sql
-- The def statement below can be used instead of querying the user is required:
undef NumberOfDays
--def NumberOfDays = 5
-----
The report is required to be ordered by truck/container combination within purpose,
however, it is cot clear whether purpose number or purpose description is meant.
This is another example of insufficiently specific business requirements.
We choose to order by purpose description.
-- On top of the requirement we display the daily driver hire rate in the truck class column.
-- This gives the user a better way to see where the total charge comes from.
select pd.PURPOSE_NO || ': ' || pd.PURPOSE_DESC || ' (' || cm.CM_LOAD_CAPACITY || 't)' as "Purpose",
tc.TRUCK_REGO_NO || ' / ' || tc.C_CODE || ' (daily T/C rate: $' || tc.TC_HIRERATE || ')' as "Truck/Container",
tt.TC_CLASS || ' (daily driver rate: $' || tcl.TC_DRIVER_HIRE_RATE || ')' as "Truck Class",
'$' || &&NumberOfDays * (tc.TC_HIRERATE + tcl.TC_DRIVER_HIRE_RATE) as "Total charge for &&NumberOfDays day(s)"
from PURPOSE_LIST pl, PURPOSE_DESC pd, CONTAINER c, CONTAINER_MODEL cm, TRUCK_CONTAINER tc,
TRUCK t, TRUCK_TYPE tt, TRUCK_CLASS tcl
where pl.PURPOSE_NO = pd.PURPOSE_NO and pl.C_CODE = c.C_CODE and c.CM_MODEL_NO = cm.CM_MODEL_NO
and pl.TRUCK_REGO_NO = tc.TRUCK_REGO_NO and pl.C_CODE = tc.C_CODE
and pl.TRUCK_REGO_NO = t.TRUCK_REGO_NO and tt.TT_CODE = t.TT_CODE
and tcl.TC_CLASS = tt.TC_CLASS
order by pd.PURPOSE_DESC, pd.PURPOSE_NO, pl.TRUCK_REGO_NO, pl.C_CODE;
page 30 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Task Q2.XII
Determing the availability of drivers is a task that the MMLtd clerks would like to automate. Prepare a report which will show for a given range of
dates (specified by a start date and an end date), supplied by the user, which drivers are available and what class of truck they are licenced to drive.
For a single day range the user will enter the same date for the start date and end date. The report should be in employee number within licence
highest authorised truck class order. (8 marks)
Your report should have the form (this will vary with your data and the date ranges selected):
Listing qry12.sql
undef StartDate_DD
undef StartDate_MM
undef StartDate_YYYY
undef EndDate_DD
undef EndDate_MM
undef EndDate_YYYY
-- Uncomment the following 6 lines to prompt for all dates in 2009
-- instead of prompting the user:
-- def StartDate_DD = '01'
-- def StartDate_MM = '01'
-- def StartDate_YYYY = '2009'
-- def EndDate_DD = '01'
-- def EndDate_MM = '01'
-- def EndDate_YYYY = '2010'
-----
The strategy here is as follows:
First select the count of all jobs within the specified period that are executed
by a specific driver. Then, nest this into another statement that selects all drivers
for which that count is zero.
select d.EMP_NUMB as "Emp Number", e.EMP_GNAME || ' ' || e.EMP_FNAME as "Employee name",
d.TC_CLASS as "Highest Authorised Truck Class"
from DRIVER d, EMPLOYEE e
page 31 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
where e.EMP_NUMB = d.EMP_NUMB and e.EMP_CURRENT = 'Y'
and 0 = (select count(j.EMP_NUMB) from QUOTE q, JOB j
where q.QUOTE_NUM = j.JOB_QUOTE_NUM
and j.EMP_NUMB = d.EMP_NUMB
and ( -- start date is between interval bounds or end date is between bounds:
(trunc(q.JOB_START_DATE, 'DD') >= to_date(&&StartDate_DD || '/' || &&StartDate_MM || '/' || &&StartDate_YYYY, 'dd/mm/yyyy')
and trunc(q.JOB_START_DATE, 'DD') <= to_date(&&EndDate_DD || '/' || &&EndDate_MM || '/' || &&EndDate_YYYY, 'dd/mm/yyyy'))
or (trunc(q.JOB_END_DATE, 'DD') >= to_date(&&StartDate_DD || '/' || &&StartDate_MM || '/' || &&StartDate_YYYY, 'dd/mm/yyyy')
and trunc(q.JOB_END_DATE, 'DD') <= to_date(&&EndDate_DD || '/' || &&EndDate_MM || '/' || &&EndDate_YYYY, 'dd/mm/yyyy'))
or (trunc(q.JOB_ START_DATE, 'DD') <= to_date(&&StartDate_DD || '/' || &&StartDate_MM || '/' || &&StartDate_YYYY, 'dd/mm/yyyy')
and trunc(q.JOB_END_DATE, 'DD') >= to_date(&&EndDate_DD || '/' || &&EndDate_MM || '/' || &&EndDate_YYYY, 'dd/mm/yyyy'))
)
)
order by d.TC_CLASS asc, d.EMP_NUMB;
Task Q3.I
Write a trigger which will implement an update cascade referential integrity rule between PURPOSE_DESC and PURPOSE_LIST. Your trigger will need to
take care of any resultant action due to this update cascade. A message should be displayed to indicate the update has occurred successfully. (4
marks)
Listing trigger1.sql
show errors
create or replace trigger PURPOSE_DESC_CASCADE_TO_LIST
after delete or update on PURPOSE_DESC
for each row
begin
-- A change in PURPOSE_NO should be cascaded to other tables as well, but the problem description
-- explicitly asks for PURPOSE_LIST only.
if updating('PURPOSE_NO') then
update PURPOSE_LIST pl set pl.PURPOSE_NO = :new.PURPOSE_NO where pl.PURPOSE_NO = :old.PURPOSE_NO;
dbms_output.put_line('The change of field PURPOSE_NO in table PURPOSE_DESC has been cascaded to table PURPOSE_LIST.');
elsif deleting then
delete from PURPOSE_LIST pl where pl.PURPOSE_NO = :old.PURPOSE_NO;
dbms_output.put_line(' The deletion of PURPOSE_NO in table PURPOSE_DESC has been cascaded to table PURPOSE_LIST.');
end if;
end;
/
commit;
page 32 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Listing trigger1-test.sql
-- We use the following code to test the trigger PURPOSE_DESC_CASCADE_TO_LIST.
set serveroutput on
-- Temporarily remove constraints such that we can modify PURPOSE_DESC freely
-- and set a rollback point:
alter table QUOTE disable constraint PD_QUOTE_FK;
alter table PURPOSE_LIST disable constraint PD_PL_FK;
savepoint Trigger1_Test_SP;
-- Display data as it is before the test:
select * from PURPOSE_DESC pd right outer join PURPOSE_LIST pl on pd.PURPOSE_NO = pl.PURPOSE_NO order by pd.PURPOSE_NO;
-- Execute an update:
update PURPOSE_DESC set PURPOSE_NO = 'P06' where PURPOSE_NO = 'P05';
-- Display data after the update. The update should be cascaded to the PURPOSE_LIST table:
select * from PURPOSE_DESC pd right outer join PURPOSE_LIST pl on pd.PURPOSE_NO = pl.PURPOSE_NO order by pd.PURPOSE_NO;
-- Execute a delete:
delete from PURPOSE_DESC where PURPOSE_NO = 'P06';
-- Display data after the delete. The delete should be cascaded to the PURPOSE_LIST table:
select * from PURPOSE_DESC pd right outer join PURPOSE_LIST pl on pd.PURPOSE_NO = pl.PURPOSE_NO order by pd.PURPOSE_NO;
-- Rollback
rollback to
alter table
alter table
all changes and reenable constraints:
Trigger1_Test_SP;
PURPOSE_LIST enable constraint PD_PL_FK;
QUOTE enable constraint PD_QUOTE_FK;
-- Display the data after the test. It should be same as before the test:
select * from PURPOSE_DESC pd right outer join PURPOSE_LIST pl on pd.PURPOSE_NO = pl.PURPOSE_NO order by pd.PURPOSE_NO;
Task Q3.II
MMLtd do not wish to allow any truck to be assigned to a job (either a new job, or update of an existing job) where the vehicle has exceeded
20,000Kms since the last service. Write a trigger which will implement this restriction such that during assignment of a truck to a job, any truck
which has not been serviced for 20,000 kms or more will be unable to be assigned due to a need for service. (8 marks)
page 33 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Listing trigger2.sql
show errors
create or replace trigger ENSURE_JOB_TRUCK_SERVICED
before insert or update on JOB
for each row
declare
T_CURRENT_KM TRUCK.TRUCK_KMS%type;
T_SERVICE_KM TRUCK.TRUCK_LASTSERVICEKMS%type;
begin
select t.TRUCK_KMS, t.TRUCK_LASTSERVICEKMS into T_CURRENT_KM, T_SERVICE_KM
from TRUCK t where t.TRUCK_REGO_NO = :new.TRUCK_REGO_NO;
if (T_CURRENT_KM - T_SERVICE_KM > 20000) then
raise_application_error(-20000, 'Cannot assign truck ' || to_char(:new.TRUCK_REGO_NO) || ' to job '
|| to_char(:new.JOB_QUOTE_NUM) || ' because this truck exceeds the maximum'
|| ' allowance of 20000km between services by '
|| to_char(T_CURRENT_KM - T_SERVICE_KM) || 'km.');
end if;
end;
/
commit;
Listing trigger2-test.sql
-- We use the following code to test the trigger ENSURE_JOB_TRUCK_SERVICED.
set serveroutput on
set echo on
-- Let's see the trucks:
select t.*, t.TRUCK_KMS - t.TRUCK_LASTSERVICEKMS from TRUCK t;
-- Set a rollback point so we can discard test data later:
savepoint Trigger2_Test_SP;
-- Add a test customer:
insert into CUSTOMER (CUST_NO, CUST_GNAME, CUST_FNAME, CUST_CONTACT_NO, CUST_STREET, CUST_SUBURB, CUST_STATE, CUST_PCODE)
values (51, 'Test-Trigger2-FirstName', 'Test-Trigger2-Surname', '000000000', '0 Some Street', 'Suburb', 'VIC', '1234');
page 34 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
-- Add a test quote:
insert into QUOTE (QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, QUOTE_FULFILLED, PURPOSE_NO, CUST_NO)
values (51, to_date('01/05/2009', 'dd/mm/yyyy'), to_date('02/05/2009', 'dd/mm/yyyy'), to_date('03/05/2009', 'dd/mm/yyyy'),
0, 'Y', 'P03', 51);
-- Select all jobs with the test quote. There should be no jobs yet:
select * from JOB j right outer join QUOTE q on q.QUOTE_NUM = j.JOB_QUOTE_NUM where q.CUST_NO = 51;
-- Add a job with a truck that exceeded 20K km after service. This should not work:
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (51, '0 Some Street', 'Suburb', 'VIC', '1234', 0, 'Y', 7, 'VIC100458', 'DRY03');
-- There should be still no jobs:
select * from JOB j right outer join QUOTE q on q.QUOTE_NUM = j.JOB_QUOTE_NUM where q.CUST_NO = 51;
-- Now add a job with a truck that is ok:
insert into JOB (JOB_QUOTE_NUM, JOB_MEET_STREET, JOB_MEET_SUBURB, JOB_MEET_STATE, JOB_MEET_PCODE,
JOB_HIRE_CHARGE, JOB_PAYMENT_MADE, EMP_NUMB, TRUCK_REGO_NO, C_CODE)
values (51, '0 Some Street', 'Suburb', 'VIC', '1234', 0, 'Y', 7, 'VIC223456', 'DRY04');
-- Now there should be one job:
select * from JOB j right outer join QUOTE q on q.QUOTE_NUM = j.JOB_QUOTE_NUM where q.CUST_NO = 51;
-- Try to update to a bad truck:
update JOB set C_CODE = 'DRY03', TRUCK_REGO_NO = 'VIC100458' where JOB_QUOTE_NUM = 51;
-- The job should not change:
select * from JOB j right outer join QUOTE q on q.QUOTE_NUM = j.JOB_QUOTE_NUM where q.CUST_NO = 51;
-- Try to update to a good truck:
update JOB set C_CODE = 'DRY08', TRUCK_REGO_NO = 'VIC291909' where JOB_QUOTE_NUM = 51;
-- The job should now change:
select * from JOB j right outer join QUOTE q on q.QUOTE_NUM = j.JOB_QUOTE_NUM where q.CUST_NO = 51;
-- Get rid of all test data:
rollback to Trigger2_Test_SP;
-- This should select 0 rows:
select * from JOB j right outer join QUOTE q on q.QUOTE_NUM = j.JOB_QUOTE_NUM where q.CUST_NO = 51;
page 35 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Task Q4.I
This task should be attempted only after Q1, Q2 and Q3 have been successfully completed.
In an endeavor to improve the efficiency of the company the MMLtd manager has decided that she would like to multi skill some employees. This
would allow nominated employees to carry out multiple roles - in particular some drivers would also like to be able to act as clerks. Marie wishes to
keep a record in the database where an employee has been designated to carry out multiple jobs.
• Describe the changes which will need to be made to the supplied MMLtd model supplied above, and
• Write a script file (designmod1.sql) which can be run after the supplied schema file, less the data insert statements, has been run, to make these
changes. You are not required to make any data changes, just the database structural changes. (6 marks)
Listing designmod1.sql
-- At the beginning we display the jobs of all employees such that we can verify
-- the result at the end:
select e.EMP_NUMB, e.EMP_GNAME, e.EMP_FNAME, e.JOB_CODE, jt.JOB_DESCRIPTION
from EMPLOYEE e, JOB_TYPE jt
where e.JOB_CODE = jt.JOB_CODE
order by e.EMP_NUMB;
-- All we need to do for this task is to change the multiplicity between
-- the EMPLOYEE and the JOB_TYPE tables from n:1 to n:n. This is done as
-- usual with the help of an additional table:
drop table EMPLOYEE_JOB purge;
create table EMPLOYEE_JOB (
EMP_NUMB number(3,0) not null,
JOB_CODE number(2,0) not null,
constraints EMPLOYEE_JOB_PK primary key (EMP_NUMB, JOB_CODE)
);
alter table EMPLOYEE_JOB add constraint EMPLOYEE_JOB_JOB_CODE_FK
foreign key (JOB_CODE) references JOB_TYPE(JOB_CODE)
not deferrable;
alter table EMPLOYEE_JOB add constraint EMPLOYEE_JOB_EMP_NUMB_FK
foreign key (EMP_NUMB) references EMPLOYEE(EMP_NUMB)
not deferrable;
-- Although not required by the problem statement, we can now easily copy all
-- required data into the new table:
page 36 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
insert into EMPLOYEE_JOB(EMP_NUMB, JOB_CODE) select EMP_NUMB, JOB_CODE from EMPLOYEE;
commit;
-- Now we need to remove the unnecessary JOB_CODE column from the EMPLOYEE table;
-- we also have to remove the corresponding constraint:
alter table EMPLOYEE drop constraint JT_EMPLOYEE_FK;
alter table EMPLOYEE drop column JOB_CODE;
-- Display results for verification:
select * from EMPLOYEE order by EMP_NUMB;
select * from EMPLOYEE_JOB order by EMP_NUMB;
select e.EMP_NUMB, e.EMP_GNAME, e.EMP_FNAME, ej.JOB_CODE, jt.JOB_DESCRIPTION
from EMPLOYEE e, EMPLOYEE_JOB ej, JOB_TYPE jt
where e.EMP_NUMB = ej.EMP_NUMB and ej.JOB_CODE = jt.JOB_CODE
order by e.EMP_NUMB;
Task Q4.II
This task should be attempted only after Q1, Q2 and Q3 have been successfully completed.
MMLtd have decided they wish to archive unfulfilled QUOTE details into a table called UF_QUOTE (ie. remove the records from QUOTE and place them
in UF_QUOTE)
• Describe the changes which will need to be made to the supplied MMLtd model, and
• Write a script file (designmod2.sql) which can be run after the supplied schema file has been run to make these changes. For this task you should
include in your script the SQL commands to archive any unfulfilled quote records which cannot any longer be converted to a job. (6 marks)
Listing designmod2.sql
-- Before we begin, we display the quotes we have for a later check:
select * from QUOTE order by QUOTE_FULFILLED desc, QUOTE_NUM asc;
-- First, we create the new UF_QUOTE table. It is the same as the QUOTE table except
-- that the QUOTE_FULFILLED flag is not necessary:
drop table UF_QUOTE purge;
create table UF_QUOTE (
QUOTE_NUM number(7,0) not null,
QUOTE_DATE date not null,
page 37 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
JOB_START_DATE date not null,
JOB_END_DATE date not null,
QUOTE_HIRE_CHARGE number(6,2) not null,
PURPOSE_NO char(3) not null,
CUST_NO number(6,0) not null,
constraint UFQUOTE_PK primary key (QUOTE_NUM),
constraint UFQ_JOB_DATES_CHK check (JOB_START_DATE <= JOB_END_DATE)
);
alter table UF_QUOTE add constraint CUSTOMER_UFQUOTE_FK
foreign key (CUST_NO) references CUSTOMER(CUST_NO)
not deferrable;
alter table UF_QUOTE add constraint PD_UFQUOTE_FK
foreign key (PURPOSE_NO) references PURPOSE_DESC(PURPOSE_NO)
not deferrable;
-- Now we copy the data over.
-- For this we need today's date and the maximum number of days that a quote can wait for confirmation:
undef Today
undef MaxQuoteWaitDays
def Today = sysdate
-- def Today = to_date('01-01-2010', 'dd-mm-yyyy')
def MaxQuoteWaitDays = 7
insert into UF_QUOTE(QUOTE_NUM, QUOTE_DATE, JOB_START_DATE, JOB_END_DATE, QUOTE_HIRE_CHARGE, PURPOSE_NO, CUST_NO)
(select q.QUOTE_NUM, q.QUOTE_DATE, q.JOB_START_DATE, q.JOB_END_DATE, q.QUOTE_HIRE_CHARGE, q.PURPOSE_NO, q.CUST_NO
from QUOTE q
where q.QUOTE_FULFILLED = 'N' and trunc(&&Today, 'DD') > trunc(q.QUOTE_DATE + 7, 'DD'));
delete from QUOTE
where QUOTE_FULFILLED = 'N' and trunc(&&Today, 'DD') > trunc(QUOTE_DATE, 'DD') + 7;
commit;
-- Display the data after the changes:
select * from QUOTE order by QUOTE_NUM asc;
select * from UF_QUOTE order by QUOTE_NUM asc;
page 38 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
Task Q5.I
FIT2010 Students must attempt this question, other students should not complete this question!
The management of MMLtd would like to keep an automated check on all inserts/updates to a job to indicate that a payment has been made. To do
this it has been decided that they would like you to create:
• a new table called JOBPAY_AUDIT with the following attributes (audit_schm.sql):
o audit_no - a numeric audit PK created automatically from a sequence
o audit_date - the date/time of this entry as a TIMESTAMP
o audit_received_by - the name of the oracle user who recorded the job as paid in the database
o job_quote_num - the job number for the job recorded as paid in the database
• a trigger (audit_trigger.sql) which will automatically record entries to this table whenever the job_payment_made attribute of JOB is set to 'Y'
(2 + 4 = 6 marks)
Listing audit_schm.sql
-- Create the sequence:
drop sequence JOBPAY_AUDIT_SEQ;
create sequence JOBPAY_AUDIT_SEQ
start with 100
increment by 1
nocache nocycle;
-- Create the table and the corresponding constraints:
drop table JOBPAY_AUDIT purge;
create table JOBPAY_AUDIT (
AUDIT_NO integer not null,
AUDIT_DATE timestamp not null,
AUDIT_RECEIVED_BY varchar(255) not null,
JOB_QUOTE_NUM number(7, 0) not null,
constraint JOBPAY_AUDIT_PK primary key (AUDIT_NO)
);
alter table JOBPAY_AUDIT add constraint JOBPAY_AUDIT_QUOTE_NUM_FK
foreign key (JOB_QUOTE_NUM) references QUOTE(QUOTE_NUM)
not deferrable;
Listing audit_trigger.sql
page 39 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
(Note that the trigger JOBPAY_AUDIT_TRIGGER
ENSURE_JOB_TRUCK_SERVICED above.)
can
be
tested
in
a
similar
manner
to
triggers
PURPOSE_DESC_CASCADE_TO_LIST
and
show errors
create or replace trigger JOBPAY_AUDIT_TRIGGER
after insert or update on JOB
for each row
begin
if (inserting or updating('JOB_PAYMENT_MADE')) and :new.JOB_PAYMENT_MADE = 'Y' then
insert into JOBPAY_AUDIT(AUDIT_NO, AUDIT_DATE, AUDIT_RECEIVED_BY, JOB_QUOTE_NUM)
values (JOBPAY_AUDIT_SEQ.nextval, current_timestamp, user, :new.JOB_QUOTE_NUM);
end if;
end;
/
commit;
Task Q5.II
FIT2010 Students must attempt this question, other students should not complete this question!
You are also asked to create a stored procedure called ADD_TC_PL to add a new truck/container combination and purpose list entry. This procedure
will accept a truck registration number, a container code, a hire rate and a purpose number. This procedure must not add a combination if the
combination and purpose_list already exists in the database. The procedure should allow the add of an entirely new combination and its associated
purpose_list entry. It should also allow the add of a new purpose_list entry for a combination that already exists in truck_container. (14
marks)
Listing add_tc_pl.sql.sql
create or replace procedure ADD_TC_PL(addTRUCK_REGO_NO PURPOSE_LIST.TRUCK_REGO_NO%type,
addC_CODE PURPOSE_LIST.C_CODE%type,
addTC_HIRERATE TRUCK_CONTAINER.TC_HIRERATE%type,
addPURPOSE_NO PURPOSE_LIST.PURPOSE_NO%type) as
COUNTER integer;
OLD_RATE TRUCK_CONTAINER.TC_HIRERATE%type;
begin
-- Check whether combination already exists in the purpose list:
page 40 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
dbms_output.put_line('');
select count(*) into COUNTER from PURPOSE_LIST pl
where pl.TRUCK_REGO_NO = addTRUCK_REGO_NO and pl.C_CODE = addC_CODE and pl.PURPOSE_NO = addPURPOSE_NO;
-- If combination does not exist, add it to the list:
if 0 = COUNTER then
dbms_output.put_line('Truck/Container/Purpose combination
dbms_output.put_line('Adding combination to PURPOSE_LIST:
|| ',
|| ',
does not exist in the PURPOSE_LIST table.');
TRUCK_REGO_NO=' || addTRUCK_REGO_NO
C_CODE=' || addC_CODE
PURPOSE_NO=' || addPURPOSE_NO || '.');
insert into PURPOSE_LIST(TRUCK_REGO_NO, C_CODE, PURPOSE_NO)
values (addTRUCK_REGO_NO, addC_CODE, addPURPOSE_NO);
-- If combination exists, display a message and do not add it:
elsif 1 = COUNTER then
dbms_output.put_line('Truck/Container/Purpose combination already exists in the PURPOSE_LIST table.');
dbms_output.put_line('Combination will not be added to PURPOSE_LIST: TRUCK_REGO_NO=' || addTRUCK_REGO_NO
|| ', C_CODE=' || addC_CODE
|| ', PURPOSE_NO=' || addPURPOSE_NO || '.');
-- Use defensive programming style and check for unexpected conditions:
else
raise_application_error(-20000, 'A bug has occured: duplicate rows in PURPOSE_LIST detected.');
end if;
-- Now check if combination exists in the TRUCK_CONTAINER list or T/C hire rates:
dbms_output.put_line('');
select count(*) into COUNTER from TRUCK_CONTAINER tc
where tc.TRUCK_REGO_NO = addTRUCK_REGO_NO and tc.C_CODE = addC_CODE;
-- If combination does not exist, add it to the list:
if 0 = COUNTER then
dbms_output.put_line('Truck/Container/HireRate combination does not exist in the TRUCK_CONTAINER table.');
dbms_output.put_line('Adding combination to TRUCK_CONTAINER: TRUCK_REGO_NO=' || addTRUCK_REGO_NO
|| ', C_CODE=' || addC_CODE
|| ', TC_HIRERATE=' || addTC_HIRERATE || '.');
insert into TRUCK_CONTAINER(TRUCK_REGO_NO, C_CODE, TC_HIRERATE)
page 41 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
values (addTRUCK_REGO_NO, addC_CODE, addTC_HIRERATE);
-- If combination exists, display a message and do not add it:
elsif 1 = COUNTER then
select TC_HIRERATE into OLD_RATE from TRUCK_CONTAINER where TRUCK_REGO_NO = addTRUCK_REGO_NO and C_CODE = addC_CODE;
dbms_output.put_line('Truck/Container combination already exists in the TRUCK_CONTAINER table.');
dbms_output.put_line('Current rate for ' || addTRUCK_REGO_NO || ' / ' || addC_CODE || ' is $' || OLD_RATE || '.');
if OLD_RATE = addTC_HIRERATE then
dbms_output.put_line('Combination will not be updated in TRUCK_CONTAINER: TRUCK_REGO_NO=' || addTRUCK_REGO_NO
|| ', C_CODE=' || addC_CODE
|| ' since the new rate is the same as the old rate.');
else
dbms_output.put_line('Combination will be updated in TRUCK_CONTAINER: TRUCK_REGO_NO=' || addTRUCK_REGO_NO
|| ', C_CODE=' || addC_CODE
|| ', TC_HIRERATE=' || addTC_HIRERATE || '.');
update TRUCK_CONTAINER tc set tc.TC_HIRERATE = addTC_HIRERATE
where tc.TRUCK_REGO_NO = addTRUCK_REGO_NO and tc.C_CODE = addC_CODE;
end if;
-- Use defensive programming style and check for unexpected conditions:
else
raise_application_error(-20000, 'A bug has occured: duplicate primary keys in TRUCK_CONTAINER detected.');
end if;
end ADD_TC_PL;
/
show errors
Listing add_tc_pl.sql-test.sql
-- We can use this code to test the stored procedure ADD_TC_PL:
set serveroutput on
-- Display the relevant data before the test:
select tc.TRUCK_REGO_NO, tc.C_CODE, tc.TC_HIRERATE, pl.PURPOSE_NO
from TRUCK_CONTAINER tc, PURPOSE_LIST pl
where tc.TRUCK_REGO_NO = pl.TRUCK_REGO_NO and tc.C_CODE = pl.C_CODE
order by tc.TRUCK_REGO_NO asc, tc.C_CODE asc;
page 42 of 43
FIT1004/FIT2010 - Databases. Monash University, Semester 1, 2009. Assignment 2 sample solution. Greg Paperin.
-- Expected outcome: PURPOSE_NO - no changes; TC_HIRERATE - no changes:
call ADD_TC_PL('VIC100457', 'DRY10', 800, 'P03');
-- Expected outcome: PURPOSE_NO - add P04 for given T/C combo; TC_HIRERATE - no changes:
call ADD_TC_PL('VIC100457', 'DRY10', 800, 'P04');
-- Expected outcome: PURPOSE_NO - no changes; TC_HIRERATE - set to $900 for goven T/C combo:
call ADD_TC_PL('VIC100457', 'DRY10', 900, 'P04');
-- Expected outcome: PURPOSE_NO - add P01 for given T/C combo; TC_HIRERATE - set to $1000 for goven T/C combo:
call ADD_TC_PL('VIC100457', 'RUG12', 1000, 'P01');
-- Display the relevant data aftre the test:
select tc.TRUCK_REGO_NO, tc.C_CODE, tc.TC_HIRERATE, pl.PURPOSE_NO
from TRUCK_CONTAINER tc, PURPOSE_LIST pl
where tc.TRUCK_REGO_NO = pl.TRUCK_REGO_NO and tc.C_CODE = pl.C_CODE
order by tc.TRUCK_REGO_NO asc, tc.C_CODE asc;
page 43 of 43