Sample questions
Data ModelingDifficulty 2
In a data model, how do PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints differ in their roles?
- aPRIMARY KEY and UNIQUE both identify rows; FOREIGN KEY stores a copy of the referenced row's data
- bPRIMARY KEY marks the row's main identifier; UNIQUE enforces alternate uniqueness; FOREIGN KEY links to another table's key✓
- cAll three enforce uniqueness within their own table and none of them allow NULL values
- dPRIMARY KEY and FOREIGN KEY must always be the same column; UNIQUE is only an index hint
Explanation:The primary key is the chosen main identifier (one per table, unique + NOT NULL); UNIQUE enforces uniqueness on alternate/candidate keys and may allow NULLs; a foreign key references another table's key to enforce referential integrity. A foreign key does not copy the referenced data (a), UNIQUE is a real constraint rather than a mere index hint and can permit NULLs, so (c) and (d) are wrong.
Data ModelingDifficulty 2
What best describes the difference between a natural key and a surrogate key?
- aA natural key is generated by the system; a surrogate key is taken from the real-world data
- bA surrogate key must always be composite, while a natural key is always a single column
- cNatural keys are faster to join because they never need to be indexed
- dA natural key is a meaningful real-world attribute; a surrogate key is an artificial system-generated value✓
Explanation:A natural key uses existing meaningful data (e.g. ISBN, email); a surrogate key is an artificial value (e.g. an auto-generated integer or UUID) with no business meaning. Option (a) reverses the two, surrogate keys are typically single-column and not required to be composite (b), and natural keys still need indexes like any key (c).
Data ModelingDifficulty 2
What is a composite primary key?
- aA primary key that automatically increments across two tables at once
- bA primary key that is duplicated into a second column to serve as a backup
- cA primary key made up of two or more columns whose combination must be unique✓
- dA primary key that may be NULL as long as at least one of its columns has a value
Explanation:A composite key uses several columns together; uniqueness is enforced on the combination, not on each column alone. It is common on junction tables, e.g. (student_id, course_id). No part of a primary key may be NULL, so (d) is wrong; it is neither a backup copy (b) nor a cross-table auto-increment (a).
Data ModelingDifficulty 1
First normal form (1NF) requires that:
- aeach column holds a single atomic value, with no lists or repeating groups✓
- bevery table is limited to a maximum of three columns
- call textual columns are stored using fixed-length CHAR types
- deach table participates in at least one foreign-key relationship with another table
Explanation:1NF forbids multi-valued and repeating columns: every cell holds one atomic value and there are no repeating groups. Column counts (b), physical CHAR storage (c), and relationship requirements (d) have nothing to do with normal forms.
Data ModelingDifficulty 3
A table order_items(order_id, product_id, quantity, product_name) has the composite primary key (order_id, product_id), and product_name depends only on product_id. What is the problem and its fix?
- aIt violates 1NF because product_name repeats; split each value into its own row
- bIt violates 3NF through a transitive dependency; move quantity into another table
- cIt violates 2NF: product_name is a partial dependency on part of the key; move it to a products table✓
- dIt violates no normal form; a wide order-line table is always an acceptable design
Explanation:2NF requires every non-key column to depend on the whole composite key. product_name depends only on product_id, part of the key — a partial dependency — so it belongs in a products table keyed by product_id. The values are atomic, so it is not a 1NF issue (a), and a partial dependency is distinct from the transitive dependency that 3NF addresses (b).
Data ModelingDifficulty 3
A table employees(emp_id, name, dept_id, dept_name) has the single-column primary key emp_id. Here dept_name depends on dept_id, which in turn depends on emp_id. What is this, and what does 3NF prescribe?
- aA partial dependency; it is already resolved because emp_id is the sole key
- bA multi-valued dependency; resolving it requires fourth normal form (4NF)
- cNothing is wrong, since dept_name is allowed to stay while dept_id is not the primary key
- dA transitive dependency; move dept_name into a departments table keyed by dept_id✓
Explanation:dept_name depends on a non-key column (dept_id) that itself depends on the key — a transitive dependency, which 3NF removes by relocating dept_name to a departments table. With a single-column key there is no partial dependency (a), and this is a functional/transitive issue, not a multi-valued one needing 4NF (b).