ER Diagrams Explained: Entities, Relationships and Keys for Beginners

=== BOOKSTORE ER DIAGRAM (Text Representation) ===

[ CUSTOMER ]                    [ ORDER ]                    [ BOOK ]
-----------                    ---------                    --------
* CustomerID (PK)              * OrderID (PK)               * ISBN (PK)
  FirstName                    * OrderDate                    Title
  LastName                     * TotalAmount                  Author
  Email                        * CustomerID (FK)              Price
  Phone                                                        StockCount

  CUSTOMER ----places----> ORDER ----contains----> BOOK
  (one customer places      (one order contains
   many orders)              many books; one book
                             can appear in many orders)

LEGEND:
  [ ]  = Entity (becomes a table)
  *    = Part of Primary Key
  (PK) = Primary Key — uniquely identifies each row
  (FK) = Foreign Key — links to another table
  ---- = Relationship line

-- ============================================================
-- BOOKSTORE DATABASE — translating our ER diagram into SQL
-- These CREATE TABLE statements are the direct output of
-- the design decisions made in the ER diagram above.
-- ============================================================

-- ENTITY: Customer
-- CustomerID is an auto-incrementing integer — the database
-- assigns it automatically so we never get duplicates.
CREATE TABLE customer (
    customer_id   INT          PRIMARY KEY AUTO_INCREMENT,  -- surrogate PK
    first_name    VARCHAR(50)  NOT NULL,
    last_name     VARCHAR(50)  NOT NULL,
    email         VARCHAR(100) NOT NULL UNIQUE,             -- natural candidate key
    phone         VARCHAR(20)                               -- nullable: optional field
);

-- ENTITY: Book
-- ISBN is a real-world unique code for every book — a natural PK.
CREATE TABLE book (
    isbn          CHAR(13)     PRIMARY KEY,   -- ISBNs are always exactly 13 digits
    title         VARCHAR(200) NOT NULL,
    author        VARCHAR(100) NOT NULL,
    price         DECIMAL(6,2) NOT NULL,      -- 6 digits total, 2 after decimal point
    stock_count   INT          NOT NULL DEFAULT 0
);

-- ENTITY: Order
-- customer_id here is a FOREIGN KEY — it references customer(customer_id)
-- This is how the ER relationship "Customer places Order" becomes real in SQL.
CREATE TABLE `order` (
    order_id      INT          PRIMARY KEY AUTO_INCREMENT,
    order_date    DATE         NOT NULL,
    total_amount  DECIMAL(8,2) NOT NULL,
    customer_id   INT          NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    -- If you try to insert an order with a customer_id that doesn't exist
    -- in the customer table, the database will REJECT it. This enforces
    -- referential integrity — no orphan orders allowed.
);

-- JUNCTION TABLE: order_book
-- This resolves the many-to-many relationship between Order and Book.
-- One order can contain many books. One book can appear in many orders.
-- A junction table is the ONLY correct way to model many-to-many in SQL.
CREATE TABLE order_book (
    order_id      INT          NOT NULL,
    isbn          CHAR(13)     NOT NULL,
    quantity      INT          NOT NULL DEFAULT 1,
    unit_price    DECIMAL(6,2) NOT NULL,   -- price at time of purchase (may change later)
    PRIMARY KEY (order_id, isbn),          -- composite PK: combination must be unique
    FOREIGN KEY (order_id) REFERENCES `order`(order_id),
    FOREIGN KEY (isbn)     REFERENCES book(isbn)
);

=== CARDINALITY CHEAT SHEET (Crow's Foot Notation) ===

── ONE-TO-ONE ─────────────────────────────────────────

  CUSTOMER ──|────|── SHIPPING_PROFILE
             one  one

  "Each customer has exactly one shipping profile."
  "Each shipping profile belongs to exactly one customer."
  SQL: Put the FK in either table (usually the 'weaker' one).

── ONE-TO-MANY ────────────────────────────────────────

  CUSTOMER ──|────<── ORDER
             one    many

  "One customer places many orders."
  "Each order belongs to exactly one customer."
  SQL: FK (customer_id) goes in the ORDER table — the 'many' side.
       Never put it in the CUSTOMER table.

── MANY-TO-MANY ───────────────────────────────────────

  ORDER ──>────<── BOOK
        many  many

  "One order contains many books."
  "One book appears in many orders."
  SQL: You CANNOT model this directly.
       Create a junction table: ORDER_BOOK(order_id FK, isbn FK)
       This splits the M:N into two 1:N relationships:
         ORDER ──|────<── ORDER_BOOK ──>────|── BOOK

── CROW'S FOOT SYMBOLS ────────────────────────────────

  |    = exactly one      (mandatory one)
  O    = zero or one      (optional one)
  <    = many             (mandatory many)
  O<   = zero or many     (optional many)

  Combined examples:
  |──|   = exactly one on both sides
  |──<   = one on left, many on right
  O──O<  = optional one to optional many

-- ============================================================
-- UNIVERSITY ENROLLMENT SYSTEM
-- Built by following the 6-step ER diagram process above.
-- Requirements: Students enrol in Courses taught by Instructors.
-- ============================================================

-- Step 1-2: Entity INSTRUCTOR with attributes
CREATE TABLE instructor (
    instructor_id   INT          PRIMARY KEY AUTO_INCREMENT,
    full_name       VARCHAR(100) NOT NULL,
    email           VARCHAR(100) NOT NULL UNIQUE,
    department      VARCHAR(50)  NOT NULL
);

-- Step 1-2: Entity COURSE with attributes
-- Step 5: instructor_id FK links to the instructor who teaches this course
-- Cardinality: One instructor teaches many courses (1:N)
--              FK goes on the 'many' side — in the COURSE table
CREATE TABLE course (
    course_id       CHAR(8)      PRIMARY KEY,   -- e.g. 'CS-10234'
    course_name     VARCHAR(150) NOT NULL,
    credits         TINYINT      NOT NULL CHECK (credits BETWEEN 1 AND 6),
    instructor_id   INT          NOT NULL,
    FOREIGN KEY (instructor_id) REFERENCES instructor(instructor_id)
);

-- Step 1-2: Entity STUDENT with attributes
CREATE TABLE student (
    student_id      INT          PRIMARY KEY AUTO_INCREMENT,
    first_name      VARCHAR(50)  NOT NULL,
    last_name       VARCHAR(50)  NOT NULL,
    email           VARCHAR(100) NOT NULL UNIQUE,
    enrollment_year YEAR         NOT NULL
);

-- Step 4-5: JUNCTION TABLE for the M:N relationship between Student and Course
-- 'Students enrol in Courses' — one student takes many courses,
-- one course has many students. We CANNOT link these directly.
-- The ENROLMENT table breaks it into two 1:N relationships.
CREATE TABLE enrolment (
    student_id      INT          NOT NULL,
    course_id       CHAR(8)      NOT NULL,
    enrolment_date  DATE         NOT NULL,
    grade           CHAR(2),                   -- NULL until grade is submitted
    PRIMARY KEY (student_id, course_id),       -- composite PK prevents duplicate enrolment
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id)  REFERENCES course(course_id)
);

-- ── SAMPLE DATA to verify the design works ──────────────────

INSERT INTO instructor VALUES (1, 'Dr. Priya Mehta', 'priya@uni.edu', 'Computer Science');

INSERT INTO course VALUES ('CS-10234', 'Database Systems', 3, 1);
INSERT INTO course VALUES ('CS-10301', 'Algorithms',       3, 1);

INSERT INTO student VALUES (1, 'James', 'Okafor', 'james@uni.edu', 2023);
INSERT INTO student VALUES (2, 'Sofia', 'Reyes',  'sofia@uni.edu', 2023);

-- James enrols in both courses; Sofia enrols in one
INSERT INTO enrolment VALUES (1, 'CS-10234', '2024-09-01', NULL);
INSERT INTO enrolment VALUES (1, 'CS-10301', '2024-09-01', NULL);
INSERT INTO enrolment VALUES (2, 'CS-10234', '2024-09-01', NULL);

-- Query that PROVES the ER design is correct:
-- Show every student and every course they are enrolled in
SELECT
    CONCAT(s.first_name, ' ', s.last_name) AS student_name,
    c.course_name,
    i.full_name                            AS taught_by
FROM enrolment e
JOIN student    s ON e.student_id = s.student_id
JOIN course     c ON e.course_id  = c.course_id
JOIN instructor i ON c.instructor_id = i.instructor_id
ORDER BY student_name, c.course_name;

=== ER DIAGRAM PITFALLS CHECKLIST ===

Before finalising your ER diagram, verify each of the following:

[ ] Every entity has a primary key (surrogate or natural).
[ ] No entity represents something that should be an attribute.
[ ] Every relationship line has the correct cardinality symbol.
[ ] Read each relationship in both directions — do both read correctly?
[ ] Every many-to-many relationship has a corresponding junction table.
[ ] Weak entities are clearly marked (double rectangle).
[ ] Foreign keys are placed on the 'many' side of one-to-many relationships.
[ ] Multi-valued attributes (e.g., multiple phone numbers) are modeled as separate entities.
[ ] Derived attributes (e.g., Age from DateOfBirth) are marked and not stored.
[ ] Composite attributes (e.g., Address made of Street, City, Zip) are broken into simple attributes.

If any box is unchecked, go back and fix the diagram before moving to SQL.
This checklist saves hours of migration work post-launch.