Entity Relationship Diagrams (ERDs) Tutorial

Jun 18, 2024

Entity Relationship Diagrams (ERDs) Tutorial

Introduction

  • Taught by Dr. White from Business Analysis Doctor
  • Purpose: To illustrate database components and their relationships using ERDs
  • ERD - A data model to illustrate how entities (domain-specific) relate to each other within a system
    • Visual representation of database tables, columns, and relationships

Topics Covered

  1. What is an ERD?
  2. ERD Notations
  3. Main Components of ERDs
  4. Levels of Abstraction
  5. ERD Rules and Best Practices
  6. Example: Library Book Circulation

What is an ERD?

  • ERD: Illustrates how domain-specific entities relate within a system
  • Used to:
    • Establish a consistent vocabulary
    • Illustrate business rules
    • Define data requirements
    • Plan/set up relational databases

ERD Notations

  • Multiple styles depending on preferences/standards
  • Common notations: Chen, UML, Crow's Foot (used in this tutorial)

Main Components of ERDs

Entities

  • Represent nouns (person, object, concept)
  • Key components that store or produce information
  • Entity symbol includes entity name and description
  • Naming: Always as nouns (e.g., Book)
  • Noun Technique: Extract nouns from specifications, glossaries, or business rules

Attributes

  • Characteristics or properties of an entity
  • Must include allowable values and type of information
  • Examples: Book ID, title, author, reserve status
  • Primary Key (PK): Unique identifier for an entity

Foreign Keys (FK)

  • Primary key of another entity used as an attribute
  • Indicated with FK marker
  • Can have multiple foreign keys

Relationships

  • Verbs describing how entities interact
  • Represented as lines linking entities

Cardinality

  • Indicates the number of occurrences allowed on each side of a relationship
  • Common notations: One-to-one, One-to-Many, Zero-to-One, Zero-to-Many

Levels of Abstraction

  1. Conceptual ERD
    • High-level view, easy for business to understand
    • Establishes entities and relationships
  2. Logical ERD
    • Mid-level detail; business rules and attributes introduced
    • Data structure elements and relationships
  3. Physical ERD
    • Detailed enough for developers to implement the database
    • Non-functional requirements: performance, security, concurrency
    • Names become database-compatible (Table names, column names)

ERD Rules and Best Practices

  • Entities must be clearly named as nouns
  • No duplicate entities
  • Entities must not represent applications/systems
  • Every attribute must be named and should be unique
  • Primary Keys must be unique to one entity
  • Relationships must be vetted to avoid repetition

Example: Library Book Circulation

  • Entities: Reservations, books, borrowers, checkout, renewal
  • Attributes:
    • Book: Book ID (PK), title, author, reserve status
    • Reservation: Reservation ID (PK), book ID (FK), borrower ID (FK), reservation date, redeem date
    • Borrower: Borrower ID (PK), first name, last name, address, total fees due
    • Checkout: Checkout ID (PK), borrower ID (FK), book ID (FK), checkout date, return date, late fees
    • Renewal: Renewal ID (PK), checkout ID (FK), renewal count, renewal date
  • Relationships:
    • Book ↔ Reservation (1:M)
    • Reservation ↔ Borrower (1:M)
    • Borrower ↔ Checkout (0:M)
    • Checkout ↔ Renewal (0:M)
    • Renewal ↔ Book (N:M)

Conclusion

  • ERDs are essential for illustrating database components and their relationships
  • Follow rules and best practices for clear, effective diagrams
  • Useful for both business and technical team communication

For further resources, visit theba doc.com