Transcript Chapter 11 & 12 Entity Relationship Modeling (ER) Pg 342~ 386

Chapter 11 & 12
Entity Relationship Modeling
(ER)
Thomas Connolly, Carolyn Begg, Database System, A
Practical Approach to Design Implementation and
Management, 4th Edition, Addison Wesley
Pg 342~ 386
Learning Outcomes
•
•
•
•
•
•
Entity types
Relationship types
Attributes
Strong and weak entity type
Attributes on relationship
Structural constraints
Learning Outcomes
•
•
•
•
Problem with ER Models
Specialization / generalization
Aggregation
Composition
Characteristics of E-R Model
• Semantic data model
• Express the logical properties of an
enterprise database
• Design tools and documentation for data
base structure
• No physical DBMS
• Proposed by Dr. Peter Chen
• Unified Modeling Language (UML)
Components of E-R Model
•
•
•
•
•
Entity
Attribute
Key
Relationship
Structural constraints on relationship
Entity
• Definition
– An object or concept
• Types
– Strong entity (parent, owner, dominant)
– Weak entity (child, dependent, or subordinate)
• Diagram Notation
– Rectangular
Attribute
• Domain
• Types
–
–
–
–
–
Single
Composite
Single-valued
Multi-valued
Derived
Key
• Candidate key
• Primary key
• Composite key
Relationship
• Definition
– Association among entities
• Diagram Notation
– Line – (relation or role name)
• Degree of a relationship
– Number of participating entities
– Types
•
•
•
•
Unary (recursive relationship)
Binary
Ternary
Quaternary
Structural Constraints on
Relationship
• Cardinality constraints
–
–
–
–
Zero-to-one 0..1
Zero-to-many 0..*
One-to-many 1..*
Many-to-many *..*
• Participation constraints
– Total (mandatory, every one involved)
– Partial (optional, only some involved)
• Improper relationship
– Fan trap (ambiguous pathway)
– Chasm trap (missing pathway)
Enhanced Entity-Relationship Model
• Additional entity types
– Superclass: including one or more distinct subgroups in the data model
– Subclass: a distinct subgroup of an entity type in the data model
• Attribute Inheritance
– Specialization hierarchy (specialization: maximizing the differences
between members of an entity by identifying their distinguishing
characteristics)
– Generalization hierarchy (generalization: minimizing the differences
between entities by identifying their common characteristics)
– Is-A hierarchy
• Constraints on specialization/generalization
– Participation (mandatory, optional)
– Disjoint: disjoint (or), non-disjoint (and)
• Other
– Aggregation (has a or is part of)
– Composition (strong ownership of aggregation)
Design Steps
• Identify
–
–
–
–
–
–
Entity types, relationship types
Cardinality and participation constraints
Attributes
Keys
Specialize/generalize
EER diagram
• EER model example
• Create an enhanced ER diagram for a rental
management using following entities:
– Rental agency
– Staff
• Part time
• Full time
– Owner
– Renter
– Property
• Business
• Home