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

-- ============================================================
-- Translating ER Entities and Attributes into real SQL tables
-- Using io_thecodeforge schema for production naming
-- ============================================================

-- 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 io_thecodeforge.Student (
    student_id    INT          PRIMARY KEY,
    first_name    VARCHAR(50)  NOT NULL,
    last_name     VARCHAR(50)  NOT NULL,
    date_of_birth DATE         NOT NULL,
    email         VARCHAR(100) UNIQUE NOT NULL
);

-- Multi-valued attribute 'PhoneNumbers' becomes its own table.
CREATE TABLE io_thecodeforge.StudentPhone (
    phone_id      INT         PRIMARY KEY,
    student_id    INT         NOT NULL,
    phone_number  VARCHAR(15) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES io_thecodeforge.Student(student_id)
);

-- ENTITY: Course
CREATE TABLE io_thecodeforge.Course (
    course_id     INT          PRIMARY KEY,
    course_name   VARCHAR(100) NOT NULL,
    credits       INT          NOT NULL
);

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

INSERT INTO io_thecodeforge.StudentPhone VALUES (1, 101, '555-0101');
INSERT INTO io_thecodeforge.StudentPhone VALUES (2, 101, '555-0202');
INSERT INTO io_thecodeforge.StudentPhone VALUES (3, 102, '555-0303');

INSERT INTO io_thecodeforge.Course VALUES (1, 'Database Systems', 3);
INSERT INTO io_thecodeforge.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,
    EXTRACT(YEAR FROM AGE(date_of_birth)) AS age,
    email
FROM io_thecodeforge.Student
WHERE student_id = 101;

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

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

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

ALTER TABLE io_thecodeforge.Course
    ADD COLUMN professor_id INT,
    ADD CONSTRAINT fk_course_professor
        FOREIGN KEY (professor_id) REFERENCES io_thecodeforge.Professor(professor_id);

-- Relationship: STUDENT enrolls in COURSE  (Many-to-Many)
-- M:N relationships ALWAYS become their own 'junction' table
CREATE TABLE io_thecodeforge.Enrollment (
    enrollment_id   INT  PRIMARY KEY,
    student_id      INT  NOT NULL,
    course_id       INT  NOT NULL,
    enrollment_date DATE NOT NULL,
    grade           CHAR(2),
    FOREIGN KEY (student_id) REFERENCES io_thecodeforge.Student(student_id),
    FOREIGN KEY (course_id)  REFERENCES io_thecodeforge.Course(course_id),
    UNIQUE (student_id, course_id)
);

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

CREATE TABLE io_thecodeforge.Passport (
    passport_id     INT         PRIMARY KEY,
    passport_number VARCHAR(20) UNIQUE NOT NULL,
    person_id       INT         UNIQUE NOT NULL,
    expiry_date     DATE        NOT NULL,
    FOREIGN KEY (person_id) REFERENCES io_thecodeforge.Person(person_id)
);

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

UPDATE io_thecodeforge.Course SET professor_id = 1 WHERE course_id = 1;
UPDATE io_thecodeforge.Course SET professor_id = 1 WHERE course_id = 2;

INSERT INTO io_thecodeforge.Enrollment VALUES (1, 101, 1, '2024-01-10', 'A');
INSERT INTO io_thecodeforge.Enrollment VALUES (2, 101, 2, '2024-01-10', 'B+');
INSERT INTO io_thecodeforge.Enrollment VALUES (3, 102, 1, '2024-01-11', NULL);

-- Query: Alice's courses with 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 io_thecodeforge.Enrollment e
JOIN io_thecodeforge.Student  s ON e.student_id = s.student_id
JOIN io_thecodeforge.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 io_thecodeforge.CustomerOrder (
    order_id      INT          PRIMARY KEY,
    order_date    DATE         NOT NULL,
    customer_name VARCHAR(100) NOT NULL
);

-- Weak entity: ORDER_ITEM
-- item_number is the PARTIAL KEY
CREATE TABLE io_thecodeforge.OrderItem (
    order_id     INT          NOT NULL,
    item_number  INT          NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity     INT          NOT NULL,
    unit_price   DECIMAL(8,2) NOT NULL,
    PRIMARY KEY (order_id, item_number),
    FOREIGN KEY (order_id) REFERENCES io_thecodeforge.CustomerOrder(order_id)
        ON DELETE CASCADE
);

-- Specialisation / Generalisation
CREATE TABLE io_thecodeforge.Person (
    person_id   INT          PRIMARY KEY,
    full_name   VARCHAR(100) NOT NULL,
    date_of_birth DATE       NOT NULL,
    email       VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE io_thecodeforge.Employee (
    person_id       INT          PRIMARY KEY,
    employee_code   VARCHAR(20)  UNIQUE NOT NULL,
    hire_date       DATE         NOT NULL,
    salary          DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (person_id) REFERENCES io_thecodeforge.Person(person_id)
);

CREATE TABLE io_thecodeforge.Student_Person (
    person_id       INT         PRIMARY KEY,
    student_id      VARCHAR(20) UNIQUE NOT NULL,
    enrollment_year INT         NOT NULL,
    major           VARCHAR(50),
    FOREIGN KEY (person_id) REFERENCES io_thecodeforge.Person(person_id)
);

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

INSERT INTO io_thecodeforge.OrderItem VALUES (500, 1, 'Printer Paper A4',  10, 5.99);
INSERT INTO io_thecodeforge.OrderItem VALUES (500, 2, 'Black Ink Cartridge', 3, 18.50);
INSERT INTO io_thecodeforge.OrderItem VALUES (500, 3, 'Stapler',             1, 12.00);
INSERT INTO io_thecodeforge.OrderItem VALUES (501, 1, 'Whiteboard Markers',  5, 3.25);

INSERT INTO io_thecodeforge.Person   VALUES (1, 'Carol White', '1985-07-20', 'carol@company.com');
INSERT INTO io_thecodeforge.Employee VALUES (1, 'EMP-00421', '2015-03-01', 72000.00);

-- Retrieve a full order with line items
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 io_thecodeforge.CustomerOrder o
JOIN io_thecodeforge.OrderItem i ON o.order_id = i.order_id
WHERE o.order_id = 500
ORDER BY i.item_number;

-- ============================================================
-- Library System: Full ER to SQL translation
-- ============================================================

-- Strong entity: Book
CREATE TABLE io_thecodeforge.Book (
    isbn        VARCHAR(20)  PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    author      VARCHAR(100) NOT NULL,
    publisher   VARCHAR(100)
);

-- Weak entity: BookCopy (depends on Book)
CREATE TABLE io_thecodeforge.BookCopy (
    isbn           VARCHAR(20)  NOT NULL,
    copy_id        INT          NOT NULL,
    condition      VARCHAR(50),
    shelf_location VARCHAR(20),
    PRIMARY KEY (isbn, copy_id),
    FOREIGN KEY (isbn) REFERENCES io_thecodeforge.Book(isbn) ON DELETE CASCADE
);

-- Strong entity: Member
CREATE TABLE io_thecodeforge.Member (
    member_id  INT          PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL,
    email      VARCHAR(100) NOT NULL,
    phone      VARCHAR(15)
);

-- Associative entity: Loan (relationship with attributes)
CREATE TABLE io_thecodeforge.Loan (
    loan_id      INT  PRIMARY KEY,
    member_id    INT  NOT NULL,
    isbn         VARCHAR(20) NOT NULL,
    copy_id      INT  NOT NULL,
    loan_date    DATE NOT NULL,
    due_date     DATE NOT NULL,
    return_date  DATE,
    FOREIGN KEY (member_id) REFERENCES io_thecodeforge.Member(member_id),
    FOREIGN KEY (isbn, copy_id) REFERENCES io_thecodeforge.BookCopy(isbn, copy_id),
    UNIQUE (member_id, isbn, copy_id, loan_date)
);

-- Sample data
INSERT INTO io_thecodeforge.Book VALUES ('978-0134685991', 'Effective Java', 'Joshua Bloch', 'Addison-Wesley');
INSERT INTO io_thecodeforge.Book VALUES ('978-1491950357', 'Designing Data-Intensive Applications', 'Martin Kleppmann', 'OReilly');

INSERT INTO io_thecodeforge.BookCopy VALUES ('978-0134685991', 1, 'Good', 'A1-01');
INSERT INTO io_thecodeforge.BookCopy VALUES ('978-0134685991', 2, 'Fair', 'A1-02');
INSERT INTO io_thecodeforge.BookCopy VALUES ('978-1491950357', 1, 'Excellent', 'B2-10');

INSERT INTO io_thecodeforge.Member VALUES (201, 'Alice Morgan', 'alice@lib.edu', '555-1111');
INSERT INTO io_thecodeforge.Member VALUES (202, 'Bob Chen', 'bob@lib.edu', '555-2222');

INSERT INTO io_thecodeforge.Loan VALUES (1, 201, '978-0134685991', 1, '2024-04-01', '2024-04-15', NULL);
INSERT INTO io_thecodeforge.Loan VALUES (2, 202, '978-1491950357', 1, '2024-04-02', '2024-04-16', NULL);

-- Find all current loans for Alice
SELECT m.full_name, b.title, l.loan_date, l.due_date
FROM io_thecodeforge.Loan l
JOIN io_thecodeforge.Member m ON l.member_id = m.member_id
JOIN io_thecodeforge.BookCopy bc ON l.isbn = bc.isbn AND l.copy_id = bc.copy_id
JOIN io_thecodeforge.Book b ON bc.isbn = b.isbn
WHERE m.full_name = 'Alice Morgan' AND l.return_date IS NULL;

-- ============================================================
-- Summary: ER to Relational Transformation Rules
-- Each rule corresponds to a specific ER construct
-- ============================================================

-- Rule 1: Entity -> Table, Key attribute -> PK
CREATE TABLE io_thecodeforge.EntityTable (
    entity_id INT PRIMARY KEY,   -- Key attribute
    attr1 VARCHAR(100),
    attr2 VARCHAR(100)
);

-- Rule 2: Composite attribute -> multiple columns
-- Already done above: Person (full_name split into first_name, last_name)

-- Rule 3: Multi-valued attribute -> separate table with FK
CREATE TABLE io_thecodeforge.MultiValued (
    entity_id INT NOT NULL,
    value_id  INT NOT NULL,       -- Partial key or surrogate
    value     VARCHAR(100) NOT NULL,
    PRIMARY KEY (entity_id, value_id),
    FOREIGN KEY (entity_id) REFERENCES io_thecodeforge.EntityTable(entity_id)
);

-- Rule 4: Weak entity -> composite PK
-- Already done: OrderItem (order_id, item_number)

-- Rule 5: 1:1 relationship -> UNIQUE FK (shown in Person/Passport)

-- Rule 6: 1:N relationship -> FK on N side (shown in Professor/Course)

-- Rule 7: M:N relationship -> junction table (shown in Enrollment)

-- Rule 8: Specialisation -> shared PK (shown in Person/Employee)

-- Example: Applying all rules to a university schema
-- This single script creates a full schema from an ER diagram
CREATE TABLE io_thecodeforge.Department (
    dept_id   INT PRIMARY KEY,
    name      VARCHAR(100) NOT NULL
);

CREATE TABLE io_thecodeforge.Professor (
    prof_id   INT PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    dept_id   INT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES io_thecodeforge.Department(dept_id) -- 1:N: many professors in one department
);

CREATE TABLE io_thecodeforge.Course (
    course_id INT PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    credits   INT NOT NULL,
    prof_id   INT NOT NULL,
    FOREIGN KEY (prof_id) REFERENCES io_thecodeforge.Professor(prof_id) -- 1:N: one professor teaches many courses
);

CREATE TABLE io_thecodeforge.Student (
    student_id INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(100) UNIQUE NOT NULL
);

-- M:N: many students enroll in many courses -> junction table
CREATE TABLE io_thecodeforge.Enrollment (
    enrollment_id INT PRIMARY KEY,
    student_id    INT NOT NULL,
    course_id     INT NOT NULL,
    grade         CHAR(2),
    semester      VARCHAR(10) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES io_thecodeforge.Student(student_id),
    FOREIGN KEY (course_id)  REFERENCES io_thecodeforge.Course(course_id),
    UNIQUE (student_id, course_id, semester)  -- a student can enroll in same course only once per semester
);

// io.thecodeforge.db.entities
// EntitySet: structural definition, not data
public class EntitySet<E> {
    private final String entityName;
    private final Map<String, AttributeDef> attributes;
    
    public EntitySet(String name, Map<String, AttributeDef> attrs) {
        this.entityName = name;
        this.attributes = attrs;
    }
    
    // Represents the entity TYPE, not instances
    public boolean isValidInstance(Map<String, Object> instance) {
        return attributes.keySet().containsAll(instance.keySet());
    }
}

-- io.thecodeforge.er_mapping
-- Composite & multivalued attributes -> normalized tables

-- Strong entity table
CREATE TABLE customer (
    customer_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    first_name TEXT NOT NULL,      -- simple
    last_name TEXT NOT NULL,       -- simple
    date_of_birth DATE             -- simple
);

-- Composite attribute (address)
CREATE TABLE customer_address (
    address_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customer(customer_id),
    street TEXT NOT NULL,
    city TEXT NOT NULL,
    state TEXT NOT NULL,
    zip TEXT NOT NULL
);

-- Multivalued attribute (phone numbers)
CREATE TABLE customer_phone (
    phone_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customer(customer_id),
    phone_type TEXT CHECK (phone_type IN ('mobile', 'home', 'work')),
    phone_number TEXT NOT NULL
);