Entity Relationship Modeling - Computer Applications in Business, Slides of Data Structures and Algorithms

Download Entity Relationship Modeling - Computer Applications in Business and more Slides Data Structures and Algorithms in PDF only on Docsity!

THE ENTITY-

RELATIONSHIP (ER)

MODEL

CHAPTER 7 (6/E)

CHAPTER 3 (5/E)

LECTURE OUTLINE

 Using High-Level, Conceptual Data Models for Database Design

 Entity-Relationship (ER) model

• Popular high-level conceptual data model

 ER diagrams

• Diagrammatic notation associated with the ER model

A SAMPLE DATABASE APPLICATION

 Requirements gathered for COMPANY

• Employees, departments, and projects
• Company is organized into departments
• Department controls several projects
• Employee: require each employee’s name, Social Security number, address, salary, sex (gender), and birth date
• Keep track of the dependents of each employee

ER MODEL OVERVIEW

 ER model describes data in terms of:

• Entities and entity sets
  • Objects
• Relationships and relationship sets
  • Connections between objects
• Attributes
  • Properties that characterize or describe entities or relationships

ENTITY SETS

 Entity type or set

• Collection (or set) of similar entities that have the same attributes

 ER model defines entity sets, not individual entities

 But entity sets described in terms of their attributes

CATEGORIES OF ATTRIBUTES

 Simple (atomic) vs. composite attributes

 Single-valued vs. multivalued attributes

 Stored vs. derived attributes

 Key or unique attributes

• Attribute values constrained to be distinct for individual entities in entity set

WEAK ENTITY TYPES

 Entity types that do not have key attributes of their own

• Identified by their relationship to specific entities from another entity type

 Identifying relationship

• Relates a weak entity type to the identifying entity , which has the rest of the key - Dependent is meaningless in COMPANY DB independently of Employee - Identified by relationship to Employee - Dependent_name distinguishes one dependent from other dependents for the same employee: partial key

RELATIONSHIPS IN GENERAL

 Relationship

• Interaction between entities
• Indicator: an attribute of one entity refers to another entity
  • Represent such references as relationships not attributes

RELATIONSHIPS & RELATIONSHIP SETS

DIAGRAMMING RELATIONSHIP TYPE

 Diamond for relationship type

 Connected to each participating entity type

• Could be binary, ternary, or higher degree

 Remember :

• Represents a set of entities of each type, some of which are related to entities of the other type(s)
• Some entities might participate in several relationships
• Some entities might not participate in the relationship at all

USING ROLE NAMES

RELATIONSHIP CONSTRAINTS

 Cardinality ratio

• Specifies maximum number of relationship instances in which each entity can participate
• Types 1:1, 1:N, or M:N  Participation constraint
• Specifies whether existence of entity depends on its being related to another entity
• Types: total and partial
• Thus minimum number of relationship instances in which entities can participate: thus1 for total participation, 0 for partial
• Diagrammatically, use a double line from relationship type to entity type  Alternative: Structural constraint
• Generalization: specifying any min and max participation
• Replaces cardinality ratio numerals and single/double line notation
• 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
• max=N  finite, but unbounded

SUMMARY OF ER DIAGRAM SYMBOLS

⟹ 1 E 1 entity can be related to N E 2 entities

REFINING EXAMPLE ER DESIGN

 Recall preliminary ER design

 Change attributes that reference entity types into relationship types

• Weak entities use identifying relationship

 Determine cardinality ratio and participation constraints for each relationship type

• Weak entity type always has structural constraint of (1,1)

participation in identifying relationship 21