Entity Relationship Diagrams (ERDs)

1. Overview

Entity Relationship Diagrams (ERDs) are visual representations of the data model in a database system. Introduced by Peter Chen in 1976, ERDs provide a clear, standardized way to communicate database structure to both technical and non-technical stakeholders.

Purpose in Database Design:

• Represent entities (objects), their attributes (properties), and relationships (connections)
• Serve as a bridge between business requirements and physical database implementation
• Enable stakeholders to validate data requirements before writing code
• Provide documentation for system maintenance and evolution

Three Levels of ERD:

• Conceptual ERD: High-level overview, focused on main entities and relationships. Technology-independent. No attributes beyond entity names.
• Logical ERD: Detailed specification of all entities, attributes, relationships, and cardinality. Still database-agnostic. Serves as blueprint for the physical model.
• Physical ERD: Database-specific implementation. Includes data types, column names, indexes, primary keys, and foreign keys. Maps directly to CREATE TABLE statements.

ERDs are central to the database design lifecycle: requirements gathering → conceptual design → logical design → normalization → physical implementation → validation.

2. Core Elements

Entity (Rectangle): A thing or object that can be uniquely identified. Examples: Customer, Product, Order.

┌──────────────┐
│   Customer   │
└──────────────┘

Attribute (Oval): A property or characteristic of an entity. Example: Customer has CustomerID, Name, Email.

         ┌─────────────┐
         │ CustomerID  │
         └─────────────┘
              │
        ┌─────┴─────┐
        │  Customer  │
        └─────┬─────┘
             │
    ┌────────┼────────┐
    │        │        │
┌───────┐┌──────┐┌─────────┐
│ Name  ││Email ││ Phone   │
└───────┘└──────┘└─────────┘

Key Attribute (Underlined): An attribute that uniquely identifies an entity instance.

┌──────────────────────┐
│     Customer         │
├──────────────────────┤
│ CustomerID (PK)      │
│ Name                 │
│ Email                │
└──────────────────────┘

Relationship (Diamond): An association between entities. Example: Customer places Order.

┌──────────┐          ┌──────────┐
│ Customer │──────────│  places  │──────────│ Order │
└──────────┘    ◇     └──────────┘          └──────┘

Weak Entity (Double Rectangle): An entity that cannot be uniquely identified by its own attributes alone; depends on a strong entity for identity.

┌════════════════════════════════════┐
║         OrderItem                   ║
║  (weak entity, depends on Order)    ║
╚════════════════════════════════════╝

Derived Attribute (Dashed Oval): An attribute computed or derived from other attributes, not stored directly.

    ┌─────────────────┐
    │ Total (derived) │
    │ (dashed)        │
    └─────────────────┘
           │
      ┌────┴────┐
      │ Product │
      └────┬────┘

Multi-valued Attribute (Double Oval): An attribute that can have multiple values for a single entity instance. Example: Customer can have multiple phone numbers.

    ┌─────────────────┐
    │  Phone Numbers  │
    │  (double oval)  │
    └─────────────────┘
           │
      ┌────┴────────┐
      │  Customer   │
      └─────────────┘

Composite Attribute: An attribute composed of multiple sub-attributes. Example: Address (Street, City, State, ZIP).

        ┌──────────────┐
        │   Address    │
        │ (composite)  │
        └──────┬───────┘
         ┌─────┼─────┬─────┐
         │     │     │     │
      ┌──┴──┐┌─┴──┐┌─┴──┐┌─┴──┐
      │Street││City││State││ ZIP│
      └──────┘└────┘└────┘└────┘

3. Cardinality and Participation

Cardinality defines the maximum number of relationship instances. Participation defines whether all instances of an entity must participate in a relationship.

One-to-One (1:1): Each instance of Entity A relates to at most one instance of Entity B, and vice versa.

┌──────────────┐    1      1    ┌─────────────┐
│  Employee    │────────────────│  Office     │
└──────────────┘                └─────────────┘
Each employee has one office, each office has one employee.

One-to-Many (1:N): Each instance of Entity A can relate to many instances of Entity B, but each B relates to only one A.

┌──────────────┐    1      N    ┌──────────────┐
│   Customer   │────────────────│    Order     │
└──────────────┘                └──────────────┘
One customer can have many orders, but each order belongs to one customer.

Many-to-Many (M:N): Each instance of Entity A can relate to many instances of Entity B, and vice versa. Typically resolved with a junction (bridge) table.

┌──────────────┐    M      N    ┌───────────────┐
│   Student    │────────────────│    Course     │
└──────────────┘                └───────────────┘
Students enroll in many courses; courses have many students.

Total vs Partial Participation:

• Total Participation (Double Line): Every instance of an entity must participate in the relationship.
• Partial Participation (Single Line): Some instances may not participate in the relationship.

Total Participation (all customers must have at least one order):
┌──────────────┐    1      N    ┌──────────────┐
│   Customer   │═════════════════│    Order     │
└──────────────┘                └──────────────┘

Partial Participation (not all customers have orders):
┌──────────────┐    1      N    ┌──────────────┐
│   Customer   │─────────────────│    Order     │
└──────────────┘                └──────────────┘

4. Notation Styles

Chen Notation (Original, 1976): Uses diamonds for relationships. Primarily used in academia.

┌──────────────┐        ┌──────────┐        ┌──────────────┐
│   Customer   │────────│  places  │────────│    Order     │
└──────────────┘        └──────────┘        └──────────────┘
                            ◇

Crow's Foot / IE Notation (Industry Standard): Uses symbols resembling crow's feet for cardinality. Most common in practice.

One customer to many orders:
┌──────────────┐          ┌──────────────┐
│   Customer   │──────────||o────|{ Order │
└──────────────┘          └──────────────┘

Where:
  ||  = one (mandatory)
  |o  = zero or one (optional)
  |{  = many
  |{o = zero or many (optional)

UML Class Diagram Style: Focuses on class hierarchies and associations. Uses arrows and multiplicity notation.

Customer ────────── 1     *  Order
                    places

Barker's Notation (Oracle): Emphasizes optionality and uniqueness. Uses circles and lines.

┌──────────────┐        ┌──────────────┐
│   Customer   │————o──────|  Order     │
└──────────────┘        └──────────────┘

Comparing the Same Relationship Across Notations:

Scenario: A customer places many orders, but not all customers must have orders (partial participation).

Chen Notation:
                        ┌──────────┐
                        │  places  │
                        └──────────┘
                            ◇
                      1    │    N
┌──────────────┐     ──────┴──────  ┌──────────────┐
│   Customer   │                    │    Order     │
└──────────────┘    (Single line = partial)

Crow's Foot:
┌──────────────┐                    ┌──────────────┐
│   Customer   │──────────o|────|{  │    Order     │
└──────────────┘                    └──────────────┘

UML:
Customer ──────────── 0..*  Order

Barker's:
┌──────────────┐                    ┌──────────────┐
│   Customer   │────o────────|{     │    Order     │
└──────────────┘                    └──────────────┘

5. Example: E-Commerce Database

Entities: Customer, Order, OrderItem, Product, Category, Payment, ShippingAddress, Review.

┌──────────────────────┐
│     Category         │
├──────────────────────┤
│ CategoryID (PK)      │
│ Name                 │
│ Description          │
└──────────┬───────────┘
           │ 1
           │
           N
           │
┌──────────────────────┐          ┌──────────────────────┐
│     Product          │          │   Review             │
├──────────────────────┤          ├──────────────────────┤
│ ProductID (PK)       │──1───N───│ ReviewID (PK)        │
│ CategoryID (FK)      │          │ ProductID (FK)       │
│ Name                 │          │ CustomerID (FK)      │
│ Price                │          │ Rating               │
│ Stock                │          │ Comment              │
└──────────┬───────────┘          │ DateCreated          │
           │                      └──────────────────────┘
           │
           N
           │
┌──────────────────────┐
│   OrderItem          │ (Weak Entity)
├──────────────────────┤
│ OrderID (PK/FK)      │
│ ProductID (PK/FK)    │
│ Quantity             │
│ UnitPrice            │
│ Subtotal (derived)   │
└─────┬────────────────┘
      │
      N
      │
      1
      │
┌──────────────────────────┐     ┌──────────────────────┐
│     Order                │     │   ShippingAddress    │
├──────────────────────────┤     ├──────────────────────┤
│ OrderID (PK)             │──1──│ AddressID (PK)       │
│ CustomerID (FK)          │     │ OrderID (FK)         │
│ OrderDate                │     │ Street               │
│ TotalAmount              │     │ City                 │
│ Status                   │     │ State                │
└──────────┬───────────────┘     │ ZIP                  │
           │                     │ Country              │
           │ 1                   └──────────────────────┘
           │
           1
           │
┌──────────────────────┐
│   Customer           │
├──────────────────────┤
│ CustomerID (PK)      │
│ FirstName            │
│ LastName             │
│ Email                │
│ Phone                │
│ CreatedDate          │
└──────────┬───────────┘
           │
           1
           │
           N
           │
┌──────────────────────┐
│   Payment            │
├──────────────────────┤
│ PaymentID (PK)       │
│ OrderID (FK)         │
│ PaymentMethod        │
│ Amount               │
│ TransactionDate      │
│ Status               │
└──────────────────────┘

SQL DDL for E-Commerce Database:

CREATE TABLE Category (
    CategoryID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Description TEXT
);

CREATE TABLE Product (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    CategoryID INT NOT NULL,
    Name VARCHAR(150) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    Stock INT NOT NULL DEFAULT 0,
    FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID) ON DELETE RESTRICT
);

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Phone VARCHAR(20),
    CreatedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT NOT NULL,
    OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    TotalAmount DECIMAL(12, 2) NOT NULL,
    Status ENUM('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled') DEFAULT 'Pending',
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ON DELETE CASCADE
);

CREATE TABLE OrderItem (
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    UnitPrice DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID) ON DELETE RESTRICT
);

CREATE TABLE ShippingAddress (
    AddressID INT PRIMARY KEY AUTO_INCREMENT,
    OrderID INT NOT NULL,
    Street VARCHAR(150) NOT NULL,
    City VARCHAR(50) NOT NULL,
    State VARCHAR(50) NOT NULL,
    ZIP VARCHAR(10) NOT NULL,
    Country VARCHAR(50) NOT NULL,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE
);

CREATE TABLE Payment (
    PaymentID INT PRIMARY KEY AUTO_INCREMENT,
    OrderID INT NOT NULL,
    PaymentMethod ENUM('CreditCard', 'DebitCard', 'PayPal', 'BankTransfer') NOT NULL,
    Amount DECIMAL(12, 2) NOT NULL,
    TransactionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Status ENUM('Pending', 'Completed', 'Failed') DEFAULT 'Pending',
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE RESTRICT
);

CREATE TABLE Review (
    ReviewID INT PRIMARY KEY AUTO_INCREMENT,
    ProductID INT NOT NULL,
    CustomerID INT NOT NULL,
    Rating INT NOT NULL CHECK (Rating BETWEEN 1 AND 5),
    Comment TEXT,
    DateCreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID) ON DELETE CASCADE,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ON DELETE CASCADE
);

6. Example: University Database

Entities: Student, Course, Enrollment (junction table with attributes), Professor, Department, Classroom.

┌──────────────────────┐
│   Department         │
├──────────────────────┤
│ DeptID (PK)          │
│ Name                 │
│ Location             │
└──────────┬───────────┘
           │ 1
           │
           N
           │
┌──────────────────────┐          ┌──────────────────────┐
│   Professor          │          │   Classroom          │
├──────────────────────┤          ├──────────────────────┤
│ ProfID (PK)          │──1────N──│ ClassroomID (PK)     │
│ DeptID (FK)          │          │ Building             │
│ FirstName            │          │ RoomNumber           │
│ LastName             │          │ Capacity             │
│ Email                │          └──────────────────────┘
│ Specialization       │
└──────────┬───────────┘
           │
           │ 1
           │
           N
           │
┌──────────────────────┐
│   Course             │
├──────────────────────┤
│ CourseID (PK)        │
│ ProfID (FK)          │
│ ClassroomID (FK)     │
│ Code                 │
│ Title                │
│ Credits              │
│ Schedule             │
└──────────┬───────────┘
           │
           M
           │
           │
     ╔═════╩═════╗
     ║ Enrollment ║ (Weak Entity, M:N junction)
     ║  (derived  ║
     ║ attributes)║
     ╠═════════════╣
     ║ StudentID  ║
     ║ CourseID   ║
     ║ Semester   ║
     ║ Grade      ║
     ║ GPA (der.) ║
     ╚═════╤═════╝
           │
           N
           │
           │
┌──────────────────────┐
│   Student            │
├──────────────────────┤
│ StudentID (PK)       │
│ FirstName            │
│ LastName             │
│ Email                │
│ DOB                  │
│ Enrollment Date      │
│ CumulativeGPA (der.) │
└──────────────────────┘

Key Point: Enrollment is a weak entity that depends on Student and Course. Its primary key is (StudentID, CourseID, Semester). The relationship between Student and Course is many-to-many, broken down through the Enrollment junction table.

CREATE TABLE Department (
    DeptID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL UNIQUE,
    Location VARCHAR(100)
);

CREATE TABLE Classroom (
    ClassroomID INT PRIMARY KEY AUTO_INCREMENT,
    Building VARCHAR(50) NOT NULL,
    RoomNumber VARCHAR(10) NOT NULL,
    Capacity INT NOT NULL,
    UNIQUE KEY (Building, RoomNumber)
);

CREATE TABLE Professor (
    ProfID INT PRIMARY KEY AUTO_INCREMENT,
    DeptID INT NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Specialization VARCHAR(150),
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID) ON DELETE RESTRICT
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY AUTO_INCREMENT,
    ProfID INT NOT NULL,
    ClassroomID INT NOT NULL,
    Code VARCHAR(20) UNIQUE NOT NULL,
    Title VARCHAR(150) NOT NULL,
    Credits INT NOT NULL CHECK (Credits > 0),
    Schedule VARCHAR(100),
    FOREIGN KEY (ProfID) REFERENCES Professor(ProfID) ON DELETE RESTRICT,
    FOREIGN KEY (ClassroomID) REFERENCES Classroom(ClassroomID) ON DELETE RESTRICT
);

CREATE TABLE Student (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    DOB DATE,
    EnrollmentDate DATE NOT NULL
);

CREATE TABLE Enrollment (
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    Semester VARCHAR(20) NOT NULL,
    Grade CHAR(1) CHECK (Grade IN ('A', 'B', 'C', 'D', 'F', 'W')),
    PRIMARY KEY (StudentID, CourseID, Semester),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE,
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID) ON DELETE CASCADE
);

7. Example: Hospital Database

Entities: Patient, Doctor, Appointment, Department, Prescription, Medicine. Demonstrates inheritance/specialization.

┌──────────────────────┐
│    Person (Super)    │
├──────────────────────┤
│ PersonID (PK)        │
│ FirstName            │
│ LastName             │
│ DOB                  │
│ Phone                │
└──────┬───────────────┘
       │
    ╱──┴──╲
   ╱      ╲
 ╱        ╲
Patient    Doctor (Specialization/Inheritance)
│          │
│          └─────→ ┌──────────────────────┐
│                  │   Department         │
│                  ├──────────────────────┤
│                  │ DeptID (PK)          │
│                  │ Name                 │
│                  │ Budget               │
│                  └────────┬─────────────┘
│                           │ 1
│                           │
│                           N
│                  ┌────────────────────────┐
│                  │ Doctor (Specialized)   │
│                  ├────────────────────────┤
│                  │ DoctorID (PK/FK)       │
│                  │ PersonID (FK)          │
│                  │ DeptID (FK)            │
│                  │ License                │
│                  │ Specialization         │
│                  └──────────┬─────────────┘
│                             │
│                             1
│                             │
│                             N
│                  ┌────────────────────────┐
│                  │   Appointment          │
│                  ├────────────────────────┤
└─────────┬────────│ AppointmentID (PK)     │
          │        │ PatientID (FK)         │
          │        │ DoctorID (FK)          │
          │        │ DateTime               │
          │        │ Reason                 │
          │        │ Status                 │
          N        └────────────┬───────────┘
          │                     │
          │                     1
          │                     │
┌─────────┴────────────────────┐
│ Patient (Specialized)        │
├──────────────────────────────┤
│ PatientID (PK/FK)            │
│ PersonID (FK)                │
│ MedicalRecordNumber          │
│ BloodType                    │
│ Allergies                    │
└──────────┬───────────────────┘
           │
           1
           │
           N
           │
┌──────────────────────┐
│   Prescription       │
├──────────────────────┤
│ PrescriptionID (PK)  │
│ PatientID (FK)       │
│ DoctorID (FK)        │
│ DateTime             │
│ Instructions         │
└──────────┬───────────┘
           │
           N
           │
           M
           │
┌──────────────────────┐
│   Medicine           │
├──────────────────────┤
│ MedicineID (PK)      │
│ Name                 │
│ Dosage               │
│ Side Effects         │
└──────────────────────┘

-- Supertype table (Person)
CREATE TABLE Person (
    PersonID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DOB DATE,
    Phone VARCHAR(20)
);

-- Subtype table (Patient)
CREATE TABLE Patient (
    PatientID INT PRIMARY KEY AUTO_INCREMENT,
    PersonID INT NOT NULL,
    MedicalRecordNumber VARCHAR(50) UNIQUE NOT NULL,
    BloodType CHAR(3),
    Allergies TEXT,
    FOREIGN KEY (PersonID) REFERENCES Person(PersonID) ON DELETE CASCADE
);

-- Department table
CREATE TABLE Department (
    DeptID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL UNIQUE,
    Budget DECIMAL(12, 2)
);

-- Subtype table (Doctor)
CREATE TABLE Doctor (
    DoctorID INT PRIMARY KEY AUTO_INCREMENT,
    PersonID INT NOT NULL,
    DeptID INT NOT NULL,
    License VARCHAR(50) UNIQUE NOT NULL,
    Specialization VARCHAR(100),
    FOREIGN KEY (PersonID) REFERENCES Person(PersonID) ON DELETE CASCADE,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID) ON DELETE RESTRICT
);

-- Appointment table (1:N from Patient and Doctor)
CREATE TABLE Appointment (
    AppointmentID INT PRIMARY KEY AUTO_INCREMENT,
    PatientID INT NOT NULL,
    DoctorID INT NOT NULL,
    DateTime DATETIME NOT NULL,
    Reason TEXT,
    Status ENUM('Scheduled', 'Completed', 'Cancelled') DEFAULT 'Scheduled',
    FOREIGN KEY (PatientID) REFERENCES Patient(PatientID) ON DELETE CASCADE,
    FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID) ON DELETE RESTRICT
);

-- Medicine table
CREATE TABLE Medicine (
    MedicineID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL UNIQUE,
    Dosage VARCHAR(50),
    SideEffects TEXT
);

-- Prescription table (M:N between Patient and Medicine through Doctor)
CREATE TABLE Prescription (
    PrescriptionID INT PRIMARY KEY AUTO_INCREMENT,
    PatientID INT NOT NULL,
    DoctorID INT NOT NULL,
    MedicineID INT NOT NULL,
    DateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    Instructions TEXT,
    FOREIGN KEY (PatientID) REFERENCES Patient(PatientID) ON DELETE CASCADE,
    FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID) ON DELETE RESTRICT,
    FOREIGN KEY (MedicineID) REFERENCES Medicine(MedicineID) ON DELETE RESTRICT
);

8. Normalization Connection

ERDs must be normalized to eliminate data redundancy and dependency anomalies. Normalization progressively refines the logical model toward higher normal forms.

First Normal Form (1NF): Eliminate repeating groups and multi-valued attributes. Each attribute must be atomic (indivisible).

Unnormalized Table:

-- BAD: Repeating group (PhoneNumbers is multi-valued in one row)
CREATE TABLE Customer_Unnormalized (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    PhoneNumbers VARCHAR(200)  -- Stores "555-1111, 555-2222, 555-3333"
);

1NF Normalized:

-- GOOD: Separate table for multi-valued attribute
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE CustomerPhone (
    PhoneID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT NOT NULL,
    PhoneNumber VARCHAR(20),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ON DELETE CASCADE
);

Second Normal Form (2NF): Ensure all non-key attributes depend on the entire primary key (eliminate partial dependencies).

Unnormalized Table (Violates 2NF):

-- BAD: StudentCourse composite key, but StudentName depends only on StudentID, not both
CREATE TABLE StudentCourse (
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    StudentName VARCHAR(100),  -- Partial dependency: depends on StudentID only
    CourseTitle VARCHAR(150),  -- Partial dependency: depends on CourseID only
    Grade CHAR(1),             -- Depends on both (good)
    PRIMARY KEY (StudentID, CourseID)
);

2NF Normalized:

-- GOOD: Decomposed into separate tables
CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100)
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseTitle VARCHAR(150)
);

CREATE TABLE Enrollment (
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    Grade CHAR(1),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Third Normal Form (3NF): Ensure all non-key attributes depend only on the primary key, not on other non-key attributes (eliminate transitive dependencies).

Unnormalized Table (Violates 3NF):

-- BAD: DepartmentLocation depends on DepartmentName, not on EmployeeID
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    DepartmentName VARCHAR(100),
    DepartmentLocation VARCHAR(100)  -- Transitive dependency via DepartmentName
);

3NF Normalized:

-- GOOD: Separate Department table
CREATE TABLE Department (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100),
    Location VARCHAR(100)
);

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    DepartmentID INT NOT NULL,
    FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);

Boyce-Codd Normal Form (BCNF): For every determinant (attribute that determines another), it must be a candidate key.

BCNF Violation:

-- BAD: ProfessorID → Time (determines schedule), but ProfessorID is not a candidate key
CREATE TABLE ProfessorCourseSchedule (
    CourseID INT,
    ProfessorID INT,
    Time VARCHAR(50),
    PRIMARY KEY (CourseID),
    UNIQUE KEY (ProfessorID, Time)
    -- ProfessorID is not a candidate key but determines Time
);

BCNF Normalized:

-- GOOD: Separate tables ensure all determinants are candidate keys
CREATE TABLE CourseAssignment (
    CourseID INT PRIMARY KEY,
    ProfessorID INT NOT NULL,
    FOREIGN KEY (ProfessorID) REFERENCES Professor(ProfessorID)
);

CREATE TABLE ProfessorSchedule (
    ProfessorID INT PRIMARY KEY,
    Time VARCHAR(50) NOT NULL,
    FOREIGN KEY (ProfessorID) REFERENCES Professor(ProfessorID)
);

9. From ERD to Physical Schema

Translating logical ERDs to physical database schemas involves converting relationships to concrete SQL structures.

One-to-One (1:1) Relationship: Option 1: Add FK to either table. Option 2: Merge tables if always paired.

-- Option 1: FK in dependent table
CREATE TABLE Person (
    PersonID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Passport (
    PassportID INT PRIMARY KEY,
    PersonID INT UNIQUE NOT NULL,
    PassportNumber VARCHAR(50),
    ExpiryDate DATE,
    FOREIGN KEY (PersonID) REFERENCES Person(PersonID) ON DELETE CASCADE
);

-- Option 2: Merge if always paired
CREATE TABLE PersonWithPassport (
    PersonID INT PRIMARY KEY,
    Name VARCHAR(100),
    PassportNumber VARCHAR(50),
    ExpiryDate DATE
);

One-to-Many (1:N) Relationship: Add FK to the many-side table.

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    DeptID INT NOT NULL,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID) ON DELETE RESTRICT
);

Many-to-Many (M:N) Relationship: Create a junction (bridge) table with FKs to both entities.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    Title VARCHAR(150)
);

-- Junction table for M:N
CREATE TABLE StudentCourse (
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE,
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID) ON DELETE CASCADE
);

Handling Inheritance (Specialization/Generalization):

Strategy 1: Single Table Inheritance (One table for all types)

-- All Person subtypes in one table
CREATE TABLE Person (
    PersonID INT PRIMARY KEY,
    PersonType ENUM('Student', 'Teacher', 'Staff') NOT NULL,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    -- Student-specific columns
    StudentID VARCHAR(20),
    GPA DECIMAL(3, 2),
    -- Teacher-specific columns
    TeacherID VARCHAR(20),
    Specialization VARCHAR(100),
    -- Staff-specific columns
    StaffID VARCHAR(20),
    Department VARCHAR(100)
);

Strategy 2: Table Per Type (Separate table for each subtype)

-- Supertype table
CREATE TABLE Person (
    PersonID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- Subtype tables
CREATE TABLE Student (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    PersonID INT NOT NULL,
    StudentNumber VARCHAR(20) UNIQUE,
    GPA DECIMAL(3, 2),
    FOREIGN KEY (PersonID) REFERENCES Person(PersonID) ON DELETE CASCADE
);

CREATE TABLE Teacher (
    TeacherID INT PRIMARY KEY AUTO_INCREMENT,
    PersonID INT NOT NULL,
    TeacherNumber VARCHAR(20) UNIQUE,
    Specialization VARCHAR(100),
    FOREIGN KEY (PersonID) REFERENCES Person(PersonID) ON DELETE CASCADE
);

Strategy 3: Table Per Concrete Class (No supertype table)

-- Each concrete class is its own table
CREATE TABLE Student (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    StudentNumber VARCHAR(20) UNIQUE,
    GPA DECIMAL(3, 2)
);

CREATE TABLE Teacher (
    TeacherID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    TeacherNumber VARCHAR(20) UNIQUE,
    Specialization VARCHAR(100)
);

Weak Entity Translation: A weak entity's PK includes the strong entity's PK.

-- Strong entity
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT NOT NULL,
    OrderDate TIMESTAMP
);

-- Weak entity: OrderItem depends on Orders
CREATE TABLE OrderItem (
    OrderID INT NOT NULL,
    ItemSequence INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT,
    PRIMARY KEY (OrderID, ItemSequence),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE
);

10. Best Practices

• Name Entities as Singular Nouns: Use "Customer" not "Customers", "Product" not "Products". Singular naming clarifies that each table row represents one entity instance.
• Use Consistent Notation Throughout: Don't mix Chen notation with Crow's Foot within the same diagram. Choose one and maintain it for clarity.
• Identify All Relationships: Explicitly name every relationship with a verb phrase (e.g., "places", "contains", "belongs to"). Unnamed relationships suggest incomplete analysis.
• Never Skip Cardinalities: Always specify both direction of a relationship and participation constraints. Missing cardinality leaves ambiguity in the data model.
• Validate with Stakeholders: Review ERDs with business analysts and domain experts. The diagram should reflect actual business rules, not assumptions.
• Keep Diagrams Readable: Limit to 15-20 entities per diagram. For larger systems, create separate diagrams for subsystems (e.g., Orders, Inventory, Shipping).
• Use Clear Attribute Notation: Mark primary keys (PK), foreign keys (FK), and not-null constraints. Add data types at the physical design level.
• Avoid Redundant Relationships: Don't include transitive relationships. If Customer → Order and Order → OrderItem, don't add Customer → OrderItem.
• Document Assumptions: Note constraints that can't be expressed in the diagram (e.g., "Price must be > 0", "Status values are: Pending, Active, Archived").
• Common Mistakes to Avoid:
• Forgetting derived attributes (marked with dashed outline or notation)
• Mixing logical and physical design (showing data types and indexes in conceptual ERD)
• Creating junction tables without cardinality notation
• Naming relationships as nouns instead of verbs
• Adding M:N relationships without a junction table in the physical model
• Failing to normalize before translation to SQL
• Not specifying on-delete and on-update cascading rules

ERD Validation Checklist:

• Every entity has a primary key (natural or surrogate)
• Every relationship is named with a clear verb phrase
• Cardinality and participation are specified on both sides of relationships
• No cycles in relationships that suggest decomposition needed
• M:N relationships are identified and will become junction tables
• Weak entities depend on appropriate strong entities
• All attributes are assigned to the correct entity
• The design passes normalization up to at least 3NF
• Stakeholders confirm the model represents their requirements

Conclusion

Entity Relationship Diagrams are indispensable tools in database design, bridging business requirements and technical implementation. By mastering ERD notation, cardinality rules, normalization principles, and translation to SQL, you can design databases that are efficient, maintainable, and scalable. Start with a conceptual diagram, refine to logical design with all attributes and constraints, normalize to at least 3NF, then translate to a physical schema that maps directly to CREATE TABLE statements.