Data Model vs Data Dictionary vs Database Schema vs ERD

Table of Contents:


    All those database design terms might be confusing. In this short article I will try explain what they are and what the differences are.

    Data Model

    Abstract model that organizes data elements and their relationships. It is not related to any implementation. Data model may be represented in many forms, such as Entity Relationship Diagram or UML Class Diagram.

    Data Dictionary

    Is a reference and description of each data element. It is a detailed definition and documentation of data model (learn more about data dictionary). It can have two levels of abstraction: physical and logical.

    Database Schema

    Database schema is a physical implementation of data model in a specific database management system. It includes all implementation details such as data types, constraints, foreign or primary keys.

    Entity Relationship Diagram

    ER diagrams are a graphical representation of data model/schema in relational databases. It is a modelling and a database documentation tool.

    Comparison

    Stage

    • Data Model: Conceptual system modeling
    • Database Schema: System implementation
    • Data Dictionary (Logical): Detailed system design, documentation
    • Data Dictionary (Physical): System implementation, documentation
    • ERD: Conceptual system modeling, documentation

    Purpose

    • Data Model: Data design
    • Database Schema: Database implementation
    • Data Dictionary (Logical): Define each data attribute in data model - data model supplement
    • Data Dictionary (Physical): Design and documentation of each data attribute in database schema
    • ERD: Communicate data model in relational databases

    Level of detail

    • Data Model: Low or medium - key objects/entities and attributes
    • Database Schema: High - every data and relation detail defined
    • Data Dictionary (Logical): High - every key table and data attribute defined
    • Data Dictionary (Physical): Very high - every table and column defined
    • ERD: Low or medium - key entities and attributes

    Authors

    • Data Model: Data/system architects, business analysts
    • Database Schema: Data/system architects, DBAs
    • Data Dictionary (Logical): Data/system architects, business analysts
    • Data Dictionary (Physical): Data/system architects, DBAs
    • ERD: Data architects, DBAs

    Users

    • Data Model: Business analysts, business users, data/system architects
    • Database Schema: Developers, DBAs
    • Data Dictionary (Logical): Business analysts, business users, data/system architects
    • Data Dictionary (Physical): Data/system architects, DBAs, developers, testers, system administrators
    • ERD: Business analysts, business users, data/system architects, DBAs, developers

    Tools

    • Data Model: CASE, diagramming tools
    • Database Schema: Database development & management tools
    • Data Dictionary (Logical): Word/Excel
    • Data Dictionary (Physical): Word/Excel, extended properties/comments, data dictionary tools
    • ERD: CASE, diagramming tools

    Form

    • Data Model: Graphical - UML Class Diagram, ERD
    • Database Schema: Structures in DBMS: tables, columns, foreign keys etc.
    • Data Dictionary (Logical): Tables of metadata
    • Data Dictionary (Physical): Tables of metadata
    • ERD: Diagram
    0
    There are no comments. Click here to write the first comment.