AppendixkCkThekUniversitykLab:
ConceptualkDesignkVerification,kLogicalkDesign,kandkImplementation
Appendix C k
The University Lab: Conceptual Design, Verification,Logi
k k k k k k
cal Design, and Implementation
k k k
Discussion Focus
k
Howkiskakdatabasekdesignkverified,kandkwhykisksuchkverificationknecessary?
Usekourkdetailedkanswerktokquestionk1ktokfocuskclasskdiscussionkonkdatabasekdesignkverification.kStressktha
tkthekverificationkprocesskuseskthekinitialkERkmodelkaskakcommunicationktool.
Thekdesignerkmaykbeginkthekverificationkprocesskbykdescribingkthekorganization'skoperationsktokitskendkuse
rs,kbasingkthekdetailedkdescriptionkonkthekinitialkERkmodel.kNext,kexplainkhowkthekoperationskwillkbeksuppo
rtedkbykthekdatabasekdesign.kStresskthatkthekdesignkmustksupportkthekend-
userkapplicationkviews,koutputs,kandkinputs.kPointsktokbekaddressedkincludeksuchkquestionskas:
Iskthekdescriptionkaccurate?kIfknot,kwhatkaspectskofkthekdescriptionkmustkbekcorrected?
Doesk thek modelk supportk thek end-userk requirements?k Ifk not,k whatk aspectsk ofk thek end-
userkrequirementskhaveknotkbeenkaddressedkorkhavekbeenkaddressedkinadequately?
Keepk ink mindk thatk evenk ak modelk thatk perfectlyk addressesk allk initiallyk determinedk endk userk requirementski
sklikelyktokneedkadjustmentskaskthosekendkuserskbeginktokunderstandkthekramificationskofkthekdatabasekdes
ign'skcapabilities.kInkmanykcases,kthekendkuserskmayklearnkwhatkthekorganization'skprocesseskandkprocedur
eskactuallykare,kthuskleadingktoknewkrequirementskandkthekperceptionkofknewkopportunities.kThekdatabasek
designerkmustkkeepksuchklikelykdevelopmentskinkmind,kespeciallykifk(s)hekworkskaskakdatabasekdesignkcons
ultant.k(Anticipationkofksuchkdevelopmentskmustkbekfactoredkintokthekcontractknegotiationskforkconsulting
kfees.)
Discusskthekrolekofktheksystemkmodules.
1
, AppendixkCkThekUniversitykLab:
ConceptualkDesignkVerification,kLogicalkDesign,kandkImplementation
Thek usek ofk systemk modulesk cank hardlyk bek overemphasizedk ink ak databasek designk environment.k Stresskt
hesekmodulekcharacteristicskandkfeatures:
Moduleskrepresentksubsetskofkthekdatabasekmodel:kSmallerk"pieces"karekmorekeasilykunderst
ood.
Moduleskarekself-
containedkandkaccomplishkakspecificksystemkfunction;kifksuchkaksystemkfunctionkmustkbekmo
dified,kotherkfunctionskremainkunaffected.
Moduleskfitkintokakmodularkdatabasekdesign,kwhichkiskmorekeasilykmodifiedkandkadaptedktokn
ewkcircumstances.kBecausekmodificationkeffortskarekfocusedkonkakdatabaseksubset,kproducti
vitykofkbothkdesignerskandkapplicationkdeveloperskisklikelyktokbekenhanced.
Modulekinterfaceskmustkbekclearkifkthekmoduleskarekexpectedktokworkkwellkwithinkthekoverallksystem.
Answers to Review Questions
k k k
1. Whykmustkakconceptualkmodelkbekverified?kWhatkstepskarekinvolvedkinkthekverificationkprocess?
Thekverificationkofkakconceptualkmodelkiskcrucialktokaksuccessfulkdatabasekdesign.kThekverificationkproc
esskallowskthekdesignerktokcheckkthekaccuracykofkthekdatabasekdesignkby:
Re-examiningkdatakandkdataktransformations.
Enablingk thek designerk tok evaluatek thek designk efficiencyk relativek tok thek endk user'sk andks
ystem'skdesignkgoals.
Keepkinkmindkthat,ktokaklargekextent,kthekbestkdesignkiskthekonekthatkserveskthek end-
userkrequirementskbest.kForkexample,kakdesignkthatkworkskwellkforkakmanufacturingkfirmkmayknotkfitkth
ekneedskofkakmarketingkresearchkfirm,kandkvicekversa.
Thekverificationkprocesskhelpskthekdesignerktokavoidkimplementationkproblemsklaterkby:
Validatingkthekmodel'skentities.k(Rememberkthekminimalkdatakrule.)
Confirmingkentitykrelationshipskandkeliminatingkduplicate,kunnecessary,korkimproperlyk
definedkrelationships.
Eliminatingkdatakredundancies.
Improvingkthekmodel'sksemantickprecisionktokbetterkrepresentkreal-worldkoperations.
Confirmingkthatkallkuserkrequirementsk(processing,kperformance,korksecurity)karekmet.
Verificationkiskakcontinuouskactivitykinkanykdatabasekdesign.kThekdatabasekdesignkprocesskiskevolution
arykinknature:kItkrequireskthekcontinuouskevaluationkofkthekdevelopingkmodelkbykexaminingkthekeffectk
ofkaddingknewkentitieskandkbykconfirmingkthatkanykdesignkchangeskenhancekthekmodel'skaccuracy.
2
, AppendixkCkThekUniversitykLab:
ConceptualkDesignkVerification,kLogicalkDesign,kandkImplementation
Thekverificationkprocesskrequireskthekfollowingksteps:
1. Identifykthekdatabase'skcentralkentity.
Thek centralk entityk isk thek mostk importantk entityk ink ourk database,k andk mostk ofk thek otherke
ntitieskdependkonkit.
2. Identifykandkdefinekeachkmodulekandkitskcomponents.
Thek designerk dividesk thek databasek modelk intok smallerk setsk thatk reflectk thek datak needsk ofk
particularksystemskmodulesksuchkaskinventory,korders,kpayroll,ketc.
3. Identifykandkdefinekeachkofkthekmodule'skprocesses.
Specifically,k thisk stepk requiresk thek identificationk andk definitionk ofk thek databasek transactions
thatkrepresentkthekmodule'skreal-worldkoperations.
4. Verifykeachkofkthektransactionskagainstkthekdatabase.
2. Whatkstepskmustkbekcompletedkbeforekthekdatabasek designkiskfullykimplemented?k(Makeksurekthatk
youklistkthekstepskinkthekcorrectksequencekandkdiscusskeachkstepkbriefly.)
ThekDBLC,kdiscussedkinkdetailkinkChapterk9,k“DatabasekDesign,”kconstituteskakdatabase'skhistory,ktracin
gkitkfromkitskconceptualkdesignktokitskimplementationkandkoperation.kWekhighlykrecommendkthatkthekd
atabasekdesignerkfollowkthekDBLC'skstepskcarefullykinkorderktokensurekthatkthekdatabasekwillkproperlyk
meetkallkuserkandksystemkrequirements.
Beforekakdatabasekcankbeksuccessfullykimplemented,kthekfollowingkstepskmustkbekcompleted:
1. Definekthekconceptualkmodel'skcomponents:kentities,kattributes,kdomains,kandkrelationships.
2. Normalizek thek databasek tok ensurek thatk allk transitivek dependenciesk arek eliminatedk andk thatk
eachkentity'skattributeskareksolelykdependentkonkitskkeykattribute(s).
3. Verifyk thek conceptualk modelk tok ensurek thatk thek proposedk databasek willk meetk thek system'skt
ransactionkrequirementskandkthatkthekend-userkandksystemskrequirementskwillkbekmet.
3
, AppendixkCkThekUniversitykLab:
ConceptualkDesignkVerification,kLogicalkDesign,kandkImplementation
Thek verificationk processk willk probablyk deletek and/ork createk entities,k attributes,k andkrelati
onships.kItkmaykalsokrefinekexistingkentities,kattributes,kandkrelationships.
4. Createktheklogicalkdesignkwhichkrequireskthekdefinitionkofkthektablekstructures,kusingk akspecific
kDBMSk(relational,knetworkkorkhierarchical).kLogicalkdesignkalsokincludes,kifknecessary,kapprop
riatekindexeskandkviews.
5. Createkthekphysicalkdesignktokdefinekaccesskpaths,kincludingkspacekallocation,kstoragekgroupkcr
eation,k tablek spaces,k andk anyk otherk physicalk storagek characteristick thatk isk dependentk onkth
ekhardwarekandksoftwarektokbekusedkinktheksystem'skimplementation.
6. Implementkthekdesign.kkkSomehow,kthisklastkstepkseemsktoksufferkfromkplanningkneglect,ktokth
ekdetrimentkofktheksystem'skoperation.kImplementation,koperation,kandk maintenancekplansk
mustk(atkleast)kincludekcarefulkdefinitionkandkdescriptionkofkthekactivitieskrequiredktokimpleme
ntkthekdatabasekdesign:
loadingkandkconversion
definitionkofkdatabasekstandards
systemkandkprocedureskdocumentation:ksecurity,kbackup,kandkrecovery
operationalkproceduresktokbekfollowedkbykusers
akdetailedktrainingkplan
identificationkofkresponsibilitieskforkoperationkandkmaintenance.
3. Whatk majork factorsk shouldk bek addressedk whenk databasek systemk performancek isk evaluated?kDiscus
skeachkfactorkbriefly.
Databaseksystemskperformancekrefersktoktheksystem'skabilityktokretrievekinformationkwithinkakreasona
blekamountkofktimekandkatkakreasonablekcost.kKeepingkinkmindkthatk"reasonable"kmeanskdifferentkthin
gsktokdifferentkpeople,kwekmustkaddresskatkleastkthesekimportantkperformancekfactors:
Concurrentkusers
Fork anyk givenk system,k thek morek usersk connectedk tok thek system,k thek longerk thek datakr
etrievalktime.
Resourceklimits
Thekfewerkresourceskthatkarekavailablektokthekuser,ktheklongerkthekaccesskqueueskwillkbe.
Communicationkspeeds
Lowerkcommunicationkspeedskmeanklongerkresponsektimes.
Querykresponsektime
Querieskmustkbektunedktokprovidekoptimumkquerykresponsektime.k(SeekAppendixkC,k“Databas
ekPerformancekTuning.”)kLackkofkquerykresponsektuningkmeanskslowk responsektimes.kDepend
ingkonkhowkgoodkthekdesignkandkthekprogramkcodekare,kthekquerykresponsektimekcankvarykfro
mkminutesktokhourskforktheksamekquery.
Althoughkthekprecedingkdiscussionkiskfocusedkonkthekspeedkaspectkofkperformance,ktherekarekotherkeq
uallykimportantkissueskthatkmustkbekconsidered.kAksuccessfulkdatabasekimplementationkrequireskakbal
ancedkapproachktokallkdatabasekissues,kincludingkconcurrencykcontrol,kquerykresponsektime,kdatabasek
4
ConceptualkDesignkVerification,kLogicalkDesign,kandkImplementation
Appendix C k
The University Lab: Conceptual Design, Verification,Logi
k k k k k k
cal Design, and Implementation
k k k
Discussion Focus
k
Howkiskakdatabasekdesignkverified,kandkwhykisksuchkverificationknecessary?
Usekourkdetailedkanswerktokquestionk1ktokfocuskclasskdiscussionkonkdatabasekdesignkverification.kStressktha
tkthekverificationkprocesskuseskthekinitialkERkmodelkaskakcommunicationktool.
Thekdesignerkmaykbeginkthekverificationkprocesskbykdescribingkthekorganization'skoperationsktokitskendkuse
rs,kbasingkthekdetailedkdescriptionkonkthekinitialkERkmodel.kNext,kexplainkhowkthekoperationskwillkbeksuppo
rtedkbykthekdatabasekdesign.kStresskthatkthekdesignkmustksupportkthekend-
userkapplicationkviews,koutputs,kandkinputs.kPointsktokbekaddressedkincludeksuchkquestionskas:
Iskthekdescriptionkaccurate?kIfknot,kwhatkaspectskofkthekdescriptionkmustkbekcorrected?
Doesk thek modelk supportk thek end-userk requirements?k Ifk not,k whatk aspectsk ofk thek end-
userkrequirementskhaveknotkbeenkaddressedkorkhavekbeenkaddressedkinadequately?
Keepk ink mindk thatk evenk ak modelk thatk perfectlyk addressesk allk initiallyk determinedk endk userk requirementski
sklikelyktokneedkadjustmentskaskthosekendkuserskbeginktokunderstandkthekramificationskofkthekdatabasekdes
ign'skcapabilities.kInkmanykcases,kthekendkuserskmayklearnkwhatkthekorganization'skprocesseskandkprocedur
eskactuallykare,kthuskleadingktoknewkrequirementskandkthekperceptionkofknewkopportunities.kThekdatabasek
designerkmustkkeepksuchklikelykdevelopmentskinkmind,kespeciallykifk(s)hekworkskaskakdatabasekdesignkcons
ultant.k(Anticipationkofksuchkdevelopmentskmustkbekfactoredkintokthekcontractknegotiationskforkconsulting
kfees.)
Discusskthekrolekofktheksystemkmodules.
1
, AppendixkCkThekUniversitykLab:
ConceptualkDesignkVerification,kLogicalkDesign,kandkImplementation
Thek usek ofk systemk modulesk cank hardlyk bek overemphasizedk ink ak databasek designk environment.k Stresskt
hesekmodulekcharacteristicskandkfeatures:
Moduleskrepresentksubsetskofkthekdatabasekmodel:kSmallerk"pieces"karekmorekeasilykunderst
ood.
Moduleskarekself-
containedkandkaccomplishkakspecificksystemkfunction;kifksuchkaksystemkfunctionkmustkbekmo
dified,kotherkfunctionskremainkunaffected.
Moduleskfitkintokakmodularkdatabasekdesign,kwhichkiskmorekeasilykmodifiedkandkadaptedktokn
ewkcircumstances.kBecausekmodificationkeffortskarekfocusedkonkakdatabaseksubset,kproducti
vitykofkbothkdesignerskandkapplicationkdeveloperskisklikelyktokbekenhanced.
Modulekinterfaceskmustkbekclearkifkthekmoduleskarekexpectedktokworkkwellkwithinkthekoverallksystem.
Answers to Review Questions
k k k
1. Whykmustkakconceptualkmodelkbekverified?kWhatkstepskarekinvolvedkinkthekverificationkprocess?
Thekverificationkofkakconceptualkmodelkiskcrucialktokaksuccessfulkdatabasekdesign.kThekverificationkproc
esskallowskthekdesignerktokcheckkthekaccuracykofkthekdatabasekdesignkby:
Re-examiningkdatakandkdataktransformations.
Enablingk thek designerk tok evaluatek thek designk efficiencyk relativek tok thek endk user'sk andks
ystem'skdesignkgoals.
Keepkinkmindkthat,ktokaklargekextent,kthekbestkdesignkiskthekonekthatkserveskthek end-
userkrequirementskbest.kForkexample,kakdesignkthatkworkskwellkforkakmanufacturingkfirmkmayknotkfitkth
ekneedskofkakmarketingkresearchkfirm,kandkvicekversa.
Thekverificationkprocesskhelpskthekdesignerktokavoidkimplementationkproblemsklaterkby:
Validatingkthekmodel'skentities.k(Rememberkthekminimalkdatakrule.)
Confirmingkentitykrelationshipskandkeliminatingkduplicate,kunnecessary,korkimproperlyk
definedkrelationships.
Eliminatingkdatakredundancies.
Improvingkthekmodel'sksemantickprecisionktokbetterkrepresentkreal-worldkoperations.
Confirmingkthatkallkuserkrequirementsk(processing,kperformance,korksecurity)karekmet.
Verificationkiskakcontinuouskactivitykinkanykdatabasekdesign.kThekdatabasekdesignkprocesskiskevolution
arykinknature:kItkrequireskthekcontinuouskevaluationkofkthekdevelopingkmodelkbykexaminingkthekeffectk
ofkaddingknewkentitieskandkbykconfirmingkthatkanykdesignkchangeskenhancekthekmodel'skaccuracy.
2
, AppendixkCkThekUniversitykLab:
ConceptualkDesignkVerification,kLogicalkDesign,kandkImplementation
Thekverificationkprocesskrequireskthekfollowingksteps:
1. Identifykthekdatabase'skcentralkentity.
Thek centralk entityk isk thek mostk importantk entityk ink ourk database,k andk mostk ofk thek otherke
ntitieskdependkonkit.
2. Identifykandkdefinekeachkmodulekandkitskcomponents.
Thek designerk dividesk thek databasek modelk intok smallerk setsk thatk reflectk thek datak needsk ofk
particularksystemskmodulesksuchkaskinventory,korders,kpayroll,ketc.
3. Identifykandkdefinekeachkofkthekmodule'skprocesses.
Specifically,k thisk stepk requiresk thek identificationk andk definitionk ofk thek databasek transactions
thatkrepresentkthekmodule'skreal-worldkoperations.
4. Verifykeachkofkthektransactionskagainstkthekdatabase.
2. Whatkstepskmustkbekcompletedkbeforekthekdatabasek designkiskfullykimplemented?k(Makeksurekthatk
youklistkthekstepskinkthekcorrectksequencekandkdiscusskeachkstepkbriefly.)
ThekDBLC,kdiscussedkinkdetailkinkChapterk9,k“DatabasekDesign,”kconstituteskakdatabase'skhistory,ktracin
gkitkfromkitskconceptualkdesignktokitskimplementationkandkoperation.kWekhighlykrecommendkthatkthekd
atabasekdesignerkfollowkthekDBLC'skstepskcarefullykinkorderktokensurekthatkthekdatabasekwillkproperlyk
meetkallkuserkandksystemkrequirements.
Beforekakdatabasekcankbeksuccessfullykimplemented,kthekfollowingkstepskmustkbekcompleted:
1. Definekthekconceptualkmodel'skcomponents:kentities,kattributes,kdomains,kandkrelationships.
2. Normalizek thek databasek tok ensurek thatk allk transitivek dependenciesk arek eliminatedk andk thatk
eachkentity'skattributeskareksolelykdependentkonkitskkeykattribute(s).
3. Verifyk thek conceptualk modelk tok ensurek thatk thek proposedk databasek willk meetk thek system'skt
ransactionkrequirementskandkthatkthekend-userkandksystemskrequirementskwillkbekmet.
3
, AppendixkCkThekUniversitykLab:
ConceptualkDesignkVerification,kLogicalkDesign,kandkImplementation
Thek verificationk processk willk probablyk deletek and/ork createk entities,k attributes,k andkrelati
onships.kItkmaykalsokrefinekexistingkentities,kattributes,kandkrelationships.
4. Createktheklogicalkdesignkwhichkrequireskthekdefinitionkofkthektablekstructures,kusingk akspecific
kDBMSk(relational,knetworkkorkhierarchical).kLogicalkdesignkalsokincludes,kifknecessary,kapprop
riatekindexeskandkviews.
5. Createkthekphysicalkdesignktokdefinekaccesskpaths,kincludingkspacekallocation,kstoragekgroupkcr
eation,k tablek spaces,k andk anyk otherk physicalk storagek characteristick thatk isk dependentk onkth
ekhardwarekandksoftwarektokbekusedkinktheksystem'skimplementation.
6. Implementkthekdesign.kkkSomehow,kthisklastkstepkseemsktoksufferkfromkplanningkneglect,ktokth
ekdetrimentkofktheksystem'skoperation.kImplementation,koperation,kandk maintenancekplansk
mustk(atkleast)kincludekcarefulkdefinitionkandkdescriptionkofkthekactivitieskrequiredktokimpleme
ntkthekdatabasekdesign:
loadingkandkconversion
definitionkofkdatabasekstandards
systemkandkprocedureskdocumentation:ksecurity,kbackup,kandkrecovery
operationalkproceduresktokbekfollowedkbykusers
akdetailedktrainingkplan
identificationkofkresponsibilitieskforkoperationkandkmaintenance.
3. Whatk majork factorsk shouldk bek addressedk whenk databasek systemk performancek isk evaluated?kDiscus
skeachkfactorkbriefly.
Databaseksystemskperformancekrefersktoktheksystem'skabilityktokretrievekinformationkwithinkakreasona
blekamountkofktimekandkatkakreasonablekcost.kKeepingkinkmindkthatk"reasonable"kmeanskdifferentkthin
gsktokdifferentkpeople,kwekmustkaddresskatkleastkthesekimportantkperformancekfactors:
Concurrentkusers
Fork anyk givenk system,k thek morek usersk connectedk tok thek system,k thek longerk thek datakr
etrievalktime.
Resourceklimits
Thekfewerkresourceskthatkarekavailablektokthekuser,ktheklongerkthekaccesskqueueskwillkbe.
Communicationkspeeds
Lowerkcommunicationkspeedskmeanklongerkresponsektimes.
Querykresponsektime
Querieskmustkbektunedktokprovidekoptimumkquerykresponsektime.k(SeekAppendixkC,k“Databas
ekPerformancekTuning.”)kLackkofkquerykresponsektuningkmeanskslowk responsektimes.kDepend
ingkonkhowkgoodkthekdesignkandkthekprogramkcodekare,kthekquerykresponsektimekcankvarykfro
mkminutesktokhourskforktheksamekquery.
Althoughkthekprecedingkdiscussionkiskfocusedkonkthekspeedkaspectkofkperformance,ktherekarekotherkeq
uallykimportantkissueskthatkmustkbekconsidered.kAksuccessfulkdatabasekimplementationkrequireskakbal
ancedkapproachktokallkdatabasekissues,kincludingkconcurrencykcontrol,kquerykresponsektime,kdatabasek
4
