Download Database Systems Design Implementation 12e Coronel SM 2025/2026 and more Exams Database Management Systems (DBMS) in PDF only on Docsity!
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio
n
AppendixyC
TheyUniversityyLab:yConceptualyDesign,yVerification,yLog
icalyDesign,yandyImplementation
DiscussionyFocus
Howyisyaydatabaseydesignyverified,yandywhyyisysuchyverificationynecessary?
Useyourydetailedyanswerytoyquestiony 1 ytoyfocusyclassydiscussionyonydatabaseydesignyverification.yStres
sythatytheyverificationyprocessyusesytheyinitialyERymodelyasyaycommunicationytool.
Theydesignerymayybeginytheyverificationyprocessybyydescribingytheyorganization'syoperationsytoyitsyen
dyusers,ybasingytheydetailedydescriptionyonytheyinitialyERymodel.yNext,yexplainyhowytheyoperationsywil
lybeysupportedybyytheydatabaseydesign.yStressythatytheydesignymustysupportytheyend-
useryapplicationyviews,youtputs,yandyinputs.yPointsytoybeyaddressedyincludeysuchyquestionsyas:
• Isytheydescriptionyaccurate?yIfynot,ywhatyaspectsyofytheydescriptionymustybeycorrected?
• Doesy they modely supporty they end-usery requirements?y Ify not,y whaty aspectsy ofy they end-
useryrequirementsyhaveynotybeenyaddressedyoryhaveybeenyaddressedyinadequately?
Keepyinymindythatyevenyaymodelythatyperfectlyyaddressesyallyinitiallyydeterminedyendyuseryrequirem
entsyisylikelyytoyneedyadjustmentsyasythoseyendyusersybeginytoyunderstandytheyramificationsyofytheyda
tabaseydesign'sycapabilities.yInymanyycases,ytheyendyusersymayylearnywhatytheyorganization'syprocesse
syandyproceduresyactuallyyare,ythusyleadingytoynewyrequirementsyandytheyperceptionyofynewyopportu
nities.yTheydatabaseydesignerymustykeepysuchylikelyydevelopmentsyinymind,yespeciallyyify(s)heyworksya
syaydatabaseydesignyconsultant.y(Anticipationyofysuchydevelopmentsymustybeyfactoredyintoytheycontra
ctynegotiationsyforyconsultingyfees.)
Discussytheyroleyofytheysystemymodules.
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
They usey ofy systemy modulesy cany hardlyy bey overemphasizedy iny ay databasey designy environment.y Str
essytheseymoduleycharacteristicsyandyfeatures:
- Modulesyrepresentysubsetsyofytheydatabaseymodel:ySmallery"pieces"yareymoreyeasilyyund erstood.
- Modulesyareyself- containedyandyaccomplishyayspecificysystemyfunction;yifysuchyaysystemyfunctionymustyb eymodified,yotheryfunctionsyremainyunaffected.
- Modulesyfityintoyaymodularydatabaseydesign,ywhichyisymoreyeasilyymodifiedyandyadapte dytoynewycircumstances.yBecauseymodificationyeffortsyareyfocusedyonyaydatabaseysubset, yproductivityyofybothydesignersyandyapplicationydevelopersyisylikelyytoybeyenhanced.
Moduleyinterfacesymustybeyclearyifytheymodulesyareyexpectedytoyworkywellywithinytheyoverallysystem.
AnswersytoyReviewyQuestions
1. Whyymustyayconceptualymodelybeyverified?yWhatystepsyareyinvolvedyinytheyverificationyprocess?
Theyverificationyofyayconceptualymodelyisycrucialytoyaysuccessfulydatabaseydesign.yTheyverificationy
processyallowsytheydesignerytoycheckytheyaccuracyyofytheydatabaseydesignyby:
- Re-examiningydatayandydataytransformations.
- Enablingytheydesignerytoy evaluateythey designy efficiencyyrelativey toy they endyuser'sy andysystem'sydesignygoals.
Keepyinymindythat,ytoyaylargeyextent,ytheybestydesignyisytheyoneythatyservesythey end-
useryrequirementsybest.yForyexample,yaydesignythatyworksywellyforyaymanufacturingyfirmymayynoty
fitytheyneedsyofyaymarketingyresearchyfirm,yandy viceyversa.
Theyverificationyprocessyhelpsytheydesignerytoyavoidyimplementationyproblemsylateryby:
- Validatingytheymodel'syentities.y(Rememberytheyminimalydatayrule.)
- Confirmingyentityyrelationshipsyandyeliminatingyduplicate,yunnecessary,yoryimproper lyydefinedyrelationships.
- Eliminatingydatayredundancies.
- Improvingytheymodel'sysemanticyprecisionytoybetteryrepresentyreal-worldyoperations.
- Confirmingythatyallyuseryrequirementsy(processing,yperformance,yorysecurity)yareymet.
Verificationyisyaycontinuousyactivityyinyanyydatabaseydesign.yTheydatabaseydesignyprocessyisyevolut
ionaryyinynature:yItyrequiresytheycontinuousyevaluationyofytheydevelopingymodelybyyexaminingythe
yeffectyofyaddingynewyentitiesyandybyyconfirmingythatyanyydesignychangesyenhanceytheymodel'syac
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
useryandysystemsyrequirementsywillybeymet.
They verificationy processy willy probablyy deletey and/ory createy entities,y attributes,y andyrel
ationships.yItymayyalsoyrefineyexistingyentities,yattributes,yandyrelationships.
4. Createythey logicalydesigny whichyrequiresytheydefinitionyofythey tableystructures ,yusingy ayspe
cificyDBMSy(relational,ynetworkyoryhierarchical).yLogicalydesignyalsoyincludes,yifynecessary,y
appropriatey indexesy andy views.
5. Createythey physicalydesigny toydefineyaccessypaths,yincludingyspaceyallocation,ystorageygrou
pycreation,y tabley spaces,y andy anyy othery physicaly storagey characteristicy thaty isy depende
nty onytheyhardwareyandysoftwareytoybeyusedyinytheysystem'syimplementation.
6. Implementytheydesign.yyySomehow,ythisylastystepyseemsytoysufferyfromy planningy neglect,yt
oytheydetrimentyofytheysystem'syoperation.yImplementation,yoperation,yandy maintenance
yplansymusty(atyleast)yincludeycarefulydefinitionyandydescriptionyofytheyactivitiesyrequiredyt
oyimplementytheydatabaseydesign:
- loadingyandyconversion
- definitionyofydatabaseystandards
- systemyandyproceduresydocumentation:ysecurity,ybackup,yandyrecovery
- operationalyproceduresytoybeyfollowedybyyusers
- aydetailedytrainingyplan
- identificationyofyresponsibilitiesyforyoperationyandymaintenance.
3. Whatymajoryfactorsyshouldybeyaddressedywhenydatabaseysystemyperformanceyisyevaluated?yDi
scussyeachyfactorybriefly.
Databaseysystemsy performancey refersytoytheysystem'syabilityytoyretrieveyinformationywithinyayreas
onableyamountyofytimeyandyatyayreasonableycost.yKeepingyinymindythaty"reasonable"ymeansydiffer
entythingsytoydifferentypeople,yweymustyaddressyatyleastytheseyimportantyperformanceyfactors:
- Concurrentyusers Fory anyy giveny system,y they morey usersy connectedy toy they system,y they longery they d atayretrievalytime.
- Resourceylimits
Theyfeweryresourcesythatyareyavailableytoytheyuser,ytheylongerytheyaccessyqueuesywillybe.
Lowerycommunicationyspeedsymeanylongeryresponseytimes.
Queriesymustybeytunedytoyprovideyoptimumyqueryyresponseytime.y(SeeyAppendixyC,y“Data
baseyPerformanceyTuning.”)yLackyofyqueryyresponseytuningymeansyslowy responseytimes.y
Dependingyonyhowygoodytheydesignyandytheyprogramycodeyare,ytheyqueryyresponseytimey
canyvaryyfromyminutesytoyhoursyforytheysameyquery.
Althoughytheyprecedingydiscussionyisyfocusedyonytheyspeedyaspectyofyperformance,ythereyareyoth
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
eryequallyyimportantyissuesythatymustybeyconsidered.yAysuccessfulydatabaseyimplementationyrequi
resyaybalancedyapproachytoyallydatabaseyissues,yincludingyconcurrencyycontrol,yqueryyresponseyti
me,ydatabaseyintegrity,ysecurity,ybackupyandyrecovery,ydatayreplication,yandydataydistribution.
4. HowywouldyyouyverifyytheyERydiagramyshownyinyFigureyQC.4?yMakeyspecificyrecommendations.
FigureyQC.4yTheyERDyforyQuestiony 4
Theyverificationyprocessymustyincludeytheyfollowingysteps:
1. Identifyyandydefineytheymainyentities,yattributes,yandydomains.yInythisycase,ytheymainyentit
iesyareyPARTS,ySUPPLIER,yPRODUCT,yandyCUSTOMER.yIdentifyyproperyprimaryykeysyandyco
mpositeyandymulti-valuedyattributes.
2. Identifyyandydefineytheyrelationshipsyamongytheyentities.yByyexaminingytheydiagram,ywey
mayyconcludeythatyseveralyM:Nyrelationshipsyexist:
PARTSyandySUPPLIERyPAR
TSyandyPRODUCTSyPROD
UCTyandyCUSTOMER
3. Identifyytheycompositeyentitiesyandytheiryprimaryyandyforeignykeys.yEachycompositey(bridg
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
raly classes :ydiskette,ypaper,yetc.yEachy classy canyhaveymanyy types :y3.5yDDydiskette,y3.5yHDydisk
ette,y8.5x11ypaper,y8.5x14ypaper,yandysoyon.yWeymayyevenyidentifyy subtypes :yEachy typey cany
haveymanyy subtypes .yForyexample,ytheyclassy"paper"yincludesytheytypesy“single-
sheet”yandy“continuous-feed”;ytheysingle-
sheetytypeymayybeyclassifiedybyysubtypey 8 yxy 11 yinchesyory 11 yxy 14 yinches.yTheyfollowingytabley
summarizesysomeyofytheyinventoryytypesyidentifiedyinytheysystem.yNoteythatytheyhierarchyyma
yybeyillustratedyasyshownyinyTableyQC.5A.
TableyQC.5AyTheyClassificationyHierarchy Category Class Type Subtype Hardware Computer
Desktop
Desktop
yLaptop
P
P
yP
Printer
Lasery
Lasery
Inkjet
yInkje
t
Plotter
8 yppm
12 ypp
myColor
yBlack
2 yxy 3
Supply Paper
Continuous-
feedySingleyshee
t
Singleysheet
8 yxy 10
11 yxy 14
Ityisyimportantytoynoteythatyeachyitemycanybelongytoyonlyyoneyspecificyinventoryytype.yAlso,ykeepy
inymindythatytheyORDER_ITEMyentityyinterfacesywithytheyINVENTORY_TYPE,yratherythanywithytheyI
TEMyentity.yTheyreasonyforythisyinterfaceyisyclearlyybasedyonytheychapter'sydescriptionyofytheyUCLy
operations:y"TheyCLDyrequestsyitemsywithoutyspecifyingyayspecificybrandyand/oryvendor."yGivenyth
isyrequirement,yityisyclearythatytheyITEMycan'tybeyidentifiedyinytheyrequest.y(TheyITEM'syprimaryyke
yyisyitsyserialynumber,ywhichycan'tybeyidentifiedyuntilytheyITEMyisyreceived!)yHowever,ytoymakeythe
yrequest,yweymustyknowytheyrequestedyitem'syinventoryytype.yTherefore,yORDERyisyrelatedytoythe
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
yINVENTORY_TYPE,yandy noty toytheyITEM.
Theyhierarchyyshownyhereyhasyledyusytoydevelopytheyclassificationyschemeyshownyinytheytext'syInv
entoryyClassificationyHierarchy,yillustratedyinytableyQC.5B:
TableyQC.5ByAnyInventoryyClassificationyHierarchy
GROUP CATEGORY CLASS TYPE SUBTYPE
HWPCDTP5 Hardwarey(HW) PersonalyComputery(PC) Desktopy(DT) Pentiumy(P5)
HWPCLP48 Hardwarey(HW) PersonalyComputery(PC) Laptopy(LT) PentiumyIV
HWPRLS Hardwarey(HW) Printery(PR) Lasery(LS) Standard
HWPRDM80 Hardwarey(HW) Printery(PR) Inkjety(IJ) 80 - column
SUPPSS11 Supplyy(SU) Papery(PP) SingleySheety(SS) 8.5"yxy11"yl
HWEXHDID Hardwarey(HW) ExpansionyBoardy(EX) Videoy(VI) XX
SWDBXXXX Softwarey(SW) Databasey(DB) XX XX
TheyclassificationyhierarchyymayyalsoybeyillustratedywithytheyhelpyofytheytreeydiagramyshownyinyFi
gureyQC.5:
FigureyQC.5yTheyINV_TYPEyClassificationyHierarchyyAsyayTreeyDiagram
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
Followytheyverificationystepsydescribedyinytheyanswerytoyquestiony4.yNoteythatytheycompositeyTRA
ININGyentityyshownyinyFigureyQC.6yreflectsypartyofytheyverificationyprocessythatybeganywithytheyM
:NyrelationshipybetweenyEMPLOYEEyandyCOURSC.y(Anyemployeeycanytakeymanyycoursesyandyman
yyemployeesycanytakeyeachycourse.)
Partyofytheyverificationyprocessyinvolvesytheyeliminationyofymulti-
valuedyattributes.yForyexample,yanyEMPLOYEEytableythatycontainsyanyattributeyEMP_TRAININGyco
ntainingystringsysuchyasy“fireysafety,yweather,yairyregulations”yhaveyalreadyybeenyeliminatedybyyth
eycompositeyTRAININGyentity.yTheystructureyshownyinyFigureyQC.6yallowsyusytoyaddyattributesytoy
ensureythatytrainingydetailsy–ysuchyasydates,ygrades,ytrainingylocations,yetc.y--ycanybeytraced,ytoo.
Oneyadditionaly–yandyveryyimportanty--
ypointyisyworthymentioning:yatythisypoint,yFigureyQC.6’syERDycannotyhandleyrecurrentytrainingyr
equirements.y Thatyis,yifysomeycoursesymustybeyretakenyperiodically,yasyisycommonyinymanyytrans
portationybusinesses,ytheyTRAININGyentity’syPKy–
yatythisypointycomposedyofytheyEMP_NUMy+yCOURSE_CODEy–
ywillynotyyieldyayuniqueyvalueyifytheycourseyisyretakenyfromytimeytoytime.yTheysolutionytoythisypro
blemycanybeyfoundyinyeitheryoneyofytwoyways:
- AddytheytrainingydateytoytheyTRAININGyentity’sycompositeyPKytoybecomeyEMP_NU My+yCOURSE_CODEy+yTRAIN_DATC.yThisyapproachyisyillustratedyinytheyexamplesy shownyinyTablesyQC.6AythroughyQC.6C.yNoteythatyemployeey 105 ytookythey FAR- 135 - Pycourseyony 26 - Sep- 2013 yandyony 11 - Feb-2014.yEmployeey 101 ytookytheyWEA- 01 ycourseyony 26 - ySep- 2013 yandyony 26 - Mar- 2014.y NoteythatytheyadditionyofytheyTRAIN_DATEytoytheycompositeyPKypreventsy theyduplicationyofytrainingyrecords.y Foryexample,yifyyouytried toyenterytheyfirstyTRAININGyrecordytwice,ytheycombinationyofyEMP_NUM+COURSE _CODE+TRAIN_DATEywouldynotybeyuniqueyandytheyDBMSywouldydiagnoseyanyentit yyintegrityyviolation. TableyQC.6AyTheyEMPLOYEEyTableyContents
EMP_NUM EMP_LNAME
105 Ortega
101 Williams
TableyQC.6ByTheyTRAININGyTableyContents
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
EMP_NUM COURSE_CODE TRAIN_DATE TRAIN_GRADE
105 FAR- 135 - P 26 - Sep- 2013 90
105 HM- 01 18 - Dec- 2013 92
101 FAR- 135 - P 23 - Nov- 2013 93
105 WEA- 01 10 - Mar- 2014 87
101 HM- 01 15 - Sep- 2013 91
101 WEA- 01 26 - Sep- 2013 85
105 FAR- 135 - P 11 - Feb- 2014 97
101 WEA- 01 26 - Mar- 2014 89
TableyQC.6CyTheyCOURSEyTableyContents
COURSE_CODE COURSE_DESCRIPTION
FAR- 135 - P Aircraftycharteryregulationsyforypilots
FAR- 135 - M Aircraftymaintenanceyforycharteryoperations
HM- 01 Hazardousymaterialsyhandling
WEA- 01 Aviationyweathery–ybasicyoperations
WEA- 02 Aviationyweathery–yinstrumentyoperations
2. CreateyaynewyPKyattributeynamedyTRAIN_NUMytoy uniquelyyidentifyyeachyentityyoccurrenceyi
nytheyTRAININGyentity,y andythenycreateyaycompositeyindexycomposedyofyEMP_NUMy+yCOU
RSE_CODEy+yTRAIN_DATE.y Thisyactionywillyremoveytheyweak/compositeydesignationyfromyth
eyTRAINING,ybecauseytheyTRAININGyentity’syPKyisynoylongerycomposedyofytheyPKyattributesyo
fytheyEMPLOYEEyandyCOURSEyentities.y(Andythey“receives”yandy“isyusedyin”yrelationshipsywilly
noylongerybeyclassifiedyasy“identifying”y–
ythusychangingytheyrelationshipydescriptionsyfromy“identifying”yory“strong”ytoy“non-
identifying”yoryweak”).yTheycompositeyindexywillypreventytheyduplicationyofyrecords.yNoteyth
eychangeyinytheystructureyandycontentsyofytheyTRAININGytableyshownyinyTableyQC.6D.
TableyQC.6DyTheyModifiedyTRAININGyTableyStructureyandyContents
TRAIN_NUM EMP_NUM COURSE_CODE TRAIN_DATE TRAIN_GRADE
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
syusedytoyrecordyanyentryyforyeachyinventoryytransaction.yInyotherywords,ytheysystemykeepsytrackyo
fyallyinputsytoyandywithdrawalsyfromyinventoryybyyusingythisyINV_TRANSyentity.
ItyisyimportantytoyrealizeythatytheyINV_TRANSyentityyisyaycrucialyentityyinytheysystem,ybecauseyityref
lectsy ally itemytransactions.ySuchyaysolutionyisynotyuniqueytoytheyUCL'syinventoryysystem:yMostyinve
ntoryysystemsymustybeyableytoykeepytrackyofysuchytransactions.yHavingyaycentralypointyofy referenc
eyfacilitatesytheyprocessing,yupdating,yquerying,yandyreportingycapabilitiesyofytheyinventoryysystem.
TheyUCL'sydataymodelykeepsytrackyofyseveralytypesyofyinventoryytransactionypurposesyorymotives:yc
heckouts,ywithdrawals,yadjustments,yandypurchases.yNoteytheysystem'syflexibility:yTheyuseryisyabley
toyclassifyyallyinventoryytransactionsybyytypeyand/orymotive.
Inyadditionytoybeingyflexible,ytheyUCLysystemyisyeasilyyexpandable:yIfynecessary,ytheysystemy canysu
pportyadditionalytypesyofyinventoryytransactionymotives.yForyexample,ytheysystemymayybeyexpande
dytoyincludeyinter-
warehouseyinventoryytransfers,yitemsyretiredyfromyinventoryybecauseytheyyareydate-
ylimited,yandysoyon.y(Date-
limitedyinventoryyisytypicalyforysuchythingsyasypharmaceuticals,yfood,yetc.)
Givenyitsyflexibilityyandyexpandability,yweymayyconcludeythatytheyUCLysystem'syinventoryydataymod
elyrepresentsyayveryyviableysolutionytoymodelingyrealyworldyinventoryytransactions.yTherefore,yitym
ayybeyusedytoyfityintoyjustyaboutyanyyinventoryyenvironment.
Note:yOptimumyvs.yImplementedySolutions .
TheyfinalyUCLyERDymakesyuseyofy theyINV_TRANSyentityytoyreplaceytheyWITHDRAWyentity.yPerhapsyso
meyofyyourystudentsywonderyaboutytheysimilarityyofytheyCHECK_OUTyandyCO_ITEMyentitiesywhenyco
mparedytoytheyINV_TRANSyandyINTR_ITEMyentities.yForyinstance,yityisyquiteyappropriateytoyargueythat
yCHECK_OUTyisyaytypeyofyinventoryytransactionyandythat,ytherefore,yCHECK_OUTyisyaysubtypeyofyanyIN
V_TRANSysupertype.yWhyydidytheydesignerycreateysuchyapparentysystemyredundancy?yWhyywasn'tyth
eytype/subtypeyhierarchyyusedymoreyefficiently?y(Classificationyhierarchiesyandysupertypes/subtypesya
reycoveredyinyChaptery5,y“AdvancedyDatayModeling.”)
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
Toyanswerythisyquestion,yreturnytoytheydiscussionyaboutyfine-
tuningytheydatabaseyforyperformance,yintegrity,yandysecurity.yBasedyonytheyestimationyofytheynum
beryofytransactions,ytheynumberyofyitems,yandytheynumberyofytheypossibleyconcurrentyaccessesytoy
theyINV_TRANSyentity,yitywasyclearythatythisyentityywillybeyoneyofytheymostyactiveyinytheysystem.yT
heylargeynumberyofycheck-
outsyreportsyandytheyevenylargeryexpectedynumberyofyinventoryytransactionsypromptedybothytheyd
esigneryandytheyendyuserytoychooseyeitherycontrolledyredundancyyoryhavingyayperformanceybottlen
eck.
PerhapsysomeystudentsywillyargueythatytheyuseyofytheyCHECK_OUTyandyCO_ITEMyentitiesyrepresent
syaymajoryburdenytoytheysystemyandythat,ytherefore,ytheysystemyshouldybeyimplementedywithoutyt
heseyentities.yThisyargumentyclearlyyhasysomeymerit:yTheyonlyyimmediateyadvantageyofyhavingythey
CHECK_OUTyandyCO_ITEMyentitiesyisythatytheyInventoryycheck-
outsyreportyusesytheseyentities,yratherythanytheyINV_TRANSyandyINTR_ITEMyentities.yTherefore,yth
eyeliminationyofy CHECK_OUTy andyCO_ITEMyreducesytheyconcurrentyaccessyconflictsyforytheyINV_T
RANSyandyINTR_ITEMyentities.
Finally,yweynoteythatybothytheydesigneryandytheyendyuseryareyawareyofytheyconsequencesyofytheys
electedysolution.yRemember,ythisyisyayrealysolutionytoyayrealyproblem,yandyityhelpsytoyillustrateythe
ypointythatyweymadeyearlier:yTheybestysolutionyisynotyalwaysytheyoneythatyisyimplemented.yEachysy
stemyisysubjectytoyconstraints,yandytheydesignerymustyinformytheyendyuseryofytheyconsequencesyof
ytheydataymodelingydesignyselections.
Anyimportantynoteyinyprimaryykeyyselectionyforymulti-userysystems
TheyLOGyisyanyentityythatykeepsyayrecordyofyallytheystudentsythatyuseytheyUCL.yNoteythatytheyprimar
yykeyyisyformedybyyLOG_DATE,yLOG_TIME,yandyUSER_ID.
AskytheystudentsywhyyUSER_IDyhasybeenymadeyaypartyofytheyprimaryykey.ySinceyeachyuserycan ybeyinyonlyyoneyplaceyatyoneytime,yityseemsysafeytoyassumeythatyUSER_IDydoesynotyneedytoybey partyofytheyprimaryykey.ySoywhyynotyjustyuseyayprimaryykeyycomposedyofyLOG_DATEyandyLO G_TIME?yForyexample,ysupposeythatytheystudentyChristobalyColombusyentersytheyUCLyony 02 - Mar- 2014 yaty02:10:11ypm.yToyuseytheyUCL'syfacilitiesyandyservices,yMr.yColumbusymustygiveyhisyst udentyidentificationycardytoytheylabyassistant.yClearly,yMr.yColombusycanyonlyybeyatythatyoneylo cationyatythatytime.yWhenytheylabyassistantyentersyMr.yColumbus'syUSER_ID,ythatyentryyisymad eyatyayspecificyandyuniqueydateyandytime.yWhenytheylabyassistantyregistersytheynextystudent,ythat ystudent'syUSER_IDyisyenteredyatyaydifferentytimeyinytheycomputer'syclock.ySinceyeveryyUSER_I
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
requirementsythaty“general”yemployeesyareynotylikelyytoyhave.yTheyuseyofytheseysubtypesy
eliminatesynullsyinytheyEMPLOYEEytable,ythusymakingythemydesirableyinythisycase.
- Althoughysomeyemployeeyjob-relatedydatayareystoredyinytheirysubtypesy–
ysee,yforyexample,yourydiscussionyofytheySALESPERSONyandyMECHANICysubsetsy–
yweystillyneedytoyknowywhatytheyemployeeyjobyassignmentsyare.yAlthoughyweyhaveynotyinc
ludedypayyandybenefityoptionsyinythisydesign,ybothyoptionsyareylikelyytoybeyjobyrelated.ySo
meyjobsyareypaidyonyanyhourlyybasis,ysomeyonyayweeklyybasis,yandysomeyjobsyareysalaried
.yBaseypayyschedulesyareyusuallyydeterminedybyyjobyqualifications.yTherefore,ytheyJOByenti
tyystoresyayJOB_PERIODyattributey(hour,yweek,yoryyear)yandyayJOB_PAYyattribute.yIfytheyJ
OB_PERIODy=y“hour”,ytheyJOB_PAYy=
$18.90yisyclearlyyanyhourlyyrate.yIfytheyJOB_PERIODy=y“year”,yayJOB_PAYy=y$45,275yisyclea
rlyyayyearlyysalary.yInylargerycompanies,yjobyassignmentsyareyusefulyinytrackingytheydistribu
tionyofyjoby“densities”ytoyseeyifysomeyjobyclassificationydistributionsyareyappropriateytoym
eetytheybusinessyobjectives.y(Doyweyhaveytooymanyyemployeesywhoyareyclassifiedyasy“sup
port”ypersonnel?yTooymanyyaccountants?)yAlso,ynoteythatytheyrelationshipybetweenyJOBya
ndyEMPLOYEEyreflectsytheybusinessyrulesythatyeachyemployeeyhasyonlyyoneyjobyassignmen
tyatyaytime.yNaturally,yanyygivenyjobycanybeyheldybyymanyyemployees.yForyexample,ymany
yemployeesymayybeymechanics,ysupportypersonnel,yaccountants,yandysoyon.
… AdditionalydiscussionypointsyfollowyFigureyPC..
FigureyPC.1yTheyVerifiedyCaryDealershipyCrow’syFootyERD
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n …ycontinuedydiscussionyofyFigureyPC.1’syERD.
- Toytrackyallymaintenanceyproceduresyandypartsyprecisely,yonlyyqualifiedymechanicsymayyo
penyandycloseyserviceylogs,ycheckyoutyparts,yandysignyoffyserviceywork.yNoteythatytheyPART
_LOGytracksyallypartsythatyhaveybeenyloggedyout.yTheyrelationshipybetweenySERVICE_LOGy
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
10013 Oilychange.yRotateyandybalanceytires. $19.95 5DR-T- 8765432
10014 Tempygaugeyshowsyhighytemps. $135.70 4UY-D- 6543210
SampleySVC_LOG_ACTIONyData
SVC_LOG_NUM SVC_LOGACT_TYPE SVC_LOGACTy_DATE EMP_NUM
10012 Open 03 - Mar- 2014 104
10013 Open 03 - Mar- 2014 112
10012 Close 04 - Mar- 2014 112
10014 Open 04 - Mar- 2014 104
10013 Close 04 - Mar- 2014 104
SampleySVC_LOG_LINEyDatay(Severalyattributesyleftyoutytoysaveyspace)
SVC_LINE_NUM SVC_LOG_NUM SVC_LINE_WORK EMP_NUM PART_CODE
1 10012 Cleanedyinjectionynozzles^106
1 10013 Drainedyoil 112 000000
2 10013 Installedyfilter 112 FLTR- 0156
3 10013 Replacedyoil 112 Oil-PZ30/
4 10013 Rotatedytires 114 000000
5 10013 Balancedytires,yusingyfouryweigh
tsy(LF0.5oz,yRF1.1oz,yRR1.2oz,yLR
oz)
106 WT-LD
1 10014 Drainedycoolant 104 000000
2 10014 Replacedythermostat 112 THERM-007B
3 10014 Replacedycoolant 104 COOL-289XZ
SampleyPART_LOGyData
ConceptualyDesignyVerification,yLogicalyDesign,yandyImplementatio n
PARTLOG_NUM EMP_NUM PART_CODE SVC_LOG_NUM PARTLOG_DATE PARTLOG_UNITS
10185 112 FLTR-^0156 10013 03 - Mar- 2014 1
10186 112 Oil-PZ30/40 10013 03 - Mar- 2014 8
10187 114 WT-LD10012 10013 03 - Mar- 2014 4
10188 112 THERM-007B 10014 04 - Mar- 2014 1
10189 114 COOL-289XZ 10014 04 - Mar- 2014 1
Theymainyprocessesythatycanybeyidentifiedyinythisysystemyinclude:
- Theygenerationyofyanyinvoicey(INSERT).
- Theycarysalesygenerationyandyreportsy(SELECT).
- Theyregistrationyofyayserviceyforyaycustomer'sycary(INSERT,yUPDATE).
- They registrationy ofy they worky logy ory ofy they employeesy (mechanics)y whoy workedy o ny aycary(INSERT,yUPDATE).
- Theyregistrationyofypartsyinventoryy(INSERT,yUPDATE).
- Theyregistrationyofypartsyusedyinyayservicey(INSERT,yUPDATE).
- Theyregistrationyofytheycaryhistoryy(INSERT,yUPDATE).
- Queriesyandyreportsysuchyas: ➢ PartsyList ➢ CaryPriceyList ➢ SalesyReports ➢ ServiceyReport ➢ CaryHistoryyReport ➢ PartsyUsedyReport ➢ WorkyLogyReport
Theydesignerymustycheckythatytheydatabaseymodelysupportsyallytheseyprocessesyandythatytheymod
elyisyflexibleyenoughytoysupportyfutureymodifications.
Ifyproblemsyareyencounteredyduringytheymodel'syverificationyagainstytheyrequiredydatabaseytransa
ctionsythatyareydesignedytoysupportytheyidentifiedyprocesses,ytheydesignerymustymakeytheynecessa
ryychangesytoytheydataymodel.yTheseychangesyareyreflectedyinyFigureyPC.1.
