数据库ER图练习及答案.docx

数据库ER图练习及答案.docx

《数据库ER图练习及答案.docx》由会员分享，可在线阅读，更多相关《数据库ER图练习及答案.docx（19页珍藏版）》请在冰豆网上搜索。

数据库ER图练习及答案

DBModelingExamPractical

Answerthefollowingquestions.

1.CreateanE-Rschemaforadatabasesystemusedtomanageaccountinformationatacommunitybank.Thebankhasseveralbrancheswithuniquenames.Acustomermayhaveoneormoreaccountsinoneormorebranches.Anaccountmustbelongtooneandonlyonebranch.Eachaccountisoperatedonbytransactions,whichmaybedepositstoorwithdrawalsfromsomeaccount.Thedatabasekeepstrackofallthetransactionsoneachaccount,inadditiontothebalanceofindividualaccountsandtheassetsofindividualbranches.Foreachentity,specifyallitsattributes,primarykey,andalternatekey（s）.InyourERschema,besuretocapturethecardinalityconstraintsandparticipationconstraintsofallrelationships.

Makereasonableassumptionstocompletethespecification.Explicitlystateallyourassumptions.EVERYconstructinyourERschemashouldbesubstantiatedbyeitherthespecificationaboveoryourexplicitassumptions.

2.Thefollowingtablestoresinformationaboutwhichsupplierscansupplywhichparts.ThetablecapturesthefactthatapartwhosenameisPartNameandwhoseIDisPartIDcanbesuppliedbysupplierswhosenamesareinSupplierNameandwhoseIDsareinSupplierID.Notethatapartcanbesuppliedbymanysuppliers,andasuppliercansupplymanyparts.

CAN_SUPPLY

PartID

PartName

SupplierID

SupplierName

1234

Nut

223

ProMetal

1234

Nut

224

Biscayne

2134

Bolt

223

ProMetal

3.Performthefollowingtasks.

1.Listtheprimarykey.

2.ListalltheFDs.

3.WhatnormalformistherelationinExplain.

4.Applynormalizationtoitincrementally,carryingthenormalizationprocessthrougheachofthehighernormalformspossibleupto3NF.Thatis,iftherelationwereunnormalized,bringittofirstnormalform,thenbringthefirstnormalformyou'vejustcreatedtosecondnormalform,andthenbringthesecondnormalformtothirdnormalform.

ForeachtransformationtothenexthighernormalformX,

ExplainthestepsyoutooktobringittothenormalformX.

ProvidethenormalformX'stablestructure,primarykey（s）,andtheFDs.

ExplainwhyyouthinkitisinthenormalformX.Forexample,ifyouthinkthereisapartialdependency,fullydefendyourconclusionbyexplaininghowacolumnispartiallydependentonsomeothercolumn（s）.

Thatis,iftherelationwereinanunnormalizedform,youwouldexplainthetransformationyouperformedtobringittofirst,second,andthirdnormalforms.Youwouldalsoprovidethetablestructure,theprimarykeyandtheFDsforthefirst,second,andthirdnormalforms.Youwouldalsoprovideexplanationforwhyyoubelieveitisinfirst,second,andthirdnormalforms.

4.ConvertthefollowingE-Rschemaintoarelationalschemausingthemappingalgorithmspecifiedinthiscourse.Specifykeyandreferentialintegrityconstraints,usingdirectedarcs.Makesureyoualsoidentifyalternatekeys.Labeleachstepofthemappingalgorithm.

Answer:

1.

Entity:

1.Bank（BankName,BankPhone）（BankPhoneisamulti-valuedattribute.）

PK:

（BankName）

2.Cutomer（CustID,CustName,PhoneNum）

PK:

（CustID）

AK:

（PhoneNum）

3.Branch（BranchName,BranchAddr,BranchPhone,Assets）（BranchPhoneisamulti-valuedattribute.）

PK:

（BranchName）

4.Account（AccountNo,Balance）

PK:

（AccountNo）

5.Transaction（TID,OperationType,TDateTime）

PK:

（TID）

Relations:

1.Has:

,1:

N,PARTIAL/TOTAL;

2.Open:

,1:

N,PARTIAL/TOTAL;

3.AofBranch:

,1:

N,PARTIAL/TOTAL;

4.TofAccount:

,1:

N,PARTIAL/TOTAL;

Assumptions:

1.Anewbankcanestablishnobranch.

2.Onenormalbankestablishesoneormorebraches.

3.Abankhasoneormoretelephonesforcustomers.

4.AcustomercanopenoneormoreAccount.

5.Anaccountmustbelongtooneandonlyonebranch.

6.Onebranchopensoneormoreaccounts.

7.Abranchhasoneormoretelephonesforcustomers.

8.Anaccountbelongstojustonebranch;

2.

:

（PartID,SupplierID）

:

FD1:

PartID->{PartName}

FD2:

SupplierID->{SupplierName}

3.Therelationisinthefirstnormalform（1NF）.

Eachattributeoftherelationallowsasingleatomicvalue,soitisin1NF.

Butsomenone-primary-keyattributes,suchasPartNameandSupplierName,partially

dependantontheprimarykey（asFD1andFD2show）,soitisnotin2NF.

4.Normalization:

1）FD1:

PartID->{PartName}

Therelationcanbedecompositedintotworelations:

PART（PartID,PartName）,FDs={PartID->PartName},PK:

（PartID）;

CAN_SUPLY（PartID,SupplierID,SuplierName）,FDs={SupplierID->SupplierName）,PK:

（PartID,SupplierID）.

TherelationPARTisnowinthethirdnormalformbecausetheonlynone-primary-key

attributePartName,fully（notpartially）anddirectly（nottransively）dependantsontheprimarykeyPartId.

TherelationCAN_SUPPLYisstillinthefirst