Chapter 03 Conceptual Design ERD Modelling Pearson Education

Chapter 03 Conceptual Design & ERD Modelling Pearson Education © 2009 1

Chapter 12 - Objectives u How to use Entity–Relationship (ER) modeling in database design. u Basic concepts associated with ER model. u Diagrammatic technique for displaying ER. u How to identify and resolve problems with ER models called connection traps. u Concepts of Enhanced ER (EER) model. u Constraints on Specialization / Generalization Pearson Education © 2009 2

Concepts of the ER Model u Entity types u Relationship types u Attributes Pearson Education © 2009 3

Entity Type u Entity type – Group of objects with same properties, identified by enterprise as having an independent existence. u Entity occurrence – Uniquely identifiable object of an entity type. Pearson Education © 2009 4

Examples of Entity Types Pearson Education © 2009 5

Relationship Types u Relationship type – Set of meaningful associations among entity types. u Relationship occurrence – Uniquely identifiable association, which includes one occurrence from each participating entity type. Pearson Education © 2009 6

ER Diagrams, Naming Conventions 7

Relationship Types u Degree of a Relationship – Number of participating entities in relationship. u Relationship of degree : – one is unary (recursive) – two is binary – three is ternary – four is quaternary. Pearson Education © 2009 8

Recursive relationship called Supervises with role names Pearson Education © 2009 9

Relationship Types u Recursive Relationship – Relationship type where same entity type participates more than once in different roles. u Relationships may be given role names to indicate purpose that each participating entity type plays in a relationship. Pearson Education © 2009 10

Binary relationship called POwns Pearson Education © 2009 11

Ternary relationship called Registers Pearson Education © 2009 12

Quaternary relationship called Arranges Pearson Education © 2009 13

Entities associated through two distinct relationships with role names Pearson Education © 2009 14

Attributes u Attribute – Property of an entity or a relationship type. u Attribute Domain – Set of allowable values for one or more attributes. Pearson Education © 2009 15

Attributes u Simple Attribute – Attribute composed of a single component with an independent existence. u Composite Attribute – Attribute composed of multiple components, each with an independent existence. Pearson Education © 2009 16

Attributes u Single-valued Attribute – Attribute that holds a single value for each occurrence of an entity type. u Multi-valued Attribute – Attribute that holds multiple values for each occurrence of an entity type. Pearson Education © 2009 17

Attributes u Derived Attribute – Attribute that represents a value that is derivable from value of a related attribute, or set of attributes, not necessarily in the same entity type. Pearson Education © 2009 18

Keys u Candidate Key – Minimal set of attributes that uniquely identifies each occurrence of an entity type. u Primary Key – Candidate key selected to uniquely identify each occurrence of an entity type. u Composite Key – A candidate key that consists of two or more attributes. Pearson Education © 2009 19

ER diagram of Staff and Branch entities and their attributes Pearson Education © 2009 20

Entity Type u Strong Entity Type – Entity type that is not existence-dependent on some other entity type. u Weak Entity Type – Entity type that is existence-dependent on some other entity type. Pearson Education © 2009 21

Strong entity type called Client and weak entity type called Preference Pearson Education © 2009 22

Relationship called Advertises with attributes Pearson Education © 2009 23

Structural Constraints u Main type of constraint on relationships is called multiplicity. u Multiplicity - number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship. u Associate a pair of integer numbers (min, max) with each participation of an entity type E in a relationship type R, where 0 ≤ min ≤ max and max ≥ 1 u Represents user policies (called business rules) Pearson Education © 2009 24

Structural Constraints u The most common degree for relationships is binary. u Binary relationships are generally referred to as being: – one-to-one (1: 1) – one-to-many (1: *) – many-to-many (*: *) Pearson Education © 2009 25

Structural Constraints u Multiplicity is made up of two types of restrictions on relationships: cardinality and participation. Pearson Education © 2009 26

Structural Constraints u Cardinality – Describes maximum number of possible relationship occurrences for an entity participating in a given relationship type. u Participation – Determines whether all or only some entity occurrences participate in a relationship. Pearson Education © 2009 27

Multiplicity as cardinality and participation constraints Pearson Education © 2009 28

Semantic net of Staff Manages Branch relationship type Pearson Education © 2009 29

Multiplicity of Staff Manages Branch (1: 1) relationship Pearson Education © 2009 30

Semantic net of Staff Oversees Property. For. Rent relationship type Pearson Education © 2009 31

Multiplicity of Staff Oversees Property. For. Rent (1: *) relationship type Pearson Education © 2009 32

Semantic net of Newspaper Advertises Property. For. Rent relationship type Pearson Education © 2009 33

Multiplicity of Newspaper Advertises Property. For. Rent (*: *) relationship Pearson Education © 2009 34

Summary of multiplicity constraints Pearson Education © 2009 35

ER diagram of Branch user views of Dream. Home Pearson Education © 2009 36

37

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

Problems with ER Models u Fan Trap – Where a model represents a relationship between entity types, but pathway between certain entity occurrences is ambiguous. u Chasm Trap – Where a model suggests the existence of a relationship between entity types, but pathway does not exist between certain entity occurrences. Pearson Education © 2009 39

An Example of a Fan Trap Pearson Education © 2009 40

Semantic Net of ER Model with Fan Trap u At which branch office does staff number SG 37 work? Pearson Education © 2009 41

Restructuring ER model to remove Fan Trap Pearson Education © 2009 42

Semantic Net of Restructured ER Model with Fan Trap Removed u SG 37 works at branch B 003. Pearson Education © 2009 43

An Example of a Chasm Trap Pearson Education © 2009 44

Semantic Net of ER Model with Chasm Trap u At which branch office is property PA 14 available? Pearson Education © 2009 45

ER Model restructured to remove Chasm Trap Pearson Education © 2009 46

Semantic Net of Restructured ER Model with Chasm Trap Removed Pearson Education © 2009 47

Enhanced Entity-Relationship Modeling 48

The (EER) Model § Enhanced ER (EER) model Created to design more accurate database schemas • Reflect the data properties and constraints more precisely § More complex requirements than traditional applications § 49

Subclasses, Superclasses, and Inheritance § § EER model includes all ER modeling concepts In addition, EER includes: § Subclasses and superclasses § Specialization and generalization § Category or union type § Attribute and relationship inheritance 50

Subclasses, Superclasses, and Inheritance § Subtype or subclass of an entity type § Subgroupings of entities that are meaningful § Represented explicitly because of their significance to the database application § Type inheritance § Subclass entity inherits all attributes and relationships of superclass 51

Utilizing Specialization and Generalization in Refining Conceptual Schemas § § § Specialization process § Start with entity type then define subclasses by successive specialization § Top-down conceptual refinement process Certain attributes may apply to some but not all entities of the superclass Some relationship types may be participated in only by members of the subclass 52

Generalization Reverse process of abstraction § Generalize into a single superclass § Original entity types are special subclasses § Generalization § Process of defining a generalized entity type from the given entity types § Generalization process § Involves bottom-up conceptual synthesis § 53

Constraints on Specialization and Generalization § § May be several or one subclass Determine entity subtype: § Predicate-defined (or condition-defined) subclasses § Attribute-defined specialization § User-defined 54

55

Constraints on Specialization and Generalization (cont’d. ) § Disjointness constraint § Specifies that the subclasses of the specialization must be disjoint Disjoint an entity can be a member of at most one of the subclasses of the specialization § Overlap that is the same entity may be a member of more than one subclass of the specialization § § Completeness (or totalness) constraint Every entity in the superclass must be a member of some subclass in the specialization/generalization § May be total or partial § § Disjointness and completeness constraints are independent 56

Specialization and Generalization Hierarchies and Lattices § § Specialization hierarchy § Every subclass participates as a subclass in only one class/subclass relationship § Results in a tree structure or strict hierarchy Specialization lattice § Subclass can be a subclass in more than one class/subclass relationship 57

58

Modeling of UNION Types Using Categories § Union type or a category § Represents a single superclass/subclass relationship with more than one superclass § Subclass represents a collection of objects that is a subset of the UNION of distinct entity types § Attribute inheritance works more selectively § Category can be total or partial § Some modeling methodologies do not have union types 59

Design Choices for Specialization/Generalization § If all subclasses of a specialization/generalization have few specific attributes and no specific relationships Can be merged into the superclass § Replace with one or more type attributes that specify the subclass or subclasses that each entity belongs to § 60