An Entity-Relationship Diagram (ERD) is a visual representation of the data model that

depicts the structure of a database. It shows how entities (data objects) relate to one another and
is commonly used in database design to illustrate the relationships between tables.

ERDs are composed of three main components:

1. Entities
2. Attributes
3. Relationships

1. Entities

An entity represents a real-world object or concept that can have data stored about it in a
database. Each entity becomes a table in the database.

 Entities are typically represented as rectangles in an ERD.

 Examples of entities: Customer, Product, Order, Employee, etc.

Example:

 Customer entity might have the following attributes: CustomerID, CustomerName,

Address, Phone.

2. Attributes

Attributes describe the properties or characteristics of an entity. Each attribute becomes a column
in the entity's corresponding table.

 Attributes are represented as ovals connected to their respective entity.

 Types of attributes include:
o Simple Attribute: Cannot be divided further (e.g., FirstName).
o Composite Attribute: Can be divided into smaller parts (e.g., FullName can be
divided into FirstName and LastName).
o Derived Attribute: Can be derived from other attributes (e.g., Age can be derived
from DateOfBirth).
o Multivalued Attribute: Can have multiple values (e.g., a Phone attribute for an
entity Employee may have more than one phone number).
o Key Attribute: Uniquely identifies an entity (e.g., CustomerID).

Example:

For the Customer entity:

 Attributes could be CustomerID, CustomerName, Address, Phone.

3. Relationships
A relationship shows how two entities are related to each other. The relationship between
entities often translates into foreign keys in a relational database.

 Relationships are depicted using diamonds or by directly connecting entities with lines.
 Relationships can have different cardinalities, which define the number of instances of
one entity that can or must be associated with each instance of another entity.
o One-to-One (1:1): An entity in one set is related to one entity in another set.
o One-to-Many (1): One entity in a set is related to many entities in another set.
o Many-to-Many (M): Many entities in one set are related to many entities in
another set.

Example:

 A Customer places one or more Orders.

o Relationship: Customer has a one-to-many relationship with Order (One
customer can place multiple orders).

Key Concepts in ERD

a. Primary Key (PK)

 A primary key is a unique identifier for each record in a table. In an ERD, the primary
key is usually underlined or indicated in the diagram.

b. Foreign Key (FK)

 A foreign key is a field (or collection of fields) in one table that refers to the primary key
in another table. Foreign keys establish the relationship between the tables.

c. Cardinality

 Cardinality indicates the number of instances of one entity that can be related to
instances of another entity. It is typically shown using symbols such as:
o 1 for one
o N for many
 One-to-One (1:1): A customer can have only one loyalty card, and a loyalty card can
belong to only one customer.
 One-to-Many (1): A customer can place many orders, but each order belongs to only one
customer.
 Many-to-Many (M): A product can be part of many orders, and each order can contain
multiple products.
Example 1: Simple ERD for a Library System

Entities:

 Book: Represents books in the library.

 Member: Represents library members.
 Loan: Represents the borrowing transaction.

Attributes for Each Entity:

 Book:

 BookID (PK)
 Title
 Author
 PublicationYear
 ISBN

 Member:

 MemberID (PK)
 Name
 Email
 Phone

 Loan:

 LoanID (PK)
 LoanDate
 ReturnDate
 BookID (FK)
 MemberID (FK)

Relationships:

 One-to-Many (1): A Member can borrow many Books, but each Loan transaction
involves only one Book.
 One-to-Many (1): A Book can be borrowed by multiple Members over time, but each
Loan is associated with one Member.
ERD Diagram:

+-----------+ +-----------+ +---------+

| Member | 1 ------ N| Loan | N ------ 1 | Book |
+-----------+ +-----------+ +---------+
| MemberID PK| | LoanID PK | | BookID PK|
| Name | | LoanDate | | Title |
| Email | | ReturnDate| | Author |
| Phone | | BookID FK | | ISBN |
+-----------+ | MemberID FK| | PubYear |
+-----------+ +---------+

Explanation:

 Entities: Member, Loan, and Book are represented as tables in the database.
 Relationships: A member can borrow many books, so there is a one-to-many
relationship between Member and Loan, and Book and Loan.
 Attributes: Each entity has attributes that describe its properties.

Example 2: ERD for an Online Retail System

Entities:

 Customer: Represents users of the system.

 Product: Represents the products available for purchase.
 Order: Represents customer purchases.

Attributes for Each Entity:

 Customer:

 CustomerID (PK)
 CustomerName
 Email
 Phone

 Product:

 ProductID (PK)
 ProductName
 Price
 StockQuantity

 Order:
  OrderID (PK)
 OrderDate
 TotalAmount
 CustomerID (FK)

Relationships:

 One-to-Many (1): A Customer can place many Orders, but each Order is placed by
only one Customer.
 Many-to-Many (M): Each Order can contain many Products, and each Product can be
part of many Orders. This requires a junction table (like OrderDetails) to break down
the many-to-many relationship into two one-to-many relationships.

Junction Table (OrderDetails):

 OrderDetails stores the relationship between Order and Product.

 OrderID (FK)
 ProductID (FK)
 Quantity

ERD Diagram:

+-----------+ +-----------+ +-------------+

| Customer | 1 N | Order | 1 N | OrderDetails|
+-----------+-------+-----------+--------+-------------+
|CustomerID PK| |OrderID PK | |OrderID FK |
|CustomerName | |OrderDate | |ProductID FK |
|Email | |TotalAmount| |Quantity |
+-----------+ |CustomerID FK| +-------------+
+-----------+ N 1
+---------+
| Product |
+---------+
|ProductID PK|
|ProductName |
|Price |
|StockQuantity|
+---------+

Explanation:

 Entities: Customer, Order, Product, and the junction table OrderDetails.

 Relationships:
o A customer can place multiple orders, and each order can include many products.
o OrderDetails breaks down the many-to-many relationship between Order and
Product.
 Attributes: CustomerName, ProductName, OrderDate, etc., describe the data for each
entity.
Example 3: ERD for a University Database

Entities:

 Student: Represents students in the university.

 Course: Represents courses offered by the university.
 Enrollment: Represents the relationship between students and courses.

Attributes for Each Entity:

 Student:
o StudentID (PK)
o StudentName
o Email
 Course:
o CourseID (PK)
o CourseName
o Credits
 Enrollment:
o EnrollmentID (PK)
o Grade
o StudentID (FK)
o CourseID (FK)

Relationships:

 Many-to-Many (M

): Students can enroll in many courses, and each course can have many students. The
Enrollment table serves as a junction table that links Student and Course.

ERD Diagram:

+-----------+ +-----------+ +-------------+

| Student | 1 N | Enrollment| N 1 | Course |
+-----------+---------+-----------+-----------+-------------+
|StudentID PK| |EnrollmentID PK| |CourseID PK |
|StudentName | |Grade | |CourseName |
|Email | |StudentID FK | |Credits |
+-----------+ |CourseID FK | +-------------+
+-----------+

Explanation:

 Entities: Student, Course, and the junction table Enrollment.

 Relationships: Students can enroll in multiple courses, and each course can have
multiple students.
 Attributes: Each entity is described by its relevant attributes, such as StudentName for
Student, and CourseName for Course.