📘 ERD (Entity-Relationship Diagram)

An Entity-Relationship Diagram (ERD) is a visual representation of the data model for a database system. It shows entities (tables), their attributes (columns), and the relationships between entities.

ERDs are widely used in database design, data modeling, and systems analysis to map out data structures before implementation.

🔹 Why Use ERDs?

• Provides a blueprint for database design
• Helps communicate data requirements between business and technical teams
• Ensures data is structured consistently
• Supports normalization and relationship clarity
• Useful for documenting and maintaining databases over time

🔹 Key Components of ERD

1. Entities
   • Represent real-world objects or concepts
   • Become tables in the database
   • Example: Customer, Order, Product
   • Notation: Rectangles
2. Attributes
   • Properties or fields of an entity
   • Become columns in a table
   • Example: CustomerName, Email, OrderDate
   • Notation: Ovals connected to entities (or listed inside rectangles in modern ERDs)
3. Primary Key (PK)
   • Unique identifier for each entity
   • Example: CustomerID in Customer table
4. Foreign Key (FK)
   • Attribute in one table that references the primary key of another
   • Defines relationships between entities
5. Relationships
   • Show how entities are connected
   • Types:
     • One-to-One (1:1) – One entity instance relates to exactly one of another
       Example: A passport belongs to one person
     • One-to-Many (1:N) – One entity relates to many of another
       Example: One customer can place many orders
     • Many-to-Many (M:N) – Many entities relate to many of another
       Example: Students enrolled in many courses

🔹 Types of ERD Notations

1. Chen Notation
   • Entities = Rectangles
   • Attributes = Ovals
   • Relationships = Diamonds
2. Crow’s Foot Notation (most common in databases)
   • Entities = Rectangles
   • Attributes listed inside
   • Relationships = Lines with crow’s foot symbols for cardinality
3. UML (Unified Modeling Language)
   • Uses classes and associations
   • Common in software engineering

🔹 Example ERD: E-commerce Database

Entities:

• Customer (CustomerID, Name, Email)
• Order (OrderID, CustomerID, OrderDate, TotalAmount)
• Product (ProductID, ProductName, Price)
• OrderDetail (OrderID, ProductID, Quantity)

Relationships:

• One Customer → Many Orders (1:N)
• One Order → Many Products (M:N via OrderDetail)

Conceptual Layout:

Customer (PK: CustomerID)
    |
    | 1:N
    |
Order (PK: OrderID, FK: CustomerID)
    |
    | M:N (via OrderDetail)
    |
Product (PK: ProductID)

🔹 Levels of ER Diagrams

1. Conceptual ERD
   • High-level, business-focused
   • Entities + relationships (no attributes)
2. Logical ERD
   • Adds attributes and primary/foreign keys
   • More technical, but not DBMS-specific
3. Physical ERD
   • Maps directly to database tables
   • Includes data types, constraints, indexes

🔹 Tools to Create ERDs

• Online: Lucidchart, dbdiagram.io, Draw.io
• Database Tools: MySQL Workbench, SQL Server Management Studio (SSMS), DBeaver
• Enterprise Tools: ER/Studio, ERwin Data Modeler, PowerDesigner

🔹 Best Practices for ERD Design

1. Use clear, consistent naming (CustomerID, not C_ID)
2. Identify primary keys for all entities
3. Normalize data to remove redundancy (up to 3NF typically)
4. Document cardinality (1:1, 1:N, M:N) clearly
5. Use surrogate keys (auto-increment IDs) for consistency
6. Avoid many-to-many relationships – resolve with junction tables
7. Keep diagrams readable – don’t overload with too many attributes

✅ Summary

• An ERD is a visual blueprint of database design.
• It includes entities (tables), attributes (columns), and relationships.
• Helps ensure consistency, clarity, and efficiency in database development.
• Different notations (Chen, Crow’s Foot, UML) can be used depending on the audience.
• A well-designed ERD leads to a robust, scalable database architecture.