Questions: Entity-Relationship Diagrams

Question 1

A junior developer proposes storing which courses a student is enrolled in as a comma-separated list of course IDs in a single column of the students table. What is wrong with this approach?

• A. Nothing — storing comma-separated values is a common and efficient technique for many-to-many data
• B. It violates atomicity and makes querying, updating, and enforcing referential integrity extremely difficult
• C. It works fine as long as there are fewer than 100 courses per student
• D. The correct fix is to store a comma-separated list of student IDs in each course record instead

Answer: (B)

Many-to-many relationships cannot be directly represented in the relational model — they require a junction table. Storing comma-separated IDs violates the relational model's requirement for atomic values (First Normal Form). It makes SQL queries brittle (you cannot JOIN on a text list), updates dangerous (editing a substring), and referential integrity impossible (you cannot declare a FOREIGN KEY on a substring). The correct solution is an Enrollment table with student_id and course_id columns, each a foreign key to its respective table.

Question 2

An ER diagram shows a one-to-many relationship: one Department has many Employees, but each Employee belongs to exactly one Department. When translating to a relational schema, where does the foreign key go?

• A. In the Department table, as a column referencing the Employee table
• B. In both tables, with each holding a foreign key pointing to the other
• C. In the Employee table, as a column referencing the Department table
• D. In a new junction table between Department and Employee

Answer: (C)

In a one-to-many relationship, the foreign key always goes on the 'many' side — the side that can have multiple instances per record on the other side. Each Employee belongs to one Department, so Employee holds a department_id foreign key referencing Department's primary key. Putting it in Department would require a Department to store multiple employee references (impossible in a single column without violating atomicity). Junction tables are reserved for many-to-many relationships, not one-to-many.

Question 3

A many-to-many relationship in an ER diagram must be implemented as a junction (associative) table when translated into a relational schema.

• T. True
• F. False

Answer: True

The relational model has no native way to represent many-to-many relationships directly. A column can hold a single value (or, improperly, a list — but that breaks atomicity). To represent 'a Student can enroll in many Courses and a Course can have many Students,' you need a third table — e.g., Enrollment — with a student_id foreign key and a course_id foreign key. Each row in Enrollment represents one student-course pairing. This junction table can also carry additional attributes like grade or enrollment_date.

Question 4

In an ER diagram, nearly every attribute of an entity should become its own separate table in the relational schema.

• T. True
• F. False

Answer: False

Attributes become columns in the entity's table, not separate tables. A Student entity with attributes student_id, name, and email translates to a students table with three columns — not three tables. Creating a separate table for each attribute would be a severe over-normalization error, resulting in hundreds of unnecessary joins. Separate tables arise from relationships (one-to-many via foreign keys, many-to-many via junction tables) and weak entities — not from ordinary attributes of a single entity.

Question 5

What is the purpose of drawing an ER diagram before writing any SQL, and what key design decisions does it help you make?

Model answer: An ER diagram lets you think about the structure of your data at a conceptual level — what things exist, what properties they have, and how they relate — before committing to a physical implementation. It forces you to identify entities, choose primary keys, and specify cardinalities (1:1, 1:N, M:N), which directly determine the table structure, foreign key placement, and whether junction tables are needed.

The value of ER modeling is separating concerns: first get the logical structure right (what must be true about the data), then translate it mechanically into tables. Jumping straight to SQL means making structural decisions ad hoc, which leads to schemas that are hard to query, violate integrity, or require costly restructuring later. ER diagrams also communicate intent to teammates — a diagram is much easier to review than raw SQL CREATE TABLE statements.