Core Concepts of ER Model

Sql tutorial · PySpark.in

6. Modeling Databases

Before creating database tables, we first need to understand:

• What data should be stored?
• How different data is connected?
• What relationships exist between data?

Database modeling helps us answer these questions.

One of the most commonly used techniques for database design is the:

ER Model (Entity Relationship Model)

The ER Model represents:

• Entities
• Attributes
• Relationships

between real-world objects.

It acts as a blueprint for designing databases.

6.1 Core Concepts in ER Model

The ER Model consists of several important concepts.

Entity

A real-world object or concept is called an Entity.

Entities can be:

• Physical objects
• People
• Places
• Events
• Organizations

Examples of Entities

Real-Life Examples

John
Emma
Apple
Google

Each of these represents a real-world object, so they are entities.

Attributes of an Entity

Attributes are the properties or characteristics of an entity.

They describe the entity.

Example

Consider a Student entity.

Here:

• name
• age
• city

are attributes of the Student entity.

Another Example

Attributes:

name
age

Key Attribute

A key attribute uniquely identifies each entity.

No two entities can have the same key value.

Example

Consider the Student entity:

Here:

student_id

is the key attribute because it uniquely identifies every student.

Why Key Attributes Are Important

Key attributes help us:

• Avoid duplicate records
• Identify rows uniquely
• Connect tables using relationships

Entity Type

An Entity Type is a collection of entities having the same attributes.

Example

Entity Type: Student

All students have the same attributes:

student_id
name
age

Therefore, they belong to the same entity type: Student.

Relationships

A relationship represents an association between entities.

Relationships show how entities are connected.

Example

Consider:

• Customer
• Order

A customer places orders.

So:

Customer → places → Order

This association is called a relationship.

Types of Relationships

Relationships are classified based on how many entities are associated.

1. One-to-One Relationship (1:1)

In a One-to-One relationship:

• One entity is associated with only one entity
• Vice versa is also true

Example: Person and Passport

One Person → One Passport
One Passport → One Person

Diagram

Real-Life Examples

• Person ↔ Passport
• Country ↔ Capital
• Employee ↔ Locker

2. One-to-Many Relationship (1:M)

In a One-to-Many relationship:

• One entity can be related to many entities
• But each child entity belongs to only one parent entity

Example: Person and Cars

One Person → Many Cars
One Car → One Person

Diagram

Real-Life Examples

• Customer → Orders
• Department → Employees
• Teacher → Students

3. Many-to-Many Relationship (M:N)

In a Many-to-Many relationship:

• Multiple entities are related to multiple entities

Example: Students and Courses

One Student → Many Courses
One Course → Many Students

Diagram

Real-Life Examples

• Students ↔ Courses
• Actors ↔ Movies
• Products ↔ Orders

Cardinality Ratio

Cardinality defines the maximum number of relationships between entities.

It describes:

How many instances of one entity
can relate to another entity

Types of Cardinality

Understanding Cardinality

1:1 Relationship

One Employee → One Locker

1:M Relationship

One Customer → Many Orders

M:1 Relationship

Many Employees → One Department

M:N Relationship

Many Students ↔ Many Courses

Applying ER Model Concepts

Let us understand ER modeling using a real-world application.

Example: E-Commerce Application

An e-commerce application contains entities such as:

• Customers
• Products
• Carts
• Addresses

Entity Types

Relationship 1 — Customer and Cart

A customer has only one cart.

A cart belongs to only one customer.

Relationship Type

Customer → Cart = One-to-One (1:1)

Diagram

Relationship 2 — Cart and Products

A cart can contain many products.

A product can exist in many carts.

Relationship Type

Cart → Products = Many-to-Many (M:N)

Diagram

Relationship 3 — Customer and Address

A customer can save multiple addresses.

Each address belongs to one customer.

Relationship Type

Customer → Address = One-to-Many (1:M)

Diagram

ER Model Summary for E-Commerce Application

Why ER Modeling Is Important

ER modeling helps us:

• Design databases properly
• Identify entities and relationships
• Reduce redundancy
• Improve data organization
• Create scalable database structures

More Sql tutorials

• Introduction to Databases
• Introduction To DBMS
• Introduction To SQL
• Natural Join
• INNER JOIN
• LEFT JOIN

All tutorials · Try the free PySpark compiler · Practice challenges