SQL Quiz: Table Relationships
10 questions · exam conditions
0:00
Table RelationshipsQuestion 1 of 10

A publishing database must allow a book to have several authors and an author to contribute to several books. It must also store a royalty percentage for each specific author-book pairing and reject duplicate pairings.

Which design most accurately represents these requirements?

Create BookAuthor with foreign keys to both entities, a royalty column, and uniqueness on the author-book pair.
Place one author foreign key and one royalty column in Book, allowing each book to identify its primary author.
Place one book foreign key and one royalty column in Author, allowing each author to identify a current book.
Store a list of author identifiers and royalty values in each Book row without creating another relation.
← Back to quizzes

SQL Quiz

SQL Quiz: Table Relationships

Practice Table Relationships 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 Table Relationships, 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 publishing database must allow a book to have several authors and an author to contribute to several books. It must also store a royalty percentage for each specific author-book pairing and reject duplicate pairings.

Which design most accurately represents these requirements?

  1. Create BookAuthor with foreign keys to both entities, a royalty column, and uniqueness on the author-book pair. (correct answer)
  2. Place one author foreign key and one royalty column in Book, allowing each book to identify its primary author.
  3. Place one book foreign key and one royalty column in Author, allowing each author to identify a current book.
  4. Store a list of author identifiers and royalty values in each Book row without creating another relation.
Explanation: Whenever a question describes entities that can relate to each other in a "many-to-many" fashion — one book having many authors, one author having many books — you should immediately think about junction tables (also called associative or bridge tables). This pattern is the standard SQL solution for many-to-many relationships, and it also provides a natural home for attributes that belong to the relationship itself rather than to either entity alone. Option A is correct because BookAuthor acts as exactly this kind of junction table. It holds foreign keys referencing both Book and Author, which models the many-to-many relationship correctly. The royalty column belongs here because royalty is a property of a specific pairing — not of the book alone or the author alone. Adding a unique constraint on the author-book pair enforces the business rule that duplicate pairings are rejected, satisfying every requirement in the passage. Option B fails because putting a single author foreign key in Book forces each book to have exactly one author, breaking the many-to-many requirement. It can't represent co-authored works at all. Option C has the same structural flaw in reverse — one book foreign key in Author limits each author to a single book at a time, which contradicts the requirement that authors contribute to many books. Option D describes storing a delimited list or array of author IDs inside a single column, which violates First Normal Form (1NF). This makes querying, indexing, and enforcing referential integrity extremely difficult — a classic antipattern in relational design. As a study tip: whenever you see "attribute that belongs to a relationship," that's your signal that a junction table with its own columns is needed — not a foreign key crammed into one of the parent tables.

Question 2

A college records registrations in Enrollment(student_id, course_id, term_id). All three columns form the primary key. Each column also participates in the appropriate foreign key relationship. A student may retake the same course in a later term.

Which conclusion about the modeled relationship is most accurate?

  1. Students and courses are one-to-many because a course identifier may appear in several enrollment rows.
  2. Students and courses are one-to-one within the database because each three-column primary key is unique.
  3. Courses and terms are one-to-one because adding term_id separates repeated student-course registrations.
  4. Students and courses are many-to-many, and term_id permits the same pairing to recur in different terms. (correct answer)
Explanation: When you see a question involving a junction table (sometimes called a bridge or associative table), your first instinct should be to identify what many-to-many relationship it's resolving. A many-to-many relationship exists when one entity on either side can relate to multiple entities on the other side — here, a single student can enroll in many courses, and a single course can have many students enrolled in it. The Enrollment table is the classic resolution to that many-to-many problem: it holds foreign keys to both Student and Course, meaning the relationship between those two entities is definitively many-to-many. The addition of term_id takes this a step further — it allows the same student-course pair to appear more than once across different terms, which is exactly what D describes. The three-column primary key enforces uniqueness per term, not globally. A is wrong because pointing out that a course identifier appears in multiple rows describes participation cardinality from only one side. That logic would equally apply to students, and neither perspective alone defines the relationship — which is many-to-many, not one-to-many. B incorrectly conflates the uniqueness of the composite primary key with the relationship type; a unique row per combination doesn't mean each student maps to exactly one course. C confuses the role of term_id — it doesn't create a one-to-one relationship between courses and terms; it simply adds a temporal dimension that allows repeated pairings. As a study tip, whenever you see a three-column composite key in a table referencing two other entities, ask yourself: "Is this resolving a many-to-many, and what does the extra column add?" That framing will guide you to the correct interpretation almost every time.

Question 3

A database contains Employee(workstation_id), where workstation_id is a nullable, nonunique foreign key to Workstation(workstation_id). In the current data, no two employees happen to reference the same workstation.

Which statement correctly distinguishes the current data from the relationship permitted by the schema?

  1. The schema enforces one-to-one because the existing rows show no workstation assigned more than once.
  2. The schema enforces many-to-many because both employees and workstations may currently be unassigned.
  3. The data currently resembles one-to-one, but the schema permits one workstation to relate to many employees. (correct answer)
  4. The data currently resembles one-to-many, but the schema permits each employee to reference many workstations.
Explanation: When working with foreign keys, you need to distinguish between what the schema enforces and what the current data happens to show — these are two different things, and this question tests exactly that distinction. The schema defines workstation_id as a nullable, nonunique foreign key in Employee. "Nonunique" means no unique constraint prevents multiple employees from referencing the same workstation. Therefore, the schema structurally permits a one-to-many relationship from Workstation to Employee — one workstation could legally appear in many employee rows. The current data, however, shows no workstation referenced more than once, making it look like one-to-one. Answer C captures this precisely: the data resembles one-to-one, but the schema permits one workstation to relate to many employees. Answer A is wrong because it confuses observed data with enforced rules. The schema does not enforce one-to-one — there is no unique constraint on workstation_id in Employee. Just because no duplicates exist right now doesn't mean the schema prevents them. Answer B is wrong because "many-to-many" requires a join table or bidirectional multi-reference structure. Nothing in this schema allows one employee to reference multiple workstations, so many-to-many is not applicable here. Answer D is wrong because it misidentifies which side allows "many." The employee column holds a single foreign key value — one employee references at most one workstation. It's the workstation side that could relate to many employees, not the other way around. A useful rule of thumb: constraints define what's possible; data only shows what's current. Always read foreign key columns for uniqueness constraints to determine the true cardinality the schema enforces.

Question 4

An employee hierarchy is represented by Employee(employee_id, manager_id). manager_id is nullable and is a foreign key to Employee.employee_id. The business permits a manager to supervise several employees, but each employee may report directly to no more than one manager.

How should this relationship be classified?

  1. As a many-to-many relationship between employees, because every participant belongs to the same table.
  2. As a one-to-one relationship, because each employee row contains only one manager identifier.
  3. As two unrelated entity sets, because a relationship cannot reference the table that contains it.
  4. As a recursive one-to-many relationship, optional on the subordinate employee's manager reference. (correct answer)
Explanation: When a table's foreign key points back to the primary key of that same table, you're looking at a recursive (or self-referencing) relationship — a common pattern for modeling hierarchies like org charts, category trees, or bill-of-materials structures. Your job is to classify the cardinality of that relationship just as you would for any two-table relationship. Here, one manager can supervise many employees, but each employee reports to at most one manager — the classic signature of a one-to-many relationship. Because both sides live in the same Employee table, it's recursive. The manager_id column is nullable, which means the "many" side (the subordinate) doesn't require a manager at all — making that side optional. That perfectly describes answer D: a recursive one-to-many relationship, optional on the subordinate's manager reference. A is wrong because the relationship is not many-to-many. A many-to-many would require a junction table to resolve it; here a single foreign key column is sufficient. The fact that both roles live in one table doesn't change the cardinality. B is wrong because confusing "one column" with "one-to-one" is a classic trap. One column storing a manager reference means each employee has at most one manager, which defines the many side of a one-to-many — not a one-to-one pairing. C is a fictional constraint. SQL fully supports self-referencing foreign keys; there is no rule prohibiting a table from referencing itself. Study tip: Whenever you spot a nullable foreign key pointing to the same table, immediately think "recursive one-to-many, optional participation" — that phrase covers the structure, cardinality, and optionality in one shot.

Question 5

A system has User(user_id) and UserPreferences(user_id). UserPreferences.user_id is both its primary key and a foreign key to User.user_id. Users are created before preferences, and some users never create preferences.

Which description best captures the relationship between User and UserPreferences?

  1. It is one-to-one, with preferences required to reference one user and users allowed zero or one preferences row. (correct answer)
  2. It is one-to-many, because one user can be referenced repeatedly through a foreign key in the preferences table.
  3. It is many-to-one, because several users may share the same primary-key value in the preferences table.
  4. It is required one-to-one on both sides, because a shared primary key requires every user to have preferences.
Explanation: When you see a question about table relationships, focus on two things: how many rows each side can have, and whether participation is required or optional. Here, UserPreferences.user_id is simultaneously the table's primary key and a foreign key to User. Because it's a primary key, no two preference rows can share the same user_id — so one user maps to at most one preferences row. Because it's a foreign key, every preferences row must point to a valid user. Meanwhile, the passage tells you some users never create preferences, meaning a user can exist with zero matching rows. That makes this a one-to-one relationship where preferences must reference a user, but users are not required to have preferences — exactly what answer A describes. Answer B is wrong because "one-to-many" would require the foreign key to appear in multiple rows, but the primary-key constraint prevents any duplicate user_id values in UserPreferences. Answer C inverts the logic entirely — many users cannot share one primary-key value; primary keys are unique by definition, so "many-to-one" from users to preferences makes no sense here. Answer D is the trickiest distractor: a shared primary key does not force every user to have a preferences row. It only constrains the preferences side (each row must match a user), not the user side (users can exist without preferences). A useful pattern to remember: when a table's primary key is also a foreign key, you're looking at a one-to-one relationship — the uniqueness comes from the primary key, and the directionality of optionality depends on whether the referencing side is mandatory or not.

Question 6

Initially, Invoice(customer_id NOT NULL) has a nonunique foreign key to Customer(customer_id). A proposed revision adds a unique constraint to Invoice.customer_id but makes no other changes.

How would the proposed revision change the relationship enforced between customers and invoices?

  1. It would remain one-to-many because a foreign key always allows the referenced customer to have several invoices.
  2. It would become one-to-one at most: every invoice has one customer, while each customer can have zero or one invoice. (correct answer)
  3. It would become required one-to-one: every customer and every invoice would be guaranteed a matching row.
  4. It would become many-to-many because uniqueness allows different customers to share the same invoice relationship.
Explanation: When reasoning about database relationships, focus on what constraints are placed on each side of the relationship — specifically, what a foreign key enforces versus what a unique constraint enforces. A foreign key on Invoice.customer_id referencing Customer(customer_id) means every invoice must point to a valid customer — that's the "every invoice has one customer" side. By itself, the foreign key places no restriction on how many invoices can reference the same customer, so multiple invoices can share a customer (one-to-many). When you add a unique constraint to Invoice.customer_id, you prevent any two invoices from having the same customer_id value. Combined with NOT NULL, every invoice must have exactly one customer, and no customer can appear on more than one invoice. That's a one-to-one at most relationship — each customer has zero or one invoice, and each invoice has exactly one customer. This makes B correct. A is wrong because it assumes a foreign key always produces one-to-many behavior. The unique constraint overrides that, collapsing the "many" invoices side down to at most one. C is tempting but incorrect. Nothing in this revision guarantees that every customer has an invoice. A customer row can exist with no matching invoice row — that would require an additional foreign key going the other direction, or a NOT NULL constraint on the customer side. D is a fabrication. Uniqueness has nothing to do with creating many-to-many relationships; those require a junction table and foreign keys on both sides. A handy rule: unique + NOT NULL on a foreign key column = one-to-one at most. Memorize that pattern — it appears frequently in schema design questions.

Question 7

An ordering system contains Order, Product, and OrderLine. Each OrderLine row references exactly one order and exactly one product and stores a quantity. The pair (order_id, product_id) is unique. An order may contain several products, and a product may appear in several orders.

Which statement best describes the relationships represented by OrderLine?

  1. Order and product are one-to-one because each unique order-product pair can occur only once.
  2. Order and product are one-to-many because every order line references only one product row.
  3. Order and product are many-to-many overall, resolved through two many-to-one references from OrderLine. (correct answer)
  4. Order and product are unrelated because only order lines, rather than orders, directly reference products.
Explanation: When you see a question about table relationships, ask yourself: how many rows on each side can connect to how many rows on the other side? That thinking reveals the underlying cardinality. Here, a single order can contain many products, and a single product can appear in many orders. That's the hallmark of a many-to-many relationship — and in relational databases, you can't store many-to-many directly in two tables. Instead, you introduce a junction table (also called a bridge or associative table), which is exactly what OrderLine is. Each OrderLine row points to one order via a foreign key (many OrderLine rows → one Order) and one product via another foreign key (many OrderLine rows → one Product). Those are two separate many-to-one references, and together they resolve the many-to-many relationship between orders and products. That's precisely what C describes. A is wrong because the uniqueness of the (order_id, product_id) pair is a constraint preventing duplicates — it doesn't mean orders and products have a one-to-one relationship. One order still links to many products overall. B is wrong because "one-to-many" would mean one order links to many products but a product appears in at most one order. That's not the case here; products recur across many orders. D is wrong because saying orders and products are "unrelated" ignores that OrderLine exists specifically to connect them. Indirect relationships through a junction table are still meaningful, real relationships. Your study tip: whenever you see a junction table with two foreign keys, immediately recognize it as the SQL pattern for resolving a many-to-many relationship into two many-to-one joins.

Question 8

An organization stores Employee(badge_id NOT NULL), where badge_id is a foreign key referencing Badge(badge_id). Every employee must have one badge, and a badge must not be assigned to more than one employee. The badge_id column in Employee is not currently unique.

What change is needed to make the implemented relationship consistent with the intended one-to-one rule?

  1. Make badge_id nullable so an unassigned badge cannot be referenced by more than one employee.
  2. Remove the foreign key because referential integrity converts the relationship into many-to-one.
  3. Add a unique constraint to Employee.badge_id while retaining its foreign key and NOT NULL constraint. (correct answer)
  4. Add a second foreign key from Badge to Employee without making either foreign key unique.
Explanation: When you see a question about enforcing a one-to-one relationship in SQL, think about the three properties required: referential integrity (foreign key), mandatory participation (NOT NULL), and uniqueness (UNIQUE constraint). A foreign key alone only enforces many-to-one — it prevents orphaned references but allows multiple rows to share the same referenced value. In this scenario, Employee.badge_id already has a foreign key (ensuring every badge_id exists in the Badge table) and a NOT NULL constraint (ensuring every employee has a badge). What's missing is the guarantee that no two employees share the same badge. Without a UNIQUE constraint, ten employees could all reference badge #42, making it many-to-one rather than one-to-one. Adding UNIQUE to Employee.badge_id — while keeping the foreign key and NOT NULL — is exactly what closes that gap, making C the correct answer. A is wrong because making badge_id nullable would actually weaken the relationship, allowing employees to exist without any badge — the opposite of what "every employee must have one badge" requires. B is wrong on two counts: removing the foreign key destroys referential integrity entirely, and foreign keys enforce referential integrity, not cardinality. A foreign key does not automatically make a relationship many-to-one; it simply links values between tables. D is wrong because adding a reverse foreign key from Badge to Employee without a UNIQUE constraint on either side still doesn't prevent multiple employees from referencing the same badge. A useful pattern to memorize: one-to-one = foreign key + NOT NULL + UNIQUE. If any of the three is missing, the relationship isn't truly one-to-one.

Question 9

A billing system applies these rules: Every account belongs to exactly one customer. A customer may exist before opening an account and may later open several accounts. An account cannot be shared by customers.

Which description best characterizes the relationship between Customer and Account?

  1. It is one-to-one because an individual account cannot be shared by multiple customers.
  2. It is one-to-many from customer to account, with account participation required and customer participation optional. (correct answer)
  3. It is many-to-many because customers can open several accounts and accounts contain multiple transactions.
  4. It is many-to-one from customer to account, with every customer required to have exactly one account.
Explanation: When analyzing entity relationships in SQL database design, always ask two separate questions: (1) how many of entity B can relate to one entity A, and (2) is each side's participation mandatory or optional? Here, a customer can own multiple accounts, but each account belongs to exactly one customer — that's a one-to-many relationship running from Customer to Account. Now check participation: the passage states a customer "may exist before opening an account," meaning a customer can exist with zero accounts — optional participation on the customer side. Meanwhile, "every account belongs to exactly one customer" means every account must have a customer — required participation on the account side. That perfectly matches answer B. Answer A misreads the cardinality. It focuses on the fact that an account can't be shared and concludes one-to-one, but that constraint simply prevents accounts from having multiple customers — it says nothing about how many accounts one customer can own. One-to-one would require each customer to own at most one account, which contradicts the passage. Answer C introduces transactions as a red herring. Accounts containing multiple transactions is a separate relationship entirely — mixing it in here confuses two different entity pairs. Nothing in the passage supports many-to-many between Customer and Account. Answer D flips the cardinality label and misrepresents participation. "Many-to-one from customer to account" is technically equivalent phrasing to one-to-many, but claiming every customer is required to have exactly one account directly contradicts the passage's statement that customers may exist without accounts. Study tip: Always identify cardinality and participation separately. Draw a quick mental diagram — ask "how many?" and "is it required?" for each side independently.

Question 10

A company stores Department(manager_employee_id). The column is nullable, unique, and a foreign key to Employee(employee_id). No other rule requires an employee to manage a department or a department to currently have a manager.

What relationship does this design enforce between departments and employees in the manager role?

  1. A required one-to-one relationship because uniqueness requires every department and employee to participate.
  2. An optional one-to-one relationship because each side can participate at most once but may participate zero times. (correct answer)
  3. A one-to-many relationship because one employee identifier may be referenced by several department rows.
  4. A many-to-many relationship because both departments and employees may exist without a manager assignment.
Explanation: When analyzing a foreign key column, you need to examine three properties together: nullability, uniqueness, and which table holds the column. These three properties determine the cardinality and optionality of the relationship. Here, Department.manager_employee_id is nullable, unique, and a foreign key to Employee. Nullable means a department can exist without a manager — participation is optional on the department side. Unique means no two department rows can reference the same employee — so one employee can manage at most one department, making participation optional on the employee side as well. Each side may participate zero or one time, which is the definition of an optional one-to-one relationship, confirming B is correct. A is wrong because it claims the relationship is required. Nullability directly contradicts this — a department row with NULL in manager_employee_id participates in no relationship at all. Uniqueness enforces a ceiling of one, not a floor of one. C is wrong because it describes a one-to-many relationship, which would occur if the column were not unique — allowing multiple department rows to reference the same employee. The UNIQUE constraint explicitly prevents that here. D is wrong because many-to-many requires a junction table and means each side can reference multiple rows on the other side. Nothing here allows one employee to manage many departments or one department to have many managers simultaneously. A useful rule of thumb: nullable + unique foreign key = optional one-to-one. Spot those two keywords together and you can immediately rule out required, one-to-many, and many-to-many options.