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;