DTD - Entity-Relationship (ER) Modeling

Entity-Relationship (ER) Modeling

1. Purpose

ER modeling is part of the Conceptual Database Design phase.

  • It’s used to visually represent data requirements.

  • Helps identify entities, attributes, and relationships before converting into a logical relational schema.

  • Acts as a communication tool between database designers and non-technical stakeholders.

2. Core Components

  1. Entities

    • Objects or concepts about which data is stored.

    • Examples: Student, Course, Employee, Product.

    • Represented as rectangles in ER diagrams.

    • Types of Entities:

      • Strong Entity: Exists independently (e.g., Student).

      • Weak Entity: Depends on another entity (e.g., Order Item depends on Order).

  2. Attributes

    • Properties or characteristics of entities.

    • Examples: StudentName, DateOfBirth, CourseCode.

    • Represented as ellipses/ovals.

    • Types of Attributes:

      • Simple (e.g., Age).

      • Composite (e.g., FullName = FirstName + LastName).

      • Multivalued (e.g., PhoneNumbers).

      • Derived (e.g., Age derived from DateOfBirth).

  3. Relationships

    • Associations between entities.

    • Example: Student ENROLLS IN Course.

    • Represented as diamonds.

    • Relationship Types:

      • One-to-One (1:1): A person has one passport.

      • One-to-Many (1:N): A customer places many orders.

      • Many-to-Many (M:N): Students enroll in many courses, and courses have many students.

  4. Keys

    • Primary Key: Unique identifier for an entity (e.g., StudentID).

    • Foreign Key: Attribute linking entities (e.g., CourseID in Enrollment table).

3. Steps in ER Modeling

  1. Identify Entities: From user requirements (e.g., Customer, Order, Product).

  2. Define Attributes: List key details for each entity (e.g., Customer → Name, Email, Phone).

  3. Establish Relationships: Determine how entities interact (Customer places Order).

  4. Assign Cardinality: Indicate relationship types (1:1, 1:N, M:N).

  5. Draw ER Diagram: Use notations (Chen, Crow’s Foot, UML).

  6. Validate with Stakeholders: Confirm the diagram reflects actual business rules.

4. Example

For a Library Database:

  • Entities: Book, Member, Loan.

  • Attributes:

    • Book → ISBN (PK), Title, Author, Year.

    • Member → MemberID (PK), Name, Address.

    • Loan → LoanID (PK), DateBorrowed, DueDate.

  • Relationships:

    • Member borrows Book (1:N).

    • Loan acts as a weak entity linking Member and Book.

5. Why It Matters

  • Provides a blueprint for the logical design (tables, relationships).

  • Reduces redundancy and improves clarity.

  • Makes sure all data requirements are captured before implementation.