- Which description best captures the relationship between
User and UserPreferences? Answer: It is one-to-one, with preferences required to reference one user and users allowed zero or one preferences row.
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.
- How would the proposed revision change the relationship enforced between customers and invoices?
Answer: It would become one-to-one at most: every invoice has one customer, while each customer can have zero or one invoice.
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.
- Which conclusion about the modeled relationship is most accurate?
Answer: Students and courses are many-to-many, and term_id permits the same pairing to recur in different terms.
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.
- Which statement best describes the relationships represented by
OrderLine? Answer: Order and product are many-to-many overall, resolved through two many-to-one references from OrderLine.
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.
- What relationship does this design enforce between departments and employees in the manager role?
Answer: An optional one-to-one relationship because each side can participate at most once but may participate zero times.
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.
- Which design most accurately represents these requirements?
Answer: Create BookAuthor with foreign keys to both entities, a royalty column, and uniqueness on the author-book pair.
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.
- Which description best characterizes the relationship between
Customer and Account? Answer: It is one-to-many from customer to account, with account participation required and customer participation optional.
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.
- What change is needed to make the implemented relationship consistent with the intended one-to-one rule?
Answer: Add a unique constraint to Employee.badge_id while retaining its foreign key and NOT NULL constraint.
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.
- Which statement correctly distinguishes the current data from the relationship permitted by the schema?
Answer: The data currently resembles one-to-one, but the schema permits one workstation to relate to many employees.
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.
- How should this relationship be classified?
Answer: As a recursive one-to-many relationship, optional on the subordinate employee's manager reference.
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.
