- Which key interpretation correctly models the reporting relationship?
Answer: EmployeeID is the primary key, and nullable ManagerID is a foreign key referencing Employee.EmployeeID in the same relation.
When a table references itself, you're dealing with a self-referencing foreign key — a powerful pattern for modeling hierarchies like org charts, category trees, or bill-of-materials structures. The key question to ask is: what uniquely identifies each row, and what column points back into the same table?
In Employee, each employee has a unique EmployeeID, making it the natural primary key. ManagerID holds the EmployeeID of another employee — or NULL for top-level executives who have no manager. Because ManagerID points to a value in the same table's primary key column, it qualifies as a **foreign key referencing Employee.EmployeeID** within the same relation. Allowing NULL is essential here, since not every employee has a manager. This is exactly what A describes, making it correct.
B has the logic inverted — managers are themselves employees, so ManagerID is not a primary key; it's a subordinate attribute that references the primary key. Making EmployeeID a foreign key in B makes no structural sense.
C incorrectly promotes the composite (EmployeeID, ManagerID) to a primary key. A primary key must be minimal and unique per row. EmployeeID alone already uniquely identifies each employee, so adding ManagerID is redundant — and NULL values in a primary key are forbidden anyway.
D confuses "contains employee identifiers" with "is a primary key." Having employee ID values doesn't grant primary key status; uniqueness and row identification do.
Study tip: Whenever you see a hierarchy modeled in a single table, immediately look for a self-referencing foreign key — and remember that NULL in that foreign key simply means "no parent exists."
- Which classification best fits
Employee.DepartmentID? Answer: It is a nullable foreign key; null represents no department, while each non-null value must match a department key.
When you see a question about foreign keys, ask yourself two things: does this column reference another table's primary key, and must it always have a value? These are independent questions — a foreign key can absolutely be nullable.
Employee.DepartmentID references Department.DepartmentID, making it a foreign key by definition. The passage also tells you an employee can be unassigned, meaning the column has no required value — it's nullable. When a value is present, referential integrity requires it to match an existing department. That's exactly what A describes, making it the correct answer.
B is wrong because it states a false rule. SQL standards and every major database system allow nullable foreign keys. A NULL in a foreign key column simply means "no reference exists," which doesn't violate referential integrity — it's not pointing to a nonexistent row, it's pointing nowhere.
C confuses "foreign key" with "primary key." A primary key uniquely identifies rows within its own table. Employee.DepartmentID doesn't identify individual employees — multiple employees can share the same department, or have NULL. It identifies an associated department, which is the definition of a foreign key.
D is doubly wrong. The employee's primary key is EmployeeID, not DepartmentID. A single column can serve dual roles in some schemas, but here DepartmentID clearly isn't what makes each employee row unique.
Study tip: Remember that NULL means "unknown or inapplicable," not "invalid." A nullable foreign key is a common, legitimate design pattern for optional relationships — don't let answer choices like B trick you into thinking nullability disqualifies a column from being a foreign key.
- Which key assignment directly enforces the required relationship?
Answer: Make UserPreferences.UserID its primary key and also a foreign key referencing UserAccount.UserID.
When modeling a one-to-zero-or-one relationship in SQL, ask yourself two questions: "What prevents duplicate child rows?" and "What prevents orphaned child rows?" You need constraints that answer both simultaneously.
The relationship here says each UserPreferences row must belong to exactly one UserAccount, and no user can have more than one preferences row. Making UserPreferences.UserID the primary key handles the first requirement — primary keys enforce uniqueness, so the same UserID can never appear twice in the preferences table, capping each user at one preferences row. Making that same UserID column a **foreign key referencing UserAccount.UserID** handles the second requirement — it guarantees no preferences row can exist without a matching user account. Answer D combines both constraints on the same column, cleanly enforcing the entire relationship with nothing extra needed.
Answer A is wrong because a composite primary key (UserID, Theme) allows multiple preferences rows per user (different themes would create distinct composite keys), breaking the "at most one" rule entirely. Making only Theme a foreign key to the user table is also structurally nonsensical.
Answer B is wrong because using Theme as the primary key means preferences rows are uniquely identified by theme, not by user — multiple users could share a theme, and a user could appear multiple times with different themes.
Answer C reverses the relationship entirely. A UserAccount foreign key pointing to UserPreferences would mean a user account can't exist without preferences, which is the opposite of what's required.
Remember: in a one-to-zero-or-one relationship, the child table's primary key is the foreign key — same column, both roles.
- How should the address-related columns in
SalesOrder be classified? Answer: Each is a separate foreign key to Address.AddressID, because the columns represent two roles in the same parent relation.
When a column in one table stores a value that must match a primary key in another table, it's a foreign key. The key insight here is that a single parent table can be referenced multiple times by different columns in a child table — each reference is its own independent foreign key, playing a distinct role.
In SalesOrder, both BillingAddressID and ShippingAddressID reference Address.AddressID. They are separate foreign keys that happen to point to the same parent table, but they serve different semantic roles: one identifies where the bill goes, the other where the package ships. This makes B correct — each column is an independent foreign key to Address.AddressID.
A is wrong because a composite foreign key means multiple columns together form a single reference to a multi-column primary key — like (City, ZipCode) jointly referencing a composite PK. Here, Address.AddressID is a single column, so there's no composite key situation. The two address columns don't combine into one reference; they are two separate references.
C is incorrect because there's no rule requiring billing information to be part of a primary key. BillingAddressID is simply another attribute that references an address row — it has no special obligation to be a PK component.
D confuses the concept entirely. A foreign key points to a row in another table; it doesn't make the referencing column a candidate key of the current table. An order isn't uniquely identified by an address ID.
Study tip: Whenever you see two columns in the same table referencing the same parent table, recognize them as separate foreign keys with different roles — not a composite key.
- Which key design best satisfies the stated requirements?
Answer: Use CountryID as the country primary key and as the referenced value for the airport foreign key; constrain ISOCode separately as needed.
When designing relational schemas, the key question is: which identifier remains stable over time and can safely anchor foreign key relationships? A primary key should be permanent and immutable, while other unique attributes can be constrained separately without carrying the full burden of referential integrity.
CountryID fits this role perfectly. The passage explicitly states it is permanently assigned and never changes — exactly what you want from a primary key. Airport records reference CountryID as a foreign key, so even if ISOCode gets corrected or reassigned in the future, the airport-country relationship remains intact. ISOCode can still be declared UNIQUE or given its own constraint for lookup purposes without being the referenced key. This is option C, and it correctly separates the concerns of identification from human-readable labeling.
Option A is tempting but introduces unnecessary complexity. A composite primary key of (CountryID, ISOCode) means every airport row must store both columns as a foreign key — and if ISOCode changes, you face cascading updates across all airport records, which defeats the purpose of having a stable identifier.
Option B is a classic trap: using a recognizable, human-friendly code as a primary key feels intuitive, but the passage explicitly warns that ISOCode can change. Basing referential integrity on a mutable value is a schema design anti-pattern that leads to update anomalies.
Option D confuses direction — a foreign key points from a child table to a parent, not the other way around. AirportID identifying a country makes no logical sense.
Study tip: When a question mentions that one attribute is "permanent" and another "may change," that's a direct signal — use the permanent attribute as your primary key and foreign key reference.
- Which conclusion about the relation's primary key is justified by this information alone?
Answer: No primary key can be identified with certainty; observed data patterns in a sample do not establish a declared or durable constraint on future values.
When working with SQL and relational database theory, it's critical to distinguish between observed data patterns and declared schema constraints. A primary key isn't something you infer from a snapshot of data — it's a formal declaration that guarantees uniqueness and non-nullability for every row, past, present, and future.
That distinction makes D the only defensible answer. Even if a column looks unique and non-null right now, nothing prevents a future INSERT from violating that pattern unless a constraint enforces it. Without a declared primary key or uniqueness constraint, you simply cannot conclude that any column or combination of columns is the primary key.
A is tempting but wrong. Observing that A currently has no duplicates or nulls is a fact about today's data, not a guarantee about tomorrow's. A column can satisfy primary key criteria in a snapshot without being declared as one. Calling it "necessarily the primary key" overstates what the data alone can tell you.
B is doubly flawed. C already contains a null, which immediately disqualifies it from being a primary key under SQL rules (primary keys must be entirely non-null). Assuming the null "will be corrected later" introduces speculation about future schema decisions — which is exactly the kind of assumption this question warns against.
C is also unjustified. Combining columns doesn't automatically create a reliable composite key. Without testing all combinations and, crucially, without a declared constraint, you cannot guarantee (B, C) will remain unique as data changes.
Study tip: On SQL exam questions about keys, always ask: Is this a declared constraint or just an observation? Data can lie — constraints enforce truth.
- Which statement correctly classifies
LoginAudit.SubmittedUsername if the relationship is enforced against Account.Username? Answer: It can be a foreign key because a foreign key may reference a declared unique candidate key rather than only a primary key.
When a question asks whether a column can serve as a foreign key, your first instinct should be to ask: what can a foreign key actually reference? Many students memorize that foreign keys point to primary keys — but that's only part of the rule.
In standard SQL, a foreign key may reference any column (or set of columns) that has a UNIQUE constraint, not just the primary key. A primary key is simply a special case of a candidate key — one that is both unique and not null. Account.Username carries both UNIQUE and NOT NULL, which makes it a valid candidate key. Therefore, LoginAudit.SubmittedUsername can legally declare a foreign key constraint referencing Account.Username. Answer A captures this precisely: foreign keys may reference any declared unique candidate key.
B is wrong because referencing a unique column in a parent table doesn't force SubmittedUsername to become LoginAudit's primary key. The two concepts are independent — a foreign key column is just a referencing column, not necessarily an identifier of its own table.
C reflects the common misconception that only the primary key can be referenced. This is incorrect per SQL standards; any uniquely constrained column is fair game.
D is wrong on two counts: column names don't need to match between referencing and referenced tables, and a properly defined foreign key constraint is far more than a "copied attribute" — it's an enforced referential integrity rule.
Study tip: Remember that PRIMARY KEY = NOT NULL + UNIQUE, so any UNIQUE NOT NULL column qualifies as a candidate key and is a valid foreign-key target.
- Which statement correctly identifies the keys of
OrderLine? Answer: (OrderID, LineNumber) is the primary key; OrderID and ProductID are foreign keys to their respective parent relations.
When working with keys in a relational database, you need to ask two questions: what combination of columns uniquely identifies each row, and which columns reference primary keys in other tables?
For OrderLine, think carefully about what makes a row unique. The passage tells you line numbers restart at 1 for each new order — so LineNumber alone can't uniquely identify a row (multiple orders each have a Line 1). However, combining OrderID with LineNumber gives you a unique identifier: within a single order, line numbers are distinct. That composite pair (OrderID, LineNumber) is the primary key. Additionally, OrderID references SalesOrder(OrderID), making it a foreign key, and ProductID references Product(ProductID), making it a foreign key as well. This confirms C as correct.
A is wrong because ProductID alone cannot be a primary key — many order lines can reference the same product. Designating LineNumber as "never part of any key" also ignores its essential role in the composite primary key.
B fails because LineNumber alone is not unique across orders. The claim that (OrderID, ProductID) forms a composite foreign key is also a category error — those are two separate foreign keys pointing to two different parent tables, not one combined key.
D incorrectly proposes (OrderID, ProductID) as the primary key. A customer could order the same product on multiple lines, so this pair isn't necessarily unique. Claiming LineNumber is a foreign key is nonsensical — there's no parent table it could reference.
A good study habit: when you see a junction or detail table, always check whether uniqueness requires a composite primary key before assuming any single column qualifies.
- Which primary-key and foreign-key interpretation is correct for
Registration? Answer: Its primary key is (StudentID, CourseID, Term, SectionNumber), with separate references to Student and the composite offering key.
When designing a relational schema, you need to ask two questions for every table: "What combination of columns uniquely identifies each row?" and "Which columns reference other tables?" For Registration, think carefully about what a single row represents — one student enrolled in one specific course offering.
A course offering is identified by the composite key (CourseID, Term, SectionNumber) — all three columns together. A student can retake the same course in a different term, meaning (StudentID, CourseID) alone cannot distinguish those two enrollments. The only combination that uniquely identifies a registration is (StudentID, CourseID, Term, SectionNumber). This four-column primary key also naturally decomposes into two foreign key references: StudentID references Student, and (CourseID, Term, SectionNumber) references CourseOffering. That makes B the correct interpretation.
A is wrong because dropping Term and SectionNumber from the primary key means you can't distinguish a student taking the same course in two different terms — those rows would appear identical, violating uniqueness. C incorrectly uses only the offering key as the primary key, which would prevent more than one student from registering for the same offering — clearly wrong for a real enrollment system. D reduces the primary key to just StudentID, implying each student can only ever have one registration total, which contradicts the entire purpose of the table.
A useful rule of thumb: in junction/association tables (tables that connect two or more entities), the primary key is almost always the combination of all the foreign keys it references. When one of those references is itself composite, every column in that composite must be included.
- Which key assignment correctly represents these identification rules?
Answer: Customer has primary key (TenantID, CustomerNumber); SalesOrder has primary key OrderID; and its customer columns form a composite foreign key.
When designing keys in a multitenant database, ask yourself two questions: "What combination of attributes makes each row unique?" and "Do my foreign keys reference the entire primary key of the parent table?"
In this schema, CustomerNumber alone doesn't uniquely identify a customer — two different tenants can both have customer 1001. Uniqueness only emerges when you pair TenantID with CustomerNumber. So Customer's primary key must be the composite (TenantID, CustomerNumber). Since SalesOrder already has a globally unique OrderID, that single column is sufficient as its primary key. Finally, because SalesOrder references a Customer, its foreign key must mirror the full composite primary key — meaning both TenantID and CustomerNumber together form the foreign key. That's exactly what Answer A describes, making it correct.
B fails because it uses CustomerNumber alone as Customer's primary key. This would incorrectly allow only one customer numbered 1001 across all tenants — violating the scenario's core premise.
C makes TenantID the primary key of Customer, which is wrong on two counts: a tenant has many customers, so TenantID alone isn't unique per row, and it also makes SalesOrder's primary key a composite (OrderID, TenantID) when OrderID is already globally unique — unnecessarily inflating the key.
D makes (TenantID, CustomerNumber) the primary key of SalesOrder, which conflates the customer identifier with the order identifier. Multiple orders per customer would be impossible, and OrderID demoted to a foreign key makes no logical sense.
Study tip: Whenever you see "independently assigned within a scope," that scope column must be part of the primary key — and any referencing table's foreign key must include it too.
