1 / 43

Chapter 2 Entity-Relationship Model

Chapter 2 Entity-Relationship Model. 2.1 Introduction 2.2 Basic concepts 2.3 mapping constrains 2.4 keys 2.5 Entity Relationship Diagram 2.6 Weak Entity Sets 2.7 Extended E-R Features 2.8 Reduction of an E-R Schema to Table. 2.1 Introduction.

patricia-villarreal
Download Presentation

Chapter 2 Entity-Relationship Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 2 Entity-Relationship Model • 2.1 Introduction • 2.2 Basic concepts • 2.3 mapping constrains • 2.4 keys • 2.5 Entity Relationship Diagram • 2.6 Weak Entity Sets • 2.7 Extended E-R Features • 2.8 Reduction of an E-R Schema to Table

  2. 2.1 Introduction • We can characterize the overall approach to the semantic modeling problem in terms of the following four steps: • First, identify a set of semantic concepts. ① we might agree that the world is made up of entities. ② we might go further and agree that entities can usefully be classified into entity types. ③ we might go still further and agree that every entity has a special property that severs to identify that entity. ④ we might go further again and agree that any entity can be related to other entities by means of relationships.

  3. 2.1 Introduction • Next we try to devise a set of corresponding symbolic objects that can be used to represent the foregoing semantic concepts. • We also devise a set of formal, general integrity rules to go along with those formal objects. • Finally, we also develop a set of formals operators for manipulating those formal objects.

  4. 2.2 Basic Concepts • There are three basic notions that the E-R data model employs: entity sets, relationship sets, and attributes. • 1.Entity Sets An entity is a “thing ” or “object” in the real world that is distinguishable from all other objects. An entity has a set of properties, and the values for some set of properties may uniquely identify an entity, an entity may be concrete or it may be abstract. An entity set is a set of entities of the same type that share the same properties, or attributes. Entity set do not need to be disjoint. An entity is represented by a set of attributes. For each attribute, there is a set of permitted values, called the domain, or value set, of that attribute.

  5. customer-address customer-name street city state postal-code first-name middle-initial last-name street-number street-name apartment-number 2.2 Basic Concepts • An attribute, as used in the E-R model, can be characterized by the following attribute types: 1)simple and composite attributes the simple attributes are not divided into subparts composite attributes can be divided into subparts Example: Figure 2.2 composite attributes customer-name and customer-address

  6. 2.2 Basic Concepts 2)single-valued and multivalued attributes the attributes that have a single value for a particular entity is called single-valued attributes. an attribute that has a set of values for a specific entity is called multivalued attributes. Example:phone-number, dependent-name 3)null attributes A null value is used when an entity does not have a value for an attribute. an attribute value is unknown : missing (name) or not known(apartment-number) 4)derived attribute the value for this type of attribute can be derived from the values of other related attributes or entities. age=date–date-of-birth

  7. 2.2 Basic Concepts 2.Relationship Sets A relationship is an association among several entities. A relationship set is a set of relationships of the same type. A relationship set is a mathematical relation on n≥2 entity sets. If E1,E2,……En are entity sets, then a relationship set R is a subset of {(e1,e2,……en) ︱e1∈E1, e2∈E2…… en∈En} The entities involved in a given relationship are said to be participants in that relationship. The number of participants in a given relationship is called the degree of that relationship.

  8. 321-12-3123 Jones Main Harrison L-17 1000 019-28-3746 Smith North Rye L-23 1000 677-89-9001 Hayes Main Harrison L-15 1500 555-55-5555 Jackson Dupont Woodside L-14 1500 244-66-8800 Curry North Rye L-19 500 963-96-3969 Williams Nassau Princeton L-11 900 335-57-7991 Adams Spring Pittsfield L-16 1300 loan customer Figure 2.3 Relationship set borrower

  9. 2.3 Mapping constrains • An E-R enterprise schema may define certain constraints to which the contents of a database must conform. 1.Mapping cardinalities mapping cardinalities, of cardinality ratios, express the number of entities to which another entity can be associated via a relationship set. For a binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following: 1)one to one : an entity in A is associated with at most one entity in B, and entity in B is associated with at most one entity in A. 2)one to many: an entity in A is associated with any number of entities in B. An entity in B, however, can be associated with at most one entity in A.

  10. A B A B A A B B a1 b1 a1 a1 b1 b1 a1 b1 a2 b2 a2 b2 a2 b2 a3 b3 b2 a2 a3 b3 b3 a3 a4 b4 a3 b3 a4 a4 b4 b4 a5 b5 Many to many One to many One to one Many to one 2.3 Mapping constrains 3)many to one: An entity in A is associated with at most one entity in B. An entity in B, however, can be associated with any number of entities in A. 4)many to many: An entity in A is associated with any number of entities in B, and an entity in B is associated with any number of entities in A.

  11. 2.3 Mapping constrains 2. Existence Dependencies If the existence of entity x depends on the existence of entity y, then x is said to be existence dependent on y. Operationally, if y is deleted, so is x. Entity y is said to be a dominant entity, and x is said to be a subordinate entity. The participation of an entity set E in a relationship set R is said to betotal if every entity in E participates in at least one relationship in R. If only some entities in E participate in relationship in R, the participation of entity set E in relationship R is said to be partial.

  12. superkey candidate keys primary key 2.4 key • A key allows us to identify a set of attributes that suffice to distinguish entities from each other. (relationships) • 1. Entity Sets superkey : superkey is a set of one or more attributes, that, taken collectively, allows us to identify uniquely an entity in the entity set. candidate keys: minimal superkeys primary key: a candidate key that is chosen by the database designer as the principal means of identifying entities within an entity set.

  13. 2.4 key 2. Relationship Sets Let R be a relationship set involving entity sets E1,E2,……En. Let primary-key(Ei) denote the set of attributes that forms the primary key for entity set Ei. The composition of the primary key for a relationship set depends on the structure of the attributes associated with the relationship set R. If the relationship set R has no attributes associated with it, then the set of attributes primary-key(E1) ∪primary-key(E2)∪…… ∪primary-key(En) describes an individual relationship in set R.

  14. 2.4 key • If the relationship set R has attributes a1,a2,……an associated with if, then the set of attributes. primary-key(E1) ∪primary-key(E2) ∪…… ∪ primary-key(En) ∪{a1,a2,……an } describes an individual relationship in set R. In both of the above cases, the set of attributes. primary-key(E1) ∪primary-key(E2) ∪…… ∪ primary-key(En) from a superkey for the relationship set. The structure of the primary key for the relationship set depends on the mapping cardinality of the relationship set.

  15. 2.5 Entity Relationship Diagram • A E-R diagram consists of the following major components: • 1. Rectangles, which represent entity sets • 2. Ellipses, which represent attributes • 3. Diamonds, which represent relationship sets • 4. Lines, which link attributes to entity sets and entity sets to relationship sets • 5. Double ellipses, which represent multivalued attributes • 6. Dashed ellipses, which denote derived attributes • 7. Double links, which indicate total participation of an entity in a relationship set

  16. borrower M M loan customer customer-name customer-street loan-number amount customer-id customer-city borrower loan customer borrower 1 M loan customer borrower 1 M loan customer 2.5 Entity Relationship Diagram many to many one to many many to one one to one

  17. customer-name customer-street account-number balance customer-id customer-city depositor account customer 2.5 Entity Relationship Diagram We have the access-date descriptive attribute attached to the relationship set depositor to specify the most recent date on which a customer accessed that account. access-date Figure 2.10 E-R diagram with an attribute attached to a relationship set

  18. street-name middle-initial street-number apartment-number first-name last-name street city composite attributes name customer-id address state customer zip-code phone-number date-of-birth age 2.5 Entity Relationship Diagram Example: multivalued attributes derived attributes

  19. employee-name employee-id telephone-number manager works-for employee worker 2.5 Entity Relationship Diagram role Figure2.12 E-R diagram with role indicators

  20. title level job employee-name street branch-name branch-city employee-id city works-on branch assets employee 2.5 Entity Relationship Diagram Figure2.13 E-R diagram with a ternary relationship

  21. customer-name customer-street loan-number amount customer-id customer-city borrower loan customer 2.5 Entity Relationship Diagram total participation Figure2.14 Total participation of an entity set in a relationship set

  22. customer-name customer-street loan-number amount customer-id customer-city borrower loan customer 2.5 Entity Relationship Diagram 0..* 1..1 Figure2.15 Cardinality limits on relationship sets l..h: lminimum cardinalityhmaximum cardinality A minimum value of 1 : total participation A maximum value of 1 : the entity participates in at most one relationship A maximum value * : no limit1..* =total participation

  23. loan-number payment-number payment-date payment-amount amount loan-payment M 1 loan payment primary key of payment = loan-number+payment-number primary key of loan discriminator 2.6 Weak Entity Sets • An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. discriminator weak entity set total participation doubly outlined box doubly outlined diamond strong entity set

  24. Example:(E-R) partly • A manufacturing company will wish to record information about :the projects it has on hand;the parts used in those projects;the suppliers who supply those parts;the employees who work on those projects;the departments which employees belong to; the employee’s dependent . • The employee comprise those information: the number of the employee, employee’s name, (include first name ... ) and the salary. • The supplier comprise those information: the number of the supplier, the supplier’s name, supplier’s address(include status and city) • A employee(manager,worker) must belong to a department and may take part in more than one projects • A manager is responsible for more than one projects but a project should assign to one manger • A supplier provide a certain quantity parts to projects.

  25. DEPART MENT CITY S# SNAME STATUS 1 DEPT-EMP SUPPLIER SALARY PROJ- WORK M M EMP# M M M SUPP_ PART SUPP PART_PROJ EMPLOYEE M PROJECT ENAME 1 PROJ- MANAGER M M 1 M QTY FIRST MI LAST PART EMP_DEP M M M PART_ STRUCTURE DEPENDENT QTY one to many many to many total participation composite attributes weak entity set

  26. 2.7 Extended E-R Features • 1. Specialization • An entity set may include subgroupings of entities that are distinct in some way from other entities in the set. The process of designating subgroupings within an entity set is specialization. • Specialization emphasizes differences among entities within the set • 2. Generalization • This commonality can be expressed by generalization, which is a containment relationship that exists between a higher-level entity set and one or more lower -level entity sets. • generalization emphasizes commonalties among entity sets ISA

  27. name street city person specialization credit-rating salary employee customer officer teller secretary generalization ISA ISA office-number hours-worked station-number hours-worked employee is a person officer is a employee Figure2.17 Specialization and generalization

  28. person account ISA ISA employee customer savings- account checking- account 2.7 Extended E-R Features Total(double line) partial(line) disjoint (d) overlapping(o) partial participation total participation overlapping disjoint

  29. 2.7 Extended E-R Features • 3. Attribute Inheritance • A crucial property of the higher-and lower-level entities created by specialization and generalization is attribute inheritance. The attributes of the higher-level entity sets are said to be inherited by the lower-level entity sets. • If an entity set is a lower-level entity set in more than one ISA relationship, the resulting structure is said to be a lattice. • A higher-level entity set with attributes and relationships that apply to all of its lower-level entity sets. • Lower-level entity sets with distinctive features that apply only within a particular lower-level entity set.

  30. job works-on branch employee manages manager 2.7 Extended E-R Features 4. Aggregation • One limitation of the E-R modal is that it is not possible to express relationships among relationships. redundant relationships Figure2.18 E-R diagram with redundant relationships

  31. job works-on branch employee manages manager 2.7 Extended E-R Features • Aggregation is an abstraction through which relationship are treated as higher-level entities. aggregation higher-level entities Figure2.19 E-R diagram with aggregation

  32. employee-name employee-id telephone-number employee 2.8 Reduction of an E-R Schema to Tables • A database that conforms to an E-R database schema can be represented by a collection of tables. 1. Tabular Representation of Strong Entity Sets In general, if we have a table of n columns. We denote the Cartesian product of D1,D2……Dn by D1×D2 ×…… ×Dn-1 ×Dn. • 1. Tabular Representation of Strong Entity Sets employee

  33. loan-number payment-number payment-date payment-amount amount loan-payment M 1 loan payment 2.8 Reduction of an E-R Schema to Tables • 2. Tabular Representation of Weak Entity Sets Let A be a weak entity set with attributes a1,a2……an. Let B be the strong entity set on which A is dependent. Let the primary key of B consist of attributes b1,b2……bn. {a1,a2……am} ∪{b1,b2……bn} payment

  34. access-date customer-name customer-street account-number balance customer-id customer-city depositor account customer 2.8 Reduction of an E-R Schema to Tables 3. Tabular Representation of Relationship Sets depositor

  35. loan-number payment-number payment-date payment-amount amount loan-payment M 1 loan payment 2.8 Reduction of an E-R Schema to Tables ⑴ Redundancy of Tables In general, the table for the relationship set linking a weak entity set to its corresponding strong entity set is redundant and does not need to be present in a tabular representation of an E-R diagram. loan-payment payment redundancy

  36. account-number balance branch-name branch-city assets account- branch account branch 2.8 Reduction of an E-R Schema to Tables ⑵ Combination of Tables (1 to many) account- branch account combination

  37. customer-street customer-id customer customer-address customer-city customer-name 2.8 Reduction of an E-R Schema to Tables 4. Composite Attributes customer

  38. employee-id employee … dependent-name … 2.8 Reduction of an E-R Schema to Tables 5. Multivalued Attributes New tables should be created for multivalued attributes. For a multivalued attributes M, we create a table T with a column C that corresponds to M and columns corresponding to the primary key of the entity set or relationship set of which M is an attribute. dependent-name

  39. account account-number balance savings-account checking-account ISA interest-rate overdraft-amount 2.8 Reduction of an E-R Schema to Tables 6. Tabular Representation of Generalization

  40. 2.8 Reduction of an E-R Schema to Tables 6. Tabular Representation of Generalization 1、Create a table for the higher-level entity set. For each lower-level entity set, create a table that includes a column for each of the attributes of that entity set plus a column for each attribute of the primary key of the higher-level entity set. account checking-account savings-account

  41. 2.8 Reduction of an E-R Schema to Tables 2、If the generalization is disjoint and complete then we should not create a table for the higher-level entity set. Instead, for each lower-level entity set, create a table that includes a column for each of the attributes of that entity set plus a column for each attribute of the higher-level entity set. savings-account checking-account

  42. borrower loan customer loan-number customer-name loan-officer employee-number employee 2.8 Reduction of an E-R Schema to Tables 7. Tabular Representation of Aggregation loan-officer

  43. Exercises: P72: 2.1 2.3 2.5 2.6 2.9 2.12

More Related