parttwodatabasedesign(编辑修改稿)

parttwodatabasedesign(编辑修改稿)内容摘要：

the primary key  need to consider semantics of relationship set in selecting the primary key in case of more than one candidate key Keys For Entity Sets (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 33  Keys for relationship sets R on entities E1, E2, …, En  R = {(e1, e2, …, e n) | e1∈ E1,…, e n∈ En} ⊆E1 ╳ E2 ╳ … ╳ En , how to uniquely distinguish each relationship instances {(e1, e2, …, e n) ?  R is the bination of E1, E2, …, En, each Ei can be uniquely distinguished by primary_key(Ei), 1 ≤i ≤n, so the set of all attributes in primary_key(E1), primary_key(E2), …, primary_key(En) can be used to recognize (e1, e2, …, e n) Keys For Relationship Sets March 2020 Database System Concepts Chapter6 Database Design and ER Model 34  The super_key for R  primary_key(E1) ∪ primary_key(E2) …. ∪ primary_key(En)  . in , (customerid, accountnumber) is the super key of depositor  note  if the attribute names of primarykeys are not unique, the attributes with the same names should be renamed  The candidate keys for R  minimal, nonredundant super keys Keys For Relationship Sets (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 35  The candidate keys or the primary key for a binary relationship set R among entity sets A and B can be decided as follows, in accordance with the mapping cardinality of R  R is manytomany, primary_key(R) = primary_key(A) ∪ primary_key(B)  R is manytoone from A to B, !! primary_key(R) = primary_key(A)  R is onetomany, !! primary_key(R) = primary_key(B) Keys For Relationship Sets (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 36  R is onetoone, primary_key(R) = primary_key(A) or: primary_key(R) = primary_key(B) Keys For Relationship Sets (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 37  The participation of an entity E in a relationship R is total, if  every entity in E participates in at least one relationship in R  . participation of loan in borrower is total  refer to  every loan must have a customer associated to it via borrower  The participation of an entity E in a relationship R is partial, if  some entities in E may not participate in any relationship in R  . participation of customer in borrower is partial  refer to Fig. 167。 Participate Constraints March 2020 Database System Concepts Chapter6 Database Design and ER Model 38 A B R . . A B R (b) total participation (a) partial participation Fig. Total/participation participation Participate Constraints (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 39 Fig. Total participation of an entity set in a relationship set partial total Participate Constraints (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 40  Cardinality limits (参与的基数界限 ) are used to express quantitative constraints on participation  /* 设联系 R关联了 entity sets A和 B, 为 定量地 描述 A参与 R的 total/partial participation 和 A中的 entity与 B中的 entity的 mapping cardinality, 引入实体参与联系的 cardinality limits  A参与 R的基数下界 lA和上界 hA , refer to  A中的每个实体 a通过 R关联了最少 lA个、最多 hA个 B中实体 b  lA : 对 A中的每个实体 a, B中至少有 lA个实体 b通过 R与其对应 /关联  hA:对 A中的每个实体 a, B中至多有 hA个实体 b通过 R与其对应 /关联 Cardinality Limits for Participation (pp219220) a1 a2 a3 am b1 b2 b3 … bn1 bn bj … lA…. hA … A R B lB…. hB Illustration for cardinality limits lA=1, hA=3 lB=0, hB =2 March 2020 Database System Concepts Chapter6 Database Design and ER Model 42 Cardinality limits on the relationship set Cardinality Limits for Participation (cont.) one partial many total pared with March 2020 Database System Concepts Chapter6 Database Design and ER Model 43  Note  * indicates no limit  in some textbooks, 将 lA ,hA 放在 ER图中的 B处  Cardinality limits vs total/partial participation  lA =0: A is partial participation of R  lA 0: A is total participation of R, equivalent to double line ( . in , ) Cardinality Limits for Participation (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 44  Cardinality limits vs mapping cardinality  设联系 R关联了实体集 A和 B, 利用 A的 基数界限 lA , hA中的 hA 、 B的 基数界限 lB , hB中的 hB , 可推导出联系 R的映射基约束  hB , hA 表示了联系 R的 从 A到 B的映射基约束 !!!! . in , considering mapping cardinality form customer to loan mapping cardinality form customer to loan depends on hloan, hcustomer for customer, hcustomer=*, for loan, hloan =1 so, hloan, hcustomer = 1, *, and is one to many Cardinality Limits for Participation (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 45  The associations between cardinality limits and the mapping cardinality are classified as follows, and illustrated by Cardinality Limits for Participation (cont.) b5 a4 From A to B, one to one (1:1), lA , hA = 1, 1, lB , hB = 0, 1。 From A to B, one to many (1:2), lA , hA = 0, 2, lB , hB = 1, 1 Associations between cardinality limits and mapping cardinality Form A to B, many to one (2:1), lA , hA = 1, 1, lB , hB = 1, 2 Form A to B, many to many (2:2) lA , hA = 1, 2 lB , hB = 1, 2 March 2020 Database System Concepts Chapter6 Database Design and ER Model 48 167。 ER diagrams  Rectangles represent entity sets  Diamonds represent relationship sets  Lines link attributes to entity sets and entity sets to relationship sets.  Ellipses represent attributes  double ellipses represent multivalued attributes  dashed ellipses denote derived attributes  Underline ind。