Solutions manual for database systems design implementation and management 12th edition coronel

AppendixMCMTheMUniversityMLab:
ConceptualMDesignMVerification,MLogicalMDesign,MandMImplementati
on
Appendix C M




The University Lab: Conceptual Design, Verification, Lo
M M M M M M



gical Design, and Implementation
M M M




DiscussionMFocus


HowMisMaMdatabaseMdesignMverified,MandMwhyMisMsuchMverificationMnecessary?



UseMourMdetailedManswerMtoMquestionM1MtoMfocusMclassMdiscussionMonMdatabaseMdesignMverification.M
StressMthatMtheMverificationMprocessMusesMtheMinitialMERMmodelMasMaMcommunicationMtool.



TheMdesignerMmayMbeginMtheMverificationMprocessMbyMdescribingMtheMorganization'sMoperationsMtoMits
MendMusers,MbasingMtheMdetailedMdescriptionMonMtheMinitialMERMmodel.MNext,MexplainMhowMtheMopera

tionsMwillMbeMsupportedMbyMtheMdatabaseMdesign.MStressMthatMtheMdesignMmustMsupportMtheMend-
userMapplicationMviews,Moutputs,MandMinputs.MPointsMtoMbeMaddressedMincludeMsuchMquestionsMas:

 IsMtheMdescriptionMaccurate?MIfMnot,MwhatMaspectsMofMtheMdescriptionMmustMbeMcorrected?
 DoesM theM modelM supportM theM end-
userM requirements?M IfM not,M whatM aspectsM ofM theM end-
userMrequirementsMhaveMnotMbeenMaddressedMorMhaveMbeenMaddressedMinadequately?


KeepM inM mindM thatM evenM aM modelM thatM perfectlyM addressesM allM initiallyM determinedMendM userM requ
irementsMisMlikelyMtoMneedMadjustmentsMasMthoseMendMusersMbeginMtoMunderstandMtheMramificationsM
ofMtheMdatabaseMdesign'sMcapabilities.MInMmanyMcases,MtheMendMusersMmayMlearnMwhatMtheMorganizat
ion'sMprocessesMandMproceduresMactuallyMare,MthusMleadingMtoMnewMrequirementsMandMtheMperceptio
nMofMnewMopportunities.MTheMdatabaseMdesignerMmustMkeepMsuchMlikelyMdevelopmentsMinMmind,Mesp
eciallyMifM(s)heMworksMasMaMdatabaseMdesignMconsultant.M(AnticipationMofMsuchMdevelopmentsMmustM
beMfactoredMintoMtheMcontractMnegotiationsMforMconsultingMfees.)



DiscussMtheMroleMofMtheMsystemMmodules.


1

, AppendixMCMTheMUniversityMLab:
ConceptualMDesignMVerification,MLogicalMDesign,MandMImplementati
on
TheM useM ofM systemM modulesM canM hardlyM beM overemphasizedM inM aM databaseM designM environment
.M StressMtheseMmoduleMcharacteristicsMandMfeatures:

 ModulesMrepresentMsubsetsMofMtheMdatabaseMmodel:MSmallerM"pieces"MareMmoreMeasilyM
understood.
 ModulesMareMself-
containedMandMaccomplishMaMspecificMsystemMfunction;MifMsuchMaMsystemMfunctionMmus
tMbeMmodified,MotherMfunctionsMremainMunaffected.
 ModulesMfitMintoMaMmodularMdatabaseMdesign,MwhichMisMmoreMeasilyMmodifiedMandMada
ptedMtoMnewMcircumstances.MBecauseMmodificationMeffortsMareMfocusedMonMaMdatabaseMs
ubset,MproductivityMofMbothMdesignersMandMapplicationMdevelopersMisMlikelyMtoMbeMenha
nced.


ModuleMinterfacesMmustMbeMclearMifMtheMmodulesMareMexpectedMtoMworkMwellMwithinMtheMoverallMsystem.

AnswersMtoMReviewMQuestions


1. WhyMmustMaMconceptualMmodelMbeMverified?MWhatMstepsMareMinvolvedMinMtheMverificationMprocess?


TheMverificationMofMaMconceptualMmodelMisMcrucialMtoMaMsuccessfulMdatabaseMdesign.MTheMverifica
tionMprocessMallowsMtheMdesignerMtoMcheckMtheMaccuracyMofMtheMdatabaseMdesignMby:

 Re-examiningMdataMandMdataMtransformations.
 EnablingM theM designerM toM evaluateM theM designM efficiencyM relativeM toM theM endM user
'sM andMsystem'sMdesignMgoals.


KeepMinMmindMthat,MtoMaMlargeMextent,MtheMbestMdesignMisMtheMoneMthatMservesMtheM end-
userMrequirementsMbest.MForMexample,MaMdesignMthatMworksMwellMforMaMmanufacturingMfirmMmay
MnotMfitMtheMneedsMofMaMmarketingMresearchMfirm,MandMviceMversa.




TheMverificationMprocessMhelpsMtheMdesignerMtoMavoidMimplementationMproblemsMlaterMby:

 ValidatingMtheMmodel'sMentities.M(RememberMtheMminimalMdataMrule.)
 ConfirmingMentityMrelationshipsMandMeliminatingMduplicate,Munnecessary,MorMimprop
erlyMdefinedMrelationships.
 EliminatingMdataMredundancies.
 ImprovingMtheMmodel'sMsemanticMprecisionMtoMbetterMrepresentMreal-worldMoperations.
 ConfirmingMthatMallMuserMrequirementsM(processing,Mperformance,MorMsecurity)MareMmet.


VerificationMisMaMcontinuousMactivityMinManyMdatabaseMdesign.MTheMdatabaseMdesignMprocessMisMe
2

, AppendixMCMTheMUniversityMLab:
ConceptualMDesignMVerification,MLogicalMDesign,MandMImplementati
volutionaryMinMnature:MItMrequiresMtheMcontinuous
on MevaluationMofMtheMdevelopingMmodelMbyMexa
miningMtheMeffectMofMaddingMnewMentitiesMandMbyMconfirmingMthatManyMdesignMchangesMenhance
MtheMmodel'sMaccuracy.




TheMverificationMprocessMrequiresMtheMfollowingMsteps:



1. IdentifyMtheMdatabase'sMcentralMentity.


TheM centralM entityM isM theM mostM importantM entityM inM ourM database,M andM mostM ofM t
heM otherMentitiesMdependMonMit.



2. IdentifyMandMdefineMeachMmoduleMandMitsMcomponents.


TheM designerM dividesM theM databaseM modelM intoM smallerM setsM thatM reflectM theM dataM n
eedsM ofMparticularMsystemsMmodulesMsuchMasMinventory,Morders,Mpayroll,Metc.



3. IdentifyMandMdefineMeachMofMtheMmodule'sMprocesses.


Specifically,M thisM stepM requiresM theM identificationM andM definitionM ofM theM databaseM transactions
thatMrepresentMtheMmodule'sMreal-worldMoperations.

4. VerifyMeachMofMtheMtransactionsMagainstMtheMdatabase.


2. WhatMstepsMmustMbeMcompletedMbeforeMtheMdatabaseMdesignMisMfullyMimplemented?M(MakeMs
ureMthatMyouMlistMtheMstepsMinMtheMcorrectMsequenceMandMdiscussMeachMstepMbriefly.)


TheMDBLC,MdiscussedMinMdetailMinMChapterM9,M“DatabaseMDesign,”MconstitutesMaMdatabase'sMhistor
y,MtracingMitMfromMitsMconceptualMdesignMtoMitsMimplementationMandMoperation.MWeMhighlyMreco
mmendMthatMtheMdatabaseMdesignerMfollowMtheMDBLC'sMstepsMcarefullyMinMorderMtoMensureMthat
MtheMdatabaseMwillMproperlyMmeetMallMuserMandMsystemMrequirements.




BeforeMaMdatabaseMcanMbeMsuccessfullyMimplemented,MtheMfollowingMstepsMmustMbeMcompleted:

1. DefineMtheMconceptualMmodel'sMcomponents:Mentities,Mattributes,Mdomains,MandMrelationships.
2. NormalizeM theM databaseM toM ensureM thatM allM transitiveM dependenciesM areM eliminatedM a
ndM thatMeachMentity'sMattributesMareMsolelyMdependentMonMitsMkeyMattribute(s).
3

, AppendixMCMTheMUniversityMLab:
ConceptualMDesignMVerification,MLogicalMDesign,MandMImplementati
3. VerifyM theM conceptualM modelM toM ensure
on M thatM theM proposedM databaseM willM meetM theM s
ystem'sMtransactionMrequirementsMandMthatMtheMend-
userMandMsystemsMrequirementsMwillMbeMmet.


TheM verificationM processM willM probablyM deleteM and/orM createM entities,M attributes,M and
Mrelationships.MItMmayMalsoMrefineMexistingMentities,Mattributes,MandMrelationships.



4. CreateMtheMlogicalMdesignMwhichMrequiresMtheMdefinitionMofMtheMtableMstructures,MusingM
aMspecificMDBMSM(relational,MnetworkMorMhierarchical).MLogicalMdesignMalsoMincludes,MifMne
cessary,MappropriateMindexesMandMviews.
5. CreateMtheMphysicalMdesignMtoMdefineMaccessMpaths,MincludingMspaceMallocation,MstorageM
groupMcreation,M tableM spaces,M andM anyM otherM physicalM storageM characteristicM thatM isM
dependentM onMtheMhardwareMandMsoftwareMtoMbeMusedMinMtheMsystem'sMimplementation.
6. ImplementMtheMdesign.MMMSomehow,MthisMlastMstepMseemsMtoMsufferMfromMplanningMnegl
ect,MtoMtheMdetrimentMofMtheMsystem'sMoperation.MImplementation,Moperation,MandM mai
ntenanceMplansMmustM(atMleast)MincludeMcarefulMdefinitionMandMdescriptionMofMtheMactiviti
esMrequiredMtoMimplementMtheMdatabaseMdesign:
 loadingMandMconversion
 definitionMofMdatabaseMstandards
 systemMandMproceduresMdocumentation:Msecurity,Mbackup,MandMrecovery
 operationalMproceduresMtoMbeMfollowedMbyMusers
 aMdetailedMtrainingMplan
 identificationMofMresponsibilitiesMforMoperationMandMmaintenance.


3. WhatM majorM factorsM shouldM beM addressedMwhenM databaseM systemM performanceM isM evaluated
?MDiscussMeachMfactorMbriefly.
DatabaseMsystemsMperformanceMrefersMtoMtheMsystem'sMabilityMtoMretrieveMinformationMwithinMaM
reasonableMamountMofMtimeMandMatMaMreasonableMcost.MKeepingMinMmindMthatM"reasonable"Mme
ansMdifferentMthingsMtoMdifferentMpeople,MweMmustMaddressMatMleastMtheseMimportantMperforman
ceMfactors:

 ConcurrentMusers
ForM anyM givenM system,M theM moreM usersM connectedM toM theM system,M theM longerM th
eM dataMretrievalMtime.
 ResourceMlimits
TheMfewerMresourcesMthatMareMavailableMtoMtheMuser,MtheMlongerMtheMaccessMqueuesMwillMbe.

 CommunicationMspeeds
LowerMcommunicationMspeedsMmeanMlongerMresponseMtimes.

 QueryMresponseMtime
QueriesMmustMbeMtunedMtoMprovideMoptimumMqueryMresponseMtime.M(SeeMAppendixMC,M“
DatabaseMPerformanceMTuning.”)MLackMofMqueryMresponseMtuningMmeansMslowM response
Mtimes.MDependingMonMhowMgoodMtheMdesignMandMtheMprogramMcodeMare,MtheMqueryMre

sponseMtimeMcanMvaryMfromMminutesMtoMhoursMforMtheMsameMquery.

4