CompTIA Tech+ (FC0-U71)Data and Database FundamentalsHard
A university database tracks Students and Courses, where a single student can enroll in many courses and a single course can have many students enrolled. Which design technique correctly models this relationship?
- AStore a comma-separated list of CourseIDs in a single field in the Students table
- BMerge the Students and Courses tables into one combined table
- CAdd a CourseID column directly to the Students table
- DCreate a junction table containing StudentID and CourseID as foreign keys
Show answer & explanationAnswer & explanation
Correct answer: D. Create a junction table containing StudentID and CourseID as foreign keys
A many-to-many relationship cannot be represented with a simple foreign key on either side, so a junction (linking) table is created with two foreign keys, one referencing each parent table's primary key, forming a composite key that records each valid pairing.
Why the other options are wrong
- A. Storing multiple values in one field violates first normal form and makes querying and referential integrity difficult.
- B. Merging the tables would create massive redundancy, repeating student or course data for every enrollment combination.
- C. A single foreign key column only supports one course per student, modeling a one-to-many relationship instead.
Many-to-Many Relationship
A relationship where multiple records in one table can relate to multiple records in another table, resolved using a junction (associative) table.
- Cannot be modeled with a simple foreign key on one side
- Requires a junction table with foreign keys to both parent tables
- The junction table's combined foreign keys often form a composite primary key
Memory trick: Many-to-many needs a middleman table to link both sides.