Questions: Converting ER Diagrams to Relational Schemas

Question 1

A university ER diagram has a many-to-many relationship between Student and Course, with an enrollment date as a relationship attribute. Which relational schema correctly represents this?

• A. Add a course_id foreign key to the Student table and store enrollment_date there
• B. Add a student_id foreign key to the Course table and store enrollment_date there
• C. Create a junction table Enrollment(student_id FK, course_id FK, enrollment_date) with a composite primary key
• D. Store both student_id and course_id as a comma-separated list in a single Enrollment column

Answer: (C)

Many-to-many relationships require a junction table because neither side can hold the other's foreign key without duplicating rows. The junction table's composite primary key (student_id, course_id) uniquely identifies each pairing, and relationship attributes like enrollment_date become additional columns. Options A and B each force one side to repeat rows for every pairing, violating relational design. Option D violates first normal form — you cannot store multiple values in one cell.

Question 2

A Student entity has a 'phones' attribute that can hold multiple phone numbers. How should this be mapped to a relational schema?

• A. Add a phones column to the Student table storing all numbers separated by commas
• B. Create a separate StudentPhone(student_id FK, phone) table
• C. Add phone1, phone2, phone3 columns to Student to accommodate up to three numbers
• D. Omit phone numbers entirely — multivalued attributes cannot be represented relationally

Answer: (B)

Multivalued attributes require their own table with a foreign key back to the parent entity. Storing all values in one cell (option A) or in fixed columns (option C) both violate first normal form, making it impossible to cleanly query for a specific phone number or handle an arbitrary number of phones. Option D is wrong — the separate table approach is exactly how multivalued attributes are handled.

Question 3

A one-to-many relationship between Department and Employee is correctly represented by adding a dept_id foreign key to the Employee table, with no separate junction table required.

• T. True
• F. False

Answer: True

For a 1:N relationship, the 'many' side holds the foreign key. Each Employee row stores a dept_id referencing one Department row, while many Employee rows can reference the same Department — exactly the 1:N semantics. Junction tables are needed only for M:N relationships, where neither side can hold the other's key without repeating rows. Creating a junction table for a 1:N relationship is unnecessary complexity.

Question 4

When converting a specialization hierarchy (e.g., Person → Student, Faculty) to a relational schema, the main correct approach is to create a separate table for each entity type in the hierarchy.

• T. True
• F. False

Answer: False

There are three valid strategies: (1) a single table with a type discriminator column and nullable subclass attributes; (2) separate tables for each subclass that include all superclass attributes; or (3) a superclass table joined to subclass tables via shared primary key. Each has tradeoffs — single-table avoids joins but wastes space with nulls; separate joined tables are cleaner when subclasses have many distinct attributes. The right choice depends on query patterns and schema requirements.

Question 5

Explain the difference between how a one-to-many and a many-to-many relationship are represented in a relational schema, and why this difference is necessary.

Model answer: In a 1:N relationship, the 'many' side's table gets a foreign key column pointing to the 'one' side's primary key — no new table needed. In an M:N relationship, neither side can hold the other's key without repeating rows, so a junction table is created with foreign keys to both sides, forming a composite primary key. The difference is necessary because the relational model stores one value per cell: a cell cannot hold a list of IDs.

The relational model's fundamental constraint — one atomic value per cell — is why M:N needs a junction table. If a student can enroll in many courses, you can't store all course IDs in one Student row cell (that's a non-atomic value). And you can't store all student IDs in one Course cell. The junction table represents every pairing as its own row, which is the only way to handle arbitrary M:N relationships without violating 1NF or duplicating data.