数据库系统概念(database system concepts)英文第六版 课后练习

选填,简要介绍文档的主要内容,方便文档被更多人浏览和下载。

CHAPTER

7

Database Design and the E-R ModelPractice Exercises7.1 Answer: The E-R diagram is shown in Figure 7.1. Payments are modeled as weak entities since they are related to a speci c policy. Note that the participation of accident in the relationship participated is not total, since it is possible that there is an accident report where the participating car is unknown.

customer1..* customer_id name address owns 1..1

carcovers license_no model

policypolicy_id

participated payment

accidentreport_id date place

premium_paymentpayment_no due_date amount received_on

Figure 7.1 E-R diagram for a car insurance company. 1

选填,简要介绍文档的主要内容,方便文档被更多人浏览和下载。

2Chapter7DatabaseDesignandtheE-RModel

Figure7.2E-R

diagramformarksdatabase.

Figure7.3AnotherE-Rdiagramformarksdatabase.

7.2Answer:Note:thenameoftherelationship"courseoffering"needstobe

changedto"section".

a.TheE-RdiagramisshowninFigure7.2.Notethatanalterantiveistomodelexaminationsasweakentitiesrelatedtoasection,ratherthan

asastrongentity.Themarksrelationshipwouldthenbeabinaryrelationshipbetweenstudentandexam,withoutdirectlyinvolvingsection.

b.TheE-RdiagramisshowninFigure7.3.Notethatherewehavenotmodeledthename,placeandtimeoftheexamaspartofthe

relationshipattributes.Doingsowouldresultinduplicationoftheinformation,onceperstudent,andwewouldnotbeabletorecordthisinformationwithoutanassociatedstudent.Ifwewishtorep-resentthisinformation,wewouldneedtoretainaseparateentitycorrespondingtoeachexam.

选填,简要介绍文档的主要内容,方便文档被更多人浏览和下载。

Exercises3

Figure7.4E-Rdiagramforfavouriteteamstatistics.

7.3Answer:ThediagramisshowninFigure7.4.

7.4Answer:Thedifferentoccurrencesofanentitymayhavedifferentsets

ofattributes,leadingtoaninconsistentdiagram.Instead,theattributesofanentityshouldbespeci edonlyonce.Allotheroccurrencesoftheentityshouldomitattributes.Sinceitisnotpossibletohaveanentitywithoutanyattributes,anoccurrenceofanentitywithoutattributesclearlyindicatesthattheattributesarespeci edelsewhere.

7.5Answer:

a.IfapairofentitysetsareconnectedbyapathinanE-Rdiagram,

theentitysetsarerelated,thoughperhapsindirectly.Adisconnectedgraphimpliesthattherearepairsofentitysetsthatareunrelatedtoeachother.Inanenterprise,wecansaythatthetwodepartmentsarecompletelyindependentofeachother.Ifwesplitthegraphintoconnectedcomponents,wehave,ineffect,aseparatedatabasecorre-spondingtoeachconnectedcomponent.

b.Asindicatedintheanswertothepreviouspart,apathinthegraph

betweenapairofentitysetsindicatesa(possiblyindirect)relationshipbetweenthetwoentitysets.Ifthereisacycleinthegraphtheneverypairofentitysetsonthecyclearerelatedtoeachotherinatleasttwodistinctways.IftheE-Rdiagramisacyclicthenthereisauniquepathbetweeneverypairofentitysetsand,thus,auniquerelationshipbetweeneverypairofentitysets.

7.6Answer:

a.LetE={e1,e2},A={a1,a2},B={b

},andR1},C={c1},RA={(e1,a1),(e2,a2)},

R

eB={(e1,b1)C={(e1,c1)}.Weseethatbecauseofthetuple(2,a2),noinstanceofRexistswhichcorrespondstoE,RA,RBand

RC.

选填,简要介绍文档的主要内容,方便文档被更多人浏览和下载。

4Chapter7DatabaseDesignandtheE-RModel

Figure7.5E-RdiagramforExercise7.6b.

Figure7.6E-RdiagramforExercise7.6d.

b.SeeFigure7.5.TheideaistointroducetotalparticipationconstraintsbetweenEandtherelationshipsRA,RB,R

andC.CsothateverytupleinE

hasarelationshipwithA,B

c.SupposeAtotallyparticipatesintherelationhipR,thenintroducea

totalparticipationconstraintbetweenAandRA.

d.ConsiderEasaweakentitysetandRA,RBandRCasitsidentifying

relationshipsets.SeeFigure7.6.

7.7Answer:Theprimarykeyofaweakentitysetcanbeinferredfromits

relationshipwiththestrongentityset.Ifweaddprimarykeyattributestotheweakentityset,theywillbepresentinboththeentitysetandtherelationshipsetandtheyhavetobethesame.Hencetherewillberedundancy.

7.8Answer:Inthisexample,theprimarykeyofsectionconsistsoftheat-

tributes(course

course,whilecoursecoursereferencingcourse.Theseconstraintsensurethataparticularsectioncanonlycorrespondtoonecourse,andthusthemany-to-onecardinalityconstraintisenforced.However,theseconstraintscannotenforceatotalparticipationconstraint,sinceacourseorasectionmaynotparticipateinthesec

选填,简要介绍文档的主要内容,方便文档被更多人浏览和下载。

Exercises5

IDasaInadditiontodeclaringssuperkeyforadvisor(thiscanbedoneinSQLusingtheuniqueconstraint

oni

partcheck(bin(selectbfromA));

setconstraintstotal

idandinstructor

timesorname,withsubattributessuchasstart

选填,简要介绍文档的主要内容,方便文档被更多人浏览和下载。

6Chapter7DatabaseDesignandtheE-RModel

Figure7.7E-RdiagramforExercise7.13

Thevalueattributeisasubattributeofseveralattributessuchasname,tot

选填,简要介绍文档的主要内容,方便文档被更多人浏览和下载。

Exercises7

student(student)

studenttimes(studenttime,end)

studentid,value,starttime

deptid,value,starttime

totid,value,starttime

id

validid,starttime

name(instructortime,end

instructorname(instructortime,end

instructorid,value,starttime

id,intructortime,end)

TheprimarykeysshownarederiveddirectlyfromtheE-Rdiagram.Ifweaddtheadditionalconstraintthattimeintervalscannotoverlap(oreventheweakerconditionthatonestarttimecannothavetwoendtimes),wecanremovetheend

选填,简要介绍文档的主要内容,方便文档被更多人浏览和下载。