Study of Entity Relationship Model

Definition

The Entity Relationship Model is a high-level conceptual data model used to describe the structure of a database in terms of entities, attributes, and relationships among those entities.

It visually represents real-world objects and their connections through an ER diagram, which helps in designing a logical and efficient database structure before actual implementation.

Main Content

1. First Concept: Entity, Attribute, and Relationship

Entity

An entity is any real-world object, person, place, event, or thing that can be uniquely identified and about which data is stored.
Examples:

Student
Teacher
Book
Department
Order

Entities can be:

Strong entity: Exists independently and has its own primary key.
Weak entity: Depends on another entity for identification.

Example:
In a college database, Student is an entity because we can store information like student_id, name, and address.

Attribute

An attribute is a property or characteristic of an entity. Attributes describe the details of an entity.
Example:
For the entity Student, attributes may include:

student_id
name
age
phone_number
date_of_birth

Types of attributes:

Simple attribute: Cannot be divided further, e.g., age.
Composite attribute: Can be divided into smaller parts, e.g., name can be split into first_name and last_name.
Single-valued attribute: Contains one value only, e.g., date_of_birth.
Multi-valued attribute: Can contain multiple values, e.g., phone numbers.
Derived attribute: Calculated from other attributes, e.g., age from date_of_birth.

Relationship

A relationship is an association among entities. It shows how entities interact with each other.
Example:

A Student enrolls in a Course
An Employee works in a Department
A Customer places an Order

Relationships can also have attributes. For example, in the relationship Enrolls, attributes may include:

enrollment_date
grade

Example ER representation:

Student ---- Enrolls ---- Course

This means students enroll in courses, and the relationship can store additional information such as the semester or marks.

2. Second Concept: Cardinality and Participation

Cardinality

Cardinality defines the numerical relationship between entities. It tells how many instances of one entity can be associated with another entity.

The main types are:

One-to-One (1:1)
One instance of entity A is related to one instance of entity B.
Example:
- One person has one passport
- One employee has one employee ID card
One-to-Many (1:N)
One instance of entity A is related to many instances of entity B.
Example:
- One department has many employees
- One teacher teaches many students
Many-to-Many (M:N)
Many instances of entity A are related to many instances of entity B.
Example:
- Many students enroll in many courses
- Many authors write many books

Simple visual representation:

Student ----- enrolls ----- Course
Many students can enroll in many courses

Participation

Participation specifies whether the existence of an entity depends on its relationship with another entity.

Types of participation:

Total participation: Every instance of an entity must participate in the relationship.
Example: Every employee must belong to some department.
Partial participation: Some instances of an entity may not participate in the relationship.
Example: Not every customer places an order.

Participation is important because it helps define mandatory and optional links in a database.

Example:

If every Order must be placed by exactly one Customer, then Order has total participation in the Places relationship.

3. Third Concept: ER Diagram and Mapping to Tables

ER Diagram

An ER diagram is a graphical representation of the ER Model. It uses symbols to show entities, attributes, and relationships. It is one of the most useful tools in database design because it provides a visual summary of the database structure.

Common symbols:

Rectangle for entity
Oval for attribute
Diamond for relationship
Underlined attribute for primary key

Example ER structure:

Student(student_id, name, age) ---- Enrolls ---- Course(course_id, title)

This diagram helps database designers and users understand the data model easily.

Mapping ER Model to Relational Tables

After designing the ER diagram, it is converted into tables in a relational database.

General rules:

Each entity becomes a table.
Each attribute becomes a column.
Primary key identifies each row uniquely.
Relationships are represented using foreign keys or separate tables.

Example:

Student(student_id, name, age)
Course(course_id, title)
Enrolls(student_id, course_id, enrollment_date)

Here:

student_id and course_id are foreign keys in the relationship table.
Enrolls becomes a separate table because it is a many-to-many relationship.

Illustrative structure:

Student

student_id
name
age

Course

course_id
title

Enrolls

student_id
course_id
enrollment_date

This mapping is crucial because it converts conceptual design into practical database implementation.

Working / Process

1. Identify entities from the real-world system

The first step is to observe the real-world scenario and list all important objects, people, events, or concepts that need to be stored in the database.
Example: In a school system, entities may include Student, Teacher, Subject, Class, and Department.

2. Define attributes and relationships

After identifying entities, determine the properties of each entity and how entities are connected.
Example:

Student has student_id, name, class, and age
Teacher teaches Subject
Student enrolls in Subject

3. Create the ER diagram and convert it to tables

Draw the ER diagram using standard symbols, then transform it into relational tables by assigning keys and foreign keys.
Example:

Student table
Subject table
Teacher table
Enrolls table for many-to-many association

Advantages / Applications

Simple and clear database design

The ER Model makes complex real-world systems easier to understand by breaking them into entities and relationships.

Useful for planning and analysis

It helps database designers analyze requirements before actual coding or table creation, reducing design mistakes.

Supports effective communication

ER diagrams provide a visual language that can be easily understood by developers, analysts, and users.

Used in many real-world systems

It is applied in banking systems, hospital systems, university databases, library management, online shopping, inventory control, and payroll systems.

Helps in normalization and table creation

The ER Model is often the starting point for building well-structured relational databases with minimal redundancy.

Summary

The Entity Relationship Model is a conceptual way to design databases.
It uses entities, attributes, and relationships to represent real-world data.
ER diagrams help in understanding and converting database requirements into tables.
Important terms to remember: entity, attribute, relationship, cardinality, participation, ER diagram, primary key, foreign key