Menu

Earn Premium with Referrals

Invite your friends and earn Premium rewards through our referral program.

See how it works and start inviting friends.

ER Model & Relationship Constraints
DBMS

ER Model & Relationship Constraints

Master ER Diagrams, Entity types, Attributes, and the nuances of Cardinality and Participation.

ER Model & Relationship Constraints

Before creating database tables, we first need a clear picture of the real-world system. The Entity-Relationship (ER) Model helps us visualize data, understand how different pieces of information are connected, and identify the rules that the database should follow.

Instead of immediately writing SQL statements, database designers first create an ER Diagram. This diagram acts as a blueprint that shows entities, their attributes, and the relationships between them.

The ER Model is one of the most important topics in Database Management Systems because almost every database begins with an ER diagram.

Learning Objectives

After completing this chapter, you will be able to:

  • Understand what an ER Model is.
  • Explain entities and entity sets.
  • Differentiate between strong and weak entities.
  • Understand different types of attributes.
  • Explain relationships between entities.
  • Understand cardinality and participation constraints.
  • Read and understand ER diagrams.
  • Prepare for mapping ER diagrams into relational tables.

What is an ER Model?

The Entity-Relationship Model (ER Model) is a high-level conceptual data model used to represent the structure of a database before implementation.

It describes:

  • What information will be stored.
  • How different pieces of information are connected.
  • Rules governing those connections.
  • Important properties of each object.

Think of it as a map of your database.

Just as a city map shows roads connecting different locations, an ER Model shows relationships connecting different entities.

Why Do We Need an ER Model?

Imagine creating software for a university.

Without planning, you might create random tables that don’t work well together.

Instead, you first identify:

  • Students
  • Teachers
  • Courses
  • Departments

Then you ask:

  • Which student studies which course?
  • Which teacher teaches which subject?
  • Which department offers which course?

The ER Model answers these questions before any SQL code is written.

Main Components of an ER Model

Every ER diagram is built using three basic building blocks.

1. Entity

An Entity is a real-world object that can be identified independently.

Examples:

  • Student
  • Teacher
  • Employee
  • Department
  • Book
  • Customer

An entity usually represents a noun.

In an ER Diagram, an entity is represented using a Rectangle.

Example:

+-----------+
| Student   |
+-----------+

2. Attribute

An Attribute describes a property or characteristic of an entity.

For a Student entity, attributes may include:

  • Student_ID
  • Name
  • Age
  • Address
  • Phone Number

In an ER Diagram, attributes are represented using Ellipses.

Example

          Name
           ○
            \
+-----------+
| Student   |
+-----------+

3. Relationship

A Relationship connects two or more entities.

Examples

Student enrolls in Course

Teacher teaches Subject

Customer places Order

In an ER Diagram, relationships are represented using a Diamond.

Student ◇ Enrolls ◇ Course

Example ER Diagram

        Name
         ○
          \
     +-----------+
     | Student   |
     +-----------+
           |
      ◇ Enrolls ◇
           |
     +-----------+
     | Course    |
     +-----------+

This simple diagram tells us:

  • Student is an entity.
  • Course is another entity.
  • Enrolls is the relationship.
  • Name is an attribute.

Entity Types

Entities are mainly classified into two types.

  • Strong Entity
  • Weak Entity

Strong Entity

A Strong Entity is an entity that can be uniquely identified using its own attributes.

It does not depend on another entity.

Example

Student

Attributes

  • Student_ID
  • Name
  • Email

Student_ID uniquely identifies every student.

Therefore, Student is a Strong Entity.

Representation

Single Rectangle

+-----------+
| Student   |
+-----------+

Examples

  • Employee
  • Customer
  • Product
  • Doctor
  • Teacher

Weak Entity

A Weak Entity cannot be uniquely identified using its own attributes.

It depends on another entity called the Owner Entity.

Without the owner, the weak entity has no meaning.

Example

Employee

↓

Dependent

A dependent cannot exist unless the employee exists.

Representation

Double Rectangle

+=============+
|| Dependent ||
+=============+

Examples

  • Employee → Dependent
  • Order → Order Item
  • Book → Book Copy

Strong Entity vs Weak Entity

Strong EntityWeak Entity
Has its own primary keyDepends on owner entity
IndependentDependent
Single rectangleDouble rectangle
Can exist aloneCannot exist alone

Quiz 1

Question 1

Which of the following is a Strong Entity?

A. Student

B. Dependent

C. Order Item

D. Book Copy

Answer

Student

Question 2

A Weak Entity depends on:

A. SQL

B. Database

C. Owner Entity

D. Attribute

Answer

Owner Entity

Attributes

Attributes describe entities.

There are several types of attributes.

  • Simple Attribute
  • Composite Attribute
  • Single-Valued Attribute
  • Multi-Valued Attribute
  • Derived Attribute
  • Key Attribute

Simple Attribute

A Simple Attribute cannot be divided further.

Examples

  • Age
  • Salary
  • Gender

These values are stored directly.

Composite Attribute

A Composite Attribute can be divided into smaller meaningful parts.

Example

Address

can be divided into

  • House Number
  • Street
  • City
  • State
  • Country
  • PIN Code

Similarly,

Name

can become

  • First Name
  • Middle Name
  • Last Name

Composite attributes help organize data more effectively.

Multi-Valued Attribute

A Multi-Valued Attribute can have multiple values for one entity.

Example

A student may have multiple phone numbers.

Student

Phone Numbers

  • 9876543210
  • 9123456789

Representation

Double Ellipse

((Phone))

Other examples

  • Skills
  • Email Addresses
  • Languages Known

Derived Attribute

A Derived Attribute is calculated using another attribute.

Example

Age

Age can be calculated from

Date of Birth

Therefore, Age usually does not need to be stored permanently.

Representation

Dashed Ellipse

- - Age - -

Examples

  • Years of Experience
  • Total Marks
  • Account Balance
  • Total Price

Key Attribute

A Key Attribute uniquely identifies an entity.

Example

Student_ID

Employee_ID

Passport Number

Representation

Underlined Attribute

Student_ID
──────────

Quiz 2

Question 1

Which attribute can be divided into smaller parts?

A. Age

B. Salary

C. Address

D. Gender

Answer

Address

Question 2

Which attribute usually should not be stored because it can be calculated?

A. Name

B. Age

C. Address

D. Email

Answer

Age

Relationship

A Relationship describes how two or more entities are associated.

Examples

Student studies Course

Doctor treats Patient

Customer buys Product

Relationships represent actions or associations.

Cardinality

Cardinality specifies how many entities can participate in a relationship.

There are three main types.

One-to-One (1:1)

One manager manages one department.

Manager
   |
 1:1
   |
Department

One-to-Many (1:N)

One department has many employees.

Department
     |
    1:N
     |
Employee

Many-to-Many (M:N)

Many students enroll in many courses.

Student
    |
   M:N
    |
Course

Many-to-Many relationships are extremely common.

Examples

  • Doctors and Patients
  • Actors and Movies
  • Books and Authors

Participation Constraint

Participation tells us whether an entity must participate in a relationship.

There are two types.

Total Participation

Every entity must participate.

Example

Every employee must belong to a department.

Representation

Double Line

Partial Participation

Participation is optional.

Example

Some employees may receive bonuses.

Not every employee receives one.

Representation

Single Line

Quiz 3

Question 1

Which relationship allows one student to study many courses and one course to have many students?

A. 1:1

B. 1:N

C. M:N

D. None

Answer

M:N

Question 2

In Total Participation,

A. Participation is optional

B. Every entity must participate

C. Only weak entities participate

D. None

Answer

Every entity must participate

ER Diagram Symbols

SymbolMeaning
RectangleEntity
Double RectangleWeak Entity
EllipseAttribute
Double EllipseMulti-Valued Attribute
Dashed EllipseDerived Attribute
Underlined AttributeKey Attribute
DiamondRelationship
Double LineTotal Participation
Single LinePartial Participation

Remember These Points

  • Every database starts with identifying entities.
  • Entities contain attributes.
  • Relationships connect entities.
  • Strong entities exist independently.
  • Weak entities depend on owner entities.
  • Composite attributes have multiple components.
  • Derived attributes are calculated.
  • Multi-valued attributes store multiple values.
  • Cardinality tells how many entities participate.
  • Participation tells whether participation is mandatory or optional.

Interview-Focused Questions

Q: Why can’t a Weak Entity exist independently?

A: Because it does not have enough attributes to uniquely identify itself. It depends on the primary key of its owner entity.

Q: Why is Age considered a derived attribute?

A: Age can always be calculated from the Date of Birth, so storing it separately may lead to inconsistent data over time.

Q: What is the difference between One-to-Many and Many-to-Many relationships?

A: In a One-to-Many relationship, one entity is associated with multiple entities on the other side. In a Many-to-Many relationship, both entities can have multiple related records.

This version removes both the Chapter Summary and Practice Questions sections while keeping the lesson comprehensive, beginner-friendly, and consistent with your MDX course format.

My Private Notes

Notes are auto-saved locally to this device.