会计信息系统12版英文教辅资源rais12-IM-CH17GE

Page1of13

CHAPTER17

DATABASEDESIGNUSINGTHEREADATAMODEL

Instructor’sManual

LearningObjectives:

1.Discussthestepsfordesigningandimplementingadatabase

system.

2.UsetheREAdatamodeltodesignanAISdatabase.

3.DrawanREAdiagramofanAISdatabase.

4.ReadanREAdiagramandexplainwhatitrevealsaboutthe

businessactivitiesandpoliciesoftheorganizationbeing

modeled.

Questionstobeaddressedinthischapterinclude:

1.Whatstepsarefollowedtodesignandimplementadatabasesystem?

2.HowistheREAdatamodelusedtodesignanAISdatabase?

3.Howisanentity-relationshipREAdiagramofanAISdatabasedrawn?

4.HowareREAdiagramsread,andwhatdotheyrevealaboutthebusinessactivitiesandpoliciesoftheorganizationbeingmodeled?

Introduction

Thischapterintroducesthetopicofdatamodeling,oneaspectof

databasedesignthataccountantsshouldunderstand.

Wediscussentity-relationship(E-R)diagramsandtheREAaccounting

modelanddemonstratehowtousethesetoolstobuildadatamodelof

anAIS.

LearningObjectiveOne

Discussthestepsfordesigningandimplementing

adatabasesystem.

DatabaseDesignProcess

Figure17-1onpage514showsthefivebasicstepsindatabasedesign.

1.SystemsAnalysis

Page2of13

2.ConceptualDesign

3.PhysicalDesign

4.ImplementationandConversion

5.OperationandMaintenance

Thefirststage(systemsanalysis)consistsofinitialplanningto

determinetheneedforandfeasibilityofdevelopinganewsystem.

Thesecondstage(conceptualdesign)includesdevelopingthedifferent

schemasforthenewsystem,attheconceptual,external,andinternal

levels.

Thethirdstage(physicaldesign)consistsoftranslatingtheinternal-

levelschemaintotheactualdatabasestructuresthatwillbe

implementedinthenewsystem.

Thefourthstage(implementationandconversion)includesallthe

activitiesassociatedwithtransferringdatafromexistingsystemsto

thenewdatabaseAIS,testingthenewsystem,andtrainingemployees

howtouseit.

Thefifthandfinalstageisusingandmaintainingthenewsystem.

Accountantscanandshouldparticipateineverystageofthedatabase

designprocess.

Accountantsmayprovidethegreatestvaluetotheirorganizationsby

takingresponsibilityfordatamodeling.

Datamodelingistheprocessofdefiningadatabasesothatit

faithfullyrepresentsallaspectsoftheorganization,includingits

interactionswiththeexternalenvironment.

Entity-RelationshipDiagrams

Anentity-relationship(E-R)diagramisagraphicaltechniquefor

portrayingadatabaseschema.

Anentityisanythingaboutwhichtheorganizationwantstocollectand

storeinformation.

InanE-Rdiagram,entitiesaredepictedasrectangles.

Figure17-2onpage515showsvariousE-Rdiagrams

Somedatamodelersusediamondstodepictrelationships(panelA),

whereasothersdonot(panelB).

Sometimestheattributesassociatedwitheachentityaredepicted

asnameovalsconnectedtoeachrectangle(panelC)whereas

othersassociateattributeswitheachentitywhicharelistedin

aseparatetable(panelD).

Page3of13

E-Rdiagramscanbeusedtorepresentthecontentsofanykindof

database.

Inthisbook,ourfocusisondatabasesdesignedtosupportan

organization’sbusinessactivities.

BusinessprocessreengineeringiscoveredinPartVinthischapterin

whichwefocusonusingE-Rdiagramsfordatabasedesignandfor

understandingthecontentsofexistingdatabases.

MultipleChoice1

Whichofthefollowingstatement(s)istrue?

a.Anentityisanythingaboutwhichtheorganizationwantsto

collectandstoreinformation.

b.Eventsarethevariousbusinessactivitiesaboutwhichmanagement

wantstocollectinformationforplanningorcontrolpurposes.

c.Somedatamodelers,tools,andauthorsusediamondstodepict

relationships.

d.E-Rdiagramscanbeusedtorepresentthecontentsofanykindof

database.

e.Alloftheabovearetrue.

LearningObjectiveTwo

UsetheREAdatamodeltodesignanAISdatabase.

TheREADataModel

TheREAdatamodelwasdevelopedforuseindesigningAIS.TheREAdatamodelfocusesonthebusinesssemanticsunderlyinganorganization’svaluechainactivities.

ThreeBasicTypesofEntities

TheREAdatamodelissonamedasithasthreedistinctcategories:

1.R—resourcestheorganizationacquiresanduses

2.E—events(businessactivities)inwhichtheorganization

engages

3.A—agentsparticipatingintheseevents

Resourcesarethosethingsthathaveeconomicvaluetothe

organization.

Eventsarethevariousbusinessactivitiesaboutwhichmanagementwants

tocollectinformation.

TherearetwoevententitiesinFigure17-3onpage516;Sales

andReceiveCash.

Page4of13

Agentsarethepeopleandorganizationsthatparticipateintheevents.

Figure17-3includestwotypesofagententities:Employeesand

Customers.

Someresearchershaveproposedafourthtypeofentitycalledlocations

suchasstoresandwarehouses.

However,becauselocationsarenormallycontrolledinternally;

theauthorofthisbookseesnocompellingreasontoaddthis

fourthtypeofentity.

StructuringRelationships:TheBasicREATemplate

Theessentialfeaturesofthebasicpatternare:

1.Eacheventislinkedtoatleastoneresourcethatitaffects.

2.Eacheventislinkedtoatleastoneotherevent.

3.Eacheventislinkedtoatleasttwoparticipatingagents.

Rule1—EveryEventEntityMustBeLinkedtoatLeastOneResource

Entity

Eventsmustbelinkedtoatleastoneresourcethattheyaffect.

The“GetResourceA”inFigure17-4onpage517increasesthequantity

ofaresource.

Forexample,“Get”includesthereceiptofgoodsfromasupplier

whichincreasestheamountofinventory,andthereceiptof

paymentfromacustomerincreasestheamountofcash.

Relationshipsthataffectthequantityofaresourceare

sometimesreferredtoasstockflowrelationships.

“Give”eventsincludepayingsuppliersandsellingmerchandise

whichdecreasestheamountofcashandinventoriesrespectively.

Noteveryeventchangesthequantityofaresource.Forexample,

acustomer’scommitmenttopurchasemerchandisewillnotalter

thequantityofaresourceuntilthesaleisactuallymade.The

commitmentofacompanytopurchasemerchandisefromavendor

willnotchangethequantityofaresourceuntiltheactual

purchaseismadeandtheitemsarereceived.

Rule2—EveryEventEntityMustBeLinkedtoatLeastOneOther

EventEntity

Figure17-4showsthattheGetResourceAeventislinkedtothe

GiveResourceBeventinwhatiscalledaneconomicduality

relationship.

Page5of13

Figure17-5onpage518showsthateachaccountingcyclecanbe

describedintermsofsuchgive-to-geteconomicduality

relationships.

Theaccountingcyclesinclude1)revenuecycle,2)

expenditurecycle,3)payrollcycle,4)financingcycle,

and5)productioncycle.

Rule3—EveryEventEntityMustBeLinkedtoatLeastTwo

ParticipatingAgents

Foraccountability,organizationsneedtobeabletotrackthe

actionsofemployees.

Figure17-4onpage517showseacheventlinkedtotwo

participatingagententities.

Foreventsthatinvolveexternalagents,theinternalagentis

theemployeeandtheexternalagentistheoutsideparty.

Foreventsthatinvolveinternalagents,theinternalagentis

theemployeethatisgivinguparesourceandtheexternalagent

istheemployeewhoisreceivingtheresource.

MultipleChoice2

InregardstoREAdatamodeling,someresearchershaveproposeda

fourthtypeofentity,whichtheycall_____.

a.accounts

b.locations

c.data

d.specialagents

MultipleChoice3

Theessentialfeaturesofrelationshippatternsinclude:

a.Eacheventislinkedtoatleasttwoparticipatingagents.

b.Eacheventislinkedtonomorethanoneresourcethatitaffects.

c.Eacheventislinkedtoatleastoneotherevent.

d.AandC

e.Alloftheabove.

MultipleChoice4

InorderforamanufacturertoGetinventory,theymustGive:

a.cash

b.rawmaterials

c.machinery

d.BandC

e.AandC

LearningObjectiveThree

DevDpgREaAndraogfrAISdatabase.

Page6of13

Figure17-6onpage519showstheREAdiagramPauldevelopedfor

therevenuecycleofFred’sTrainShop.

ThischapterfocusesondevelopinganREAdiagramforan

individualtransactioncycle.

DevelopinganREAdiagramforaspecifictransactioncycle

consistsofthefollowingthreesteps:

1.Identifytheeventsaboutwhichmanagementwantsto

collectinformation.

2.Identifytheresourcesaffectedbyeacheventandthe

agentswhoparticipateinthoseevents.

3.Determinethecardinalitiesofeachrelationship.

Step1:IdentifyRelevantEvents

ThefirststepindevelopinganREAmodelofatransactioncycleisto

identifytheeventsofinteresttomanagement.

Ataminimumthismustincludetwoeventsthatincludethebasicgive-

to-geteconomicexchange.

Usually,thereareothereventsthatmanagementisinterestedin

planning,controlling,andmonitoringthatalsoneedtobeincludedin

theREAmodel.

FromChapter10,therevenuecycletypicallyconsistsoffour

sequentialactivities:

1.Takecustomerorders.

2.Fillcustomerorders.

3.Billcustomers.

4.Collectpaymentfromcustomers.

Thefirstactivityoftakingthecustomer’sorderdoesnotinvolvethe

acquisitionofresources(get)orprovisionofresourcestoanexternal

party(give).

Thesecondactivityoffillingthecustomer’sorderdoesinvolvethe

reductionoftheorganization’sinventorylevel;representingtheGive

Resourceevent.

Thethirdactivityofbillingcustomersalsodoesnotinvolvean

increaseordecreaseofanyeconomicresource.

Thefourthactivityofcollectingpaymentsfromcustomersdoesinvolve

anincreaseinthecashresource.

InFigure17-6onpage519,itshowsthatthebasicbusinessactivities

intherevenuecycleindicatethatthebasicgive-to-geteconomic

Page7of13

exchangeconsistsoftwoevents:1)fillcustomerorders(referredto

astheSalesevent)andcollectpaymentsfromcustomers(referredtoas

theReceiveCashevent).

IndrawinganREAdiagramforanindividualtransactioncycle,itis

usefultodividethepaperintothreecolumns,oneforeachtypeof

entity.

Usetheleftcolumnforresources,thecentercolumnforevents,

andtherightcolumnforagents.

Afteridentifyingtheeconomicexchangeevents,itisnecessaryto

determinewhichotherbusinessactivitiesshouldberepresentedas

eventsintheREAmodel.

Thisrequiresunderstandingwhateachactivityentailsbecauseonly

thoseactivitiesthatinvolvetheacquisitionofnewinformationneed

tobeincludedinthemodel.

RefertoFigure17-6onpage519thatwasforFred’sTrainShopin

whichtheREAdiagramwasdesignedbyPaul.

PaulnotesthattheSalesandReceiveCashreflectsmostin-storesales

transactionsinwhichthecustomerselectstheitemstobuyandthen

paysforthem.

However,therearesomecustomersthatcallthestoreandaskFredto

putitemsasideinwhichtheywillpickthemuplaterduringtheweek.

SonowPaulneedstoaddthecommitmenteventTakeCustomerOrderto

theREAdiagrambeforetheSalesevent.

Freddoeshavecreditsalestosomecustomerssuchasshoppingcenters

andhotels.However,billingcustomersonlyinvolvesprintingand

mailinginvoices.Thecustomer’sobligationtopayarisesfromthe

deliveryofthemerchandise,notfromtheprintingofaninvoice.

Therefore,Pauldoesnotneedtoaddthebillingeventintherevenue

cycleREAdiagram.

Whataboutaccountsreceivable?Howcanyoupossiblymonitorthe

accountsreceivablebalancesheetitem?Whatinformationwouldthe

billingeventaddthatyoudon’talreadyhave?Answer:“Nothing.”You

alreadyhavetheinformationfromtheSalesevent.Accountsreceivable

equalsallsalesinwhichcustomershavenotyetpaid.Soagain,Paul

doesnothavetoincludethebillingevent.

Finally,noticethattherearenoeventsthatpertaintotheentryof

data.ThereasonforthisisthattheREAdatamodelisusedtodesign

transactionprocessingdatabases.Theobjectiveistomodelthebasic

value-chainbusinessactivitiesofanorganization:1)whatitdoesin

ordertogeneraterevenuesand2)howitspendscashandusesitsother

resources.

Thus,whatgetsmodeledintheREAdiagramisthebusinessevent(e.g.,

thesalestransaction)andthefactsthatmanagementwantstocollect

aboutthatevent,nottheentryofthatdata.

Page8of13

Step2:IdentifyResourcesandAgents

Next,theresourcesthatareaffectedbythoseeventsneedtobe

identified.Thisinvolvesansweringthreequestions:

1.Whateconomicresourceisreducedbythe“Give”event?

2.Whateconomicresourceisacquired/increasedbythe“Get”event?

3.Whateconomicresourceisaffectedbyacommitmentevent?

Again,asolidunderstandingofbusinessprocessesmakesiteasyto

answerthesequestions.

Tocontinuewithourexample,PaulobservedthattheSalesevent

involvesgivinginventorytocustomers.

TheCashReceiptseventinvolvesreceivingcashfromcustomers.Cash

canbeintheformofmoney,checks,creditcards,ordebitcards.

PauladdedtheInventoryresourceandCashresourceentitiestotheREA

diagram.

Finally,theTakeCustomerOrdereventinvolvesthesettingasideof

merchandiseforcustomers.

Inadditiontospecifyingtheresourcesaffectedbyeachevent,itis

alsonecessarytoidentifytheagentswhoparticipateinthoseevents.

Therewillalwaysbeatleastoneinternalagent(employee)and,in

mostcases,anexternalagent(customerorvendor)whoparticipatein

eachevent.

InthecaseofFred’sTrainShop’srevenuecycle,acustomeranda

salespersonparticipateineachsalesevent.

FortheReceiveCashevent,thecustomerandcashierarethetwoagents.

BoththerevenuecycleandtheTakeCustomerOrdereventinvolve

customersandemployees.

Step3:DetermineCardinalitiesofRelationships

ThefinalstepindrawinganREAdiagramforonetransactioncycleis

toaddinformationaboutrelationshipcardinalities.

Cardinalitiesdescribethenatureoftherelationshipbetweentwo

entitiesbyindicatinghowmanyinstancesofoneentitycanbelinked

toeachspecificinstanceofanotherentity.

Nouniversalstandardexistsforpresentinginformationabout

cardinalitiesinREAdiagrams.Thetextadoptsthegraphical“crow’s

feet”notationstyleforrepresentingcardinalityinformation.

Table17-1onpage522explainsthemeaningofthesymbolsusedto

representcardinalityinformation.

Page9of13

FOCUS17-1onpage523comparesthenotationusedinthisbookwith

othercommonlyusedconventions.

Figure17-7onpage524depictsbothminimumandmaximumcardinalities

foreachentityparticipatinginarelationship.

Minimumcardinalityindicateswhetheraspecificinstanceofthe

entitynexttothecardinalitymustbelinkedtoatleastone

instanceoftheentityontheoppositesideofthatrelationship.

Aminimumcardinalityofzeromeansthataninstanceofthe

entityonthissideoftherelationshipneednotbelinkedtoany

specificinstancesoftheotherevent.

Forexample,inFigure17-7theminimumcardinalityof0

nexttotheSalesentityintheCustomer-Salesrelationship

indicatesthatinformationaboutanewcustomercanbe

addedtotheCustomerentitywithoutanyspecificsales

transaction.

Aminimumcardinalityofonemeansthateachinstanceofthat

entitymustbelinkedtoatleastoneinstanceoftheother

entityparticipatinginthatrelationship.

Forexample,inFigure17-7theminimumcardinalityof1

nexttotheCustomerentityintheCustomer-Sale

relationshipindicatesthatinformationaboutanewsale

canonlybeaddedifitislinkedtoaspecificcustomer.

Themaximumcardinalityindicateswhetheroneinstanceofthat

entitycanbelinkedtomorethanoneinstanceoftheother

entityparticipatinginthatrelationship.

InFigure17-7themaximumcardinalitynexttoCustomer

entityintheCustomer-Salerelationshipis1.Thismeans

thateachsalestransactioncanbelinkedtoonlyone

customer.

ThreeTypesofRelationships

Figure17-7portraysthesethreetypesofrelationships:

1.Aone-to-one(1:1)relationshipexistswhenthemaximum

cardinalityforeachentityintherelationshipis1.

PanelA:one-to-one(1:1)relationship

2.Aone-to-many(1:N)relationshipexistswhenthemaximum

cardinalityofoneentityintherelationshipis1andthe

maximumcardinalityfortheotherentityinthatrelationship

ismany.

PanelB:one-to-many(1:N)relationship

PanelC:Oppositeaone-to-manyrelationshipiswhatis

referredtoas(N:1)

Page10of13

3.Amany-to-many(M:N)relationshipexistswhenthemaximum

cardinalityforbothentitiesintherelationshipismany.

PanelD:many-to-many(M:N)relationship

Figure17-7showsthatanyofthesepossibilitiesmightdescribethe

relationshipbetweentheSalesandReceiveCashevents.

Thecardinalitiesmustreflecttheorganization’sbusinesspolicies.

Figure17-7,panelA,representsthetypicalrevenuecyclerelationship

forbusiness-to-consumerretailsales.

Notethatisdoesnotmatterhowcustomerspayforeachsales

transaction.

Ifmanagementisinterestedintrackingthefrequencyofdifferent

paymentmethods,thisfactmightberecordedasanattributeofthe

Salesevent.

PanelBandCofFigure17-7depictstwowaysthatone-to-many(1:N)

relationshipscanoccur.

PanelBindicatesthattheorganizationhasabusinesspolicy

thatallowscustomerstomakeinstallmentpaymentstotheselling

organization.However,thisdoesnotmeanthateverysales

transactionispaidforininstallments.

PanelCshowsanothertypeof1:NrelationshipbetweenSaleand

CashReceipts.Thisindicatesthattheorganizationhasa

businesspolicythatdoesnotpermitcustomerstomake

installmentpayments.Thistypeisespeciallyusedforbusiness-

to-businesssalesofnondurablegoods.

PanelDofFigure17-7depictsamany-to-manyrelationshipbetweenthe

SaleandCashReceiptevents.Thistyperepresentsanorganizationthat

hasbusinesspoliciesthatallowcustomerstomakeinstallmentpayments

andalsopermitcustomerstoaccumulateabalancerepresentingasetof

salestransactionsoveraperiodoftime.Somesalestransactionsmay

bepaidinfullinonepaymentandsomecustomersmaypayforeach

salestransactionseparately.

MultipleChoice5

ThesecondstepindevelopinganREAdiagramis:

a.Determinethecardinalitiesofeachrelationship.

b.Identifytheeventsaboutwhichmanagementwantstocollect

information.

c.Identifytheresourcesaffectedbyeacheventandtheagentswho

participateinthoseevents.

d.Noneoftheabove.

MultipleChoice6

WhichoftheactivitiesinarevenuecycledoesnotinvolveaGetor

Giveevent?

Page11of13

a.Billcustomers.

b.Takecustomerorders.

c.Fillcustomerorders.

d.Collectpaymentfromcustomers.

e.BandC

MultipleChoice7

Threetypesofrelationshipsbetweenentitiesinclude:

a.1:M

b.N:M

c.1:N

d.0:1

LearningObjectiveFour

ReadanREAdiagramandexplainwhatitreveals

aboutthebusinessactivitiesandpoliciesofthe

organizationbeingmodeled.

BusinessMeaningofCardinalities

Theinformationthatreflectsfactsabouttheorganizationandits

businesspracticesisobtainedduringthesystemsanalysisand

conceptualdesignstagesofthedatabasedesignprocess.

Let’snowexamineFigure17-6toseewhatitrevealsaboutFred’sTrain

Shop.

First,notethatalloftheagent-eventrelationshipsare1:N.A

particularagentoftenparticipatesinmanyevents.

Theminimumcardinalitiesassociatedwiththeagent-event

relationshipsreflecttypicalbusinessprocesseswhichshowsthat

eacheventmustbelinkedtoanagent(e.g.,asalemustinvolve

acustomerandapaymentfromacustomer).

Figure17-6showsthattheminimumcardinalityontheeventside

oftheagent-eventrelationshipis0.Theorganizationmaywish

tostoreinformationaboutpotentialcustomersandalternate

suppliers.

Figure17-6depictsM:Nrelationshipsbetweentheinventory

resourceandthevariouseventsthataffectit.

Mostorganizationstracksuchinventorybyanidentifiersuchas

partnumber,itemnumber,orstock-keepingunit(SKU)numberand

donotattempttotrackeachphysicalinstanceofthatproduct.

Whenasaleoccurs,thesystemnoteswhichproductnumber(s)were

sold.Thesameinventoryitemmaybelinkedtomanydifferent

salesevents.

Page12of13

Whatifanorganizationsellsunique,one-of-a-kindinventory,suchas

originalartwork?Suchitemscanonlybesoldonetime;consequently,

themaximumcardinalityontheeventsideoftheinventory-salesevent

wouldbe1.

Theminimumcardinalitiesoneachsideoftheinventory-event

relationshipsshowninFigure17-6reflecttypicalbusinesspractices;

everyorderorsaleseventmustbelinkedtoatleastoneinventory

item.

NowconsidertherelationshipbetweenthecashresourceandtheReceive

Cashevent.Eachcashreceiptfromacustomerisdepositedintoone

cashaccount.Thenthetreasurertrans