Chapter Objectives

• Understand the fundamental concepts of the Entity-Relationship (E/R) model
• Build a graphical representation of an E/R model
• Identify and represent entities, attributes, associations, and cardinalities
• Optimize a conceptual E/R model

Introduction

The database design process goes through several key stages. It starts with analysis, where you study the real-world problem, constraints, and requirements.

Then comes the conceptual data model (CDM) — a graphical abstraction that focuses on the essential elements of the problem, without worrying about implementation.

From there, you derive the logical data model (LDM) — a formal description of the solution, still independent of the database engine.

Finally, you build the physical data model (PDM) — the actual database implementation in a DBMS, often optimized for performance.

In short: Analysis → Conceptual → Logical → Physical → Implementation.

Model E/R

The Entity-Relationship (E/R) model is the foundation of relational database design. Its goal is to build a logical and consistent structure that represents real-world data clearly and efficiently.

It focuses on entities (things like students, films, orders) and the relationships between them (a student enrolls in a course, a film has actors).

This model helps create a reliable database schema for application development.

The beauty of the E/R model lies in its simplicity: it’s graphical, easy to understand, and powerful enough for most relational databases. However, it’s not perfect — it has several limitations:

• It doesn’t define operations to manipulate data.
• It struggles to express complex constraints.
• It can become ambiguous for large, intricate systems.

In the end, it’s up to the designer’s judgment to adapt the model and meet the system’s requirements as precisely as possible.

Jargon!

• An entity is similar to the concept of an object; it describes an “entity” in the real world.
  • Example: a book, a student, an account, an invoice, ...
• An Relation is a link between several entities.
  • Example: an invoice contains several products.

E/R: Informal representation

"Consider a database describing movies, including their titles and actors, as well as the theaters where these movies are shown. This database is accessible on the web, and users can rate the movies they have seen."

E/R: Formal representation

Entity, attribute, and entity type

Entity: any object that is identifiable and relevant to the application

• e.g.: Movie, Artist, Internet user, etc.

Attribute: a property that characterizes an entity

• e.g.: Movie title, artist name, etc.
• An attribute is atomic: it takes one and only one value
• An attribute can be multivalued: set of values taken from the same domain (telephone numbers)
• An attribute can be composite: made up of other attributes (address: street number, street name, zip code, country)

Entity type is made up of the following elements:

• Its name;
• The list of its attributes with, optionally, the domain where the attribute takes its values: the
• The list of its attributes with, -- optionally -– the domain where the attribute takes its values: integers, character strings;
• The indication of the attribute(s) used to identify the entity: these constitute the key.

Unique identifier: key

Key: Let E be a type of entity and A the set of its attributes. A key of E is a minimal subset that uniquely identifies an entity among any extension of E.

The characteristics of a good key are:

• its value is known for every entity;
• it should never need to be modified;
• its storage size should be as small as possible (storage performance)

Examples: id_Movie to identify a film, the pair (email,password) to identify an internet user, etc.

Choosing an Identifier

• Avoid identifiers composed of multiple attributes (for example, an identifier formed by both name and surname):

  • they degrade the performance of the DBMS (Database Management System),
  • but most importantly, the uniqueness assumed by such an approach usually ends up being disproven sooner or later.

• Avoid identifiers that are likely to change over time (such as a vehicle’s license plate).

• Avoid identifiers of the string type.

Relationships

• A relationship represents the semantic links that may exist between several entities.
• An association class can have attributes (for example, you could add date_rea).
• Here are the types of association classes:
  • A recursive (or reflexive) association class links the same entity class.
  • A binary association class links two entity classes.
  • A ternary association class links three entity classes.
  • An n-ary association class links n entity classes.

Cardinality

Let there be an association (E1, E2) between two types of entities.
The cardinality of the association for Ei (E1 or E2) is a pair [Min, Max] such that:

• Max: the maximum cardinality, representing the maximum number of times an instance of Ei can appear in the association (1 or n).
• Min: the minimum cardinality, representing the minimum number of times an instance of Ei can appear in the association (0 or 1).

Example: An artist creates 0 or more films, and a film is created by one and only one artist.

Optimization

Name normalization: The name given to an entity, an association, or an attribute must be unique.

• Risk of inconsistency if attributes share the same name, as well as redundancy and unnecessary memory usage.

Before --> After
=================

    ┌────────────────────────┐           ┌──────────────────────────────┐
    │     InternetUser       │           │        InternetUser          │
    ├────────────────────────┤           ├──────────────────────────────┤
    │ num        INT         │           │ user_id          INT         │
    │ name       VARCHAR(50) │   -->     │ user_name        VARCHAR(50) │
    │ firstname  VARCHAR(50) │           │ user_firstname   VARCHAR(50) │
    └────────────────────────┘           └──────────────────────────────┘

    ┌────────────────────────┐           ┌──────────────────────────────┐
    │      Artist            │           │            Artist            │
    ├────────────────────────┤           ├──────────────────────────────┤
    │ num        INT         │           │ artist_id        INT         │
    │ name       VARCHAR(50) │   -->     │ artist_name      VARCHAR(50) │
    │ firstname  VARCHAR(50) │           │ artist_firstname VARCHAR(50) │
    └────────────────────────┘           └──────────────────────────────┘

Attribute Normalization

• Each multiple (structured) attribute must be transformed into an entity, and an additional association must be added (example: several addresses for a single user).
• The same applies to enumerations (example: for Film, the attribute Genre = {action, comedy, fiction, …}).
• Avoid redundant attribute information through calculation or transition: an attribute derived from other attributes (example: birth date and age).

Before ⟶ After
=================

        ┌──────────────────────────────┐           ┌──────────────────────────────┐
        │        InternetUser          │           │        InternetUser          │
        ├──────────────────────────────┤           ├──────────────────────────────┤
        │ user_id           INT        │           │ user_id           INT        │
        │ user_name         VARCHAR(50)│    -->    │ user_name         VARCHAR(50)│
        │ user_firstname    VARCHAR(50)│           │ user_firstname    VARCHAR(50)│
        │ address           VARCHAR(50)│           └──────────────────────────────┘
        └──────────────────────────────┘                     1,n
                                                              │
                                                              │
                                                           ┌──┴──┐
                                                           │lives│
                                                           └──┬──┘
                                                              │
                                                              │
                                                             0,n
                                               ┌─────────────────────────────┐
                                               │          Address            │
                                               ├─────────────────────────────┤
                                               │ address_id       INT        │
                                               │ street_number    INT        │
                                               │ street_name      VARCHAR(50)│
                                               │ postal_code      INT        │
                                               └─────────────────────────────┘

Merge when possible

• Entities and associations should be consolidated whenever possible.

ENTITY FUSION
--------------

        ┌─────────────────────────────┐
        │          Actor              │
        ├─────────────────────────────┤
        │ actor_id         INT        │
        │ actor_name       VARCHAR(50)│
        │ actor_firstname  VARCHAR(50)│
        └─────────────────────────────┘

        ┌───────────────────────────────┐
        │           Director            │
        ├───────────────────────────────┤
        │ director_id      INT          │
        │ director_name    VARCHAR(50)  │
        │ director_firstname VARCHAR(50)│
        └───────────────────────────────┘

                    ⬇︎ Merge

        ┌─────────────────────────────┐
        │           Artist            │
        ├─────────────────────────────┤
        │ artist_id        INT        │
        │ artist_name      VARCHAR(50)│
        │ artist_firstname VARCHAR(50)│
        │ artist_role      VARCHAR(50)│
        └─────────────────────────────┘


ASSOCIATION FUSION
------------------

Before:
--------

     ┌─────────────────────────────┐
     │           Artist            │
     ├─────────────────────────────┤
     │ artist_id        INT        │
     │ artist_name      VARCHAR(50)│
     │ artist_firstname VARCHAR(50)│
     │ artist_role      VARCHAR(50)│
     └─────────────────────────────┘
         │       │        │
         │       │        │
         │       │        │
    (acts)  (produces) (directs)
        0,n     0,n       0,n
        │        │         │
        │        │         │
        └────────┴─────────┘
                1,n
                 │
                 ▼
     ┌────────────────────────────┐
     │           Film             │
     ├────────────────────────────┤
     │ film_id         INT        │
     │ film_title      VARCHAR(50)│
     │ film_year       INT        │
     │ film_genre      VARCHAR(50)│
     │ film_summary    VARCHAR(50)│
     └────────────────────────────┘


After:
-------

     ┌─────────────────────────────┐
     │           Artist            │
     ├─────────────────────────────┤
     │ artist_id        INT        │
     │ artist_name      VARCHAR(50)│
     │ artist_firstname VARCHAR(50)│
     │ artist_role      VARCHAR(50)│
     └─────────────────────────────┘
           │
         (participates)
           │  role VARCHAR(50)
           │
           ▼
     ┌────────────────────────────┐
     │           Film             │
     ├────────────────────────────┤
     │ film_id         INT        │
     │ film_title      VARCHAR(50)│
     │ film_year       INT        │
     │ film_genre      VARCHAR(50)│
     │ film_summary    VARCHAR(50)│
     └────────────────────────────┘

If two paths exist between two entities, then these two paths must have distinct meanings.
Example: if a customer cannot pay another customer’s invoice, then the pay association is unnecessary.
Otherwise, the shortest path should be removed, since it can be inferred from the other paths.

Before → After
===============

Before:
--------

     ┌─────────────────────────────┐
     │           Client            │
     ├─────────────────────────────┤
     │ client_id       VARCHAR(50) │
     │ client_name     VARCHAR(50) │
     │ client_firstname VARCHAR(50)│
     └─────────────────────────────┘
            │
          (0,n)
            │
           [Pay ❌]
            │
          (1,1)
            │
     ┌────────────────────────────┐
     │          Payment           │
     ├────────────────────────────┤
     │ payment_id      INT        │
     │ payment_date    DATE       │
     │ amount          CURRENCY   │
     └────────────────────────────┘


After:
-------

     ┌─────────────────────────────┐
     │           Client            │
     ├─────────────────────────────┤
     │ client_id       VARCHAR(50) │
     │ client_name     VARCHAR(50) │
     │ client_firstname VARCHAR(50)│
     └─────────────────────────────┘
            │
         (0,n)
          │
        [Receive]
          │
         (1,1)
          │
     ┌────────────────────────────┐
     │           Invoice          │
     ├────────────────────────────┤
     │ invoice_num     INT        │
     │ invoice_date    DATE       │
     │ total_amount    CURRENCY   │
     └────────────────────────────┘
            │
         (0,n)
          │
       [CorrespondsTo]
          │
         (1,1)
          │
     ┌────────────────────────────┐
     │          Payment           │
     ├────────────────────────────┤
     │ payment_id      INT        │
     │ payment_date    DATE       │
     │ amount          CURRENCY   │
     └────────────────────────────┘

Explanation:

• The "Pay" relationship was removed (redundant link between Client and Payment).
• The model is simplified:
  • A Client now receives an Invoice (Receive)
  • Each Invoice corresponds to a Payment (CorrespondsTo)
• Cardinalities preserved:
  • Client → Invoice (0,n)
  • Invoice → Payment (0,n)

TL;DR

1. Identify the Present Entities

• In general, an entity is created if it has at least two occurrences.
• Each element of an entity is called an occurrence of that entity.

2. List the Attributes of Each Entity

• We generally limit attributes to those necessary for development.
• Each attribute must have only one possible value per occurrence; otherwise, it should be modeled as a separate entity.
• Attributes should be atomic and non-decomposable. > Exemple: the property address (street, postal code, city) is composed of three elementary entities.

3. Identify Each Occurrence Uniquely

• Suppose two users are both named “John” — we need a way to distinguish them.
• We therefore add a unique property that identifies each occurrence: the primary key.
• This key is usually underlined to highlight its role as an identifier.

4. Establish Relationships Between Entities

• Relationships are generally expressed using verbs.
• They define how entities interact or depend on one another.

5. Identify the Cardinalities

• Cardinalities specify how many occurrences of one entity are related to another.
• They are generally explicitly indicated in the problem statement or description.

Tools

• Looping
• Canva
• Efrei Paris