天津大学《数据库管理系统》课件Ch3-95.pptVIP

101112137815TranslatingISAHierarchiestoRelationsGeneralapproach:3relations:Employees,Hourly_EmpsandContract_Emps.Hourly_Emps:EveryemployeeisrecordedinEmployees.Forhourlyemps,extrainforecordedinHourly_Emps(hourly_wages,hours_worked,ssn);mustdeleteHourly_EmpstupleifreferencedEmployeestupleisdeleted).Queriesinvolvingallemployeeseasy,thoseinvolvingjustHourly_Empsrequireajointogetsomeattributes.Alternative:JustHourly_EmpsandContract_Emps.Hourly_Emps:ssn,name,lot,hourly_wages,hours_worked.Eachemployeemustbeinoneofthesetwosubclasses.Review:Binaryvs.TernaryRelationshipsIfeachpolicyisownedbyjust1employee:KeyconstraintonPolicieswouldmeanpolicycanonlycover1dependent!Whataretheadditionalconstraintsinthe2nddiagram?agepnameDependentsCoversnameEmployeesssnlotPoliciespolicyidcostBeneficiaryagepnameDependentspolicyidcostPoliciesPurchasernameEmployeesssnlotBaddesignBetterdesignBinaryvs.TernaryRelationships(Contd.)ThekeyconstraintsallowustocombinePurchaserwithPoliciesandBeneficiarywithDependents.ParticipationconstraintsleadtoNOTNULLconstraints.WhatifPoliciesisaweakentityset?CREATETABLEPolicies(policyidINTEGER,costREAL,ssnCHAR(11)NOTNULL,PRIMARYKEY(policyid).FOREIGNKEY(ssn)REFERENCESEmployeesONDELETECASCADE)CREATETABLEDependents(pnameCHAR(20),ageINTEGER,policyidINTEGER,PRIMARYKEY(pname,policyid).FOREIGNKEY(policyid)REFERENCESPoliciesONDELETECASCADE)聚集的转换1.需要定义以下三个实体集Employees,Projects,Departments2.定义Sponsors联系集,它的关键字是(pid,did),外关键字是pid,did3.定义Monitors联系集,它的关键字是(pid,did,ssn),外关键字分别是(pid,did)参考于Sponsors关系,ssn参考于Employees关系.RelationalModel:SummaryAtabularrepresentationofdata.Simpleandintuitive,currentlythemostwidelyused.Integrityconstraintscanbespecifiedbyth