Studyledge

Databases and SQL: Question 6

Syllabus 8.1

Multiple choice AS 1 mark

Riverside Secondary School records student club membership. Each student may join several different clubs, and each club typically has many student members - for example, Amara is a member of both the Chess Club and the Debate Club, while the Chess Club itself has many other student members besides Amara.

Which of the following correctly identifies the relationship between STUDENT and CLUB, and how a relational database would properly implement it?

Choose an answer to check it, then compare with the worked solution below.
Show worked solution Hide worked solution

Worked solution

Why C is correct

Two things are true in this scenario at once: a single student (Amara) belongs to more than one club, and a single club (Chess Club) has more than one student member. This combination - many on both sides - is exactly what defines a many-to-many relationship.

A single foreign key cannot record a many-to-many relationship, because a foreign key field can only hold one value per row: putting a ClubID foreign key in STUDENT would only let each student link to one club, and putting a StudentID foreign key in CLUB would only let each club link to one student. Neither captures the full picture.

The correct solution is a linking table (sometimes called a junction or bridging table), for example MEMBERSHIP(StudentID, ClubID), with one row for every individual student-club pairing. Its primary key is the composite (StudentID, ClubID), and it holds a foreign key referencing STUDENT and a foreign key referencing CLUB. This lets any student link to any number of clubs, and any club link to any number of students, with each pairing stored as its own row.

Why the other options are wrong

  • A is false. The scenario itself shows a student belonging to more than one club (Amara is in both Chess Club and Debate Club), so this is not one-to-one.
  • B is false. This describes only the “club has many students” half of the relationship; it ignores that a student can also belong to many clubs, so a single foreign key in CLUB is not enough.
  • D correctly names the relationship as many-to-many, but its proposed implementation is wrong: storing multiple ClubID values inside one field of a single STUDENT record creates a repeating group, which breaks First Normal Form. A relational table must hold a single, atomic value in every field.

Final answer

C - the relationship between STUDENT and CLUB is many-to-many, and it must be implemented with a separate linking table holding one row per (StudentID, ClubID) pairing, not a single foreign key in either table and not a repeating group inside one field.