Comparison Between ER Model and Relational Model

 

Comparison Between ER Model and Relational Model

The Entity–Relationship (ER) model is a conceptual, high-level design model, while the Relational model is a logical, implementation-level model. Each construct in the ER model is mapped to one or more constructs in the relational model.

1. Conceptual Correspondence Between ER and Relational Models

ER Model ConstructRelational Model Construct
Entity type        Entity relation (table)
1:1 or 1:N relationship type        Foreign key (or relationship relation)
M:N relationship type        Relationship relation with two foreign keys
n-ary relationship type        Relationship relation with n foreign keys
Simple attribute        Attribute (column)
Composite attribute        Set of simple component attributes
Multivalued attribute            Separate relation with a foreign key
Value set        Domain
Key attribute        Primary key (or secondary/unique key)

2. Representation of Relationships

ER Model

  • Relationships are explicitly represented

  • Relationship types are shown as diamonds

  • Cardinality and participation constraints are visually clear

Relational Model

  • Relationships are not explicitly represented

  • They are implemented using:

    • Primary keys

    • Foreign keys

  • Two tuples are related when:

    • A foreign key value matches a primary key value

📌 Example:

  • EMPLOYEE.Ssn (primary key)

  • WORKS_ON.Essn (foreign key)

3. Role of JOIN Operations

In the relational model, relationships are materialized using JOIN operations:

  • EQUIJOIN → when attribute names differ

  • NATURAL JOIN → when attribute names are the same

Relationship TypeNumber of JOINs Required
1:1 or 1:N (foreign key approach)1 join
M:N relationship2 joins
n-ary relationshipn joins

4. Example: EMPLOYEE–PROJECT–WORKS_ON

To retrieve:

Employee name, project name, and hours worked

Steps:

  1. Join EMPLOYEE and WORKS_ON

    EMPLOYEE.Ssn = WORKS_ON.Essn

  2. Join the result with PROJECT

    WORKS_ON.Pno = PROJECT.Pnumber

This requires two joins because WORKS_ON is an M:N relationship relation.

5. Drawback of the Relational Model

  • Foreign key–primary key relationships are not visually obvious

  • Users must know the schema well to write correct joins

  • Incorrect joins can:

    • Produce spurious (meaningless) data

    • Appear syntactically correct but be semantically wrong

📌 Example of a meaningless join:

PROJECT ⋈ DEPT_LOCATIONS
ON Dlocation = Plocation

This join does not represent a real relationship.

6. Key Difference Summary

AspectER ModelRelational Model
Level    Conceptual    Logical/Implementation
Relationship representation    Explicit    Implicit (via keys)
Visualization    Clear and intuitive    Less obvious
Join requirement    Not needed    Required to reconstruct relationships
Risk of wrong combinations    Low    High if joins are incorrect

7. Conclusion 

The ER model represents entities and relationships explicitly and intuitively, making it suitable for conceptual database design. In contrast, the relational model represents relationships implicitly through foreign keys and requires join operations to reconstruct relationships, which can make the schema harder to interpret and more error-prone if joins are not used correctly.

Comments

Post a Comment

Popular posts from this blog

Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme

 About Me Database Management Systems DBMS PCCST402 Scheme and Syllabus Module-1 Introduction to DBMS Introduction to DBMS Example  Database Database System Vs Traditional File Processing Advantages of using DBMS Database System Concepts and Architecture Data Models, Schemas and Instances   Three schema architecture and Data Independence Database Languages and Interfaces Centralized and Client Server Architectures in DBMS Classification of Database Management Systems Conceptual Data Modeling and Database Design Conceptual Modeling and the Role of the ER Model in Database Design Entities and Attributes  Company Database Application - Example Entity Types, Entity Sets, Keys, and Value Sets in the ER Model Initial Conceptual Design of the COMPANY Database Relationship Types in the ER Model Relationship Degree, Role Names, and Recursive Relationships Constraints on Binary Relationship Types Attributes of Relationship Types Weak Entity Types Summary of the Notations Used ...
Read more

Data Models, Schemas and Instances

  Data Models, Schemas and Instances  1. Data Abstraction In databases, data abstraction means hiding the complex details of how data is stored and focusing only on what is important. Different users can view data at different levels of detail depending on their needs. 2. Data Models A data model is a collection of concepts used to describe the structure of a database. It defines: Data types Relationships among data Constraints on data Operations for accessing and updating data Data models help us understand and design databases without worrying about low-level storage details. Categories of Data Models a) High-level / Conceptual Data Models Close to how users think about data. Use concepts like entities , attributes , and relationships . Example: ER (Entity–Relationship) Model . b) Representational (Implementation) Data Models In between conceptual and physical models. Easy for users to understand but still close to how data is st...
Read more

Introduction to Database Management System -DBMS

Image
In today’s digital world, almost every application—from mobile banking and e-commerce to university portals and hospital systems—depends on the efficient storage, organization, and retrieval of data. As the amount of information generated by individuals and organizations continues to grow, managing this data in a structured and meaningful way becomes essential. This is where database systems play a central role. A database can be thought of as a well-organized collection of related data, designed so that it can be easily accessed, managed, and updated. But the database itself is only one component. To manage it effectively, we rely on a specialized software package known as a Database Management System (DBMS) . A DBMS provides a systematic and automated way to store large volumes of data, enforce rules for consistency and correctness, protect data from unauthorized access, and allow multiple users to work with the data simultaneously without conflicts. A database typically has three...
Read more