Module 2: Data Models & ER Diagrams

The Entity-Relationship (ER) Model is a high-level conceptual data modeling framework created by Peter Chen in 1976. It allows database designers to visually architect real-world requirements into entities, attributes, and relationships before writing any SQL table creation scripts.

Every conceptual database schema is composed of three building blocks: entities (real-world things), attributes (properties describing them), and relationships (associations connecting them).

Attribute Classifications

• Simple vs. Composite: Simple attributes are atomic (e.g. age). Composite attributes can be subdivided into smaller components (e.g. Address subdivided into Street, City, ZipCode).
• Single-Valued vs. Multi-Valued: Single-valued holds at most one value (e.g. date_of_birth). Multi-valued can hold a set of values (e.g. phone_numbers, represented by double ellipses in ER diagrams).
• Stored vs. Derived: Stored attributes exist physically in tables. Derived attributes are calculated on-the-fly (e.g. age derived from current_date - dob, represented by a dashed ellipse).

Cardinality Ratios

Cardinality defines the maximum number of entity instances that can participate in a relationship:

1. One-to-One (1:1): One department has one manager; one person has one passport.
2. One-to-Many (1:N): One customer places many orders; one department employs many engineers.
3. Many-to-Many (M:N): Students enroll in many courses, and each course contains many students.

Once an ER diagram is designed, it must be systematically transformed into actual relational database tables through standard deterministic mapping rules.

Step-by-Step ER-to-Relational Mapping Rules

1. Strong Entity Sets: Create a table for the entity. All simple attributes become table columns; primary key attribute becomes the primary key.
2. Weak Entity Sets: Create a table containing all weak attributes, plus the primary key of the identifying owner entity as a foreign key. The composite of owner PK + partial discriminator becomes the PK.
3. 1:1 Relationships: Choose one table and place the primary key of the other table into it as a Foreign Key (with a UNIQUE constraint).
4. 1:N Relationships: Place the primary key from the "One" side into the table on the "Many" side as a Foreign Key. (Never put the foreign key on the 1-side, which would create repeating groups!).
5. M:N Relationships: Create a dedicated Junction / Bridge Table. Its columns include the primary keys of both participating entities as Foreign Keys. The composite of both foreign keys forms the bridge table's primary key.
6. Multi-Valued Attributes: Create a separate table containing the primary key of the parent entity and the multi-valued attribute column.

-- 1. Strong Entities
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id VARCHAR(10) PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    credits INT DEFAULT 3
);

-- 2. M:N Relationship mapped to Junction Table
CREATE TABLE student_courses (
    student_id INT,
    course_id VARCHAR(10),
    enrolled_at DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);