- Which relational design preserves the required association without introducing ambiguous combinations?
Answer: Use one bridge containing supplier_id, part_id, project_id, and the agreed price
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.
- Which outcome follows from these referential actions?
Answer: Deleting an employee removes that employee's bridge rows; deleting a referenced skill is rejected
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.
- After joining an author through both bridge tables, which expression correctly calculates the requested value?
Answer: COUNT(DISTINCT BookGenre.genre_id), because a genre may recur across an author's books
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.
- Which additional database rule most directly enforces the stated relationship while retaining the surrogate key?
Answer: Add a unique constraint on (user_id, role_id) and keep foreign keys to both parent tables
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.
- Which bridge-table design best represents the required relationship and prevents duplicate assignment rows?
Answer: ConsultantProject(consultant_id, project_id, billing_period, billing_rate) with a primary key on (consultant_id, project_id, billing_period)
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.
- Which join and counting strategy satisfies the report requirement?
Answer: Left join Product to ProductTag and then to Tag, place Tag.active = 1 in the tag join condition, and count Tag.tag_id
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.
- After joining
StudentClub to Club and filtering for active clubs, which aggregate produces the required count for each campus? Answer: COUNT(DISTINCT StudentClub.student_id), because a student may join several active clubs
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.
- Which design and calculation best supports an accurate department total?
Answer: Store allocation_pct in the bridge and sum each project budget multiplied by that relationship's allocation
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:
Department Total=∑projects(budget×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. - Which bridge-table rule most reliably enforces these requirements?
Answer: Store the smaller user ID first, use a unique pair constraint, and require the two IDs to differ
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.
- Which migration sequence best introduces the many-to-many relationship without losing current data?
Answer: Create EmployeeSkill, copy each non-null employee-skill pair into it, validate the bridge, and then remove Employee.skill_id
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.
