ER Modeling

E-R Modeling Overview

  • Instructor: Goonjan Jain

  • Institution: Department of Applied Mathematics, Delhi Technological University

Database Design Phases

  • Stages of Database Design:

    • Requirement Analysis

    • Conceptual Design

    • Logical Design

    • Schema Refinement

    • Physical Design

    • Security Design

Detailed Breakdown of Phases

  • Requirement Analysis:

    • Understanding user’s needs.

  • Conceptual Design:

    • High-level entity-relationship (ER) modeling.

  • Logical Design:

    • Development of tables.

  • Schema Refinement:

    • Normalization process.

  • Physical Design:

    • Implementation of indices, etc.

  • Security Design:

    • Establishing access control measures.

Miniworld Requirements Collection

  • Functional and Data Requirements:

    • Identifying functional needs and data specifics.

  • Functional Analysis and Conceptual Design:

    • High-level transaction specifications.

    • Create a conceptual schema independent of any specific DBMS.

  • Logical Design:

    • Create a logical (conceptual) schema that maps to a specific DBMS.

  • Transaction Implementation and Application Program Design:

    • Develop application programs as per design.

Key Concepts in E-R Modeling

  • Entities:

    • Objects in the real world, distinguishable from others, e.g., car, university.

  • Attributes:

    • Qualities or properties of entities, e.g., Employees entity includes name, ssn, salary.

  • Relationships:

    • Associations between entities.

Classifications of Attributes

  • Composite vs Simple Attributes:

    • Composite attributes can be divided (e.g., address).

    • Simple attributes are indivisible.

  • Single-Valued vs Multivalued Attributes:

    • Single-valued (e.g., age).

    • Multivalued (e.g., college degrees).

  • Stored vs Derived Attributes:

    • Stored attributes hold data directly.

    • Derived attributes are calculated from other attributes.

Keys in E-R Modeling

  • Definition of Keys:

    • Unique values identifying entities in a set.

    • Keys are underlined in diagrams.

Exploring Relationships

  • Definition of Relationships:

    • Associations among entities (e.g., Zulu works in pharmacy).

  • Descriptive Attributes:

    • Used to record additional info about relationships.

Special Types of Relationships

  • Ternary Relationship:

    • Associations involving three entities.

  • Recursive Relationship:

    • Relationships where entities relate to themselves.

Understanding Mapping Cardinalities

  • Types of Mapping Cardinalities:

    • 1-to-1

    • 1-to-many

    • Many-to-many

Participation Constraints

  • Total Participation:

    • All entities must partake in at least one relationship.

  • Partial Participation:

    • Only some entities partake.

Weak Entity Sets

  • Definition of Weak Entity Set:

    • Entity set lacking sufficient attributes to form a primary key.

  • Partial Key:

    • Uniquely identifies related weak entities.

    • Denoted with dashed lines in diagrams.

Identifying Relationships and Characteristics

  • Identifying Relationships:

    • Associations between weak and identifying entities.

    • Weak entities must have an identifying relationship.

Examples in E-R Modeling

  • Example of DEPENDENT with EMPLOYEE:

    • Dependency tracking via weak entities.

  • Complex Weak Entity Representation:

    • May use multivalued composite attributes.

Relational Model Reduction

  • Strong Entity Sets:

    • Table representation based on attributes.

    • Derived attributes are not explicitly stored.

Representation of Entities and Relationships

  • Primary Keys in Relational Schema:

    • Rules for binary relationships (many-to-many, one-to-one).

  • Combining Schemas:

    • When these participate in a relationship, merging schemas helps streamline data.