SQL Quiz: Bridge Tables
10 questions · exam conditions
0:00
Bridge TablesQuestion 1 of 10

A university uses StudentClub(student_id, club_id) as a bridge table. Its composite primary key prevents duplicate memberships. Each club has a campus and an active indicator. A report must count, by campus, the number of different students who belong to at least one active club.

After joining StudentClub to Club and filtering for active clubs, which aggregate produces the required count for each campus?

COUNT(*), because each bridge row represents one valid student membership
COUNT(DISTINCT StudentClub.student_id), because a student may join several active clubs
COUNT(DISTINCT StudentClub.club_id), because active clubs determine campus membership
COUNT(Club.club_id), because the club primary key removes repeated student memberships
← Back to quizzes

SQL Quiz

SQL Quiz: Bridge Tables

Practice Bridge Tables 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 Bridge Tables, 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 university uses StudentClub(student_id, club_id) as a bridge table. Its composite primary key prevents duplicate memberships. Each club has a campus and an active indicator. A report must count, by campus, the number of different students who belong to at least one active club.

After joining StudentClub to Club and filtering for active clubs, which aggregate produces the required count for each campus?

  1. COUNT(*), because each bridge row represents one valid student membership
  2. COUNT(DISTINCT StudentClub.student_id), because a student may join several active clubs (correct answer)
  3. COUNT(DISTINCT StudentClub.club_id), because active clubs determine campus membership
  4. COUNT(Club.club_id), because the club primary key removes repeated student memberships
Explanation: When a question asks you to count distinct entities after a join, your first instinct should be to ask: "Could my join produce duplicate rows for the thing I'm counting?" That's the core concept being tested here. After joining StudentClub to Club and filtering for active clubs, consider what the result set looks like. If a student belongs to three active clubs on the same campus, that student appears in three rows — one per club membership. The goal is to count each student once per campus, regardless of how many active clubs they joined. That's exactly what COUNT(DISTINCT StudentClub.student_id) does: it collapses duplicate student IDs within each campus group, giving you the true count of unique students. Answer B is correct. Answer A fails because COUNT(*) tallies rows, not unique students. That student in three clubs contributes 3 to the count instead of 1, inflating your results. The composite primary key prevents duplicate memberships, not duplicate student appearances after a join. Answer C counts distinct clubs per campus, which tells you how many active clubs exist there — not how many students belong to any of them. These are entirely different metrics. Answer D uses COUNT(Club.club_id) without DISTINCT, so it counts the number of club references in your joined rows. Again, a student in multiple clubs inflates the count, and you're still measuring club appearances rather than unique students. Study tip: Whenever you join a bridge/junction table and aggregate, always ask whether the join multiplies rows for the entity you're counting — if yes, COUNT(DISTINCT ...) is almost certainly what you need.

Question 2

An application uses UserRole(user_role_id, user_id, role_id, granted_at) as a bridge table. user_role_id is a surrogate primary key. Business rules allow a user to hold many roles but do not allow the same role to be assigned to the same user more than once.

Which additional database rule most directly enforces the stated relationship while retaining the surrogate key?

  1. Add a unique constraint on (user_id, role_id) and keep foreign keys to both parent tables (correct answer)
  2. Add separate unique constraints on user_id and role_id and keep both foreign keys
  3. Add a unique constraint on (user_role_id, user_id) and keep both foreign keys
  4. Add an index on (user_id, role_id) without declaring that index to be unique
Explanation: When designing bridge tables, your goal is to enforce the business rule at the database level — not just track data, but prevent invalid states. Here, the rule is clear: a user can have many roles, but the same user-role combination must be unique. The surrogate key (user_role_id) handles identity, but it does nothing to prevent duplicate (user_id, role_id) pairs on its own. Option A is correct because a composite unique constraint on (user_id, role_id) directly mirrors the business rule: no two rows can share the same user and role together. Paired with foreign keys to both parent tables, this gives you referential integrity and logical uniqueness — exactly what's needed while keeping the surrogate key intact. Option B adds separate unique constraints on user_id alone and role_id alone. This would mean each user could only appear once and each role could only be assigned once across the entire table — far more restrictive than intended and completely breaking the "many roles per user" requirement. Option C constrains (user_role_id, user_id), but since user_role_id is already a unique primary key, adding it to a composite unique constraint is redundant — every row is automatically distinct. This enforces nothing new about the user-role relationship. Option D creates a regular (non-unique) index on (user_id, role_id). Indexes improve query performance but carry no enforcement power — duplicate combinations would still be allowed. Study tip: Whenever you see a bridge table with a surrogate key, ask yourself: "What composite unique constraint reflects the actual business rule?" The surrogate key ensures row identity; only a unique constraint on the natural key enforces the real-world uniqueness requirement.

Question 3

Projects may be funded by several departments. ProjectDepartment(project_id, department_id, allocation_pct) stores the funding relationship, and each project has one total budget in Project. A department report must calculate its allocated share of project budgets without counting an entire shared project's budget for every department.

Which design and calculation best supports an accurate department total?

  1. Store the project budget in every bridge row and sum those copies for each participating department
  2. Store allocation_pct in Department and sum each joined project's full budget once per department
  3. Store allocation_pct in the bridge and sum each project budget multiplied by that relationship's allocation (correct answer)
  4. Omit allocation data and divide every project's budget by the total number of departments in the database
Explanation: When a project's budget is shared across multiple departments, the key challenge is proportional attribution — each department should only "own" the slice of a project it actually funds. This is a classic bridge table design question, where the bridge (ProjectDepartment) captures the many-to-many relationship between projects and departments. The correct approach, C, stores allocation_pct directly in the bridge row — exactly where it belongs, since allocation is a property of the relationship, not of either entity alone. The calculation then becomes: \text{Department Total} = \sum_{\text{projects}} (\text{budget} \times \text{allocation_pct}) This ensures each department receives only its proportional share, and the allocations across all departments can sum cleanly to 100% per project. A is tempting but dangerous — copying the full budget into every bridge row means each department appears to "own" the entire project. Summing those copies inflates totals massively for shared projects. B misplaces the allocation percentage in Department, which implies a single department-wide allocation rate. But a department might fund Project X at 30% and Project Y at 70% — that variation lives at the relationship level, not the department level. D is a logical shortcut that breaks immediately in real data. Dividing by the total number of departments in the database (not even per-project participants) bears no relationship to how funding is actually structured. A useful rule of thumb: attributes that describe a relationship belong in the bridge table, not in either parent entity. Whenever you see allocation, priority, or role in a many-to-many scenario, look for it — or put it — in the junction table.

Question 4

A publishing database has two bridge tables: AuthorBook(author_id, book_id) and BookGenre(book_id, genre_id). Both bridges prevent duplicate pairs. A report must show how many different genres are represented by the books of each author. An author may have several books in the same genre.

After joining an author through both bridge tables, which expression correctly calculates the requested value?

  1. COUNT(*), because both bridge tables already prevent duplicate relationship pairs
  2. COUNT(DISTINCT AuthorBook.book_id), because every book contributes one genre
  3. COUNT(DISTINCT BookGenre.genre_id), because a genre may recur across an author's books (correct answer)
  4. SUM of each book's genre count, because book-level aggregation removes all repeated genres
Explanation: When joining across multiple bridge tables, you need to think carefully about what duplicates are introduced at the join level versus what duplicates already existed in the source tables. Here's the key insight: when you join AuthorBook to BookGenre, an author with 3 books each appearing in 2 genres produces 6 rows. If two of those books share the same genre, that genre_id appears twice in the result set — even though BookGenre itself prevented duplicate (book_id, genre_id) pairs. The question asks for distinct genres per author, so you need to collapse those repetitions. COUNT(DISTINCT BookGenre.genre_id) does exactly that — it counts each genre only once regardless of how many of the author's books belong to it. That's why C is correct. A is tempting because the bridge tables do prevent duplicate pairs within themselves, but that guarantee doesn't survive the join. The cross-product of two bridge tables can absolutely re-introduce repeated genre_ids at the author level, so COUNT(*) would overcount. B misconstrues the goal entirely. Counting distinct books tells you how many books an author has written — not how many genres those books span. A single genre could cover all of an author's books, and this expression would still return a large number. D introduces unnecessary complexity and doesn't solve the problem. Summing per-book genre counts would double-count any genre shared across multiple books, producing inflated totals. The study tip here: whenever you join through multiple bridge tables, ask yourself what new duplicates the join creates before choosing your aggregation. COUNT(DISTINCT) is your tool when the same value can legally reappear across joined rows.

Question 5

A legacy Employee table contains a nullable skill_id foreign key, so each employee can currently have at most one recorded skill. The system must be changed to support many skills per employee and many employees per skill while preserving existing assignments.

Which migration sequence best introduces the many-to-many relationship without losing current data?

  1. Add multiple skill columns to Employee, copy the current value into the first column, and remove the skill table
  2. Remove Employee.skill_id, create EmployeeSkill, and infer previous assignments from the remaining employee records
  3. Create EmployeeSkill, leave all existing assignments only in Employee.skill_id, and use both locations permanently
  4. Create EmployeeSkill, copy each non-null employee-skill pair into it, validate the bridge, and then remove Employee.skill_id (correct answer)
Explanation: When migrating a schema to support many-to-many relationships, your guiding principle should be preserve first, restructure second, then clean up. Think of it as a three-phase operation: build the new structure, migrate the data, validate it, and only then remove the old structure. Option D follows this exact pattern. You create the EmployeeSkill bridge table, copy every non-null skill_id from Employee into it as a row, confirm the data transferred correctly, and finally drop Employee.skill_id. No existing assignments are lost, and the new structure properly supports multiple skills per employee going forward. This is the correct approach. Option A moves in the wrong direction entirely — adding more columns to Employee is a classic anti-pattern called "repeating groups," which violates first normal form and doesn't scale. You'd also be deleting the Skill table, destroying referential integrity. Option B removes skill_id before migrating the data, which means you've already discarded the existing assignments before ever recording them in EmployeeSkill. The phrase "infer previous assignments" is a red flag — inferring data you had is just guessing. Option C creates the bridge table but intentionally maintains two sources of truth permanently. This causes synchronization problems: which table is authoritative? Queries become inconsistent, and updates risk diverging between both locations. The strategy to remember: any migration that removes or abandons existing data before safely copying it elsewhere is automatically disqualifying. Always look for the answer that validates data integrity at each step before committing to the next.

Question 6

A social application models an undirected friendship between users with a self-referencing bridge. A friendship between users 12 and 40 must be treated as the same relationship whether an application submits (12, 40) or (40, 12). A user cannot befriend the same user account.

Which bridge-table rule most reliably enforces these requirements?

  1. Store either user ID first, use a unique pair constraint, and allow reversed pairs as separate rows
  2. Store the smaller user ID first, use a unique pair constraint, and require the two IDs to differ (correct answer)
  3. Make each user ID separately unique, and require the first user ID to be smaller
  4. Use only a surrogate friendship ID, and rely on two foreign keys to detect reversed duplicates
Explanation: When designing a bridge table for undirected relationships, you need to solve two problems simultaneously: preventing duplicate pairs regardless of insertion order, and preventing a row where both columns reference the same entity. A unique constraint alone only works if the data is always in a consistent format — so your design must guarantee that format. The most reliable approach is to canonicalize the pair before it even hits the unique constraint. By always storing the smaller user ID in the first column, you ensure that (12, 40) and (40, 12) both get stored as (12, 40) — making them identical to the database. Pair that with a composite unique constraint on both columns and a CHECK constraint requiring the two IDs to differ (e.g., user_$id_1$ < user_$id_2$), and you've fully enforced both requirements at the schema level. That's exactly what B describes. A is the trap answer — storing either user ID first and relying on a unique constraint sounds reasonable, but the database will happily store both (12, 40) and (40, 12) as distinct rows, defeating the purpose entirely. C is flawed because making each user ID separately unique would mean each user could appear in only one friendship row total — a severe and incorrect restriction that would break the entire feature. D relies on application logic to detect reversed duplicates, which is fragile. A surrogate ID provides no structural guarantee; two rows with swapped foreign keys would pass all constraints undetected. A useful rule of thumb: whenever you store undirected relationships, normalize direction at write time and enforce uniqueness on the normalized form — never leave duplicate detection to the application layer.

Question 7

An EmployeeSkill(employee_id, skill_id) bridge has two foreign keys. The employee foreign key uses ON DELETE CASCADE, while the skill foreign key uses ON DELETE RESTRICT. Employee and skill rows may each participate in many bridge rows.

Which outcome follows from these referential actions?

  1. Deleting an employee removes that employee's bridge rows; deleting a referenced skill is rejected (correct answer)
  2. Deleting an employee also removes referenced skills; deleting a skill preserves its bridge rows
  3. Deleting either parent removes only its bridge rows, because both relationships are many-to-many
  4. Deleting either parent is rejected whenever any bridge row references that parent
Explanation: When a question gives you multiple foreign keys with different referential actions, treat each relationship independently — don't let the many-to-many structure fool you into thinking they behave the same way. Here, EmployeeSkill has two foreign keys: one pointing to Employee with ON DELETE CASCADE, and one pointing to Skill with ON DELETE RESTRICT. These constraints operate separately on each parent table. ON DELETE CASCADE means that when a parent row is deleted, all child rows referencing it are automatically deleted too. So deleting an employee wipes out that employee's bridge rows — no manual cleanup needed. ON DELETE RESTRICT means the database refuses the delete if any child row still references that parent. So if a skill has even one bridge row, attempting to delete that skill raises an error and the deletion is rejected. This makes A correct: employee deletes cascade into the bridge; skill deletes are blocked by the bridge. B is wrong because ON DELETE CASCADE on the employee key affects only bridge rows, not the skills themselves — skills are a separate parent table and are untouched. C is wrong because the many-to-many nature of the relationship is irrelevant to how referential actions work; those actions are defined per foreign key, not per cardinality. D is wrong because it describes RESTRICT behavior for both foreign keys, ignoring that the employee side uses CASCADE, which always succeeds. A useful study tip: when you see multiple foreign keys on a bridge table, mentally annotate each one with its own action. Don't assume they share behavior just because they're on the same table.

Question 8

A manufacturer must record which supplier provides which part for which project. A supplier may provide the same part to several projects, and a project may obtain the same part from several suppliers. The agreed price depends on the complete supplier-part-project combination.

Which relational design preserves the required association without introducing ambiguous combinations?

  1. Use three independent pairwise bridges and derive the agreed price from any matching pair
  2. Use a supplier-part bridge and a separate part-project bridge, storing the agreed price with the part
  3. Use a supplier-project bridge and store one preferred supplier directly in each part row
  4. Use one bridge containing supplier_id, part_id, project_id, and the agreed price (correct answer)
Explanation: Whenever you see a scenario where three entities interact and the key attribute (like a price) depends on the combination of all three, you're dealing with a ternary relationship — and that signals you need a single bridge table capturing all three foreign keys together. Here, the agreed price is not a property of a supplier alone, a part alone, or a project alone — it only makes sense when all three are specified together. That's exactly what D models: a single bridge table with supplier_id, part_id, project_id, and agreed_price. The composite primary key (supplier_id, part_id, project_id) uniquely identifies each combination, preventing ambiguity and correctly enforcing the business rule. A breaks the relationship into three separate pairwise tables, but pairwise bridges can't represent a true three-way association — you'd have no way to determine which supplier-part pair applies to which project without creating phantom or conflicting combinations. B stores the agreed price with the part, but the price depends on the full supplier-part-project trio, not the part alone — this violates the dependency and produces incorrect or misleading data whenever multiple suppliers or projects are involved. C stores a single "preferred supplier" inside the part row, which can only record one supplier per part and entirely loses the project dimension — it fundamentally fails to represent the many-to-many-to-many relationship described. As a study tip: when a business rule says an attribute depends on multiple entities together, that's your cue that no pairwise or embedded approach will work — you need a ternary bridge table with all participating keys. Watch for price, quantity, or role attributes that "depend on the combination" as a signal.

Question 9

Products and tags have a many-to-many relationship represented by ProductTag(product_id, tag_id). The Tag table contains an active column. A report must return every product, including products with no active tags, and show the number of active tags for each product.

Which join and counting strategy satisfies the report requirement?

  1. Left join Product to ProductTag and then to Tag, place Tag.active = 1 in the tag join condition, and count Tag.tag_id (correct answer)
  2. Left join Product to ProductTag and then to Tag, place Tag.active = 1 in the WHERE clause, and count Tag.tag_id
  3. Left join Product to ProductTag and then to Tag, place Tag.active = 1 in the tag join condition, and count ProductTag.tag_id
  4. Inner join Product to ProductTag and then to Tag, place Tag.active = 1 in the tag join condition, and count Tag.tag_id
Explanation: When a report must include all rows from a driving table — even those with no matches — you need a LEFT JOIN. The second critical decision is where to place filter conditions, because WHERE and ON behave very differently after a left join. Answer A is correct because it does two things right. First, it left-joins Product → ProductTag → Tag, ensuring every product appears even if it has zero active tags. Second, it places Tag.active = 1 in the JOIN condition (the ON clause), not in WHERE. This means inactive tags are simply excluded from the join, leaving the product row intact with NULL values. Finally, counting Tag.tag_id (a nullable column from the right side) correctly returns 0 for products with no active tags, since COUNT ignores NULLs. Answer B fails because moving Tag.active = 1 into the WHERE clause silently converts your left join into an inner join — any product with no active tags produces a NULL for Tag.active, which fails the WHERE filter and eliminates that product row entirely. This is one of the most common left-join traps in SQL. Answer C counts ProductTag.tag_id instead of Tag.tag_id. Since ProductTag is joined before the active filter, its tag_id is non-null even for inactive tags, so you'd overcount active tags. Answer D uses an inner join, immediately excluding products with no active tags from the result set. Study tip: Always remember — filter conditions that should limit joined rows but preserve the driving table's rows belong in the ON clause, not WHERE.

Question 10

A consulting firm records which consultants work on which projects. A consultant may work on the same project during several billing periods, but there can be at most one assignment row for a given consultant, project, and billing period. The billing rate is an attribute of that period-specific assignment.

Which bridge-table design best represents the required relationship and prevents duplicate assignment rows?

  1. ConsultantProject(consultant_id, project_id, billing_period, billing_rate) with a primary key on (consultant_id, project_id, billing_period) (correct answer)
  2. ConsultantProject(consultant_id, project_id, billing_period, billing_rate) with a primary key on (consultant_id, project_id)
  3. ConsultantProject(consultant_id, project_id, billing_period, billing_rate) with a primary key on (project_id, billing_period)
  4. ConsultantProject(assignment_id, consultant_id, project_id, billing_period, billing_rate) with a primary key only on assignment_id
Explanation: When designing a bridge (junction) table in SQL, your primary goal is to define a composite primary key that uniquely identifies each meaningful combination of entities — and nothing more, nothing less. Ask yourself: what combination of attributes makes one row truly distinct from another? Here, the passage tells you a consultant can work on the same project across multiple billing periods, but no two rows should share the same consultant, project, and billing period together. That three-column combination is your natural unique identifier, making Answer A — a primary key on (consultant_id, project_id, billing_period) — the correct design. The database engine will automatically reject any duplicate combination of those three values, enforcing the business rule at the schema level. Answer B fails because its primary key on (consultant_id, project_id) only allows each consultant-project pair to appear once total, completely blocking the multi-period assignments the passage explicitly requires. Answer C is equally flawed but in a different direction: keying on (project_id, billing_period) assumes only one consultant can ever work on a project in a given period, which is an unwarranted restriction. Answer D introduces a surrogate key (assignment_id) as the sole primary key, which prevents no duplicates at all — you could insert ten identical rows for the same consultant, project, and period, each getting a unique assignment_id. A surrogate key can coexist with a uniqueness constraint, but alone it enforces nothing meaningful here. The study tip: when evaluating bridge-table designs, trace through a concrete duplicate scenario for each option. If the primary key would allow that duplicate to insert, the design is broken.