Skip to content
ZaoDocs

Relational Databases

Introduction to Data Models

A data model is an abstraction mechanism that enables the construction of a conceptual data representation for users. It describes not only the data structure at a conceptual level but also includes a set of basic operations on the structured data.

Database Management Systems (DBMS) are designed based on specific data models, which can be classified as:

  • Relational
  • Hierarchical
  • Network
  • Object-oriented
  • Logical

Conceptual Design

Focus on high-level data structure representation

Implementation

Independent of specific database systems

Entity-Relationship (E-R) Model

What is the Entity-Relationship Model?

The Entity-Relationship (E-R) Model, introduced by Peter Chen in 1976, is a cornerstone of database design that provides a conceptual framework for describing data in terms of entities, their attributes, and relationships between entities.

Key Features

Data Focus

Emphasizes data structure and relationships without implementation details

Visual Representation

Provides clear graphical notation for complex data relationships

System Independence

Works independently of specific database management systems

Scalability

Easily adaptable to evolving system requirements

Core Components

1. Entities

Entities are the foundation of the ER model, representing distinct objects or concepts in the system.

erDiagram
    EMPLOYEE {
        string employee_id PK
        string name
        date hire_date
    }
  • Self-sufficient entities
  • Have their own primary key
  • Represented by single rectangle

Strong Entities

2. Relationships

Relationships define how entities interact with each other.

Types of Relationships

erDiagram
    CUSTOMER ||--|{ ORDER : places
    ORDER ||--|{ ORDER_ITEM : contains
    PRODUCT ||--o{ ORDER_ITEM : "appears in"

One-to-One (1:1)

  • Each entity relates to exactly one other entity
  • Example: Person ↔ Passport

Many-to-Many (N:M)

  • Multiple entities relate to multiple entities
  • Example: Students ↔ Courses

One-to-Many (1:N)

  • One entity relates to multiple entities
  • Example: Department → Employees

3. Attributes

  1. Simple Attributes

    erDiagram
        PERSON {
            string name
            number age
            string email
        }

  2. Composite Attributes

    erDiagram
        PERSON {
            string first_name
            string last_name
            address address
        }
        ADDRESS {
            string street
            string city
            string postal_code
        }

  3. Multivalued Attributes

    erDiagram
        PERSON {
            string name
            string[] phone_numbers
            string[] email_addresses
        }

  4. Derived Attributes

    erDiagram
        EMPLOYEE {
            date birth_date
            number age
            number years_service
        }

DB-Atributes

Advanced Concepts

Specialization and Generalization

erDiagram
    VEHICLE ||--o{ CAR : "is a"
    VEHICLE ||--o{ MOTORCYCLE : "is a"
    VEHICLE ||--o{ TRUCK : "is a"
    VEHICLE {
        string vehicle_id
        string manufacturer
        date year
    }
    CAR {
        number doors
        string type
    }
    MOTORCYCLE {
        string engine_type
    }
    TRUCK {
        number cargo_capacity
    }

Every supertype entity must belong to at least one subtype

Constraints

Cardinality Constraints

  • Minimum and maximum number of relationship instances
  • Notation: (min, max)

Participation Constraints

  • Total: Every entity must participate
  • Partial: Participation is optional

Best Practices

Design Guidelines

  1. Entity Identification

    • Choose meaningful entity names
    • Identify all relevant attributes
    • Define clear primary keys

  2. Relationship Modeling

    • Determine correct cardinalities
    • Consider relationship attributes
    • Evaluate relationship participation

  3. Attribute Organization

    • Group related attributes
    • Identify derived attributes
    • Handle multivalued attributes appropriately

  4. Constraint Definition

    • Apply business rules
    • Define integrity constraints
    • Document all constraints

Common Pitfalls to Avoid

Practical Examples

University Database

erDiagram
    STUDENT ||--|{ ENROLLMENT : has
    COURSE ||--|{ ENROLLMENT : contains
    PROFESSOR ||--o{ COURSE : teaches
    DEPARTMENT ||--|{ PROFESSOR : employs
    DEPARTMENT ||--|{ COURSE : offers


    STUDENT {
        string student_id PK
        string name
        date enrollment_date
        string[] contact_info
    }


    COURSE {
        string course_id PK
        string title
        number credits
        string description
    }


    PROFESSOR {
        string professor_id PK
        string name
        string[] specializations
        date hire_date
    }


    DEPARTMENT {
        string dept_id PK
        string name
        string location
        string chair_person
    }


    ENROLLMENT {
        string enrollment_id PK
        date enrollment_date
        string grade
        string semester
    }