FACULTYOF

INFORMATION

TECHNOLOGY

FIT2094Databases-Assignment2

Creating,PopulatingandManipulatingDatabases-PetsFirst(PF)

PurposeStudentswillbeaskedtoimplement,viaSQL,asmalldatabaseintheOracle

RDBMSfromaprovidedlogicalmodelcasestudy,followedbytheinsertof

appropriatedatatothecreatedtables.Oncepopulatedthedatabasewillbeused

tocarryoutspecifiedDMLcommandsandmakespecifiedchangestothe

databasestructureviaSQL.StudentswillthenuseSQLandNoSQLtowrite

queriestoproducespecifiedoutput.Thistaskcoverslearningoutcomes:

1.Applythetheoriesoftherelationaldatabasemodel.

3.Implementarelationaldatabasebasedonasounddatabasedesign.

4.Managedatathatmeetsuserrequirements,includingqueriesand

transactions.

5.Contrastthedifferencesbetweennon-relationaldatabasemodelsandthe

relationaldatabasemodel.

YourtaskThisisanopenbook,individualtask.Thefinaloutputforthistaskwillbeaset

oftablesanddataimplementedintheOracleRDBMS.Inadditionstudentswill

createasetofrelational(Oracle)andnonrelational(MongoDB)querieswhich

meettheuserrequirements.

Value40%ofyourtotalmarksfortheunit

DueDateWednesday,5thJune2024,11:55PM

(note:staffsupportisunavailableafterbusinesshours)

Submission●ViaMoodleAssignmentSubmission

●FITGitLabcheckinswillbeusedtoassesshistoryofdevelopment

Assessment

Criteria

●Applicationofrelationaldatabaseprinciples.

●Handlingoftransactionsandthesettingofappropriatetransaction

boundaries.

●ApplicationofSQLstatementsandconstructstocreateandaltertables

includingtherequiredconstraintsandcolumncomments,populate

tables,modifyexistingdataintables,andmodifythe"live"database

structuretomeettheexpressedrequirements(includingappropriateuse

ofconstraints).

●ApplicationofSQLselectstatementstoproduceoutputsthatmeetuser

requirements.

●Mappingofrelationaldatabasedataintononrelationaldatabasedata

structure.

●ApplicationofMongoDBoperationstoproduceoutputsthatmeetuser

requirements.

Page1of22

FACULTYOF

INFORMATION

TECHNOLOGY

LatePenalties●10%deductionpercalendardayorpartthereofforuptooneweek

●Submissionsmorethan7calendardaysaftertheduedatewillreceivea

markofzero(0)andnoassessmentfeedbackwillbeprovided.

Support

Resources

SeeMoodleAssessmentpage

FeedbackFeedbackwillbeprovidedonstudentworkvia:

●generalcohortperformance

●specificstudentfeedbacktenworkingdayspostsubmission

●asamplesolution

Page2of22

FACULTYOF

INFORMATION

TECHNOLOGY

INSTRUCTIONS

PetsFirsthasseveralclinicsdistributedacrossthesuburbs.Foreachclinic,theyrecordtheclinic's

ID(whichisusedtoidentifytheclinic),theclinic'sname,itsaddress,andtheclinic'scontactphone

number.Thepracticehasseveralveterinarysurgeons(vets)whoworkintheseclinics.Avetis

assignedtooneclinicastheirhome(orbase)clinic.Aclinicmusthaveatleastonevetassignedto

itasthevet'sbaseclinictofunction.

ThedetailsPetsFirstrecordsaboutavetaretheirvetID(usedtoidentifyavet),thevet'sgiven

nameandfamilyname,thevet'scontactphonenumber,andthedatetheywerefirstemployedby

PetsFirst.

SomePetsFirstvetsarespecialistsinareassuchasoncology,cardiology,dermatology,etc.Ifthe

vetisaspecialist,theirareaofspecialisationisalsorecordedaspartoftheirvetdetails(specialist

vetsonlyspecialiseinoneparticulararea).Thesespecialistvets,aswellashavingahomeclinic

(theirbaseclinic)wheretheyacceptgeneralvisits,alsorovearoundallclinicswhentheirspecialist

skillsarerequiredtotreatananimal.

Eachclinicappointsoneofthevetsbasedintheclinic(i.e.thosevetsassignedtotheclinicastheir

homeclinic)astheheadvetforthatclinic.

Petowners,whoareeachassignedauniqueownerID,havetheirgivennameandfamilyname

recordedbythepractice.Thepracticealsorecordstheowner'scontactphonenumber.Each

ownermayhaveoneormorepets;foreachpet,thepracticerecordsauniqueanimalID,the

animal'sname,theyeartheanimalwasbornandthetypeofanimaltheyare,forexample,acat.

Eachanimalisonlyrecordedasbeingownedbyoneowner.

PFalsorecordsifananimalis

currentlyaliveornot.

Whenananimalneedsveterinaryattention(suchasannualinjections)orisill,theownerbooksa

visitwithavet.Duringthebooking,thedateandtimeofthevisit,thevisitlength,therequired

service(s),theclinic,andtheattendingvetarerecorded(thisattendingvetmaybeonebasedat

thisparticularclinic,i.e.,thisistheirhomeclinic,orthevisitmayneedtheservicesofaroving

specialistvet).

Onthescheduledvisitdate,theownerbringstheirpettotheclinictobeattendedtobythevet.

Duringthevisit,thepracticerecordsthepet'sweightforthisvisitandanynotesthatthevetneeds

tomakeaboutthepet'scondition.Ifrequired,thevetalsoupdatesthelengthofthevisitandthe

requiredservice(s)basedontheexamination.

Theownermayalsovisitoneofthepractice'sclinicswithoutmakinganybooking,e.g.,for

emergencytreatment.

Everyvisitmustinvolveatleastoneservicecharge;however,theremaybenodrugcharges.

Drugsandserviceshaveastandardcharge,whichmustberecordedinthedatabase.Aparticular

servicewillonlybechargedonceforavisit.Ifnecessary,thevetwilladjustthecostchargedto

coverany"extra"workunderthisserviceortogiveadiscount.Forexample,usingtheinvoice

below-thestandardservicecostforaskinallergytreatment(S009)is$85,buthereonthesample

invoice,thevethasgivena$5discountandchargedonly$80.

Page3of22

FACULTYOF

INFORMATION

TECHNOLOGY

Duringavisit,theattendingvetmayneedtoprescribedrugsfortheanimal.Thepracticeidentifies

adrugbyauniquedrugIDandrecordsthedrug'snameandusageinstructions(forexample,

"Analgesicforpost-surgerypainrelief;0.01mgperkg").Whenadrugisprescribedduringavisit,

theactualdrugdoseandfrequencyofadministrationarerecorded.Thelinecostlistedonan

invoiceisthetotalcostforitemslistedonthatline;forexample,intheinvoicebelow,the$13.5

chargeforcorticosteroidisthetotalchargeforthe3items.

Somevisitsrequirefurtherfollow-upvisits,thesystemneedstorecord,forthesesubsequentvisits,

whichvisitgeneratedthefollow-up.Agivenvisitmayrequiremultiplefollow-upvisitstoaddressan

issueidentifiedintheinitialdiagnosis.Forexample,thepetmayhaveaninfectedwoundfroma

fight.Thefirstvisit(e.g.visitid21)forthisissueresultsinantibioticsbeinggivenandthewound

beingstitched.Follow-upvisitsmayberequiredforexample,toremovethestitches(e.g.visitid

23),checkthewoundhealing(e.g.visitid25),providefurtherantibioticinjections(e.g.visitid27),

etc.Allofthesefollow-upvisitsarerelatedtotheoriginalvisit(visitid21).

Page4of22

FACULTYOF

INFORMATION

TECHNOLOGY

Basedontheserequirements,adatamodelhasbeencreatedforPF:

Theschema/insertfileforcreatingthismodel(pf_initialSchemaInsert.sql)isavailableinthearchive

ass2_student.zip.ThisfilepartiallycreatesthePetsFirsttablesandpopulatesseveralofthem

(thoseshowninpurpleonthesuppliedmodel).Youshouldreadthisschemacarefullyandbesure

youunderstandthevariousdatarequirements.

Page5of22

FACULTYOF

INFORMATION

TECHNOLOGY

IMPORTANTpointsforyoutoobservewhencompletingthisassignmentare:

1.Theass2-student.ziparchivealsocontainssevenscriptfilestocodeyouranswersin.You

MUSTensurethesefilesareregularlypushedtotheGitLabserversothataclear

developmenthistoryisavailableforthemarkertoverifyyourwork(aminimumof

fourteenpushesarerequired-2pushesperfile).Ineachfile,youmustfillintheheader

detailswithyournameandstudentIDbeforebeginningwork.YourSQLscriptfilesmustnot

includeanySPOOLorECHOcommands.Althoughyoumightincludesuchcommands

whentestingyourwork,theymustberemovedbeforesubmission(a-10markpenalty

willbeappliedifyourdocumentscontainspoolorechocommands).

2.Youarefreetomakeassumptionsifneeded.However,yourassumptionsmustalignwith

thedetailshereandintheEdAssignment2forumandmustbeclearlydocumented(see

therequiredsubmissionfiles).

REMEMBER,youmustkeepuptodatewiththeEdAssignment2forum,wherefurther

clarificationsmaybeposted(thisforumistobetreatedasyourclient).Pleaseensureyou

donotpostanythingthatincludesyourreasoning,logicoranypartofyourworkto

thisassignmentforum,asdoingsoviolatesMonashplagiarism/collusionrules.

3.Viewsmustnotbeusedinarrivingatanysolutionsforthetasksyoumustcompleteaspart

ofthisassessment.

4.WhenhandlingdateswithSQL,thedefaultdateformatmustnotbeassumed;youmustuse

theTO_DATEandTO_CHARfunctionswhereappropriate.

5.ANSIjoinsmustbeusedwherethejoiningoftablesisrequired.

6.Incompletingthefollowingtasks,youmustdesignyourtestdatasothatyoualwaysget

outputforthequeriesspecifiedbelow-thismayrequireyoutoaddfurtherdataasyou

movethroughcompletingtherequiredtasks.SuchextradataMUSTbeaddedaspartof

Task2(i.e.aspartofyourtestdataload).Queriesthatarecorrectbutdonotproduce

anyoutput(“norowsselected”message)usingyourtestdatawilllose50%ofthe

marksallocated.So,youshouldcarefullycheckyourtestdataandensureitthoroughly

validatesyourSQLqueries.

Page6of22

FACULTYOF

INFORMATION

TECHNOLOGY

StepsforworkingonAssignment2

1.DownloadtheAssignment2RequiredFilesziparchive(ass2-student.zip)fromMoodle

2.Extracttheziparchiveandplacethecontainedfilesinyourlocalrepositoryinthefolder

/Assignments/Ass2

Donotaddtheziparchivetoyourlocalrepo.

3.Examinetheextractedfilesi.e.readcarefullythroughthemandensureandbesureyou

understandtheircontent.

4.Ineachsuppliedscript,fillintheheaderdetailswithyournameandstudentID.Then,add,

commitandpushthemtotheFITGitLabserver.

5.Runpf_initialSchemaInsert.sqlfromthesuppliedziparchivetosetuptheinitialstateofthe

database

6.Writeyouranswerforeachtaskinitsrespectivefile(e.g.writeyouranswerfortask1in

T1-pf-schema.sqlandsoon).

7.Save,add,commitandpushthefile/sregularlywhileyouareworkingontheassignment

8.Finally,whenyouhavecompletedalltasks,separatelyruneachSQLorMongoasascript

(notasindividualstatements)andensuretherearenoerrors.Uploadallrequiredfilesfrom

yourlocalrepositorytoMoodle.Checkthatthefilesyouhaveuploadedarethecorrectfiles

(downloadthemfromMoodleintoatemporaryfolderandchecktheyarecorrect).Afteryou

aresuretheyarecorrect,submityourassignment.

************************************************************************************************************

Forallassignmenttasks,yourfinalscriptmustrunasascriptwithouterrorsexceptforSQL

errorsgeneratedbytheDROPTABLE/DROPSEQUENCEstatements.Anytask'sscriptthat

runswithanerrorwillreceiveamaximumgradeofhalfofthetask'smarks-1.Forexampleif

yourtask1scriptrunswithanerror,regardlessofthecontainedcode,yourmaximumgradewillbe

12/2=>6-1=5marks.Thiswillbeappliedeveniftheerrorissimplyaforgottensemicolon.Thus,

pleasecarefullycheckthatyourfinalscriptsforalltasksrunwithouterror.

Inarrivingatyoursolutionsforassignment2youareONLYpermittedtousetheSQL/NoSQL

structuresandsyntaxwhichhavebeencoveredwithinthisunit:

●Topic6WorkshopandApplied7-Creating&PopulatingtheDatabase

●Topic7WorkshopandApplied8-Insert,Update,Delete(DML)andTransaction

Management

●Topic8WorkshopandApplied9-SQLPartI-Basic

●Topic9WorkshopandApplied10-SQLPartII-Intermediate

●Topic10WorkshopandApplied11-SQLPartIII-Advanced

●Topic11WorkshopandApplied12-Non-RelationalDatabase

Asdetailedabove,SQL/NoSQLsyntaxandcommandsoutsideofthecoveredworkwillNOT

beaccepted/marked.

Viewsmustnotbeusedincompletingthesetasks.

Page7of22

FACULTYOF

INFORMATION

TECHNOLOGY

YoumustalsokeepuptodatewiththeEdAssignment2forumwherefurtherclarificationsmaybe

posted.Pleaseensureyoudonotpubliclypostanythingthatincludesyourreasoning,logic,

oranypartofyourworktothisforum;doingsoviolatesMonashplagiarism/collusionrulesand

hassignificantacademicpenalties.AttendaconsultationsessionoruseaprivateEdposttoraise

suchquestions.

************************************************************************************************************

GITSTORAGE

YourworkforthesetasksMUSTbesavedinyourindividuallocalworkingdirectory(repo)inthe

Assignment2folderandregularlypushedtotheFITGitLabservertobuildaclearhistoryof

thedevelopmentofyourapproach.AminimumoffourteenpushestotheFITGitLabserveris

required(2pushesperfile).Pleasenotethatfourteenpushesareaminimum;weexpect

significantlymoreinpractice.Allcommitsmustincludeameaningfulcommitmessagethat

clearlydescribeswhattheparticularcommitisaboutandmustbecorrectlyassignedtoyour

validGitLabauthor.

YoumustregularlycheckthatyourpusheshavebeensuccessfulbyloggingintotheFITGitLab

server'swebinterface;youmustnotsimplyassumetheyareworking.Beforesubmissionvia

Moodle,youmustlogintotheGitLabserver'swebinterfaceandensureyoursubmissionfilesare

presentandtheirnamesareunchanged.

************************************************************************************************************

Page8of22

FACULTYOF

INFORMATION

TECHNOLOGY

AssignmentTasks

TASK1:DDL[15mks]

ENSUREyourIDandnameareshownatthetopofanyfileyousubmit.

Forthistask,youmustaddtoT1-pf-schema.sqltheCREATETABLEandCONSTRAINT

definitions,whicharemissingfromthesuppliedpartialschemascript,inthepositionsindicatedby

thescript'scomments.

Thetablebelowdetailstheattributes'meaninginthemissingthreetables.YouMUSTuseexactly

thesamerelationandattributenamesshowninthedatamodelabovetonamethetablesand

attributesyouadd.Theattributesmustbeinthesameorderasshowninthemodel.Thesenew

DDLcommandsmustbehand-coded,notgeneratedinanymanner(generatedcodewillnotbe

marked).

TablenameAttributenameMeaning

VISIT

visit_idIdentifierforvisit(addedsurrogatekey)

visit_date_timeDateandtimeofvisit

visit_length

Visitlengthinminutes(mustbebetween30-90minutes,

inclusive)

visit_notesVetnotesfromvisit

visit_weightWeightinKgs

visit_total_costTotalcostforthisvisit

animal_idAnimalidentifier

vet_idIdentifierforthevet

clinic_idIdentifierfortheclinic

from_visit_idThepreviousvisit’sidentifier(forfollow-upvisitonly)

VISIT_DRUG

visit_idIdentifierforvisit(addedsurrogatekey)

drug_idDrugidentifier

visit_drug_doseDoseprescribedinthisvisit

visit_drug_frequencyFrequencyprescribedforthisdrugforthisvisit

visit_drug_qtysuppliedQuantityofdrugsupplied

visit_drug_linecostCostchargedfordruginthisvisit

VISIT_SERVICE

visit_idIdentifierforvisit(addedsurrogatekey)

service_codeServiceidentifier

visit_service_linecostCostchargedforthisserviceinthisvisit

Page9of22

FACULTYOF

INFORMATION

TECHNOLOGY

Totestyourcode,youmustfirstruntheprovidedscriptpf_initialSchemaInsert.sqltocreatethe

otherrequiredtables.pf_initialSchemaInsert.sqlcontainsthedropcommandsforalltablesinthis

modelattheheadofthefile.Ifyouhaveproblemswithtask1simplyrerun

pf_initialSchemaInsert.sql,whichwillcausealltablestobedroppedandcorrecttheissuesinyour

script.DONOTadddroptablestatementstoT1-pf-schema.sql

TASK2:INSERT[20mks]

BeforeproceedingwithTask2,youmustensureyouhaverunthefilepf_initialSchemaInsert.sql

(whichmustnotbeeditedinanyway)followedbytheextradefinitionsthatyouaddedinTask1

above(T1-pf-schema.sql).

LoadtheVISIT,VISIT_DRUGandVISIT_SERVICEtableswithyourowntestdatausingthe

suppliedT2-pf-insert.sqlscriptfile.WriteSQLcommandsthatwillinsertasaminimum(i.e.

youmayandshouldinsertmore)thefollowingsampledata:

(i)10VISITentries

●Includeatleast5differentanimals

●Includeatleast3differentvets

●Includeatleast3differentclinics

●Includeatleast2follow-upvisits

●Includeatleast8completedvisitsand2incompletevisits(i.e.futurevisit

bookings)

(ii)15VISIT_SERVICEentries

●Includeatleast4visitsthatrequiremorethanoneserviceinasinglevisit

(iii)10VIST_DRUGentries

●Includeatleast2visitswithmorethanonedrugprescribedinasinglevisit

Inaddingthisdata,youmustensurethatthetestdatathoroughlyteststhemodelas

supplied,toensureyourschemaiscorrect(youarenotrequiredtosubmitorcodefailtests,

allinsertstatementsmustexecutecorrectly).

Yourinserteddatamustconformtothefollowingrules:

1.Youmaytreatallthedatayouaddasasingletransactionsinceyouaresettinguptheinitial

teststateforthedatabase.

2.Theprimarykeyvaluesforthisdatashouldbehardcodedvalues(i.e.NOTmakeuseof

sequences)andmustconsistofvaluesbelow100.

3.Datesusedmustbechosenbetweenthe1stApril2024and30thJune2024.

4.Thedataaddedmustbesensible(e.g.,thefollow-upvisitdatetimeshouldbeafterthe

previousvisit’sdatetime,thedruglinecostmustbecorrectlycalculated,etc.).

ForthistaskONLY,Task2,youmaymanuallylookupandincludevaluesfortheloaded

tables/datadirectlywhererequired.However,youcanstilluseSQLtogetanynon-keyvalues

ifyouwish.

IncarryingoutTask2,youmustnotmodifyanydataoraddanyfurtherdatatothe

tablespopulatedbythepf_initialSchemaInsert.sqlscript.Designyourtestdatasothat

yougetoutputfortheSQLscripts/queriesspecifiedbelow-thismayrequireyouto

addfurtherdataasyoucompletetherequiredtasks.

Page10of22

FACULTYOF

INFORMATION

TECHNOLOGY

TASK3:DML[20mks]

YouranswersforthistaskmustbeplacedintheSQLfileT3-pf-dml.sql

ForthisandallsubsequentTasks,youareNOTpermittedto:

●manuallylookupavalueinthedatabase,obtainitsprimarykey,ormanuallyobtainthe

highest/lowestvalueinacolumn,

●manuallycalculatevaluesexternaltothedatabase,e.g.onacalculatorandthenusesuch

valuesinyouranswers.Anynecessarycalculationsmustbecarriedoutaspartof

yourSQLcodeor

●assumeanyparticularcontentsinthedatabase-rowsinatablearepotentiallyina

constantstateofchange

Youranswersmustrecognisethatyouaredealingwithonlyaverysmallsamplesnapshotofa

multiuserdatabase;assuch,youmustoperateonthebasisthattherewillbemoredatainallof

thedatabasetablesthanyoucurrentlyhaveaccessto.Thus,datawillbeinaconstantstate

ofchange.Youranswersmustworkregardlessoftheextraquantityofthisextra"real"data

andthefactthatmultipleuserswilloperateinthetablessimultaneously.Youmustconsider

thisaspectwhenwritingSQLstatements.

ForanyfollowingSQLtasks,yourSQLmustcorrectlymanagetransactionsanduse

sequencestogeneratenewprimarykeysfornumericprimarykeyvalues(underno

circumstancesmayanewprimarykeyvaluebehardcodedasanumberorvalue).

YoumustONLYusethedataasprovidedinthetextofthequestions.

ForTask3,youmustcompletethefollowingsub-tasksinthesameordertheyarelisted.Where

youhavebeensuppliedwithastringinitalics,suchasBaysideVeterinaryClinic,youmaysearch

inthedatabaseusingthestringaslisted.Whereaparticularcaseforawordisprovided,youmust

onlyusethatcase

(samespacing,case,etc)inyourSQLcode

.Whenanameissupplied,you

maybreakthenameintofirstnameandlastname.Forexample,OliviaSMITHcanbesplitinto

OliviaandSMITH;again,notethatthecasemustbemaintainedasitwassupplied.Failureto

adheretotheserequirements,suchaschangingthecaseofaprovidedstring,willresultin

agradepenalty.

(a)AnOraclesequencewillbeimplementedinthedatabasetoinsertrecordsintothe

databasefortheVISITtable.

ProvidetheCREATESEQUENCEstatementtocreateasequencethatcouldbeusedto

provideprimarykeyvaluesfortheVISITtables.Thissequencemuststartat100and

incrementby10.Immediatelybeforethecreatesequencecommands,placeanappropriate

DROPSEQUENCEcommandsothatthesequencewillbedroppedbeforebeingcreated,if

itexists.PleasenotethatthisistheONLYsequencethatcanbeintroducedandusedin

Task3.

[1mark]

Page11of22

FACULTYOF

INFORMATION

TECHNOLOGY

Questions3b,3c,3dand3earerelatedquestions.Youcanusetheinformationbelow

asneededinanypartofTask3.

(b)JackJONES,acurrentcustomer,calledPetsFirsttobookavisitforOreo,hisrabbitwhich

wasbornon01Jun2018.Thevisitisscheduledfor19May2024at2PMfor30minutes.

Heindicatedthatthevisitisforgeneralconsultation(servicecode:S001)withDr.Anna

KOWALSKIatBaysideVeterinaryClinic(clinicid:3).Youmayassumethatthereisonly

onecustomerwithsuchanameandJackonlyhasonerabbitbornonsuchadate.You

mayalsoassumethatthereisonlyonevetwithsuchanameinthesystem.Takethe

necessarystepsinthedatabasetorecordtherequiredentriesforthisvisitbooking.

[5marks]

(c)On19May2024,JackbroughtOreototheclinicasscheduled.Dr.AnnaKOWALSKI

examinedOreoandfoundanearinfection.Shedecidedtorecordanearinfection

treatmentserviceonlyandchargeJackthestandardcostforthistreatment.Shegave(and

charged)1bottleofClotrimazoletoJack.Youmayassumethatthereisonlyoneservice

namedearinfectiontreatmentandonedrugnamedClotrimazoleinthesystem.

Dr.KOWALSKIalsoscheduledafollow-upvisitforOreoat2PMsevendaysafterthisvisit

foranother earinfectiontreatmentatthesameclinic.

Maketheserequiredchangestothedatabasedata.Youmaymakeup(invent)anyother

requiredinformationwhenmakingthesechanges.

[10marks]

(d)On21May2024,JackcalledPFandcancelledOreo’s19Mayfollow-upvisitsincehehas

togooverseasforaworkemergency.

Maketheserequiredchangestothedatainthedatabase.

[4marks]

Page12of22

FACULTYOF

INFORMATION

TECHNOLOGY

TASK4:DATABASEMODIFICATIONS(13marks):

Youranswersforthesetasks(Task4)mustbeplacedinthesuppliedSQLscript

T4-pf-mods.sql

Therequiredchangesmustbemadetothe"live"database(thedatabaseafteryouhave

completedtasks1,2and3).YouMUSTnoteditandexecuteyourschemafileagain.Before

carryingouttheworkbelow,pleaseensurethatyouhavecompletedtasks1,2and3above.

If,inansweringthesequestions,youneedtocreateatable,pleaseplaceadroptable

statementimmediatelybeforeyourcreatetablestatement.

(a)PFwouldliketodeterminethetotalnumberoftimeseachservicewasprovidedinavisit

wherethechargemadewaslowerorhigherthanthestandardcost(i.e.,notchargedat

thestandardcost).Addanewattributethatwillrecordthisrequirement.Thedefault

valueforthisnewattributeis0.

Basedonthedatacurrentlystoredinthesystem,thisattributemustbeinitialisedtothe

numberoftimeseachservicewasprovidedinavisitandnotchargedatitsstandard

cost.

Aspartofyoursolution,provideappropriateselectanddescstatementstoshowthe

changesyouhavemade.Selecttoshowanydatachangesthathaveoccurred,anddesc

tablename,e.g.,desccustomer,toshowanytablestructuralchanges.

[5marks]

(a)PFwouldliketoallowthepet’sownertopayforthevisit'scostininstalments,i.e.the

pet’sownercanpayeachvisit'scostviamultiplepaymentsacrossseveraldatesand

times.PFalsowantstorecordthepaymentmethodforeachpayment.Theownercan

paythecost/partofthecostbyCardorCash.PFplanstoincludeotherpayment

methodsinthefuture.

Changethedatabasestructuretosupportthesenewbusinessrules.Allcurrently

completedvisitsinthesystemmustberecordedasbeingfullypaidontheirvisitdate.

ThepaymentmethodforthesevisitsmustberecordedasHistorical.

Aspartofyoursolutionprovideappropriateselectanddescstatementstoshowthe

changesyouhavemade.Selecttoshowanydatachangesthathaveoccurredanddesc

tablenamee.g.

desccustomer

toshowanytablestructuralchanges.

[8marks]

Page13of22

FACULTYOF

INFORMATION

TECHNOLOGY

TASK5:SQLQueries[15mks]

Youranswersforthistask(Task5)mustbeplacedinthesuppliedSQLscriptT5-pf-select.sql.

Youareonlypermittedtocodeasingleselectstatementforeachquestionbelow.Notethat

anSQLselectstatementbeginswiththeSELECTkeywordandendswithasemicolon(;)-

withinthisstatement,theSELECTkeywordcanbeusedmultipletimes.

Whereyouhavebeensuppliedwithastringinitalics,youmustsearchthedatabaseusingthe

providedstringexactlyassupplied.Whereyouneedtoshowafullname,youmustnothave

anyextraspaces(e.g.leadingspace,extraspaceinthemiddleofthename).

PleaserememberVIEWSand/orPL/SQL(includinganonymousblocksBEGIN...END)must

notbeused.

Sampleoutputshowingtheformofwhatyoumustproduceisprovided.Notethatthisisthe

formoftheoutputONLY,i.e.,theappearance;thedatayoureturnwillclearlybedifferent.

(a)PetsFirstwouldliketocreateareportshowingthenumberofcompletedappointments

involvingadogoracatforeachclinicbytheservicingvet.Theoutputmustshow,fora

singlelineofoutput,theclinicID,thevetID,andthevetname.Ifthevetistheheadof

theclinic,"HEADVET"mustbeshown.Thenumberofdogand/orcatcompleted

appointmentsmustbeshownforthisvetandthepercentageofthisclinic'stotaldogand

catappointments.

Thereportmustbeinclinicorder.Withinaclinic,theheadvetmustappearfirstifthey

havetreatedanydogsorcats.Theremainingvetswhohaveworkedintheclinicmust

beinvetID.Foragivenvet,theiroutputmustbeinanimaltypeorder.

Sampleoutputisshownbelow(youroutputmusthavethesameformat):

[7marks]

Page14of22

FACULTYOF

INFORMATION

TECHNOLOGY

(b)PetsFirstwishestoanalysevisitservicechargesmadebyavetduringavisit.They

wouldliketodetermine,foraparticularservicecode,whichvetschargedmorethanthe

averagechargedforthatservicecodeacrossallvisits.

Forserviceswherethevetchargedmorethantheaveragecharge,theoutputshould

showtheservicecode,servicedescription,thevetID,thevetgivenandfamilynames,

theclinicIDandtheclinicname,thevisitID,thevisitdateandtimeandtheactual

chargethevetmade

[8marks]

Page15of22

FACULTYOF

INFORMATION

TECHNOLOGY

TASK6:MongoDB[12mks]

Youranswersforthistask(Task6)mustbeplacedinthesuppliedsqlfileT6-pf-json.sqland

thesuppliedMongoDBscriptfileT6-pf-mongo.mongodb.js

YoumustnotaddanyfurthercommentstothesuppliedMongoDBscriptfile,nor

remove/renameanycommentsindicatedby//

(a)WriteanSQLstatementinT6-pf-json.sqltogenerateacollectionofJSONdocuments

usingthefollowingstructure/format.Eachdocumentinthecollectionrepresentsaclinic,

theirheadvetdetailsandalistofallvetswhoarebasedonthisclinic.Notethatthe_id

inthisstructureistheclinic_id,theno_of_vetsisthenumberofvetswhoarebasedon

thisclinic.Ifavetdoesnothaveaspecialisation,thenitislistedas“N/A”.

{

"_id":5,

"name":"BrightonEastPetCare",

"address":"123ThomasSt,BrightonEastVIC3187",

"phone":"0398765412",

"head_vet":{

"id":1007,

"name":"JessicaLee"

},

"no_of_vets":3,

"vets":[

{

"id":1007,

"name":"JessicaLee",

"specialisation":"BehavioralMedicine"

},

{

"id":1010,

"name":"MichaelClarkson",

"specialisation":"Cardiology"

},

{

"id":1009,

"name":"SarahMorris",

"specialisation":"N/A"

}

]

}

[7marks]

Page16of22

FACULTYOF

INFORMATION

TECHNOLOGY

WritetheMongoDBcommandsforthefollowingquestions,6(b)-6(d),inthesuppliedMongoDB

scriptfilenamedT6-pf-mongo.mongodb.js.

(b)Createanewcollectionandinsertalldocumentsgeneratedin6(a)aboveinto

MongoDB.Provideadropcollectionstatementrightabovethecreatecollection

statement.Youmaypickanycollectionname.Afterthedocumentshavebeeninserted,

useanappropriatedb.findcommandtolistallthedocumentsyouadded.

[1mark]

(c)Listthenameandaddressofallclinicswhichhaveatleastonevetwhoisspecialisingin

DermatologyorCardiology.

[2marks]

(d)AnewvetwhoisspecialisedinDentistrynamedSarahWilkinson(vetid1020)joins

PetsFirst.Dr.Wilkinson'shomebaseclinicistheBrightonEastPetCare(“_id”:5).Due

toherextensiveexperience,sheisappointedasthecurrentheadvetofherhomebase

clinic.

Useanappropriatedb.findcommandbeforemakingthechangesothatyouillustrate

whichdocument/swillbechanged.

WritethenecessaryMongoDBcommandstoaddthisnewvetintothecollection.

Useanappropriatedb.findcommandaftermakingthechangesothatyou

illustrate/confirmthechangewhichwasmade.

[2marks]

Page17of22

FACULTYOF

INFORMATION

TECHNOLOGY

SubmissionRequirements

DueDate:Wednesday,5thJune2024at11:55PM

Pleasenote,ifyouneedtoresubmit,youcannotdependonyourstaffs'availability,forthis

reason,pleasebeVERYCAREFULwithyoursubmission.Itisstronglyrecommendedthat

yousubmitseveralhoursbeforethistimetoavoidsuchissues.

Forthisassignment,therearesevenfilesyouarerequiredtosubmittoMoodle:

●T1-pf-schema.sql

●T2-pf-insert.sql

●T3-pf-dml.sql

●T4-pf-mods.sql

●T5-pf-select.sql

●T6-pf-json.sql

●T6-pf-mongo.mongodb.js

Ifyouneedtomakeanycommentstoyourmarker/tutorpleaseplacethemattheheadofeachof

yoursolutionscripts/answersinthe"Commentsforyourmarker:"section.

Donotzipthesefilesintooneziparchive,submitsevenindependentSQLscripts

.Theindividualfiles

mustalsohavebeenpushedtotheFITGitLabserverwithanappropriatehistoryasyoudeveloped

yoursolutions.

Latesubmissionwillincurpenaltiesattherateof-10marksforevery24hoursthe

submissionislate.

ThesevenfilesmustalsoexistinyourFITGitLabserverrepoandshowaclearhistoryof

development(aminimumoftwopushesperfile).

PleasenotewecannotmarkanyworkontheGitLabServer,youneedtoensurethatyou

submitcorrectlyviaMoodlesinceitisonlyinthisprocessthatyoucompletetherequiredstudent

declarationwithoutwhichworkcannotbeassessed.

ItisyourresponsibilitytoENSUREthatthefilesyousubmitarethecorrectfiles-we

stronglyrecommendafteruploadingasubmission,andpriortoactuallysubmitting,thatyou

downloadthesubmissionanddouble-checkitscontents.

YourassignmentMUSTshowastatusof"Submittedforgrading"beforeitwillbemarked.

Page18of22

FACULTYOF

INFORMATION

TECHNOLOGY

Ifyoursubmissionshowsastatusof"Draft(notsubmitted)"itwillnotbeassessedandwillincur

latepenaltiesaftertheduedate/time.

Pleasecarefullyreadthedocumentationunderthe"AssignmentSubmission"ontheMoodle

Assessmentspagewhichcoversthingssuchasextensionsandresubmission.

Page19of22

FACULTYOF

INFORMATION

TECHNOLOGY

MarkingGuide

Submittedcodewillbeassessedagainstanoptimalsolutionforthesetasks.Insometaskswhere

SQLisinvolvedthereareoftenseveralalternativeapproachespossible,suchalternativeswillbe

gradedbasedonthecodesuccessfullymeetingthebriefsrequirements.Ifitdoes,theanswerwill

beacceptedandgradedappropriately.

MarkingCriteriaItemsAssessed

TASK1DDL15marks

DDLCreationoftablesMaximum7marks-Createtable:

●MarksawardedforcorrecttableDDL

●Marksawardedforcorrectattributes/datatypes

●MarksawardedforcorrectPKdefinition

●Markpenaltyappliedifdifferenttable/attributenamesused

thanexpressedinthesupplieddatamodel

●Markpenaltyappliedifdifferentorderofattributesusedthan

expressedinthesupplieddatamodel

●Nomarksawardedifgeneratedschemaused

DDLimplementationof

non-PKdatabase

constraints

Maximum8marks-Non-PKConstraints:

●Marksawardedforcorrectimplementationofnon-PK

constraints

●Marksawardedforcorrectuseofcolumncomments

TASK2DataInsert20marks

Insertofrequireditems

testdata

Maximum10marks-Insertofdata:

●Marksawardedforcorrectinsertofrequireddata

●Marksawardedforcorrectmanagementoftransactions

InsertofvalidtestdataMaximum10marks-Validdatainserted:

●Marksawardedforvalidityofdatainserted

○meetstherequirementsexpressedinthe

assignmentbrief

●Marksawardedforcorrectmanagementofdateswhen

inserting

Task3DML20marks

Maximum20marks-Satisfybriefrequirements:

●Marksawarded(a)-(d)forSQLcodewhichmeetsthe

expressedrequirement

●Markpenaltyappliedifcommitnotusedappropriately

●Markpenaltyappliedifdatehandlingandstringdatabase

lookupsnotmanagedcorrectly

Page20of22

FACULTYOF

INFORMATION

TECHNOLOGY

Task4DatabaseModifications13marks

Maximum13marks-Satisfybriefrequirements:

●Marksawarded(a)-(b)forSQLcodewhichmeetsthe

expressedrequirement(includingappropriateuseof

constraints).Inmakingthesemodificationstheremustbeno

lossofexistingdataordataintegritywithinthedatabase.

●Markpenaltyappliedifcommitnotusedappropriately

●Markpenaltyappliedifcolumncommentsnotusedwhere

required

Task5SQLQueries15marks

Maximum15marks-Satisfybriefrequirements:

●Marksawarded,aslisted,(a)-(b)forSQLcodewhich

meetstheexpressedrequirement

●Markpenaltyappliedifoutputdoesnotmatchtheform

suppliedforeachquestion

●Markpenaltyappliedifdatehandlingandstringdatabase

lookupsarenotmanagedcorrectly

●Markpenaltyappliedifcolumnaliasesarenotusedwhen

arithmeticcalculation,concatenation,functions,orother

outputmanipulationisusedinaquery

●Markpenaltyappliedifmanuallookup/calculationisused

forvaluesfromthedatabase

●StatementswhichdonotexecutecorrectlyinOraclewillbe

awardedamaximumof50%oftheavailablemarksless1

mark.Forexample,ifaquestionisworth6marksandruns

withanerrorinSQLthemaximummarkawardedwillbe2

marks

Task6NonRelationalDatabaseQueries-MongoDB12marks

Maximum12marks-Satisfybriefrequirements:

●Maximumof7marksawardedforcreationofaJSON

documentwhichmatchesthesupplieddocumentformat

●Marksawarded,aslisted,(b)-(d)forMongoDBcodewhich

meetstheexpressedrequirement

●Markpenaltyappliediffieldnamesandpredicates(suchas

"&eq")arenotenclosedindoublequotes

●StatementswhichdonotexecutecorrectlyinMongoDBwill

beawardedamaximumof50%oftheavailablemarksless

1mark.Forexample,ifaquestionisworth6marksand

runswithanerrorinMongoDBthemaximummarkawarded

willbe2marks

Page21of22

FACULTYOF

INFORMATION

TECHNOLOGY

CorrectuseofGit5marks

●Marksawardedforaminimumoffourteenpushes(twoper

file)showingacleardevelopmenthistoryoftheworkfor

Assignment2

●MarksawardedforcorrectGitauthordetailsusedinpushes

●Marksawardedfortheuseofmeaningfulcommitmessages

(i.e.notblankoroftheform"Push1")

Penalties

Useof

●VIEWs

●SETECHOor

SPOOL

commands,

and/or

●PL/SQL

UseofVIEWS,inclusionofSETECHO/SPOOL,and/orPL/SQL

commandswillresultinagradedeductionof10marksbeing

applied.

Latesubmission-10marksforeach24hourslateorpartthereof

FinalAssignmentMarkCalculation

Total:100marks,recordedasagradeoutof40

Page22of22