Entity Relationship Modeling

1
Entity Relationship Modeling
2
Database Design

• Requirements analysis
• Conceptual database design
• Logical database design
• Schema refinement
• Physical database design
• Security design
• Tuning

3
Alternative Terminology
4
Conceptual Model

• Global view of data
• Basis for identification and description of main
  data items
• Hardware and software independent
• The result of conceptual design

5
The Entity Relationship (E-R) Model

• Main Components
• Entities
• Attributes
• Relationships
• Descriptive attributes
• ERD visual representation of E-R Model

6
Entities

• are objects (physical or conceptual) in the real
  world, which are distinguishable from other
  objects
• Entity occurrence (entity instance)
• Entity sets
• Attributes
• An instance of an entity set

7
Attribute Values

• Domain
• Atomic (simple) vs. composite
• Single-valued vs. multi-valued
• Derived

8
Erratum (Seventh ed.)
composite
9
Keys

• Composite key a key containing more than one
  attribute
• A candidate key irreducible subset of
  attributes whose values uniquely identify an
  entity in the set
• A primary key
• An alternative key

10
Relationships

• Are associations among two or more entities
• Relationship set
• Instance of a relationship set
• Descriptive Attributes
• A relationship is uniquely identified by
  participating entities

11
Degree of a relationship set

• Indicates number of associated entity sets
• Binary
• Ternary
• Quaternary
• Unary - Recursive relationships

12
Constraints

• Connectivity the number of possible occurrences
  of an entity set that may relate to a single
  occurrence of an associated entity set through a
  particular relationship
• One-to-one
• One-to-many
• Many-to-many

13
Cardinality of a Relationship Set

• Describes the maximum number of possible
  relationship occurrences for an entity set
  participating in a given relationship set
• Assigns a specific value to connectivity

14
Connectivity for Complex Relationships

• Complex relationships are relationships of degree
  3 or higher
• Connectivity the number of possible occurrences
  of an entity set in an n-ary relationship when
  the other (n-1) values of participants are fixed

15
Connectivity and Cardinality in an ERD
16
Constraints (continued)

• One-to-many (Key constraint ) Each entity from
  an instance of entity set E appears in at most
  one relationship in a correspondent instance of
  the relationship set R
• Participation constraints
• Mandatory (total) participation
• Optional (partial) participation

17
Weak Entities

• Existence dependence
• Doesnt have a primary key
• A partial key - some attributes of the weak
  entity
• Identifying owner an entity which determines
  the existence of the weak entity

18
Identifying Relationships

• A relationship between weak entity and
  identifying owner
• One-to-many (one owner to one or more weak
  entities)
• Mandatory participation of weak entity set
• Primary key of a weak entity set is the primary
  key of identifying owner along with partial key
  of weak entity set

19
Relationship Strength

• Weak (non-identifying)
• One entity is existence-independent on another
• PK of related entity doesnt contain PK component
  of another entity
• Strong (identifying)
• A relationship between weak entity set and
  identifying owner
• PK of weak entity contains PK component of
  identifying owner entity

20
Composite Entity Sets

• Used to bridge between MN relationships

21
Generalization Hierarchy

• ISA hierarchy
• Supertype versus subtype
• Inheritance
• Two ways to view a hierarchy
• Specialization
• Generalization

22
Generalization Hierarchy (cont)

• Overlapping constraints
• Disjoint (non-overlapping) subtypes use a d
  (G) symbol
• Overlapping subtypes use a o (Gs) symbol
• Completeness (Covering) constraints
• Total
• Partial
• No symbol is available

23
Generalization Hierarchy (cont)

• Reasons to use Generalization (ISA) hierarchy
• To add extra attributes to a part of entities
  from a set
• To specify a relationship with a subset of an
  entity set

24
Erratum (Sixth ed.)
Supertype/Subtype Relationship in an ERD
Figure 4.57 (3.42)
25
Comparison of ERD Symbols
26
Additional ERD Symbols for Chen Notation
A_name
Attribute
Primary Key
A_name
A_name
Derived Attribute
A_name
Multi-Valued Attribute
Strong Relationship Set
R_name
27
Conceptual Database Design

• Entity versus entity attribute
• need to record a set of values
• Entity versus relationship attribute
• need to record a set of values
• Binary versus ternary relationships

28
Conceptual Database Design

• Business rule brief and precise description of
  a policy, procedure, or principle within a
  specific organizations environment
• ERD must reflect business rules
• Main point
• If we cannot express all necessary business rules
  with a current model, the model must be revised

29
Conceptual design Tips

• 11 relationships may indicate a mistake in
  conceptual model
• Eliminate composite attributes
• Eliminate multi-valued attributes
• Eliminate derived attributes

30
Developing an ERD

• Iterative Process
• Step1 General narrative of organizational
  operations is developed
• Step2 Basic E-R Model is depicted and reviewed
• Step3 Modifications are made to incorporate
  newly discovered E-R components
• Repeat process until designers and users agree
  E-R Diagram complete

31
Conceptual Design of Enterprisewide Database

• Global design (centralized)
• All conflicts are resolved on the step of
  requirements analysis
• A global conceptual model and schema are produced
• Integration (decentralized)
• Multiple conceptual schemas are developed
• Conflicts are resolved on tuning step
• The final schema is built based on integration of
  multiple schemas

32
Challenge of Database Design Conflicting Goals

• Database must be designed to conform to design
  standards
• High-speed processing may require design
  compromises
• Other concerns
• Security
• Performance
• Shared access
• Integrity