1 / 47

Chapter 11

Chapter 11. Entity-Relationship modeling Transparencies. Chapter 11 - Objectives. The use of high-level conceptual data models to support database design. The basic concepts associated with the Entity-Relationship (ER) model, a high-level conceptual data model.

clay
Download Presentation

Chapter 11

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 11 Entity-Relationship modeling Transparencies

  2. Chapter 11 - Objectives • The use of high-level conceptual data models to support database design. • The basic concepts associated with the Entity-Relationship (ER) model, a high-level conceptual data model. • A diagrammatic technique for displaying an ER model. 2

  3. Chapter 11 - Objectives • How to identify problems called connection traps, which may occur when creating an ER model. • The limitations of the basic ER modeling concepts and the requirements to model more complex applications using enhanced data modeling concepts. 3

  4. Concepts of the Entity-Relationship Model • Entity types • Relationship types • Attributes 5

  5. Entity Type • Entity Type • An object or concept that is identified by the enterprise as having an independent existence. • Entity • An object or concept that is uniquely identifiable. 7

  6. Examples of Entity Types 8

  7. Entity Type • Weak Entity Type (dependent entity) • An entity type that is existence-dependent on some other entity type (parent vs child entity). • Strong Entity Type (independent entity) • An entity type that is not existence-dependent on some other entity type. 9

  8. Diagrammatic Representation of Strong and Weak Entity Types 10

  9. Attributes • Attribute • A property of an entity or a relationship type. • Attribute Domain • A set of values that may be assigned to a single-valued attribute. 11

  10. Attributes • Simple Attribute (Salary) • An attribute composed of a single component with an independent existence. • Composite Attribute (Address) • An attribute composed of multiple components each with an independent existence. 12

  11. Attributes • Single-valued Attribute (Branch Number) • An attribute that holds a single-value for a single entity. • Multi-valued Attribute (Tel_No) • An attribute that holds multiple values for a single entity. 13

  12. Attributes • Derived Attribute • An attribute that represents a value that is derivable from the value of a related attribute or set of attributes, not necessarily in the same entity. 14

  13. Diagrammatic Representation of Entities and their Attributes 15

  14. Keys • Candidate Key • An attribute or set of attributes that uniquely identifies individual occurrences of an entity type. • Primary Key • An entity type may have one or more possible candidate keys, one of which is selected to be the primary key. 16

  15. Keys • Composite Key • A candidate key that consists of two or more attributes. (Advert entity – Property_no + Newspaper_name + Date_advert) 17

  16. Primary vs Foreign Keys • Rule: Primary keys are Indicated by the letters PK under the appropriate column heading(s) • Rule: Primary keys must never be null (PK implies NN) • Rule: Primary key column may not contain duplicate values (PK implies ND) Foreign Key • Rule: Foreign keys are Indicated by the letters FK under the appropriate column heading(s)

  17. Primary Key Selection ·Select Primary keys that are not likely to change. ·Select Primary keys that are short. ·Select Primary keys that are numeric. ·Select Primary keys that are familiar to the system user.

  18. Relationship Types • Relationship Type • A meaningful association among entity types. • Relationship (1:1; 1: M; M:M) • An association of entities where the association includes one entity from each participating entity type. 18

  19. Relationship Types • Degree of a Relationship • The number of participating entities in a relationship. • Recursive Relationship • A relationship where the same entity participates more than once in a different roles. 19

  20. Diagrammatic Representation of Entities, Relationships, and Primary Key Attributes 21

  21. Binary Relationship called Owns 22

  22. Ternary Relationship called SetsUp 23

  23. Quaternary Relationship called Arranges 24

  24. Recursive Relationship called Supervises 25

  25. Entities associated through two distinct Relationships 26

  26. Relationship called Views with attributes 27

  27. Structural Constraints • Two main types of restrictions on relationships are cardinality and participation constraints. • Cardinality Constraints (Ratio) (non-identifying) • Determines the number of possible relationships for each participating entity. • Most common degree for relationships is binary with cardinality ratios of one-to-one (1:1), one-to-many (1:M) or many-to-many (M:N). 28

  28. Structural Constraints • Participation Constraints (Identifying relationship) • Determines whether the existence of an entity depends on its being related to another entity through the relationship. 29

  29. Identifying Relationshipvs.Non-Identifying Relationship • Identifying Relationship An identifying relationship is a relationship between two entities in which primary key of one entity appears as a foreign key in the other entity as a key data element or set of data elements. For example, the relationship between parent entity and child entity is an identifying relationship.

  30. Identifying Relationshipvs.Non-Identifying Relationship ·Non-Identifying Relationship A non-identifying relationship is a relationship between two entities in which the primary key of one entity appears as a foreign key in the other entity as a non-key data element. For example, the relationship between an Organization entity and a Project entity is a non-identifying relationship. Additionally, the Organization identifier appears as a foreign key (as an element), but not part of the primary key in the Project entity.

  31. Types of Entities • Independent Entity (Fundamental entity) An independent entity is a group of data that exists without dependence upon any other entity. Therefore it does not have any identifying parent(s). Its primary key does not contain a foreign key from any other entity ·  Dependent Entity (Attribute Entity) A dependent entity (or child entity) is a group of data that cannot exist without the support of another entity (or parent entity). Its primary key contains a foreign key from another entity.

  32. Types of Entities ·Associative Entity An associative entity is an entity that exists when decomposing a many-to-many relationship between two entities. For example, a many-to-many relationship between an entity called Project and another called Organization would result inan associative entity called Project-Organization.

  33. Participation Constraints • Total (Mandatory) –connected by a double line. • Partial (Optional) – connected by a single line.

  34. Staff Manages Branch (1:1) Relationship 31

  35. Staff Oversees Property_for_Rent (1:M) Relationship 33

  36. Newspaper Advertises Property_for_Rent (M:N) Relationship 35

  37. Types of Associations 1:1 1:1 1,M 0,1 0,M 1,1 1,1 0.1 1,M 1.M

  38. Participation Constraints of Branch IsAllocated Staff Relationship 36

  39. Displaying Participation Constraints using (Min, Max) Notation Relationship: 5:N Branch ------------------- Staff ------------------ 0:1 37

  40. Relationship Branch Branch # (PK) Branch Tel # 2 123-4567 234-5678 3 Staff Staff # (PK) Staff Name Branch # (FK) 2 John 101 Dean 102

  41. A Hierarchy of Models Subject Level Denormalized Entity Normalized Entity

  42. Problems with ER Models • Problems may arise when designing a conceptual data model called connection traps. • Often due to a misinterpretation of the meaning of certain relationships. • Two main types of connection traps are called fan traps and chasm traps. 38

  43. Problems with ER Models • Fan Trap • When a model represents a relationship between entity types, but the pathway between certain entity occurrences is ambiguous. • Chasm Trap • When a model suggests the existence of a relationship between entity types, but the pathway does not exist between certain entity occurrences. 39

  44. An Example of a Fan Trap 40

  45. Restructuring ER model to remove Fan Trap 42

  46. An Example of a Chasm Trap 44

  47. ER Model restructured to remove Chasm Trap 46

More Related