数据库管理系统概述英文版课件：4 Relational Model..pptx

数据库管理系统概述英文版课件：4 Relational Model..pptx

《数据库管理系统概述英文版课件：4 Relational Model..pptx》由会员分享，可在线阅读，更多相关《数据库管理系统概述英文版课件：4 Relational Model..pptx（55页珍藏版）》请在冰点文库上搜索。

COMP231,COMP231,1,RelationalModel,Outline,IntroductionTerminologyER-to-RelationalMapping,COMP231,2,Introduction,COMP231,3,TherelationalmodelwasfirstintroducedbyTedCoddofIBMResearchin1970andattractedduetoitssimplicity,eleganceandmathematicalfoundationsThemodelusestheconceptofamathematicalrelationwhichlookslikeatableofvalues,Outline,IntroductionTerminologyER-to-RelationalMapping,COMP231,4,Terminology,COMP231,5,Relationtable;denotedbyR（A1,A2,.,An）whereRisarelationnameand（A1,A2,.,An）istherelationschemaofRAttribute（column）denotedbyAiTuple（Record）rowAttributevaluevaluestoredinatablecellDomaindenotedbydom（Ai）,Attribute:

AgeAttribute:

EmpNameAttribute:

Salary,Domain:

0-100Domain:

50alphabeticcharsDomain:

non-negativeinteger,Terminology,Attributes/Columns（collectivelyasaschema）,Tuples/Rows,RelationName/TableName,COMP231,6,Schema,Therelationschemais（Name,Student-id,Age,CGA）OR,Theprimarykeyisunderlinedintheabove,Name,Student-idAge,CGA,COMP231,7,ForeignKey,COMP231,8,Aforeignkeyisasetofattributesinonerelationrthatisusedtorefertoatupleinanotherrelations.（itmustcorrespondtotheprimarykeyofthesecondrelation）,ForeignKey,COMP231,9,Student（Student-id,Student-Name）Take（Student-id,Course-id,semesterNo）Student-idinrelationStudentisaprimarykeyStudent-idinrelationTakeisaforeignkey,ForeignKey,COMP231,10,Student（Student-id,Student-Name）Take（Student-id,Course-id,semesterNo）Course（Course-id,Course-Name）Course-idinrelationCourseisaprimarykeyCourse-idinrelationTakeisaforeignkey,Outline,IntroductionTerminologyER-to-RelationalMapping,COMP231,11,ER-to-RelationalMapping,COMP231,12,Typically,databasedesignersbeginwiththeERmodel,whichisveryexpressiveanduser-friendlytohumanThen,theERmodelismappedtotherelationalmodelforDBMSmanipulationsDatabasequeriesandupdateswillbewrittenaccordingtotherelationalmodel,ER-to-RelationalMapping,TranslatingtraditionalERdiagramsTranslatingClassHierarchy,COMP231,13,Dependentt,name,sex,bdate,relationship,Controls,Project,pnamepnumber,plocation,supervision,supervisee,works_on,Dependentt_off,hours,Employee,sex,name,eid,bdate,addr,salarysupervisor,works_for,Manages,start_date,dnamenumberDepartment,COMP231,14,Controls,Project,pnamepnumber,plocation,works_on,hours,Employee,sex,name,eid,bdate,addr,salary,works_for,Manages,start_date,dnamenumberDepartment,COMP231,15,Steps,COMP231,16,Step1（StrongEntitySet）Step2（WeakEntitySet）Step3（1-to-1Relationship）Step4（1-to-manyRelationship）Step5（Many-to-manyRelationship）Step6（Non-binaryRelationship）,Step1（StrongEntity）,COMP231,17,ForeachstrongentitysetEintheERschema,createarelationschemaRthatincludesalltheattributesofE.chooseonesetofkeyattributesofEasaprimarykeyforR.,Dependentt,name,sex,bdate,relationship,Controls,Project,pnamepnumber,plocation,supervision,supervisee,works_on,Dependentt_off,hours,Employee,sex,name,eid,bdate,addr,salarysupervisor,works_for,Manages,start_date,dnamenumberDepartment,COMP231,18,Project,pnamepnumber,plocation,Employee,sex,name,eid,addr,bdatesalary,dnamenumberDepartment,COMP231,19,Step1（StrongEntity）,ExampleWecreatetherelationschemasEMPLOYEE,DEPARTMENTandPROJECT.,name,EMPLOYEE,DEPARTMENT,PROJECT,eid,bdate,addr,sexsalary,dname,dnumber,pname,pnumber,plocation,COMP231,20,Ifthereisaderivedattribute,whatshouldwedo?

Wehavetwochoices.Choice1:

IncludethisderivedattributeAdv:

WecandirectlyobtainthevalueofthederivedattributeDisadv:

WemayencountersomedatainconsistenciesChoice2:

NOTincludethisderivedattributeAdv:

WecanavoiddatainconsistencyDisadv:

Weneedtoperformsomeoperationstoobtainthevalueofthederivedattribute,COMP231,21,Ifthereisacompositeattribute,whatshouldwedo?

Wehavetwochoices.Choice1:

Includethehigh-levelattributeonly（e.g.,address）Choice2:

Includealllow-levelattributes（e.g.,street,city,country）,Employee,address,street,city,country,COMP231,22,Ifthereisamulti-valuedattribute,whatshouldwedo?

Wehavetwochoices.Choice1:

Includeoneattributeonly（e.g.,phone）Choice2:

Createanothertablecontainingtheprimarykeyoftheentitysetandthemulti-valuedattributee.g.,createaschemaPhoneTable（eid,phone）,Employee,phone,eid,COMP231,23,Step2（WeakEntity）,COMP231,24,ForeachweakentitysetWintheERmodel,createarelationschemaR,andincludeallattributes.Inaddition,includetheprimarykey（s）oftheowner（s）.TheprimarykeyofRisthecombinationoftheprimarykey（s）oftheowner（s）andthediscriminatoroftheweakentitysetW.,Dependentt,name,sex,bdate,relationship,Controls,Project,pnamepnumber,plocation,supervision,supervisee,works_on,Dependentt_off,hours,Employee,sex,name,eid,bdate,addr,salarysupervisor,works_for,Manages,start_date,dnamenumberDepartment,COMP231,25,Dependentt,name,sex,bdate,relationship,Dependentt_off,sex,name,addr,eidEmployeebdatesalary,COMP231,26,Dependenteiddependent-name,sexbdate,relationship,COMP231,27,Step3（1-to-1Relationship）,COMP231,28,Foreachbinaryone-to-one（1:

1）relationshipsetRT-SChooseoneofthe2relationschemas,sayS,getprimarykeyofT,includeitasforeignkeysinS.BetterifShastotalparticipationinRIncludetheattributesoftherelationshipsetRasattributesofS.,Dependentt,name,sex,bdate,relationship,Controls,Project,pnamepnumber,plocation,supervision,supervisee,works_on,Dependentt_off,hours,Employee,sex,name,eid,bdate,addr,salarysupervisor,works_for,Manages,start_date,dnamenumberDepartment,COMP231,29,Employee,sex,name,eid,bdate,addr,salary,Manages,start_date,dnamenumberDepartment,COMP231,30,WeincludetheprimarykeyofEMPLOYEEasforeignkeyinDEPARTMENTandrenameitmgr_id.WeincludetheattributestartdateofMANAGESandrenameitmgr_start_date.,DEPARTMENT,dnamednumbermgr_idmgr_start_date,COMP231,31,ComparethefollowingtwochoicestoincludeMANAGES:

AddinformationtoEMPLOYEE,AddtoDEPARTMENT,EMPLOYEE,MANAGES,DEPARTMENT,COMP231,32,total.,Intheabove,theNULLvalueisaspecialvaluemeaningthatthevalueiseitherunknownornotapplicable.Noticethatanalternativemappingofaone-to-onerelationshipsetispossiblebymergingthetwoentitysetsandtherelationshipintoasinglerelation.ThisisappropriatewhenbothparSttiecpip3a:

tionsare,Advantage:

ThetotalnumberofrelationsremainunchangedDisadvantage:

ItmaystoreNULLvaluesifthereisnototalparticipation,COMP231,33,Employee,sex,name,eid,bdate,addr,salary,Manages,start_date,dnamenumberDepartment,Canwecreateanewrelation,Manages（eid,number,start_date）,Or,Manages（eid,number,start_date）,forthisrelationship?

Yes.,ItcanbeusedifthereareonlyafewrelationshipinstancesAdvantage:

ItcanavoidstoringNULLvaluesifthereisnototalparticipationDisadvantage:

Thereisoneadditionalrelation,COMP231,34,Step4（1-to-manyRelationship）,COMP231,35,Foreachbinaryone-to-manyrelationshipsetT-SIncludeasforeignkeyinStheprimarykeythatrepresentstheotherentitysetTparticipatinginR.Includeanyattributesoftheone-to-manyrelationshipsetasattributesofS.,Dependentt,name,sex,bdate,relationship,Controls,Project,pnamepnumber,plocation,supervision,supervisee,works_on,Dependentt_off,hours,Employee,sex,name,eid,bdate,addr,salarysupervisor,works_for,Manages,start_date,dnamenumberDepartment,COMP231,36,Controls,Project,pnamepnumber,plocation,supervision,supervisee,Employee,sex,name,eid,bdate,addr,salarysupervisor,works_for,dnamenumberDepartment,COMP231,37,TheprimarykeydnumberoftheDEPARTMENTrelationschemaisincludedasforeignkeyintheEMPLOYEErelationschema.Werenameitasdno.（Therenamingisnotnecessarybutmakesthenamemoremeaningful.）,EMPLOYEE,nameeidbdateaddrsexsalarydnoEMPLOYEEWORKS_FORDEPARTMENT,COMP231,38,EMPLOYEE,Comparethefollowing2choices:

DEPARTMENT,Addemployeestodepartment,COMP231,Adddepartmenttoemployee,39,ForSUPERVISON,includetheprimarykeyoftheEMPLOYEEasforeignkeyintheEMPLOYEE,andcallitsuper_id.,nameeidbdateaddrsexsalarydnosuper_id,EMPLOYEE,pnamepnumberplocationdnum,COMP231,40,PROJECT,ForCONTROLSrelationship,includednumasforeignkeyinPROJECT,whichreferencestheprimarykeydnumberofDEPARTMENT.,Controls,Project,pnamepnumber,plocation,supervision,supervisee,Employee,sex,name,eid,bdate,addr,salarysupervisor,works_for,dnamenumberDepartment,Canwecreateanewrelation,works_for（eid,number）,forthisrelationship?

Yes.,COMP231,41,Step5（Many-to-manyRelationship）,COMP231,42,Foreachbinarymany-to-manyrelationshipsetR,createanewrelationschemaStorepresentR.IncludeasforeignkeyattributesinStheprimarykeysoftherelationschemasfortheparticipatingentitysetsinRtheircombinationwillformtheprimarykeyofS.Alsoincludeattributesofthemany-to-manyrelationshipsetasattributesofS.,Dependentt,name,sex,bdate,relationship,Controls,Project,pnamepnumber,plocation,supervision,supervisee,works_on,Dependentt_off,hours,Employee,sex,name,eid,bdate,addr,salarysupervisor,works_for,Manages,start_date,dnamenumberDepartment,COMP231,43,Project,pnamepnumber,plocation,hoursworks_on,sex,name,addr,eidEmployeebdatesalary,COMP231,44,Mapthemany-to-manyrelationshipsetsWORKS_ONbycreatingtherelationschemaWORKS_ON.IncludetheprimarykeysofPROJECTandEMPLOYEEasforeignkeys.,eidpnumberhours,COMP231,45,WORKS_ON,Comparethefollowingthreechoicestoincludeworks_on,AddtoEMPLOYEE,AddtoPROJECT,NewrelationWORKS_ON,WORKS_ONEMPLOYEEPROJECT,COMP231,46,Step6（Non-binaryRelationship）,COMP231,47,Foreachnon-binaryrelationshipset,createanewrelationschemaStorepresentR.IncludeasforeignkeyattributesinStheprimarykeysoftheparticipatingentitysets.Alsoincludeanyattributesofthenon-binaryrelationshipsetasattributesofS.,Fornon-binaryrelationships,TheprimarykeyofSisusuallyacombinationofalltheforeignk