Comprehensive Study Guide to the Physical Data Model (MPD)

Fundamentals and Core Objectives of the Physical Data Model (MPD)

  • Definition of the Physical Data Model (MPD):

    • The Physical Data Model, known in French as Modèle Physique de Données (MPD), constitutes the ultimate, terminal stage in the data modeling and data processing lifecycle.

    • It serves as the direct technical blueprint required to implement, instantiate, and manage a relational database within a specific Database Management System (DBMS / Système de Gestion de Base de Données - SGBD).

    • The MPD represents the concrete, real physical expression of data (l'expression réelle de la donnée), translating abstract concepts into hardware- and software-executable database structures.

  • Role in the Database Design Progression:

    • Conceptual Data Model (MCD / Modèle Conceptuel de Données): Establishes high-level business entities, rules, and semantic relationships without regard to technical implementation.

    • Logical Data Model (MLD / Modèle Logique de Données): Normalizes structures into relational tables, keys, and foreign keys independent of specific DBMS constraints.

    • Physical Data Model (MPD / Modèle Physique de Données): Translates logical relations into physical database objects with explicit data storage types, field length allocations, physical storage constraints, and strict naming standards tailored to the target DBMS.

Conceptual and Terminological Correspondence: MLD vs. MPD

  • Vocabulary Equivalencies across Modeling Stages:

    • Entity (Entité) to Table (Table):

    • In the Logical Data Model (MLD), the organizational container is designated as an Entité.

    • In the Physical Data Model (MPD), this structure is designated strictly as a Table.

    • Property (Propriété) to Field or Column (Champs ou colonne):

    • In the MLD, an individual data attribute belonging to an entity is defined as a Propriété.

    • In the MPD, this element becomes an implementation-level physical Champ (field) or Colonne (column).

    • Identifier (Identifiant) to Primary Key (Clé primaire):

    • In the MLD, the unique distinguishing property is termed the Identifiant (frequently depicted with an underline).

    • In the MPD, the unique distinguishing key is technically defined as the Clé primaire (abbreviated as PK for Primary Key).

    • Foreign Key Notation Transition:

    • In MLD notation, a foreign key attribute migrated from another relation is prefixed with a hash symbol (#).

    • In MPD notation, physical foreign key constraints are designated explicitly as FK (Foreign Key) or combined into PK, FK when serving in composite primary keys.

Transformation Rules: Transitioning from MLD to MPD

  • Rule 1: Definitive Normalization of Object Identifiers (Naming Conventions):

    • Entity names and property names must be converted into definitive physical identifiers (Noms définitifs des entités et propriétés).

    • Elimination of Accented Characters: All diacritics and accents must be systematically removed (e.g., Diplômes becomes Diplomes, Employés becomes Employes, désignation becomes designation, prénom becomes prenom, and téléphone becomes telephone).

    • Elimination of Special Characters: All symbols such as #, punctuation, and mathematical symbols are prohibited in identifier names.

    • Elimination of Whitespace: Spaces between words must be eliminated or replaced with underscores (e.g., id dip becomes id_dip, id emp becomes id_emp).

  • Rule 2: Complete Specification of Physical Data Types (Spécification du type de chaque propriété):

    • Every single attribute must be assigned an explicit storage data type recognized by the target DBMS engine.

    • Data types must include size or precision specifiers where applicable (e.g., specifying character allocations such as varchar(50) or varchar(15), and integer types such as int).

  • Rule 3: Exhaustive Specification of Field Constraints (Spécification des contraintes de chaque propriété):

    • Primary Key Constraint (PK): Assigned to the unique identifier of each physical table to enforce entity integrity.

    • Foreign Key Constraint (FK): Assigned to attributes referencing primary keys of related tables to maintain referential integrity.

    • Composite Key Constraint (PK, FK): Assigned to junction table columns where attributes simultaneously serve as components of the primary key and reference external parent tables.

    • Nullability Constraints (NOT NULL vs. Nullable):

    • Mandatory attributes must be tagged explicitly with the NOT NULL constraint, preventing empty values from being stored.

    • Optional attributes (such as optional telephone records) omit the NOT NULL clause, thereby allowing NULL values.

Case Study Schema Analysis: Employees and Degrees System

  • Source Logical Data Model (MLD) Schema:

MLD diagram showing the entities Diplômes, Diplômes_Employés, and Employés
  • Entity Diplômes:

    • Identifier: id dip (underlined).

    • Property: désignation.

  • Associative Entity Diplômes_Employés:

    • Represents the resolution of a many-to-many relationship linking employees to their acquired diplomas.

    • Properties: #id dip and #id emp (both marked with # as migrated foreign identifiers).

  • Entity Employés:

    • Identifier: id emp (underlined).

    • Properties: nom, prénom, téléphone.

    • Target Physical Data Model (MPD) Schema:

MPD relational diagram showing tables Diplomes, Diplomes_Employes, and Employes with data types and constraints
  • Table Diplomes:

    • id_dip: Data type int | Constraint: PK (Primary Key).

    • designation: Data type varchar(50) | Constraint: NOT NULL.

  • Table Diplomes_Employes (Junction Table):

    • id_dip: Data type int | Constraints: PK, FK (References parent table Diplomes).

    • id_emp: Data type int | Constraints: PK, FK (References parent table Employes).

    • The combination (id_dip, id_emp) forms a composite primary key while simultaneously enforcing two independent foreign key constraints.

  • Table Employes:

    • id_emp: Data type int | Constraint: PK (Primary Key).

    • prenom: Data type varchar(50) | Constraint: NOT NULL.

    • nom: Data type varchar(50) | Constraint: NOT NULL.

    • telephone: Data type varchar(15) | Constraint: None specified (Nullable, allowing empty entries).

    • Attribute order reflects the physical table column ordering: id_emp, prenom, nom, telephone.

Comprehensive Taxonomy of Common Database Data Types

Classification diagram of common SQL data types across Numeric, Date and Time, String, Spatial, and JSON categories
  • Numeric Data Types (Numeric):

    • INT: Standard signed or unsigned integer used for counting, surrogate primary keys, and discrete whole values.

    • FLOAT: Single-precision floating-point format used for approximate numerical computation.

    • DOUBLE: Double-precision floating-point format providing higher precision for complex scientific calculations.

    • DECIMAL: Exact fixed-point numeric format crucial for monetary values, financial operations, and scenarios requiring absolute precision.

    • BIT: Bit-field data type storing binary values (00 or 11) for boolean flags or bitmasks.

  • Date and Time Data Types (Date and Time):

    • DATETIME: Stores combined date and time representations without timezone conversion.

    • TIMESTAMP: Stores point-in-time values tracking seconds elapsed since the Unix epoch, frequently updating automatically upon row modifications.

    • DATE: Stores calendar dates exclusively (comprising year, month, and day).

    • TIME: Stores clock times or elapsed time intervals (comprising hours, minutes, and seconds).

    • YEAR: Stores year data formatted in two-digit or four-digit representations.

  • Character and Binary String Data Types (String):

    • CHAR: Fixed-length character string; pads unused allocated space with blank characters up to the declared limit.

    • BINARY: Fixed-length raw byte string for binary data storage.

    • VARCHAR: Variable-length character string allocating only the storage required for actual text plus overhead (e.g., varchar(50) allocates up to 5050 characters).

    • VARBINARY: Variable-length raw byte string.

    • BLOB: Binary Large Object designed to store extensive binary files, multimedia, audio, or compiled data payloads.

    • TEXT: Variable-length large-scale text container designed for long-form documentation, paragraphs, and descriptions.

  • Spatial Data Types (Spatial):

    • GEOMETRY: Foundational generic spatial data type representing geometric shapes and spatial objects.

    • POINT: Represents an exact zero-dimensional geometric location specified by coordinate pairs (e.g., latitude and longitude: X,YX, Y).

    • LINESTRING: Represents a one-dimensional geometric path constructed from a continuous series of connected line segments.

    • POLYGON: Represents a two-dimensional geometric surface bounded by closed linear rings.

  • Semi-Structured Document Data Types (JSON):

    • JSON: Native JavaScript Object Notation data type that enables automated syntax validation and optimized binary storage for flexible document-oriented schemas within relational database engines.