【正文】 Chapter6 Database Design and ER Model 57 Total participation of loan in borrower Partial participation of customer in borrower March 2020 Database System Concepts Chapter6 Database Design and ER Model 58 ER diagrams (cont.) ? Roles are indicated in ER diagrams by labeling the lines that connect diamonds to rectangles. ? Role labels are optional, and are used to clarify semantics of the relationship ? . in , the labels ―manager‖ and ―worker‖ are called roles。 Weak Entity Sets ? An entity set that does not have a primary key is referred to as a weak entity set ? . payment (number, date, amount) in in next slide, and Fig. ? A weak entity set E1 can only be distinguished through another (strong) entity set E2, called identifying/owner entity set, which has association/ relationship R with E1 ? E2 owns E1 , E1 must be related to E2 via an instance of R, called the identifying relationship. ? R is manytoone ( or onetoone) from the weak entity set E1 to the identity set E2 March 2020 Database System Concepts Chapter6 Database Design and ER Model 75 Weak Entity Sets (cont.) ? E1 is of total participation in the relationship set R ? R is depicted using a double diamond ? weak entity set is depicted as double rectangles. ER diagram with a weak entity set many one total E1 E2 loan1 (1, $10) loan2 (2, $10) payment(1, 2020/02/21, $3) payment(2, 2020/03/21, $3) payment(3, 2020/04/21, $4) payment(1, 2020/02/21, $3) payment(2, 2020/03/21, $3) payment(3, 2020/04/21, $4) one many A example of weak entity set E2 E1 Loan3(3, 20) March 2020 Database System Concepts Chapter6 Database Design and ER Model 77 ? The discriminator (or partial key) of a weak entity set is the set of attributes that distinguishes among all those entities in the weak entity set that depending on a particular strong entity ? . discriminator paymentnumber for weak entity set payment, which depends on loan ? the discriminator of a weak entity set is underlined with a dashed line Weak Entity Sets (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 78 ? The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity set’s discriminator ? primary key for weak entity set E1 = discriminator ∪ primary_key(E2) . ? . discriminator of the payment entity set: paymentnumber, primary key for payment: (loannumber, paymentnumber) ? Weak entity set E1 with several identifying entity sets E2, E3, .., En, primary key for weak entity set E1 = discriminator ∪ primary_key(E2) ∪ … ∪ primary_key(En) . Weak Entity Sets (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 79 ? Another example, in a university, ? a strong entity set ? course(cnumber, title, credits, syllabus, prerequisites) ? a weak entity ? courseoffering(year, semester, instructor(s), section number, timing, classroom) ? the discriminator of courseoffering would be semester (including year) and sectionnumber (if there is more than one section) ? if we model courseoffering as a strong entity we would model coursenumber as an attribute ? then the relationship with course would be implicit in the coursenumber attribute Weak Entity Sets (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 80 ? Other identifying method for the weak entity set E1 ? E1 can be expressed as a multivalued and posite attribute of the identifying entity set E2, refer to Appendix B Weak Entity Sets (cont.) March 2020 Database System Concepts Chapter6 Database Design and ER Model 81 ? Objectoriented (OO) ER ? specialization (特化，特殊化，例化） ? generalization (概括化，泛化，普遍化 ) ? attributes inheritance (屬性繼承） Specialization ? Specialization ? topdown design process of designating subgroups w。 how to represent the other properties of ―telephone‖ object ? ? model 2. entity: employee(employeename) telephone(telephonenumber, location, … ) relationship: emptelephone 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 indicates primary key attributes ? Double rectangles represent weak entity sets (refer to 167。 Mapping Cardinalities Note: some elements in A or B may not be mapped to any elements in the other set Fig. Mapping cardinalities –注意 manytoone和 onetomany 定義和方向 ！ One to one One to many a4 a5 Many to one Many to many Note: Some elements in A or B may not be mapped to any elements in the other set Fig. Mapping cardinalities –注意 manytoone和 onetomany 定義和方向！ b4 March 2020 Database System Concepts Chapter6 Database Design and ER Model 30 ? Key is a set of attributes ( of a entity set or relationship set) ? the values of these attributes in one entity can be used to uniquely distinguish this entity from others , or ? the values of these attributes in one relationship are used to uniquely identify the relationship ? Keys include ? superkey (超鍵 ), candidate key (候選鍵 ), primary key (主鍵 ) 167。 Constraints ? Data model = syntax + semantic/constraints + operations ? Semantic/constraints ? as integrity constraints in DBS, to maintain DBS consistency ? Three types of integrity constraints in ER model ? mapping cardinalities (167。 ) relational schema normalizing (chapter 7) Fig. DB design phases Application areas/problem