数据库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.Whatnormalformistherelationin?
Explain、
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、
Bank
Account
Customer
Transaction
Branch
BankName
BankPhone
BranchAddr
BranchName
Has
N
1
BranchPhone
Assets
CustName
PhoneNum
CustID
AccountNo
Balance
OperationType
TID
TDateTime
AofBranch
TofAccount
Open
1
N
M
1
1
N
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、
1、pk:
(PartID,SupplierID)
2、FDs:
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_SUPPLYisstillinthefirstnormalbecausetheonlynone-primary-key
attributeSupplierName,partiallydependantsontheprimarykey(PartId,SupplierID)、
2)CAN_SUPLY(PartID,SupplierID,SuplierName),FDs={SupplierID->SuplierName):
ForSupplierID->SuplierName,therelationcanbedecompositedintotworelations:
SUPPLIER(SupplierID,SupplierName),FDs={SupplierID->SuplierName},PK:
(SupplierID);
CAN_SUPLY(PartID,SupplierID),FDs={},PK:
(PartID,SupplierID)、
Bothrelationsareinthethirdnormalform,becauseforeachone,nonone-primary-key
attributepatiallyortransivelydependantsonitsprimarykey、
3)Three3NFrelations:
PART(PartID,PartName),FDs={PartID->PartName},PK:
(PartID);
SUPPLIER(SupplierID,SupplierName),FDs={SupplierID->SuplierName},PK:
(SupplierID);
CAN_SUPLY(PartID,SupplierID),FDs={},PK:
(PartID,SupplierID)、
3、
九步算法:
三种异常:
修改异常、插入异常、删除异常。
S1:
每一强实体用一个新表表示
S2:
处理参与1:
1标识联系的弱实体W
S3:
处理参与1:
N标识联系的弱实体W
S4:
处理每一二元1:
1联系R,确定参与该联系的实体型对应的表S与T,将T的主码作为外码加入S,将R的所有简单属性与复合属性成分作为列加入S。
S5:
处理每一二元1:
N联系R,确定处于N端的实体表S与1端的实体表T,将T的主码作为外码加入S,将R的所有简单属性与复合属性成分作为列加入S、
S6:
处理每一N元联系(包括二元M:
N联系),对应新表T,将R的所有简单属性与复合属性成分作为列加入T,将参与联系的(强、弱)实体型的主码作为外码加入T,所有外码组合,共同构成T的主码、
S7:
处理每一多值属性A,将A的所有简单属性与复合属性成分作为列加入T,将A所属的实体或联系型的主码作为外码加入T,将(上步得到的)外码与A对应的属性确定为T的主码、
S8:
处理每一非相交子类的特化、
S9:
处理每一相交子类的特化、
S1:
T1:
Coach(Name,Age)
PK:
(Name)
T2:
Team(Name)
PK:
(Name)
T3:
Player(Name,Age)
PK:
(Name)
T4:
Game(Number,Score,Time,Date)
PK:
(Number)
T5:
Stadium(Name,Size,Location)
PK:
(Name)
S2:
处理参与1:
1标识符联系的弱实体
S3:
处理参与1:
N标识符联系的弱实体
S4:
T2:
Team(Name,CoachName)
PK:
(Name)
FK:
CoachNamereferencesCoach(Name)
S5:
T3:
Player(Name,Age,TeamName)
PK:
(Name)
FK:
TeamNamereferencesTeach(Name)
S6:
T6:
Practice(TeamName,StadiumName,Date)
PK:
(TeamName,StadiumName)
FK:
TeamNamereferencesTeach(Name)
FK:
StadiumNamereferencesStadium(StadiumName)
T7:
PlaysWith(HostName,VisitorName,GameNumber)
PK:
(GameNumber)
FK:
HostNamereferencesTeam(name)
FK:
VisitorNamereferencesTeam(name)
S7:
T8:
TeamColor(TeamName,color)
PK:
(TeamName,color)
FK:
TeamNamereferencesTeam(name)
Thelastseventables:
T1:
Coach(Name,Age)
PK:
(Name)
T2:
Team(Name,CoachName)
PK:
(Name)
FK:
CoachNamereferencesCoach(Name)
T3:
Player(Name,Age,TeamName)
PK:
(Name)
FK:
TeamNamereferencesTeach(Name)
T4:
Game(Number,Score,Time,Date)
PK:
(Number)
T5:
Stadium(Name,Size,Location)
PK:
(Name)
T6:
Practice(TeamName,StadiumName,Date)
PK:
(TeamName,StadiumName)
FK:
TeamNamereferencesTeach(Name)
FK:
StadiumNamereferencesStadium(StadiumName)
T7:
PlaysWith(HostName,VisitorName,GameNumber)
PK:
(GameNumber)
FK:
HostNamereferencesTeam(name)
FK:
VisitorNamereferencesTeam(name)
t8:
TeamColor(TeamName,color)
PK:
(TeamName,color)
FK:
TeamNamereferencesTeam(name)
(T4andT7havethesameprimarykey,so,theycanbejoinedtoanewtable、)
Normalization
Provideyoursolutiontothefollowinginanormalization、txt、Note:
Thediscussioninthisexerciseisindependentof(i、e、,completedunrelatedto)theE-CommerceprojectdescribedintheAppendix、
ThefollowingtablecapturesthefollowingfactaboutanE-Commercebookstore:
theemployeewhosenameisEmpNameandwhoseIDisEmpIDhasshippedtheorder(whoseOrderNumberisOrderNo)totheaddressShipToAddronthedateShippedDate、ThetrackingnumberfortheshipmentisTrackingNum、TheTrackingNumisprovidedbythecouriercompanythatpicksuptheshipment、Thebookstoreusesonlyonecouriercompany、Notethatasingleordercouldbesplitupintomultipleshipmentsbasedontheavailabilityoftheordereditems、Onlyoneemployeehandlesashipment、However,multipleemployeescouldhandleanorderiftheorderisshippedinmultipleshipments、
SHIPMENT
EmpID
EmpName
OrderNo
ShipToAddr
ShippedDate
TrackingNum
1234
Joe
223
4615ForbesAve,Pittsburgh,PA15147
12/21/99
12435678
2134
Jones
224
4615ForbesAve,Pittsburgh,PA15147
12/25/99
21345678
1.Listtheprimarykey、
2.ListalltheFDs、
3.Listalltheupdateanomaliesandprovideanexampleofeach、
4.Whatnormalformistherelationin?
Explain、
5.Applynormalizationtoitincrementally,bringingtherelationto3NF、Thatis,iftherelationisunnormalized,bringittofirstnormalform,thenbringthefirstnormalformyou'vejustcreatedtosecondnormalform,andthenbringthesecondnormalformtothirdnormalform、
ForeachtransformationtothenexthighernormalformX,
oExplainthestepsyoutooktobringittothenormalformX、
oProvidethenormalformX'stablestructure,primarykey(s),andtheFDs、
oExplainwhyyouthinkitisinthenormalformX、
Thatis,iftherelation