ER Diagram in DBMS

ER DIAGRAM

An Entity Relationship Diagram is a visual representation of data that shows how entities (things we store data about) are related to each other in a database. Think of it as a blueprint for building the database structure!

ER Model Components:

• Entities → Real-world objects (noun) like Student, Teacher, Book.
• Attributes → Properties of entities (like Name, Age, ID).
• Relationships → How entities are connected (e.g., Student ENROLLS in Course).
• Keys → Unique identifiers like `student_id`.
• Cardinality → Describes the number of instances in a relationship (1:1, 1:N, M:N).

ENTITIES

Entities are the objects we want to store information about.

• Strong Entity: Exists independently (e.g., Student, Teacher)
• Weak Entity: Cannot exist without another entity (e.g., Dependent, Receipt)

Example:

Student
┌────────────┐
│  student_id│
│  name      │
│  age       │
└────────────┘

ATTRIBUTES

Types of Attributes:

• Simple (Atomic): Cannot be broken down → `age`
• Composite: Can be divided → `Name` = First + Last
• Derived: Can be derived from other values → `Age` from DOB
• Multivalued: Has multiple values → `PhoneNumbers`

Diagram Notation:

• Ellipses for attributes
• Double ellipse for multivalued
• Dotted ellipse for derived

RELATIONSHIPS

Relationships represent how two entities are connected.

3 Types of Relationships:

1️⃣ One-to-One (1:1):

• Each entity in A is related to at most one in B, and vice versa.
• Example: A `Person` has one `Passport`.
• Min. 2 tables (Person, Passport)
• You can merge into 1 table if both have 1:1 total participation.

Person ─────── has ───────> Passport
 (1)                          (1)

CREATE TABLE Person (
    person_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Passport (
    passport_id INT PRIMARY KEY,
    person_id INT UNIQUE,
    FOREIGN KEY (person_id) REFERENCES Person(person_id)
);

2️⃣ One-to-Many (1:N):

• One entity in A is related to many in B, but B is related to only one A.
• Example: A `Department` has many `Employees`.
• Min. 2 tables (Employee will have dept_id as FK)

Department ─────── supervises ───────> Employee
    (1)                                (N)

CREATE TABLE Department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

3️⃣ Many-to-Many (M:N):

• Many entities in A are related to many in B.
• Example: `Students` can enroll in many `Courses`, and `Courses` can have many `Students`.
• Min. 3 tables: Student, Course, and Enroll (junction table)

Student ─────── enrolls ─────── Course
  (M)                              (N)

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Course (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE Enroll (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student(student_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

MINIMUM NUMBER OF TABLES NEEDED FOR EACH RELATIONSHIP TYPE

EDGE CASES

• 🔁 Recursive Relationship: An entity related to itself Example: Employee supervises another Employee Needs self-referencing FK

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES Employee(emp_id)
);

• Ternary Relationship: A relation involving 3 entities Example: Doctor prescribes Medicine to Patient Needs a junction table with 3 FKs

• Total Participation: Every instance of one entity MUST participate Represented by double lines in ER diagram.

SUMMARY

STUDENT - COURSE - ENROLL RELATION

┌─────────────┐       ┌─────────────┐
│   Student   │       │   Course    │
└─────────────┘       └─────────────┘
            |                     |
            |                     |
            └───── Enroll ───────┘
               (student_id,
               course_id)