SQL Quiz: Many To Many Modeling
10 questions · exam conditions
0:00
Many To Many ModelingQuestion 1 of 10

A training company stores Student, Course, and Term data. A student may take the same course in different terms but may enroll in that course at most once during any one term. Each course can have many students in each term.

Which design best enforces the stated enrollment rule while modeling the many-to-many relationship?

Add CourseID and TermID to Student, using StudentID as the only primary key.
Create Enrollment(StudentID, CourseID) with the two columns as its composite primary key.
Create Enrollment(StudentID, CourseID, TermID) with all three columns as its composite primary key.
Create Enrollment(EnrollmentID, StudentID, CourseID, TermID) with no additional uniqueness constraint.
← Back to quizzes

SQL Quiz

SQL Quiz: Many To Many Modeling

Practice Many To Many Modeling in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Many To Many Modeling, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A training company stores Student, Course, and Term data. A student may take the same course in different terms but may enroll in that course at most once during any one term. Each course can have many students in each term.

Which design best enforces the stated enrollment rule while modeling the many-to-many relationship?

  1. Add CourseID and TermID to Student, using StudentID as the only primary key.
  2. Create Enrollment(StudentID, CourseID) with the two columns as its composite primary key.
  3. Create Enrollment(StudentID, CourseID, TermID) with all three columns as its composite primary key. (correct answer)
  4. Create Enrollment(EnrollmentID, StudentID, CourseID, TermID) with no additional uniqueness constraint.
Explanation: When modeling many-to-many relationships in SQL, your primary key must capture exactly what makes one row unique — no more, no less. Here, the business rule is: a student can enroll in the same course across different terms, but only once per course per term. That means uniqueness is defined by the combination of all three attributes: student, course, and term. Option C is correct because Enrollment(StudentID, CourseID, TermID) with a composite primary key across all three columns enforces this rule directly at the database level. Any attempt to insert a duplicate enrollment for the same student, course, and term will be rejected automatically — no extra application logic needed. Option A fails because adding CourseID and TermID directly to the Student table can only represent one course per student, collapsing the many-to-many relationship entirely. It's a fundamental schema design error. Option B creates a junction table but omits TermID. Its composite key (StudentID, CourseID) would prevent a student from ever taking the same course again in a later term — directly contradicting the stated rule. Option D introduces a surrogate key (EnrollmentID) as the sole primary key with no uniqueness constraint on (StudentID, CourseID, TermID). This means duplicate enrollments for the same student, course, and term could be inserted freely, leaving enforcement entirely to application code — which is fragile and unreliable. Study tip: When a business rule says "at most once per [combination of things]," that combination should usually form your composite primary key or carry a UNIQUE constraint. Let the database enforce it, not your application.

Question 2

A manufacturer purchases each part from multiple suppliers, and each supplier provides multiple parts. For every supplier-part combination, the database must store the supplier's current quoted price and minimum order quantity. Only one current quotation is retained for each combination.

Which conceptual design most accurately represents these requirements?

  1. Store SupplierID, quoted price, and minimum quantity in Part, allowing one supplier per part row.
  2. Create SupplierPart(SupplierID, PartID, QuotedPrice, MinimumQuantity) keyed by the supplier-part combination. (correct answer)
  3. Store quoted price in Supplier and minimum quantity in Part, linking the tables through separate attributes.
  4. Create independent SupplierQuote and PartQuote tables without a row that identifies both entities.
Explanation: When designing a database for a many-to-many relationship, your first instinct should be to look for a junction (associative) table. Whenever two entities — like Supplier and Part — each relate to many instances of the other, you need a separate table that captures the pairing and stores any attributes specific to that relationship. That's exactly what option B does. SupplierPart(SupplierID, PartID, QuotedPrice, MinimumQuantity) uses the composite key (SupplierID, PartID) to uniquely identify each supplier-part combination, which is precisely what the requirement means by "only one current quotation retained for each combination." The quoted price and minimum quantity belong to the relationship itself, not to either entity alone — a supplier's price varies by part, and a part's minimum order varies by supplier. Option A breaks the many-to-many requirement immediately. Embedding SupplierID in the Part table forces each part to have exactly one supplier, which directly contradicts the stated requirement that each part comes from multiple suppliers. Option C scatters relationship-specific attributes across the wrong tables. Storing QuotedPrice in Supplier implies one price per supplier regardless of part, and storing MinimumQuantity in Part implies one quantity regardless of supplier — neither reflects reality. Option D splits the data into disconnected tables without a row linking both SupplierID and PartID together, making it impossible to associate a specific price with a specific supplier-part pair. Study tip: Whenever a scenario describes attributes that only make sense in the context of two entities together, that's your signal to create a junction table with a composite key.

Question 3

Doctors treat many patients, and patients may be treated by many doctors. Every appointment has its own status and scheduled time. The same doctor and patient can have multiple appointments, and an appointment must retain a stable identity if it is rescheduled.

Which design best models the relationship and preserves appointment history?

  1. Create DoctorPatient(DoctorID, PatientID, Status, ScheduledTime) keyed only by DoctorID, PatientID.
  2. Store one DoctorID and the latest appointment details directly in each Patient row.
  3. Create Appointment(AppointmentID, DoctorID, PatientID, Status, ScheduledTime) with foreign keys to both parents. (correct answer)
  4. Create separate doctor-schedule and patient-schedule tables without a shared appointment identifier.
Explanation: When modeling complex real-world relationships in SQL, your first instinct should be to identify whether an interaction between two entities has its own meaningful attributes — if it does, it deserves its own table with its own primary key. That's the core concept being tested here: recognizing when a junction table needs to become a full entity table. The doctor-patient relationship here isn't just a simple many-to-many link — each appointment carries unique data (status, scheduled time) and must survive rescheduling with a stable identity. Option C solves this elegantly by giving Appointment its own surrogate key (AppointmentID). This means the same doctor and patient can have unlimited appointments without any ambiguity, and rescheduling simply updates ScheduledTime on an existing row rather than destroying history. Foreign keys to both Doctor and Patient enforce referential integrity while preserving the relationships. Option A is the classic many-to-many junction table mistake — keying only on (DoctorID, PatientID) means you can store exactly one appointment per doctor-patient pair. A second appointment would overwrite the first, destroying history entirely. Option B is worse: embedding appointment details directly in a Patient row assumes each patient has only one doctor and one appointment, violating both the many-to-many relationship and any history tracking. Option D might sound organized, but splitting schedules into separate tables without a shared AppointmentID makes it nearly impossible to join them coherently or track a single appointment's lifecycle. Study tip: Whenever an entity in a relationship has its own attributes or needs a stable identity over time, treat it as a full table with a surrogate key — not just a linking table.

Question 4

Employees may mentor several other employees, and an employee may have several mentors. Mentorship is directional: if Lina mentors Omar, that does not imply Omar mentors Lina. Reciprocal mentorship is allowed, but an employee cannot mentor themself.

Which design most accurately models this self-referencing many-to-many relationship?

  1. Create Mentorship(EmployeeID, MentorID) but treat the two identifiers as an unordered pair.
  2. Create Mentorship(MentorID, MenteeID) with two employee foreign keys, a unique directed pair, and a no-self check. (correct answer)
  3. Add one nullable MentorID foreign key to Employee, allowing each employee to have one current mentor.
  4. Create separate Mentor and Mentee entity tables, each containing duplicated employee identity data.
Explanation: When you encounter a self-referencing relationship in SQL, ask yourself two questions: How many can each side have? and Does direction matter? Here, both answers point toward a dedicated junction table with clearly labeled, directional foreign keys. Option B — Mentorship(MentorID, MenteeID) with two employee foreign keys, a unique constraint on the ordered pair, and a CHECK constraint preventing MentorID = MenteeID — handles everything the scenario demands. The two distinct column names encode direction explicitly. The unique constraint on (MentorID, MenteeID) prevents duplicate directed relationships while still allowing the reverse pair (MenteeID, MentorID) to exist separately, which supports reciprocal mentorship. The self-check enforces the business rule that no one can mentor themselves. Option A fails because treating the pair as unordered erases directionality. If Lina mentors Omar, storing (Lina, Omar) and (Omar, Lina) as duplicates would incorrectly block reciprocal mentorship, which the passage explicitly allows. Option C places a single nullable MentorID on the Employee table, limiting each employee to one mentor. That models a many-to-one relationship, not the many-to-many the scenario requires. Option D duplicates employee identity data into separate Mentor and Mentee tables, which violates normalization. Employee data belongs in one place; role context belongs in the relationship table, not in redundant entity copies. A useful pattern to remember: any time a relationship is many-to-many and carries attributes like direction or constraints, a dedicated junction table with well-named foreign keys and appropriate constraints is almost always the right design.

Question 5

Employees may possess many skills, and each skill may be possessed by many employees. The company stores a proficiency level and certification expiration date for each employee-skill combination. Only the employee's current record for a skill is required.

Where should proficiency level and expiration date be stored?

  1. In an EmployeeSkill junction table, because both values describe a specific employee-skill association. (correct answer)
  2. In Employee, because proficiency and expiration apply uniformly to all skills held by that employee.
  3. In Skill, because every employee possessing the same skill must share those two values.
  4. In both parent tables, synchronizing duplicate proficiency and expiration values whenever either changes.
Explanation: When designing a relational database, your first instinct for any attribute should be: what does this value actually describe? Proficiency level and expiration date don't describe an employee in isolation, nor a skill in isolation — they describe the relationship between a specific employee and a specific skill. That reasoning points directly to a junction table. In a many-to-many relationship, a junction table like EmployeeSkill stores the foreign keys from both parent tables plus any attributes that belong to the pairing itself. Since proficiency and expiration date vary per employee-per-skill combination (one employee might be a beginner in SQL but an expert in Python, with different cert dates for each), these attributes belong in EmployeeSkill. Answer A is correct. Answer B places proficiency and expiration in Employee, implying every skill that employee holds shares the same proficiency and expiration — which is clearly wrong since an employee can have different skill levels for different skills. Answer C makes the opposite mistake: storing these values in Skill implies every employee holding that skill has identical proficiency and expiration, which is equally illogical. Answer D duplicates the data across both parent tables, which violates normalization principles — keeping duplicate values synchronized is error-prone, wastes storage, and is exactly the problem relational design is meant to solve. A useful rule of thumb: if an attribute requires knowing both a row from Table A and a row from Table B to be meaningful, it belongs in the junction table between them. Watch for this pattern on any question involving many-to-many relationships.

Question 6

A social application stores friendship between users. Friendship is undirected: a friendship between users 12 and 35 is the same relationship regardless of which user initiated it. The database must prevent duplicate and self-friendship rows while allowing each user to have many friends.

Which junction-table design most reliably enforces these rules?

  1. Store both directed rows for every friendship, relying on application logic to insert and delete the pair atomically and keep them synchronized.
  2. Always store the smaller user identifier in the first column, enforce that the two identifiers differ, and apply a unique constraint to the ordered pair. (correct answer)
  3. Add a single nullable FriendID foreign key to User, overwriting it each time a new friendship is accepted by that user.
  4. Allow either identifier order in Friendship and apply a separate unique constraint to each individual column to block repeated values.
Explanation: When designing a junction table for undirected relationships, your goal is to let the database itself — not application code — enforce uniqueness and validity. Ask yourself: can the database structurally prevent bad data, or does the design depend on developers doing everything correctly every time? The most reliable approach is B: always store the pair so the smaller ID comes first, add a CHECK constraint ensuring the two values differ (blocking self-friendship), and apply a UNIQUE constraint to the ordered pair (smaller_id, larger_id). Because the ordering rule is deterministic, every friendship has exactly one canonical representation. The database engine enforces both the "no duplicates" and "no self-friendship" rules without any application cooperation required. A is tempting because bidirectional rows simplify certain queries, but it fundamentally outsources integrity to application logic. If one insert fails mid-transaction, or a developer forgets to delete both rows, the data becomes inconsistent. Relying on atomic pairs "working correctly" is a design smell, not a guarantee. C collapses the entire friend relationship into a single nullable column on the User table. This means each user can only have one friend at a time — the column gets overwritten with each new friendship. It completely fails the "many friends" requirement. D misunderstands what uniqueness constraints do here. Constraining each individual column to be unique would mean no user ID could ever appear more than once in the whole table — effectively allowing only one friendship total in the system. Study tip: When you see integrity questions, prioritize designs where the schema itself enforces the rules. If correctness depends on application logic staying perfect forever, it's a fragile design.

Question 7

Members can belong to many committees, and committees can have many members. Within a committee, a member may hold several roles, such as reviewer and coordinator. The same role may be held by many members and may be used in many committees. Duplicate assignment of the same role to the same member in the same committee is prohibited.

Which design best represents the role assignments?

  1. Create MemberCommittee(MemberID, CommitteeID, RoleID) keyed only by MemberID, CommitteeID.
  2. Store one RoleID in Member and one CommitteeID in Role, using both as nullable foreign keys.
  3. Create MemberCommitteeRole(MemberID, CommitteeID, RoleID) keyed by all three foreign keys. (correct answer)
  4. Create MemberCommitteeRole(AssignmentID, MemberID, CommitteeID, RoleID) without a uniqueness constraint on the combination.
Explanation: When modeling many-to-many relationships with additional attributes — like roles — you need a junction table whose primary key enforces exactly the business rules described. Here, the rule is clear: a member cannot hold the same role twice in the same committee, but they can hold multiple different roles. That constraint lives entirely in how you define the primary key. C is correct because MemberCommitteeRole(MemberID, CommitteeID, RoleID) uses all three foreign keys as a composite primary key. This automatically prevents duplicate role assignments for the same member-committee combination, while still allowing one member to hold multiple roles in one committee — precisely what the passage requires. A fails because keying only on MemberID, CommitteeID means each member can have at most one role per committee. You'd have no way to represent a member who is both a reviewer and a coordinator on the same committee. B is a structural antipattern. Embedding CommitteeID in Role and RoleID in Member creates rigid, misleading dependencies that don't reflect the true many-to-many-to-many relationship. It also makes nullable foreign keys do the heavy lifting, which is a sign of a poor design. D introduces a surrogate key (AssignmentID) but omits a uniqueness constraint on the (MemberID, CommitteeID, RoleID) combination. Without that constraint, duplicate role assignments are allowed — directly violating the stated business rule. A useful study tip: whenever a passage says "duplicate X is prohibited," that's your signal to make X part of a composite primary key or add a unique constraint. The primary key is your enforcement mechanism.

Question 8

An Assignment junction table connects Employee and Project. Deleting an employee or project must automatically remove only the assignments involving that record. Deleting an assignment must never delete either parent record. The junction foreign keys are required and cannot be null.

Which referential-action design satisfies these requirements?

  1. Cascade deletes from Assignment to both parent tables, while restricting deletes initiated from either parent.
  2. Set both foreign keys in Assignment to null whenever an employee or project is deleted.
  3. Restrict deletion of both parent records whenever any related assignment row exists.
  4. Cascade deletes from each parent to Assignment; define no reverse cascade from the junction table. (correct answer)
Explanation: When designing referential integrity for a junction table, ask yourself two directional questions: "What should happen to child rows when a parent is deleted?" and "Should deleting a child row ever affect the parent?" Keeping these directions separate is the key to this problem. The correct design, D, places CASCADE DELETE on the foreign keys inside Assignment that point to Employee and Project. This means deleting an employee automatically removes their assignments, and deleting a project removes its assignments — exactly what the requirements demand. Crucially, no reverse cascade exists going from Assignment back up to the parent tables, so deleting an assignment row leaves both the employee and project records completely untouched. A gets the direction backwards. Cascading from Assignment to the parent tables would mean deleting a junction row could delete the employee or project — the opposite of what you want. Restricting deletes from the parent side would also prevent you from ever cleaning up assignments automatically. B violates the stated constraint that foreign keys are required and cannot be null. Setting them to null on parent deletion would immediately break the NOT NULL requirement, making this design structurally invalid. C uses RESTRICT on both parent tables, which means you could never delete an employee or project that has any assignments without first manually removing those assignments. The requirement explicitly says deletion should automatically remove related assignments — RESTRICT forces manual intervention instead. A useful mental model: cascade flows downward from parent to child. If you find yourself thinking about cascading upward from a child to a parent, that's a red flag that your design is inverted.

Question 9

A consulting firm tracks consultants and departments. A consultant may support any number of departments, including none. A department may use any number of consultants, including none. The firm creates consultant and department records before any support assignments are made.

Which design correctly preserves the optional participation on both sides?

  1. Keep independent parent tables and create ConsultantDepartment; no junction row is required until an assignment exists. (correct answer)
  2. Create ConsultantDepartment and require every consultant to have at least one junction row when inserted.
  3. Place a nullable DepartmentID in Consultant, creating another consultant row for each additional department.
  4. Place a nullable ConsultantID in Department, creating another department row for each additional consultant.
Explanation: When you see a question about optional participation on both sides of a relationship, think many-to-many with no mandatory minimums. The key is choosing a design where neither side is forced to participate before an assignment actually exists. A junction (bridge) table — here, ConsultantDepartment — is the standard solution for many-to-many relationships. The critical insight is that a junction table only needs a row when a real assignment is made. This means consultants and departments can exist independently in their own tables without any junction rows, perfectly honoring the "zero or more" participation rule on both sides. That's exactly what A describes, making it the correct design. B breaks the optional participation for consultants by requiring at least one junction row at insert time. This imposes mandatory participation, contradicting the scenario where records are created before any assignments exist. C is a classic anti-pattern called a repeating group. Storing a nullable DepartmentID in Consultant only handles one department per consultant. To support multiple departments, you'd need duplicate consultant rows, which violates first normal form and creates serious data integrity problems. D commits the same anti-pattern from the department's side — a nullable ConsultantID in Department can only reference one consultant, and duplicating department rows for each additional consultant creates redundancy and anomalies. Study tip: Whenever you see "many-to-many" and "optional on both sides," your first instinct should be two independent parent tables plus a junction table with no rows required at creation. Watch for distractors that smuggle a foreign key into one of the parent tables — that's always a red flag.

Question 10

Books may have multiple authors, and authors may write multiple books. For each book, the publisher records the display order of its authors. An author may appear only once on a given book, and two authors cannot occupy the same position on that book.

Which constraints on BookAuthor best implement these rules?

  1. Use BookID as the primary key and add a unique constraint on AuthorID and AuthorPosition.
  2. Use AuthorID as the primary key and add a unique constraint on BookID and AuthorPosition.
  3. Use BookID, AuthorPosition as the primary key without constraining repeated authors on the same book.
  4. Use BookID, AuthorID as the primary key and make BookID, AuthorPosition unique. (correct answer)
Explanation: When designing a junction table for a many-to-many relationship, you need to identify every uniqueness rule in the business requirements and map each one to a distinct constraint. Here, there are two rules: (1) an author appears at most once per book, and (2) no two authors share the same position on the same book. The cleanest solution is D: make BookID, AuthorID the primary key and add a unique constraint on BookID, AuthorPosition. The composite primary key directly enforces rule 1 — the combination of book and author must be unique, so the same author can't appear twice on one book. The separate unique constraint enforces rule 2 — no two rows can share the same book-and-position pair. Together, both rules are fully covered. Here's why the other options fall short. A uses BookID alone as the primary key, which means each book could only have one row in the table — immediately breaking the many-to-many relationship. B makes the same mistake in reverse: AuthorID as the sole primary key means each author could only link to one book. Both A and B misunderstand that neither BookID nor AuthorID alone is unique in this table. C uses BookID, AuthorPosition as the primary key, which prevents duplicate positions on a book but does nothing to stop the same author from appearing multiple times on the same book under different positions — directly violating rule 1. A useful pattern to remember: when you see multiple uniqueness rules in a requirements description, count them and verify your schema has a distinct constraint for each one.