ER Model in DBMS Explained — Entities, Attributes, and Relationships

-- ============================================================
-- Translating ER Entities and Attributes into a real SQL table
-- This is what your ER diagram eventually becomes in a database
-- ============================================================

-- ENTITY: Student
-- KEY ATTRIBUTE:    student_id  (underlined in ER diagram — must be unique)
-- SIMPLE ATTRS:     first_name, last_name, email
-- DERIVED ATTR:     age (we store date_of_birth and calculate age when needed)
-- COMPOSITE ATTR:   full_name is split into first_name + last_name
-- MULTI-VALUED:     phone_numbers gets its OWN separate table (shown below)

CREATE TABLE Student (
    student_id    INT          PRIMARY KEY,   -- Key attribute: uniquely identifies each student
    first_name    VARCHAR(50)  NOT NULL,      -- Part of composite attribute 'FullName'
    last_name     VARCHAR(50)  NOT NULL,      -- Part of composite attribute 'FullName'
    date_of_birth DATE         NOT NULL,      -- We store this; Age is DERIVED from it
    email         VARCHAR(100) UNIQUE NOT NULL -- Simple attribute
);

-- Multi-valued attribute 'PhoneNumbers' becomes its own table.
-- A student can have 0, 1, or many phone numbers.
CREATE TABLE StudentPhone (
    phone_id      INT         PRIMARY KEY,
    student_id    INT         NOT NULL,       -- Links back to the Student table
    phone_number  VARCHAR(15) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES Student(student_id)
);

-- ENTITY: Course
CREATE TABLE Course (
    course_id     INT          PRIMARY KEY,   -- Key attribute for Course entity
    course_name   VARCHAR(100) NOT NULL,
    credits       INT          NOT NULL
);

-- Insert sample data so we can see real instances
INSERT INTO Student VALUES (101, 'Alice', 'Morgan', '2001-04-15', 'alice@uni.edu');
INSERT INTO Student VALUES (102, 'Bob',   'Chen',   '2000-09-22', 'bob@uni.edu');

INSERT INTO StudentPhone VALUES (1, 101, '555-0101');  -- Alice has one phone
INSERT INTO StudentPhone VALUES (2, 101, '555-0202');  -- Alice has a second phone
INSERT INTO StudentPhone VALUES (3, 102, '555-0303');  -- Bob has one phone

INSERT INTO Course VALUES (1, 'Database Systems', 3);
INSERT INTO Course VALUES (2, 'Data Structures',  4);

-- Query to see Alice's full record including derived age
SELECT
    student_id,
    first_name || ' ' || last_name  AS full_name,       -- Composite attribute reconstructed
    EXTRACT(YEAR FROM AGE(date_of_birth)) AS age,        -- Derived attribute calculated on the fly
    email
FROM Student
WHERE student_id = 101;

-- ============================================================
-- Translating ER Relationships and Cardinality into SQL
-- ============================================================

-- Relationship: PROFESSOR teaches COURSE  (One-to-Many: 1 professor → many courses)
-- We implement 1:N by placing the foreign key on the 'many' side (Course table)

CREATE TABLE Professor (
    professor_id  INT          PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    department    VARCHAR(50)  NOT NULL
);

-- Each Course row holds a professor_id — the 'one' side of the relationship
ALTER TABLE Course
    ADD COLUMN professor_id INT,
    ADD CONSTRAINT fk_course_professor
        FOREIGN KEY (professor_id) REFERENCES Professor(professor_id);

-- Relationship: STUDENT enrolls in COURSE  (Many-to-Many)
-- M:N relationships ALWAYS become their own 'junction' table in SQL
-- The junction table holds the primary keys of BOTH entities
-- It can also hold relationship-specific attributes (like enrollment_date)

CREATE TABLE Enrollment (
    enrollment_id   INT  PRIMARY KEY,
    student_id      INT  NOT NULL,
    course_id       INT  NOT NULL,
    enrollment_date DATE NOT NULL,     -- Attribute of the RELATIONSHIP itself
    grade           CHAR(2),           -- Also a relationship attribute — Alice's grade in THIS course
    FOREIGN KEY (student_id) REFERENCES Student(student_id),
    FOREIGN KEY (course_id)  REFERENCES Course(course_id),
    UNIQUE (student_id, course_id)     -- Prevent duplicate enrollments for the same student+course
);

-- Relationship: PERSON has PASSPORT  (One-to-One: 1:1)
-- We implement 1:1 with a UNIQUE foreign key
CREATE TABLE Person (
    person_id  INT         PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL
);

CREATE TABLE Passport (
    passport_id     INT         PRIMARY KEY,
    passport_number VARCHAR(20) UNIQUE NOT NULL,
    person_id       INT         UNIQUE NOT NULL,  -- UNIQUE enforces the 1:1 — one passport per person
    expiry_date     DATE        NOT NULL,
    FOREIGN KEY (person_id) REFERENCES Person(person_id)
);

-- ---- Sample Data ----
INSERT INTO Professor VALUES (1, 'Dr. Sarah Hill',  'Computer Science');
INSERT INTO Professor VALUES (2, 'Dr. James Okafor','Mathematics');

UPDATE Course SET professor_id = 1 WHERE course_id = 1; -- Dr. Hill teaches Database Systems
UPDATE Course SET professor_id = 1 WHERE course_id = 2; -- Dr. Hill also teaches Data Structures

INSERT INTO Enrollment VALUES (1, 101, 1, '2024-01-10', 'A');  -- Alice enrolled in Database Systems
INSERT INTO Enrollment VALUES (2, 101, 2, '2024-01-10', 'B+'); -- Alice also in Data Structures
INSERT INTO Enrollment VALUES (3, 102, 1, '2024-01-11', NULL); -- Bob in Database Systems, no grade yet

-- Query: Find all courses Alice is enrolled in, with her grades
SELECT
    s.first_name || ' ' || s.last_name  AS student_name,
    c.course_name,
    e.enrollment_date,
    COALESCE(e.grade, 'Not graded')    AS grade
FROM Enrollment e
JOIN Student  s ON e.student_id = s.student_id
JOIN Course   c ON e.course_id  = c.course_id
WHERE e.student_id = 101
ORDER BY c.course_name;

-- ============================================================
-- Weak Entity: ORDER_ITEM depends on ORDER for its identity
-- Specialisation: EMPLOYEE and STUDENT are subtypes of PERSON
-- ============================================================

-- Strong entity: ORDER
CREATE TABLE CustomerOrder (
    order_id      INT          PRIMARY KEY,   -- Strong entity has its own full key
    order_date    DATE         NOT NULL,
    customer_name VARCHAR(100) NOT NULL
);

-- Weak entity: ORDER_ITEM
-- 'item_number' is the PARTIAL KEY — only unique within a specific order
-- The FULL identity is (order_id + item_number) combined
CREATE TABLE OrderItem (
    order_id     INT          NOT NULL,       -- Foreign key to the owner (strong) entity
    item_number  INT          NOT NULL,       -- Partial key — dashed underline in ER diagram
    product_name VARCHAR(100) NOT NULL,
    quantity     INT          NOT NULL,
    unit_price   DECIMAL(8,2) NOT NULL,
    -- Combined primary key: order_id + item_number together form the full identifier
    PRIMARY KEY (order_id, item_number),
    FOREIGN KEY (order_id) REFERENCES CustomerOrder(order_id)
        ON DELETE CASCADE  -- If the order is deleted, all its items are deleted too
);

-- ---- Specialisation / Generalisation ----
-- PERSON is the generalised (parent) entity with shared attributes
CREATE TABLE Person (
    person_id   INT          PRIMARY KEY,
    full_name   VARCHAR(100) NOT NULL,
    date_of_birth DATE       NOT NULL,
    email       VARCHAR(100) UNIQUE NOT NULL
);

-- EMPLOYEE is a specialised subtype — it has all Person attributes PLUS its own
CREATE TABLE Employee (
    person_id       INT          PRIMARY KEY,  -- Shares the same ID as Person
    employee_code   VARCHAR(20)  UNIQUE NOT NULL,
    hire_date       DATE         NOT NULL,
    salary          DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (person_id) REFERENCES Person(person_id)  -- ISA relationship
);

-- STUDENT is another specialised subtype — different extra attributes
CREATE TABLE Student_Person (
    person_id       INT         PRIMARY KEY,  -- Same ID as Person
    student_id      VARCHAR(20) UNIQUE NOT NULL,
    enrollment_year INT         NOT NULL,
    major           VARCHAR(50),
    FOREIGN KEY (person_id) REFERENCES Person(person_id)  -- ISA relationship
);

-- ---- Sample Data ----
INSERT INTO CustomerOrder VALUES (500, '2024-03-01', 'Greenleaf Supplies');
INSERT INTO CustomerOrder VALUES (501, '2024-03-02', 'BlueStar Corp');

-- Order 500 has 3 items; item_number restarts at 1 for each order
INSERT INTO OrderItem VALUES (500, 1, 'Printer Paper A4',  10, 5.99);
INSERT INTO OrderItem VALUES (500, 2, 'Black Ink Cartridge', 3, 18.50);
INSERT INTO OrderItem VALUES (500, 3, 'Stapler',             1, 12.00);

-- Order 501 also has an item_number = 1 — NOT a conflict because order_id differs
INSERT INTO OrderItem VALUES (501, 1, 'Whiteboard Markers',  5, 3.25);

-- Specialisation: one person who is an employee
INSERT INTO Person   VALUES (1, 'Carol White', '1985-07-20', 'carol@company.com');
INSERT INTO Employee VALUES (1, 'EMP-00421', '2015-03-01', 72000.00);

-- Retrieve a full order with its line items and running total
SELECT
    o.order_id,
    o.customer_name,
    i.item_number,
    i.product_name,
    i.quantity,
    i.unit_price,
    (i.quantity * i.unit_price) AS line_total
FROM CustomerOrder o
JOIN OrderItem i ON o.order_id = i.order_id
WHERE o.order_id = 500
ORDER BY i.item_number;