ITPEC FE Morning April 2022 Question 25

Topic: Databases

When an ER diagram is translated into a set of tables in a relational database, which of the following is an appropriate method to translate a many-to-many relationship between two entities?

a)Combine two entities and create a single table.b)Create a table for each entity and import the primary key from a table of smaller size to a table of larger size.c)Create a table for each entity and import the primary key from one table to another table, and vice versa.d)Create a table for each entity and then create a new table and import the primary keys from both entities to the new table.

Explanation

A new table holding the primary keys of both entities — a many-to-many relationship becomes a third, associative table whose rows are the related pairs.

A column holds one value per row. In a many-to-many relationship, such as students and courses, one student takes many courses and one course has many students, so a foreign key in either entity's table could record only one partner per row. The mapping therefore keeps a table for each entity and adds an associative (junction) table with one row per related pair, holding both primary keys as foreign keys; together they form its primary key.

Example: Student(StudentID, …), Course(CourseID, …) and Enrollment(StudentID, CourseID). The M:N relationship is resolved into two 1:N relationships.

Answer (d)

Why not others:

•(a) — merging the two entities into one table repeats each entity's data for every partner, exactly the redundancy normalization removes.
•(b) — importing one table's key into the other is how a one-to-many relationship is mapped (the key goes to the "many" side, not to the larger table); each row still holds one reference, so it cannot record many partners.
•(c) — a foreign key in each table still holds one value per row, so every row can point to a single partner: at most a one-to-one relationship, stored twice.

Key rule: One-to-one and one-to-many relationships are mapped with a foreign key; a many-to-many relationship always needs an associative table.

Databases · AI-written, may contain errors

Previous Next

Practice this exam

This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official FE past-paper collection or Report an issue.