Question 1

Database design principle check:
“The DBMS allows surrogate keys to be changed.”
Decide whether this statement is accurate, considering how surrogate keys are defined and used in relational systems.

Accepted Answer

Introduction / Context: Surrogate keys are system-generated identifiers (for example, integer sequences or UUIDs) used as primary keys. They have no business meaning and exist solely to uniquely identify rows. This question probes whether surrogate keys are expected to be mutable in a well-designed relational database. Given Data / Assumptions: “Surrogate key” means an artificial, non-business identifier such as an auto-increment integer or GUID. Surrogate keys typically serve as primary keys and are referenced by foreign keys in other tables. Standard relational integrity rules apply (primary key uniqueness; foreign key referential integrity). Concept / Approach: By design, surrogate keys are intended to be stable and never change once assigned. Their immutability ensures that child rows referencing the key remain valid and that replication, caching, audit logs, and foreign key relationships stay consistent. While some DBMS products technically allow updating a primary key column (for example, with cascading updates), doing so contradicts the fundamental rationale for using a surrogate key: stable identity decoupled from business meaning. If you foresee updates, you likely need a natural key (business key) or a separate alternate key for evolving attributes. Step-by-Step Solution: Define surrogate key: an identity value with no external semantics.Map usage: make it the primary key and reference it from foreign keys.Assess change: changing a surrogate key forces updates across all child references, increasing risk and cost.Conclude: the sound practice is to never change surrogate keys; treat them as immutable identifiers. Verification / Alternative check: Review vendor docs: although ON UPDATE CASCADE may exist, design guides and data modeling standards recommend immutable surrogate keys to maintain referential stability and simplify ETL and auditing. Why Other Options Are Wrong: “Correct” ignores the principle of identity stability. “Only if cascading updates are enabled” is a technical possibility but a poor modeling choice; the question tests best practice, not mere capability. “Only in NoSQL systems” and “Depends on page size” are irrelevant to relational key semantics. Common Pitfalls: Using a meaningful attribute as a surrogate key; updating primary keys after deployment; confusing surrogate, natural, and alternate keys. Final Answer: Incorrect

Question 2

Referential integrity behavior:
“For every relationship, there are six possible referential integrity actions.”
Judge the accuracy of this claim about ON UPDATE/ON DELETE behaviors.

Accepted Answer

Introduction / Context: Referential integrity constraints define what happens to child rows when a referenced parent key changes or is deleted. The SQL standard names several actions, and vendors commonly support a similar set. This question asks whether “six possible actions” is an accurate, universal count. Given Data / Assumptions: Standard actions include: RESTRICT/NO ACTION, CASCADE, SET NULL, SET DEFAULT. Actions can be specified separately for ON DELETE and ON UPDATE. Vendor naming (RESTRICT vs NO ACTION) may differ slightly in timing semantics, but the action set is the same conceptually. Concept / Approach: The core action types are four: RESTRICT (or NO ACTION), CASCADE, SET NULL, and SET DEFAULT. Because each foreign key has two events—DELETE and UPDATE—these actions can be applied in combinations (for example, ON DELETE CASCADE and ON UPDATE RESTRICT), but that does not create “new action types.” Counting both events does not change the fact that there are four fundamental actions, not six. Vendors may add nuances (for example, MATCH FULL/PARTIAL for composite keys), but the canonical action set remains four. Step-by-Step Solution: List actions: RESTRICT/NO ACTION, CASCADE, SET NULL, SET DEFAULT → four.Note that actions are specified per event (DELETE vs UPDATE).Recognize that combining events does not increase the base number of actions.Therefore, “six possible actions” is inaccurate. Verification / Alternative check: Check DBMS manuals (PostgreSQL, SQL Server, Oracle, MySQL/InnoDB): all enumerate these four actions; some omit SET DEFAULT or treat RESTRICT and NO ACTION with slightly different enforcement timing. Why Other Options Are Wrong: Calling the statement correct miscounts action types. SQL-92 does not expand this to six; nor does index type or deferrability create new action categories. Common Pitfalls: Confusing RESTRICT vs NO ACTION timing; assuming vendors with SET DEFAULT missing have a different “count.” Final Answer: Incorrect

Question 3

Parent side in a 1:N relationship:
“In one-to-many (1:N) relationships, which entity becomes the parent is arbitrary.”
Choose the most accurate evaluation.

Accepted Answer

Introduction / Context: Understanding which side is “parent” in a 1:N relationship is foundational to data modeling and foreign key placement. The parent/child direction determines how keys and constraints are implemented and how data is inserted and deleted safely. Given Data / Assumptions: A 1:N relationship means one parent row can relate to many child rows; each child relates to exactly one parent. Foreign keys are placed on the child table, referencing the parent’s primary (or candidate) key. Standard relational constraints and common DBMS behavior are assumed. Concept / Approach: Parent designation is not arbitrary: it is the “one” side—the entity that owns the key and whose primary key is referenced. The child is the “many” side, holding the foreign key. This direction flows from the business semantics: for example, one Customer has many Orders, therefore Customer is parent and Order is child. Choosing the child to be parent would invert foreign key placement and contradict the “one-to-many” semantics. Performance and indexing are design considerations but do not redefine parenthood. Step-by-Step Solution: Identify the one-side entity (unique identifier used by others).Place the primary key there; expose it to children via foreign keys.Ensure the child table carries the foreign key referencing the parent key.Conclude that parent designation follows semantics, not arbitrary choice. Verification / Alternative check: Inspect sample schemas (Customer–Order, Department–Employee). In each, the foreign key sits on the “many” side, confirming non-arbitrary direction. Why Other Options Are Wrong: Calling it “correct” ignores the semantic definition of 1:N. Nullable foreign keys, index selectivity, or the use of surrogate keys do not alter the parent/child roles. Common Pitfalls: Designing with bidirectional dependencies; duplicating keys on both sides; misplacing the foreign key on the “one” side. Final Answer: Incorrect

Question 4

Implementing relationships:
“A foreign key is used to implement relationships between tables.”
Evaluate the statement’s correctness in standard relational modeling.

Accepted Answer

Introduction / Context: Relations (tables) in a database model real-world associations: Orders belong to Customers, LineItems belong to Orders, and so on. The mechanism that enforces these associations is the foreign key constraint. This item checks your understanding of how relational links are realized technically. Given Data / Assumptions: A “foreign key” is one or more columns in a child table referencing a candidate key (usually the primary key) of a parent table. DBMS enforces referential integrity, preventing orphaned child rows and ensuring key consistency. Both natural and surrogate parent keys may be referenced. Concept / Approach: A foreign key defines and enforces the relationship between two tables. It constrains allowed values in the child table’s referencing column(s) to those that exist in the parent table’s referenced key. This enforces “existence dependency” and allows the DBMS to support ON DELETE/ON UPDATE actions like RESTRICT, CASCADE, SET NULL, or SET DEFAULT. The choice between natural and surrogate keys is orthogonal: the foreign key mechanism works for either. Step-by-Step Solution: Identify the parent’s candidate key (often PRIMARY KEY).Create a child column(s) with matching data type and semantics.Declare FOREIGN KEY (child_col) REFERENCES Parent(parent_key) with desired ON DELETE/UPDATE rules.Insert data respecting the constraint; the DBMS ensures relational integrity automatically. Verification / Alternative check: Attempting to insert a child referencing a non-existent parent key will fail; deleting a parent with existing children will follow configured action (RESTRICT, CASCADE, etc.). Why Other Options Are Wrong: “Incorrect” contradicts standard practice. “Only true for natural/composite keys” is false; foreign keys operate regardless of key type or cardinality. Isolation level governs concurrency, not referential structure. Common Pitfalls: Not indexing foreign key columns; mismatched data types or collations between parent and child; trying to enforce relationships purely in application code. Final Answer: Correct

Question 5

Primary key nullability and necessity:
“All primary keys are required.”
Decide if this statement accurately reflects relational rules for primary keys.

Accepted Answer

Introduction / Context: The primary key uniquely identifies each row of a table. To perform that role, the key must be present for every row and must be unique. This question checks whether you know that primary key attributes cannot be optional or NULL in a properly defined table. Given Data / Assumptions: A primary key may consist of one or more columns (composite key). PRIMARY KEY implies NOT NULL + UNIQUE across the key columns. DBMS enforces these constraints when the primary key is declared. Concept / Approach: Because the primary key is the row’s identity, it must always be present. Therefore, DBMSs disallow NULL in any primary key column and require that the combined values be unique. “Required” in modeling terms means the attribute cannot be missing; in SQL this is captured via NOT NULL and the PRIMARY KEY constraint. If you need an optional descriptive attribute, do not put it in the primary key; use a surrogate key for identity and alternate keys for business uniqueness as needed. Step-by-Step Solution: Declare PRIMARY KEY; the DBMS automatically prohibits NULLs in the key columns.Attempt to insert a row with a NULL key value; the engine rejects it.Attempt to insert a duplicate key; the engine rejects it due to uniqueness.Thus, primary keys are required and non-null by definition. Verification / Alternative check: Review vendor docs (PostgreSQL, MySQL, SQL Server, Oracle): all enforce NOT NULL and uniqueness for PRIMARY KEY columns. Why Other Options Are Wrong: Limiting correctness to “normalized schemas” or “surrogate keys” is unnecessary; the rule is universal. Collation/locale affects ordering and comparisons, not primary key necessity. Common Pitfalls: Using a nullable natural key as the primary key; letting business keys change and break foreign keys; omitting a surrogate when natural keys are volatile. Final Answer: Correct

Question 6

Optionality and insertion order:
“When the parent entity is required, a new parent row can always be inserted.”
Assess whether this statement aligns with relational integrity and typical DBMS behavior.

Accepted Answer

Introduction / Context: In modeling, “parent required” usually means a child cannot exist without a valid parent (the relationship is mandatory on the child side). This item checks whether such a rule affects the ability to create a new parent row by itself. Given Data / Assumptions: Parent–child relationship enforced by a foreign key in the child referencing the parent’s key. “Parent required” refers to the child’s mandatory dependency, not a requirement that a parent must have at least one child at creation time. Standard DBMS enforcement: foreign key checks on child inserts/updates; parents can exist independently unless additional constraints are modeled. Concept / Approach: Foreign key constraints prevent children from referencing non-existent parents, but they do not prevent creating parent rows without children. In most DBMSs, minimum cardinality rules on the parent side (for example, “every parent must have at least one child”) are not enforced natively. Enforcing such a rule would typically require triggers, assertions, or application logic. Therefore, even when the relationship is mandatory from the child’s perspective, you can always insert a parent row—there is no referential integrity violation because no child exists yet. Step-by-Step Solution: Create parent P with PRIMARY KEY (id).Create child C with FOREIGN KEY (p_id) REFERENCES P(id) NOT NULL.Insert into P(id) values (1) → success (no child required at this time).Insert into C(p_id) values (99) → fails because parent 99 does not exist. Verification / Alternative check: Test in a relational engine: parent inserts succeed with or without children; child inserts require an existing parent. Why Other Options Are Wrong: “Only after a child row exists” inverts the dependency and is not enforced by foreign keys. Deferrable constraints affect timing of checks, not the basic ability to insert a stand-alone parent. Index structure has no bearing on this rule. Common Pitfalls: Misreading “parent required” as “parent must have at least one child immediately”; assuming DBMS natively enforces minimum cardinality on the parent side. Final Answer: Correct

Question 7

Managing parent changes when children depend on the parent:
“When the parent entity is required, cascading updates and cascading deletions should be allowed or parent actions should be prohibited.”
Choose the best assessment.

Accepted Answer

Introduction / Context: When a child is existence-dependent on a parent, we must decide what happens if the parent’s key changes or the parent row is deleted. The two safe strategies are to cascade the change to keep children valid or to prohibit the parent action to protect referential integrity. Given Data / Assumptions: The child has a NOT NULL foreign key to the parent (mandatory on the child side). Referential integrity options include ON UPDATE/DELETE CASCADE, RESTRICT/NO ACTION, SET NULL, and SET DEFAULT. Business rules do not permit orphaned child rows. Concept / Approach: If the child requires a parent, then after any parent key update or delete, children must remain valid. Two broad patterns satisfy this: (1) CASCADE the update/delete so children follow the parent’s change, or (2) RESTRICT/NO ACTION to prevent the parent change if children exist. SET NULL or SET DEFAULT would violate the child’s mandatory dependency unless the foreign key were nullable or a default valid parent existed—conditions that contradict “parent required.” Hence, “cascade or prohibit” is the correct design guideline. Step-by-Step Solution: Model foreign key as NOT NULL (mandatory child).For updates/deletes on the parent, choose ON UPDATE/DELETE CASCADE or RESTRICT/NO ACTION.Avoid SET NULL when NOT NULL FK is required; avoid SET DEFAULT unless the default references a valid parent that satisfies business rules.Result: integrity is maintained without orphans. Verification / Alternative check: Attempt parent delete with children present under RESTRICT → blocked; under CASCADE → children removed automatically. Either preserves integrity according to policy. Why Other Options Are Wrong: Limiting to updates ignores the delete case; both require handling. Foreign key nullability and isolation level do not dictate the design choice; the dependency does. Common Pitfalls: Allowing SET NULL with a NOT NULL child foreign key; silently creating orphans by manual deletes without constraints; misunderstanding NO ACTION vs RESTRICT timing differences. Final Answer: Correct

Question 8

Modeling many-to-many (M:N) relationships:
“An intersection (associative) table is required to represent M:N relationships.”
Determine whether this is accurate for relational schemas.

Accepted Answer

Introduction / Context: Real-world data often involves many-to-many associations: students take many courses; courses have many students. Relational tables cannot directly store an M:N link between two base tables without additional structure. The standard solution is an intersection (associative) table. Given Data / Assumptions: Two entities A and B with an M:N relationship. Each entity has its own primary key (surrogate or natural). We need to enforce referential integrity and prevent duplicates. Concept / Approach: An intersection table (sometimes called a bridge or join table) contains at least two foreign keys: one to A and one to B. Its primary key is commonly the composite (a_id, b_id) or a surrogate with a unique constraint on (a_id, b_id). This structure allows any number of A–B pairings while preserving normalization and enabling ON DELETE/ON UPDATE rules. Without such a table, attempts to embed repeating groups or comma-separated lists in either parent violate first normal form and sabotage indexing and query performance. Step-by-Step Solution: Create A(id …), B(id …).Create AB(a_id REFERENCES A(id), b_id REFERENCES B(id), PRIMARY KEY(a_id, b_id)).Insert pairings into AB for each association.Query M:N via joins: A JOIN AB JOIN B as needed. Verification / Alternative check: Try to model without AB: you either lose normalization or cannot represent multiple Bs per A (or vice versa). Intersection table is the only normalized, enforceable approach. Why Other Options Are Wrong: Tying correctness to composite keys, 3NF, or optimizers misses the conceptual requirement; intersection tables are fundamental to relational modeling of M:N. Common Pitfalls: Storing comma-separated IDs; forgetting a uniqueness constraint on (a_id, b_id); failing to index both foreign keys for join performance. Final Answer: Correct

Question 9

Primary key placement in one-to-one (1:1) relationships:
“In a 1:1 relationship, the primary key placement is arbitrary.”
Evaluate this statement with respect to optionality and design practice.

Accepted Answer

Introduction / Context: In a 1:1 relationship, each row in table A matches at most one row in table B and vice versa. While it may seem that key placement is arbitrary, good modeling ties the foreign key choice to business semantics and optionality (whether either side can exist without the other). Given Data / Assumptions: Two entities A and B with a one-to-one relationship. Optionality may be mandatory or optional on either side (for example, every Person has exactly one Passport versus some Persons have a Passport). Goal: enforce integrity and support efficient access. Concept / Approach: If one side is optional, place the foreign key in the optional table and often make it both FOREIGN KEY and PRIMARY KEY (or UNIQUE) to enforce 1:1. If both sides are mandatory, you still choose the table that logically “depends on” the other or consolidate them into one table if separation brings no benefit. Arbitrary placement risks awkward insertion order, unnecessary NULLs, and harder enforcement of optionality. Therefore, primary key/foreign key decisions are guided by semantics, not caprice. Step-by-Step Solution: If B is optional, add B.pk = A.pk, declare B.pk as both PRIMARY KEY and FOREIGN KEY referencing A.pk.This ensures at most one B per A and prevents B rows without A.If both mandatory, consider merging tables or choose based on lifecycle (which is created first) and update patterns.Conclusion: placement follows optionality/lifecycle, not arbitrary choice. Verification / Alternative check: Attempt to model “optional” with the foreign key on the mandatory side; you will end up with NULLs or a schema that allows invalid states. Why Other Options Are Wrong: Calling it “correct” ignores optionality and lifecycle considerations. Surrogate keys and physical storage do not override modeling principles. Even when both sides are mandatory, “arbitrary” is poor guidance; design should be justified. Common Pitfalls: Duplicating keys on both sides; not enforcing uniqueness; splitting a single conceptual entity into two tables without a real need. Final Answer: Incorrect

Question 10

Counting minimum cardinality patterns:
“For every relationship, there are six possible sets of minimum cardinalities.”
Judge the correctness of this statement for binary relationships.

Accepted Answer

Introduction / Context: Minimum cardinality describes whether participation in a relationship is optional (0) or mandatory (1) for each entity. For binary relationships (involving exactly two entities), the number of possible minimum-cardinality combinations is finite and small. This question checks whether “six” is the correct count. Given Data / Assumptions: We are discussing binary relationships only (two entities). Each side can be optional (0) or mandatory (1) independently. Notation (Crow’s Foot, IE, UML) may differ visually but expresses the same options. Concept / Approach: For a binary relationship, there are two independent yes/no choices: Is participation mandatory on the left side? Is it mandatory on the right side? Each choice has two possibilities (0 or 1). Therefore, the total number of minimum-cardinality patterns is 2 * 2 = 4: (0,0), (0,1), (1,0), and (1,1). The number “six” might arise from confusing minimum cardinality with total “cardinality classes” (1:1, 1:N, N:1, M:N) or by mixing minimum and maximum cardinality concepts. But strictly for minimum participation across two entities, there are four combinations. Step-by-Step Solution: Define binary relationship R(A,B).Assign minimum participation for A: 0 or 1.Assign minimum participation for B: 0 or 1.Enumerate combinations: (0,0), (0,1), (1,0), (1,1) → four possibilities, not six. Verification / Alternative check: Consult any ER/EER methodology: Crow’s Foot and IE notations encode only these four minimum-participation states for binary relationships; “six” does not appear as a standard count. Why Other Options Are Wrong: “Correct” contradicts the combinatorics. “Ternary relationships” involve three entities and have more combinations, but the question is framed for general relationships and commonly taught binary cases. Composite keys and notation do not alter the count of minimum participation states. Common Pitfalls: Confusing minimum with maximum cardinality; conflating relationship type (1:1, 1:N, M:N) with participation (optional vs mandatory). Final Answer: Incorrect

Question 11

In relational database design, does the term “cascading updates” mean that child rows are automatically deleted when a parent row is deleted, or does it specifically refer to propagating key value changes from the parent to matching foreign keys in …

Accepted Answer

Introduction / Context: Cascading behavior in relational databases is part of referential integrity enforcement. Two commonly confused actions are “ON UPDATE CASCADE” and “ON DELETE CASCADE.” This question checks whether you can distinguish cascading updates (propagating key changes) from cascading deletes (removing dependent rows when a parent is removed). Given Data / Assumptions: A parent table has a primary key referenced by a child table's foreign key. Referential actions may be configured on UPDATE or on DELETE. Terminology should match standard SQL/DBMS documentation usage. Concept / Approach: “Cascading updates” means that when a parent's key value (typically the primary key) changes, the DBMS automatically updates matching foreign key values in related child rows. “Cascading deletes” means that when a parent row is deleted, the DBMS automatically deletes all related child rows. The statement in the stem describes cascading deletes, not cascading updates, therefore it is incorrect. Step-by-Step Solution: Identify operation types: UPDATE on parent key vs. DELETE on parent row.Map to referential actions: ON UPDATE CASCADE propagates key edits; ON DELETE CASCADE removes dependents.Compare to the stem: “child rows being automatically deleted” corresponds to DELETE, not UPDATE.Conclude the statement mislabels the behavior; answer is Incorrect. Verification / Alternative check: Create a small schema and enable ON UPDATE CASCADE without ON DELETE CASCADE. Update the parent key and observe child keys change; delete the parent and observe the delete fails (or is restricted) unless ON DELETE CASCADE is also defined. Why Other Options Are Wrong: “Correct” would imply deletion is part of updates, which is wrong. “Applies only to cascading deletes” confuses the naming; updates and deletes are distinct actions. “DBMS-dependent” is misleading; mainstream systems use consistent terminology. “Not enough information” is unnecessary; the terms are well-defined. Common Pitfalls: Confusing ON UPDATE CASCADE with ON DELETE CASCADE; assuming both are always enabled; forgetting that changing primary keys should be rare (use surrogate keys to minimize update cascades). Final Answer: Incorrect

Question 12

In data modeling, do surrogate keys (system-generated identifiers) carry meaningful information for end users, or are they intentionally devoid of business meaning?

Accepted Answer

Introduction / Context: Choosing primary keys is central to relational design. Surrogate keys and natural keys behave differently. This item checks whether you recognize that surrogate keys are deliberately non-semantic identifiers, created to uniquely identify rows without encoding business meaning. Given Data / Assumptions: A surrogate key is typically an integer or UUID generated by the system. Natural keys are based on business attributes (e.g., email, SKU). End users value stable, interpretable attributes, not arbitrary IDs. Concept / Approach: Surrogate keys are non-meaningful by design. They simplify joins, avoid composite keys, and remain stable even when business attributes change. End users usually do not interpret them; instead, users rely on descriptive columns (names, codes) for meaning. Therefore, the statement that surrogate keys “have much meaning for users” is false. Step-by-Step Solution: Define surrogate key: system-assigned, uniqueness only.Assess user meaning: no embedded business semantics.Compare to natural key: meaningful, but may change or be composite.Conclude: statement is incorrect. Verification / Alternative check: Survey user-facing reports: they display names/codes, not internal row IDs. Database metadata also treats surrogate keys as opaque identifiers. Why Other Options Are Wrong: “Correct” contradicts the purpose of surrogates. “Meaningful only in reporting” or “after indexing” conflates performance with semantics. “Meaningful only to the database engine” still implies meaning; engines use them for identity, not semantics. Common Pitfalls: Mistaking human-readable sequences (e.g., invoice numbers) for surrogates; conflating usability (short numbers) with semantic meaning; exposing surrogate keys in public APIs unintentionally. Final Answer: Incorrect

Question 13

If the parent entity is required by a foreign key constraint, can a new child row always be inserted unconditionally, or must a matching parent row already exist (or be created in the same transaction)?

Accepted Answer

Introduction / Context: Foreign key constraints enforce that each child row's foreign key matches an existing parent key, unless the foreign key is NULL (when allowed). This question tests whether a child can be inserted “always” when a parent is required. Given Data / Assumptions: The foreign key is NOT NULL (parent required). Referential integrity is enforced by the DBMS. Constraints may be immediate or deferrable (DBMS feature-dependent). Concept / Approach: When the parent is required, a child row can be inserted only if the corresponding parent row already exists or will exist by the time constraints are checked (e.g., within the same transaction for deferrable constraints). The blanket statement that a child can “always” be inserted is incorrect. Without a parent, the insert will fail with a referential integrity violation. Step-by-Step Solution: Check FK definition: NOT NULL and references parent PK/UK.Attempt child insert with missing parent: DBMS raises an error.Insert parent first (or defer constraint), then insert child: operation succeeds.Therefore the word “always” makes the statement false. Verification / Alternative check: Run a simple test: create parent and child tables with immediate constraints; try inserting child first — expect error; insert parent then child — succeeds. Why Other Options Are Wrong: “Correct” contradicts referential integrity rules. “Only when the foreign key is deferrable” is too narrow; even then, parent must exist by commit. “Only if the parent has a surrogate key” is irrelevant; key type doesn’t relax integrity. “ON INSERT CASCADE” is not a standard referential action. Common Pitfalls: Confusing immediate vs. deferred constraint checking; relying on application order instead of transactional grouping; misinterpreting NULLable foreign keys as “parent required.” Final Answer: Incorrect

Question 14

In transforming an entity–relationship (ER) model to a relational schema, is it correct that each entity type is represented as a separate table (with relationships mapped via keys or associative tables)?

Accepted Answer

Introduction / Context: Logical design converts an ER model into a relational schema. A core rule is that each entity type becomes a table (relation), while relationships are enforced with foreign keys or separate intersection (associative) tables for many-to-many links. Given Data / Assumptions: Standard ER-to-relational mapping is being applied. Attributes of entities become columns in the corresponding table. Identifiers (primary keys) are chosen as natural or surrogate keys. Concept / Approach: Every entity type (strong or weak) maps to a table. One-to-many relationships become foreign keys in the “many” side. Many-to-many relationships become separate intersection tables with foreign keys to both parents. Subtype/supertype hierarchies map via one of several patterns (single table, class table, or concrete table inheritance). The statement focuses on entities, and that mapping is correct. Step-by-Step Solution: Identify entity types in the ERD.Create tables for each entity; move attributes to columns.Define primary keys and necessary unique constraints.Map relationships with foreign keys or intersection tables as needed. Verification / Alternative check: Compare the resulting schema to the ERD: counts of entity types should match counts of core tables (excluding associative tables introduced for M:N relationships). Why Other Options Are Wrong: Restricting to weak or supertype entities ignores standard mapping. “Entities become views” is design-specific and not the default mapping rule. “Incorrect” contradicts standard methodology. Common Pitfalls: Forgetting to normalize multi-valued attributes; failing to create intersection tables for M:N; mishandling subtype mapping and keys. Final Answer: Correct

Question 15

If the parent entity is required and the parent uses a surrogate key, can update actions on referential integrity be ignored, or must update behavior still be considered explicitly?

Accepted Answer

Introduction / Context: Designers often choose surrogate keys because they rarely change. This question asks whether that fact allows you to ignore referential “update” actions for foreign keys when the parent is required. Given Data / Assumptions: Parent table uses a surrogate primary key. Child table references the parent key via a NOT NULL foreign key. DBMS supports referential actions on update and delete. Concept / Approach: While surrogate keys “seldom change,” they can, in principle, change (e.g., data fixes, key reseeding, migrations). Referential action settings (ON UPDATE NO ACTION/RESTRICT/CASCADE) still need to be defined intentionally. Relying on “it will never change” is risky. Therefore, you cannot simply ignore update actions; you must choose a policy (commonly NO ACTION) to protect integrity. Step-by-Step Solution: Acknowledge low-change frequency for surrogates.Recognize that referential behavior must still be declared.Select ON UPDATE NO ACTION (typical) or CASCADE if justified.Conclude the statement is incorrect because ignoring configuration is unsafe. Verification / Alternative check: Attempt to change a parent key with NO ACTION versus CASCADE; observe the referential effect on child rows. Without an explicit choice, defaults may block updates or cause errors. Why Other Options Are Wrong: “Correct” would endorse skipping a required integrity decision. “Only ignorable when keys never change” is brittle and not a good practice. “Mandatory only for natural keys” is false; referential policy applies to any key. “Applies only with cascading deletes” confuses update and delete actions. Common Pitfalls: Assuming surrogate immutability; overlooking migration scenarios; leaving default referential actions undocumented. Final Answer: Incorrect

Question 16

Primary key design guidance: is an ideal primary key short in length, numeric (or compact), and very stable (seldom changing over time)?

Accepted Answer

Introduction / Context: Primary keys influence performance, indexing, and data integrity. Good keys simplify joins and minimize maintenance. This question asks whether “short, numeric, and stable” captures the key qualities of an ideal primary key. Given Data / Assumptions: We consider general OLTP and many analytics scenarios. Keys are used in joins, indexes, and foreign key relationships. Stability avoids cascades and churn. Concept / Approach: Short keys reduce index size and I/O. Numeric or compact keys are faster to compare and sort than long strings. Stability prevents costly cascades and fragmentation. Surrogate integers (or UUIDs in some contexts) often satisfy these criteria, although UUIDs are larger and have ordering concerns unless using sequential variants. Therefore, the statement is correct as general guidance. Step-by-Step Solution: Evaluate length: prefer few bytes (e.g., INT, BIGINT).Prefer compact comparable types: numeric or fixed-size binary.Check stability: avoid attributes that change (e.g., email as PK).Adopt surrogate PK when natural keys are long or mutable. Verification / Alternative check: Benchmark joins and index scans with long string PKs versus integer PKs; observe space and speed differences. Analyze downstream FK tables to see space savings with compact keys. Why Other Options Are Wrong: “Incorrect” contradicts widely accepted design guidance. “Only for OLTP” is too narrow; the benefits are broad. “Text keys are preferred” increases size/volatility. “Only when no surrogate exists” is unrelated; surrogates are one way to achieve ideal traits. Common Pitfalls: Picking business attributes that change as PKs; using concatenated multi-column natural keys that bloat indexes; using random UUIDs without considering index locality. Final Answer: Correct

Question 17

Association (intersection) tables in many-to-many relationships: are such tables identifying (ID-dependent) on both of their parent tables?

Accepted Answer

Introduction / Context: Many-to-many relationships are implemented using an intersection (associative) table that links two parent tables. This item tests whether you recognize that the intersection table's identity is tied to both parents. Given Data / Assumptions: Two parent entities (e.g., Students and Courses). An intersection table (e.g., Enrollments) with foreign keys to both parents. Primary key often composed of both foreign keys (or a surrogate plus unique constraint on the pair). Concept / Approach: In an identifying relationship, a child's identity is defined by its parent(s). For an intersection table, the row cannot exist without valid references to both parents; its logical identity is the combination of those references. Thus, it is ID-dependent on both parents. Even if a surrogate key is used, a unique constraint on the two FKs maintains the identifying semantics. Step-by-Step Solution: Create an intersection table with two FKs to the parents.Define the PK as the pair of FKs, or use a surrogate with a unique pair constraint.Observe that deleting a parent typically restricts or cascades to the intersection row.Conclude ID-dependency on both parents. Verification / Alternative check: Attempt to insert an intersection row without a valid parent key — the insert fails due to FK constraints. Why Other Options Are Wrong: “Incorrect” contradicts standard M:N modeling. “Only with surrogate PKs” is false—the dependency is logical, not PK-type specific. “Depends on optionality” affects nullability and participation, not identity. Common Pitfalls: Forgetting the unique constraint on the pair when using a surrogate; failing to cascade or restrict deletes in a controlled way; allowing duplicates of the same association. Final Answer: Correct

Question 18

Are the terms “alternate key” and “candidate key” truly synonymous, or is an alternate key specifically a candidate key that was not chosen as the primary key?

Accepted Answer

Introduction / Context: Key terminology matters in database design. “Candidate key” refers to any minimal set of attributes that can uniquely identify a row. Among the candidate keys, one is selected as the primary key. The remaining candidate keys are called “alternate keys.” Given Data / Assumptions: There may be multiple candidate keys for a table. Exactly one candidate key is chosen as the primary key. Other candidate keys may be enforced via UNIQUE constraints. Concept / Approach: Alternate keys are a subset of candidate keys: specifically, those not selected as the primary key. Thus, “candidate key” and “alternate key” are not identical terms. The statement that they “mean the same thing” is therefore incorrect, although alternate keys are still candidate keys by nature. Step-by-Step Solution: Identify all minimal unique attribute sets — these are candidate keys.Choose one to be the primary key.Label the rest as alternate keys and enforce with UNIQUE constraints.Hence, alternate key ≠ candidate key (strict equality), but alternate ⊂ candidate. Verification / Alternative check: Define a table with both email and username unique for users. Either could be PK (candidate keys). If username is chosen as PK, email is an alternate key (still unique). Why Other Options Are Wrong: “Correct” ignores the primary-versus-alternate distinction. “Synonymous in all textbooks” is factually wrong. “Weaker than candidate keys” misrepresents; they are candidate keys not chosen as PK. “Only in NoSQL” is irrelevant; this is relational terminology. Common Pitfalls: Assuming there is only one candidate key; forgetting to enforce uniqueness on alternates; confusing superkeys with candidate keys (minimality requirement). Final Answer: Incorrect

Question 19

Mandatory child participation: if the child entity is required, are we prevented from creating a new parent row unless a corresponding child row is also created (typically within the same transaction)?

Accepted Answer

Introduction / Context: Cardinality and optionality constraints in data models dictate whether an entity must have related rows. When the child is mandatory for a parent, business rules require at least one child to exist for each parent. This question asks whether that restricts creating a parent without a child. Given Data / Assumptions: Child participation is mandatory (at least one child per parent). Enforcement may be via constraints, triggers, or application logic. We assume correct implementation of the business rule in the database. Concept / Approach: Pure foreign keys enforce child must reference a parent, not the reverse. To enforce “a parent must have at least one child,” additional mechanisms are needed (deferred constraints, triggers, or check constraints combined with modeling patterns). Conceptually, the requirement means a parent cannot persist without at least one child. Practically, this is often handled in a single transaction: insert parent, insert child, then commit. Hence, the restriction exists by design when properly enforced. Step-by-Step Solution: State rule: mandatory child existence per parent.Implement enforcement (e.g., deferred constraint or trigger).Insert parent and child together within one transaction to satisfy the rule.Conclude: the system restricts parent creation without a child. Verification / Alternative check: Attempt to commit a transaction that inserts a parent but no child with enforcement enabled; expect failure or rollback. Insert both parent and child; expect success. Why Other Options Are Wrong: “Incorrect” would ignore the mandated business rule. “Only with triggers” is too specific—other mechanisms can enforce the rule. Key types and UI concerns do not change the conceptual restriction. Common Pitfalls: Assuming foreign keys alone enforce parent-must-have-child; forgetting to make constraints deferred to allow parent-before-child within a single transaction; relying solely on application logic without database enforcement. Final Answer: Correct

Question 20

Is (STREET_ADDRESS, CITY, STATE, ZIP) an ideal choice for a primary key, or is such a multi-attribute natural key typically long, mutable, and therefore not recommended?

Accepted Answer

Introduction / Context: Primary keys should be compact and stable. Address components are neither: they are verbose, prone to change (street renames, ZIP updates, unit additions), and are hard to standardize across locales. This question evaluates whether a composite address makes an ideal primary key. Given Data / Assumptions: Address attributes: STREET_ADDRESS, CITY, STATE, ZIP. Addresses can change and be formatted inconsistently. The PK will be used in foreign keys and indexes. Concept / Approach: An ideal PK is short, stable, and unique. Address fields are lengthy, error-prone (abbreviations, punctuation, international variability), and may change over time. Using them as a composite PK inflates indexes and slows joins. The recommended approach is a surrogate key (e.g., address_id) with uniqueness and validation enforced on normalized address attributes via separate constraints and cleansing processes. Step-by-Step Solution: Assess uniqueness: addresses are not guaranteed unique (multi-unit buildings).Assess stability: addresses can change, breaking referential integrity.Assess length: composite keys enlarge indexes and FKs.Conclude: not an ideal PK; prefer a surrogate with supporting constraints. Verification / Alternative check: Compare index size and join performance using a composite address PK versus a surrogate integer PK; observe the overhead of the composite approach. Why Other Options Are Wrong: “Correct” contradicts best practices. “Ideal only for warehouses” still suffers from mutability and standardization problems. “Ideal with a surrogate” is contradictory—if you have a surrogate PK, the address should not be the PK. Country variations do not make addresses more suitable as keys. Common Pitfalls: Using meaningful but unstable attributes as keys; failing to cleanse and standardize addresses; ignoring apartment/unit numbers leading to false duplicates. Final Answer: Incorrect

Data Models into Database Designs Questions