数据库ER图练习及答案Word文档下载推荐.docx
- 文档编号:6647326
- 上传时间:2023-05-07
- 格式:DOCX
- 页数:22
- 大小:79.58KB
数据库ER图练习及答案Word文档下载推荐.docx
《数据库ER图练习及答案Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《数据库ER图练习及答案Word文档下载推荐.docx(22页珍藏版)》请在冰点文库上搜索。
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.
Entity:
1.Bank(BankName,BankPhone)(BankPhoneisamulti-valuedattribute.)
PK:
(BankName)
2.Cutomer(CustID,CustName,PhoneNum)
(CustID)
AK:
(PhoneNum)
3.Branch(BranchName,BranchAddr,BranchPhone,Assets)(BranchPhoneisamulti-valuedattribute.)
(BranchName)
4.Account(AccountNo,Balance)
(AccountNo)
5.Transaction(TID,OperationType,TDateTime)
(TID)
Relations:
1.Has:
<
Bank,Branch>
1:
N,PARTIAL/TOTAL;
2.Open:
Customer,Account>
3.AofBranch:
Branch,Account>
4.TofAccount:
Account,Transaction>
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:
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:
Bothrelationsareinthethirdnormalform,becauseforeachone,nonone-primary-key
attributepatiallyortransivelydependantsonitsprimarykey.
3)Three3NFrelations:
3.
九步算法:
三种异常:
修改异常、插入异常、删除异常。
S1:
每一强实体用一个新表表示
S2:
处理参与1:
1标识联系的弱实体W
S3:
N标识联系的弱实体W
S4:
处理每一二元1:
1联系R,确定参与该联系的实体型对应的表S和T,将T的主码作为外码加入S,将R的所有简单属性和复合属性成分作为列加入S。
S5:
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)
T3:
Player(Name,Age)
T4:
Game(Number,Score,Time,Date)
(Number)
T5:
Stadium(Name,Size,Location)
S2:
处理参与1:
1标识符联系的弱实体
S3:
N标识符联系的弱实体
S4:
Team(Name,CoachName)
FK:
CoachNamereferencesCoach(Name)
S5:
Player(Name,Age,TeamName)
TeamNamereferencesTeach(Name)
T6:
Practice(TeamName,StadiumName,Date)
(TeamName,StadiumName)
StadiumNamereferencesStadium(StadiumName)
T7:
PlaysWith(HostName,VisitorName,GameNumber)
(GameNumber)
HostNamereferencesTeam(name)
VisitorNamereferencesTeam(name)
S7:
T8:
TeamColor(TeamName,color)
(TeamName,color)
TeamNamereferencesTeam(name)
Thelastseventables:
t8:
(T4andT7havethesameprimarykey,so,theycanbejoinedtoanewtable.)
Normalization
Provideyoursolutiontothefollowinginafilenamednormalization.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
Joe
4615ForbesAve,Pittsburgh,PA15147
12/21/99
12435678
Jones
12/25/99
21345678
3.Listalltheupdateanomaliesandprovideanexampleofeach.
4.Whatnormalformistherelationin?
5.Applynormalizationtoitincrementally,bringingtherelationto3NF.Thatis,iftherelationisunnormalized,bringittofirstnormalform,thenbringthefirstnormalformyou'
oExplainthestepsyoutooktobringittothenormalformX.
oProvidethenormalformX'
oExplainwhyyouthinkitisinthenormalformX.
Thatis,iftherelationwereinanunnormalizedform,youwouldexplainthetransformationyouperformedtobringittofirst,second,andthirdnormalforms.Youwouldalsoprovidethetablestructure,theprimarykey,andtheFDsforthefirst,second,andthirdnormalforms.Youwouldalsopr
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 ER 练习 答案