# Mapping ER Diagrams to Tables — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/e-mapping

> Convert ER designs into relational schemas correctly.

## From diagram to schema

Standard rules turn an ER diagram into tables. **Strong entity** → a table with its simple attributes; composite attributes are flattened into their components; the key becomes the primary key. **Multi-valued attribute** → a separate table with the entity's key plus the value, for example `student_phones(roll_no, phone)`. **Derived attributes** are usually not stored. **Weak entity** → a table containing its attributes plus the owner's key as a foreign key; the primary key is (owner key, partial key), often with `ON DELETE CASCADE`. **1:N relationship** → add the key of the "one" side as a foreign key in the "many" side's table, plus any relationship attributes. **1:1 relationship** → a foreign key on either side, preferably the side with total participation. **M:N relationship** → a separate **junction table** whose primary key combines both entity keys, plus the relationship's attributes. **Specialisation** → one table per class with shared keys, a single table with a type column, or tables for sub-classes only, depending on the constraints.

## The university design as SQL tables

Each table comes from one mapping rule.

```sql
CREATE TABLE department (dept_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);

CREATE TABLE student (
    roll_no    INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,          -- composite attribute flattened
    last_name  VARCHAR(50) NOT NULL,
    dob        DATE NOT NULL                   -- age is derived, not stored
);

CREATE TABLE student_phone (                   -- multi-valued attribute
    roll_no INT REFERENCES student(roll_no) ON DELETE CASCADE,
    phone   VARCHAR(15),
    PRIMARY KEY (roll_no, phone)
);

CREATE TABLE course (                          -- 1:N with department, total participation
    course_id VARCHAR(10) PRIMARY KEY,
    title     VARCHAR(100) NOT NULL,
    credits   INT NOT NULL,
    dept_id   INT NOT NULL REFERENCES department(dept_id)
);

CREATE TABLE enrolment (                       -- M:N relationship with attributes
    roll_no   INT REFERENCES student(roll_no),
    course_id VARCHAR(10) REFERENCES course(course_id),
    semester  VARCHAR(10),
    grade     CHAR(2),
    PRIMARY KEY (roll_no, course_id, semester)
);
```

## M:N always needs a junction table

You cannot store a many-to-many relationship with a single foreign key column; one student would need many course IDs in one cell. The junction table is the correct relational representation.

**Quiz:** How is a many-to-many relationship between STUDENT and COURSE represented in tables?

- [x] A separate junction table holding keys of both, plus relationship attributes
- [ ] A foreign key in STUDENT pointing to COURSE
- [ ] A comma-separated list of course IDs in STUDENT
- [ ] It cannot be represented

*Answer:* A separate junction table holding keys of both, plus relationship attributes. M:N relationships map to their own table with a composite key from both entities.