FIT9132

Introductionto
databases,
Semester2,2016

UnitTest

TIMEandLOCATION:Tutorialclass,week8.
DURATION:90minutes
NUMBEROFQUESTIONS:5
TOPIC:WritingSQLQuery

INSTRUCTION:
1.

LogintoMonashcomputerusing:
o Username:
examlogin01
o Password:
examlogin01
2. OpenSQLDeveloperandconnecttoOracleusingyourOracleaccountdetails.
Connectiondetails:
Hostname
:hippo.its.monash.edu
Port
:1521
SID
:FIT9132
3. OpenMoodleonInternetExplorer,logintoMoodleusingyourMonashusername
andpassword.
4. OpenanewSQLfileintheSQLDeveloper.Usethisfiletowriteyouranswertoa
question.Savetheanswertothequestionandnameitasq1.sql.Choosethe
desktopasthelocationtosavethefile.ClosethecurrentfileinSQLDeveloper(DO
NOTclosetheSQLDeveloper

).UploadthefiletoMoodlesubmissionlinkUnitTest.
5. Repeatstep4fortherestofthequestions,namethefileaccordingly,egq2.sql,
q3.sql,etc
6. DisconnectandcloseSQLDeveloperandlogoutfromMoodle.
7. Deletethefile(clickthefile,click

shift+delete,thenclickyes).
8. Logoffandrestartthecomputer.

FIT9132UnitTest

Page1of13

APPENDIXA
DatabaseSchema

CREATE
TABLECOUNTRY
(
COUNTRY_IOC_CODE
CHAR(3)
NOTNULL,
COUNTRY_IOC_NAME
VARCHAR2(50)
NOTNULL,

CONSTRAINTCOUNTRY_PKPRIMARYKEY(COUNTRY_IOC_CODE)
);

CREATE
TABLEDRIVER
(
DRIVER_ID
NUMBER(4)
NOTNULL,
DRIVER_GIVENNAME
VARCHAR2(50),
DRIVER_FAMILYNAME
VARCHAR2(50)
NOTNULL,
DRIVER_LICENCE_NO
CHAR(18)
NOTNULL,
DRIVER_CLEARANCE_LEVEL
CHAR(1)
NOTNULL,
DRIVER_MODSCOMP
NUMBER
NOTNULL,
DRIVER_AVAIL_CODE
CHAR(1)
NOTNULL,

CONSTRAINTDRIVER_PKPRIMARYKEY(DRIVER_ID)
);

CREATE
TABLEDRIVER_AUDIT
(
AUDIT_NO
NUMBER(8)
NOTNULL,
DRIVER_ID
NUMBER(6)
NOTNULL,
AUDIT_ORA_USER
VARCHAR2(30)
NOTNULL,
AUDIT_DATETIME
DATE
NOTNULL,

CONSTRAINTDRIVER_AUDIT_PKPRIMARYKEY(AUDIT_NO)
);

CREATE
TABLEDRIVER_AVAILABILITY
(
DRIVER_AVAIL_CODE
CHAR(1)
NOTNULL,
DRIVER_AVAIL_DESC
VARCHAR2(20)
NOTNULL,

CONSTRAINTDRIVER_AVAILABILITY_PKPRIMARYKEY(DRIVER_AVAIL_CODE)
);

FIT9132UnitTest

Page2of13

CREATE
TABLEDRIVER_LANGUAGE
(
DRIVER_ID
NUMBER(4)
NOTNULL,
LANG_ISO_CODE
CHAR(2)
NOTNULL,

CONSTRAINTDRIVER_LANGUAGE_PKPRIMARYKEY(DRIVER_ID,LANG_ISO_CODE)
);

CREATE
TABLEDRIVER_TRAINING
(
DRIVER_ID
NUMBER(4)
NOTNULL,
TRAINING_CODE
CHAR(5)
NOTNULL,
DT_DATE_COMPLETED
DATE
NOTNULL,

CONSTRAINTDRIVER_TRAINING_PKPRIMARYKEY(DRIVER_ID,TRAINING_CODE,
DT_DATE_COMPLETED)
);

CREATE
TABLEFEATURE
(
FEATURE_ID
CHAR(2)
NOTNULL,
FEATURE_DESC
VARCHAR2(50)
NOTNULL,

CONSTRAINTFEATURE_PKPRIMARYKEY(FEATURE_ID)

);

CREATE
TABLEIOC_ROLE
(
IOC_ROLE_CODE
CHAR(2)
NOTNULL,
IOC_ROLE_DESC
VARCHAR2(50)
NOTNULL,

CONSTRAINTIOC_ROLE_PKPRIMARYKEY(IOC_ROLE_CODE)

);

CREATE
TABLELANGUAGE
(
LANG_ISO_CODE
CHAR(2)
NOTNULL,
LANG_NAME
VARCHAR2(50)
NOTNULL,

CONSTRAINTLANGUAGE_PKPRIMARYKEY(LANG_ISO_CODE)
);
FIT9132UnitTest

Page3of13

CREATE
TABLELOCATION
(
LOCATION_ID
NUMBER(6)
NOTNULL,
LOCATION_NAME
VARCHAR2(40),
LOCATION_STREET
VARCHAR2(50)
NOTNULL,
LOCATION_TOWN
VARCHAR2(30)
NOTNULL,
LOCTYPE_ID
NUMBER(3)
NOTNULL,

CONSTRAINTLOCATION_PKPRIMARYKEY(LOCATION_ID)
);

CREATE
TABLELOCATION_TYPE
(
LOCTYPE_ID
NUMBER(3)
NOTNULL,
LOCTYPE_DESC
VARCHAR2(50)
NOTNULL,

CONSTRAINTLOCATION_TYPE_PKPRIMARYKEY(LOCTYPE_ID)
);

CREATE
TABLEMAINT_REP
(
MR_NO
NUMBER(6)
NOTNULL,
MR_DATE
DATE
NOTNULL,
MR_ODOMETER
NUMBER(6)
NOTNULL,
MR_COST
NUMBER(7,2)
NOTNULL,
MR_DETAILS
VARCHAR2(250)
NOTNULL,
VEH_VIN
CHAR(17)
NOTNULL,

CONSTRAINTMAINT_REP_PKPRIMARYKEY(MR_NO)
);

CREATE
TABLEOFFICIAL
(
OFF_OLYMPIC_ID
CHAR(8)
NOTNULL,
OFF_GIVENNAME
VARCHAR2(50)
NOTNULL,
OFF_FAMILYNAME
VARCHAR2(50)
NOTNULL,
IOC_ROLE_CODE
CHAR(2)
NOTNULL,
COUNTRY_IOC_CODE
CHAR(3)
NOTNULL,
LANG_ISO_CODE
CHAR(2)
NOTNULL,

CONSTRAINTOFFICIAL_PKPRIMARYKEY(OFF_OLYMPIC_ID)
);

FIT9132UnitTest

Page4of13

CREATE
TABLETRAINING
(
TRAINING_CODE
CHAR(5)
NOTNULL,
TRAINING_NAME
VARCHAR2(80)
NOTNULL,

CONSTRAINTTRAINING_PKPRIMARYKEY(TRAINING_CODE)
);

CREATE
TABLETRIP
(
TRIP_ID
NUMBER(6)
NOTNULL,
TRIP_START
DATE
NOTNULL,
TRIP_END
DATE
NOTNULL,
TRIP_START_ACTUAL DATE,
TRIP_END_ACTUAL
DATE,
TRIP_START_KM
NUMBER(6),
TRIP_END_KM
NUMBER(6),
TRIP_PASSENGERS
NUMBER(2)
NOTNULL,
VEH_VIN
CHAR(17)
NOTNULL,
LOCATION_ID_FROM NUMBER(6)
NOTNULL,
LOCATION_ID_TO
NUMBER(6)
NOTNULL,
OFF_OLYMPIC_ID
CHAR(8)
NOTNULL,
DRIVER_ID
NUMBER(4)
NOTNULL,

CONSTRAINTTRIP_PKPRIMARYKEY(TRIP_ID)
);

CREATE
TABLEVEHICLE
(
VEH_VIN
CHAR(17)
NOTNULL,
VEH_REGONUMBER
CHAR(7)
NOTNULL,
VEH_MAKE
VARCHAR2(40)
NOTNULL,
VEH_MODEL
VARCHAR2(40)
NOTNULL,
VEH_COLOUR
VARCHAR2(20)
NOTNULL,
VEH_ODOMETER
NUMBER(6)
NOTNULL,
VEH_SEATSAVAILABLE NUMBER(2)
NOTNULL,
VEH_AVAIL_CODE
CHAR(1)
NOTNULL,

CONSTRAINTVEHICLE_PKPRIMARYKEY(VEH_VIN)
);

FIT9132UnitTest

Page5of13


CREATE
TABLEVEHICLE_FEATURE
(
VEH_VIN
CHAR(17)
NOTNULL,
FEATURE_ID
CHAR(2)
NOTNULL,

CONSTRAINTVEHICLE_FEATURE_PKPRIMARYKEY(VEH_VIN,FEATURE_ID)
);

CREATE
TABLEVEH_AVAILABILITY
(
VEH_AVAIL_CODE
CHAR(1)
NOTNULL,
VEH_AVAIL_DESC
VARCHAR2(20)
NOTNULL,

CONSTRAINTVEH_AVAILABILITY_PKPRIMARYKEY(VEH_AVAIL_CODE)
);

ALTERTABLEVEHICLEADDCONSTRAINTAVAILABILITY_VEHICLE_FKFOREIGNKEY(
VEH_AVAIL_CODE)REFERENCESVEH_AVAILABILITY(VEH_AVAIL_CODE);

ALTERTABLEOFFICIALADDCONSTRAINTCOUNTRY_OFFICIAL_FKFOREIGNKEY(
COUNTRY_IOC_CODE)REFERENCESCOUNTRY(COUNTRY_IOC_CODE);

ALTERTABLEVEHICLE_FEATUREADDCONSTRAINTFEATURE_VEHICLE_FEATURE_FK
FOREIGN
KEY(FEATURE_ID)REFERENCESFEATURE(FEATURE_ID);

ALTERTABLEOFFICIALADDCONSTRAINTIOC_ROLE_OFFICIAL_FKFOREIGNKEY(
IOC_ROLE_CODE)REFERENCESIOC_ROLE(IOC_ROLE_CODE);

ALTERTABLEOFFICIALADDCONSTRAINTLANGUAGE_OFFICIAL_FKFOREIGNKEY(
LANG_ISO_CODE)REFERENCESLANGUAGE(LANG_ISO_CODE);

ALTERTABLELOCATIONADDCONSTRAINTLOCATION_TYPE_LOCATION_FKFOREIGNKEY(
LOCTYPE_ID)REFERENCESLOCATION_TYPE(LOCTYPE_ID);

ALTERTABLEMAINT_REPADDCONSTRAINTVEHICLE_MAINT_REP_FKFOREIGNKEY(
VEH_VIN
)REFERENCESVEHICLE(VEH_VIN);

ALTERTABLEVEHICLE_FEATUREADDCONSTRAINTVEHICLE_VEHICLE_FEATURE_FK
FOREIGN
KEY(VEH_VIN)REFERENCESVEHICLE(VEH_VIN);

FIT9132UnitTest

Page6of13

ALTERTABLEDRIVERADDCONSTRAINTDRIVER_CLEARANCE_LEVEL_CHKCHECK(
DRIVER_CLEARANCE_LEVELIN('1','2','3','4'));

ALTERTABLEDRIVERADDCONSTRAINTDRIVER_AVAILABILITY_DRIVER_FKFOREIGNKEY(
DRIVER_AVAIL_CODE)REFERENCESDRIVER_AVAILABILITY(DRIVER_AVAIL_CODE);

ALTERTABLEDRIVER_TRAININGADDCONSTRAINTcompleted_inFOREIGNKEY(
TRAINING_CODE)REFERENCESTRAINING(TRAINING_CODE);

ALTERTABLEDRIVER_TRAININGADDCONSTRAINTcompletesFOREIGNKEY(DRIVER_ID)
REFERENCESDRIVER(DRIVER_ID);

ALTERTABLEDRIVER_LANGUAGEADDCONSTRAINTLANGUAGE_DRIVER_LANGUAGE_FK
FOREIGN
KEY(LANG_ISO_CODE)REFERENCESLANGUAGE(LANG_ISO_CODE);

ALTERTABLEDRIVER_LANGUAGEADDCONSTRAINTDRIVER_DRIVER_LANGUAGE_FK
FOREIGN
KEY(DRIVER_ID)REFERENCESDRIVER(DRIVER_ID);

ALTERTABLETRIPADDCONSTRAINTLOCATION_TRIP_TOFOREIGNKEY(LOCATION_ID_TO)
REFERENCESLOCATION(LOCATION_ID);

ALTERTABLETRIPADDCONSTRAINTLOCATION_TRIP_fromFOREIGNKEY(
LOCATION_ID_FROM)REFERENCESLOCATION(LOCATION_ID);

ALTERTABLETRIPADDCONSTRAINTOFFICIAL_TRIP_FKFOREIGNKEY(OFF_OLYMPIC_ID)
REFERENCESOFFICIAL(OFF_OLYMPIC_ID);

ALTERTABLETRIPADDCONSTRAINTVEHICLE_TRIP_FKFOREIGNKEY(VEH_VIN)
REFERENCESVEHICLE(VEH_VIN);

ALTERTABLETRIPADDCONSTRAINTDRIVER_TRIP_FKFOREIGNKEY(DRIVER_ID)
REFERENCESDRIVER(DRIVER_ID);

FIT9132UnitTest

Page7of13

APPENDIXB
TableTRIP

FIT9132UnitTest

Page8of13

TableDRIVER

TableDRIVER_TRAININGandTableDRIVER_LANGUAGE

TableTRAINING

FIT9132UnitTest

Page9of13

TableLANGUAGE

TableOFFICIAL

TableIOC_ROLE

FIT9132UnitTest

Page10of13

TableCOUNTRY

FIT9132UnitTest

Page11of13

TableVEHICLE

TableVEHICLE_FEATURE

TableFEATURE

FIT9132UnitTest

Page12of13

TableLOCATION_TYPE

TableLOCATION

Note:Theoutputofthedatalistingcontainsonlysomeofthetablesfromthe
database.Thetableswereselectedbasedonwhatarerelevanttotheunit
test.

FIT9132UnitTest

Page13of13