thunderLAB

Database System Lecture 2: Comprehensive Guide to Database Modeling (ER Model, Types, and Key Concepts)

study

Database System Lecture 2 Summary

Importance of Database Modeling

  • Business Perspective: What data should be stored?
  • IT Perspective: How should data be stored?
  • Database modeling is essential to understand the meaning of data and conceptually define/analyze business processes related to data.

Definition and Steps of Database Modeling

  • Database Modeling: The process of selecting, structuring, and designing data storage/usage methods in a database system.
  • Abstracts a part of the real world into a data model supported by DBMS.

Modeling Steps

  1. User Requirements Analysis: Understand the data to be stored/used and the business process.
  2. Conceptual Data Modeling: Abstract real-world data using an ER model.
  3. Logical Data Modeling: Structure data to fit the DBMS implementation model (schema creation).
  4. Physical Data Modeling: Define actual storage structures, indexes, and file configurations.
  5. Internal Schema: Define physical details of data storage.

User Requirements Analysis

  • Without sufficient pre-analysis, the final product may lack quality and reliability, leading to additional costs.
  • Analysis Process:
    • Requirements Elicitation → Analysis (clarity and completeness) → Documentation (recording and management)

ER Model (Entity-Relationship Model)

  • Proposed by P.Chen in 1976, it is a conceptual data model that structures real-world entities and their relationships.
  • Visualized using an ER Diagram (ERD).

Components of ER Model

  • Entity: A tangible or intangible object or concept in the real world.
  • Entity Set: A group of entities sharing the same attributes.
  • Attribute: A characteristic of an entity.
    • Types:
      • Simple/Composite: Cannot be divided/can be divided into smaller attributes.
      • Single-valued/Multi-valued: One value per entity/multiple values.
      • Stored/Derived: Actually stored/calculated values.
  • Relationship: A meaningful association between entities. Represented by a relationship set.
  • Cardinality: The number of associations between entities.
    • One-to-One (1:1), One-to-Many (1:N), Many-to-Many (N:N).
  • Participation Constraint: Specifies how entities participate in a relationship.
    • Total Participation (all entities participate), Partial Participation (only some).
  • Key Attribute: An attribute that uniquely identifies an entity.

Special Attributes and Relationships

  • Attributes of a Relationship Set: Values generated from the relationship.
  • Recursive Relationship: An entity set having a relationship with itself.
  • Weak Entity Set: An entity set that cannot exist independently and depends on a strong entity set.

Key Points to Memorize

  • 4 Stages of Database Modeling:

    1. User Requirements Analysis
    2. Conceptual Data Modeling (ER Model)
    3. Logical Data Modeling (Relational Model)
    4. Physical Data Modeling (Storage structures, indexes, etc.)

  • 3 Major Components of the ER Model:

    • Entity, Attribute, Relationship

  • Types of Attributes:

    • Simple/Composite, Single-valued/Multi-valued, Stored/Derived

  • Cardinality of Relationships:

    • One-to-One, One-to-Many, Many-to-Many

  • Participation Constraints:

    • Total Participation, Partial Participation

  • Key Attribute:

    • An attribute that uniquely identifies an entity.

  • Weak Entity Set:

    • An entity set that depends on a strong entity set.

  • User Requirements Analysis Process:

    • Elicitation → Analysis → Documentation

post-title-1747333346966

post-title-1747333346966