Mapping ER Diagrams to Tables

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.

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.

त्वरित जाँच: How is a many-to-many relationship between STUDENT and COURSE represented in tables?

• 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