Schema Design Vocabulary — Database & SQL Exercises

1 / 5

A DBA says in a schema review: "This table violates third normal form because the postcode column depends on the city column, not on the primary key." Which type of normalization violation is being described?

Normalization vocabulary — the three normal forms:

Option C is correct. A transitive dependency is when a non-key column (postcode) depends on another non-key column (city) rather than directly on the primary key. This is the defining violation of 3NF.

Normal form	Violation type	Example violation
1NF	Repeating groups; non-atomic values	`tags = "sql,nosql,graph"` in a single column
2NF	Partial dependency on composite PK	order_item.product_name depends on product_id alone, not (order_id, product_id)
3NF	Transitive dependency (non-key → non-key)	employees.department_name depends on department_id, not employee_id

Key vocabulary for normalization discussions:

Transitive dependency — A → B → C, where A is the PK; B depends on A; C depends on B but not directly on A
Partial dependency — a non-key column depends on only part of a composite primary key
Functional dependency — column B is functionally dependent on column A if each value of A determines exactly one value of B
Decomposition — splitting a table into two or more tables to eliminate a dependency violation

2 / 5

In an architecture meeting, a senior engineer says: "We should denormalize the orders summary table to avoid the expensive JOIN on every read." Which trade-off is this engineer explicitly accepting?

Denormalization trade-off vocabulary:

Option B is correct. Denormalization deliberately introduces redundancy (storing the same data in multiple places) to eliminate expensive JOIN operations on reads. The cost is write anomalies — when the source data changes, all denormalized copies must stay in sync, which increases write complexity and the risk of inconsistency.

Concern	Normalized schema	Denormalized schema
Read performance	Slower — requires JOINs	Faster — data co-located
Write complexity	Simpler — one source of truth	Higher — multiple copies to update
Data consistency	Strong — enforced by normal forms	Weaker — application must maintain sync
Storage	Efficient	Larger — data duplicated

Write anomaly types to mention in reviews:

Update anomaly — changing a value requires updating it in multiple rows/tables
Insert anomaly — cannot insert data without inserting related data
Delete anomaly — deleting one record unintentionally destroys other information

3 / 5

A developer is designing a comments table that references a posts table. The requirement is: deleting a post should fail if any comments still reference it. Which foreign key constraint behaviour implements this correctly?

FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE ???

Foreign key constraint vocabulary:

Option C is the correct choice. ON DELETE RESTRICT raises an error immediately when deleting a parent row that has child references, preventing the deletion.

Constraint action	What happens when parent is deleted	Use case
CASCADE	Child rows are automatically deleted	Owned entities (order → order_items)
SET NULL	FK column set to NULL in child rows	Optional references (author → posts, where post can be authorless)
RESTRICT	Error raised; deletion blocked immediately	Protected references (post → comments)
NO ACTION	Error raised at end of transaction (deferred check)	When ordering within a transaction matters

Referential integrity vocabulary:

Parent table — the table that owns the referenced primary key (posts)
Child table — the table that holds the foreign key (comments)
Referential integrity — the guarantee that every foreign key value corresponds to an existing primary key value
Orphaned row — a child row whose parent has been deleted without proper constraint handling

4 / 5

A data modeller describes the relationship between users and roles: "A user can have multiple roles. A role can be assigned to multiple users." Which relationship type requires an intermediate junction table (also called a join table or bridge table)?

Relationship cardinality vocabulary:

Option D is correct. Many-to-many relationships cannot be represented by a single foreign key column because one user can have many roles AND one role can belong to many users. A junction table (e.g., user_roles) holds a composite foreign key pairing, resolving the relationship into two one-to-many relationships.

Cardinality	Implementation	Example
One-to-one	FK in either table + UNIQUE constraint	users ↔ user_profiles
One-to-many	FK in the "many" side table	departments → employees
Many-to-many	Junction table with two FKs	users ↔ user_roles ↔ roles

Junction table vocabulary:

Junction table / bridge table / join table / associative entity — all refer to the same pattern
Composite primary key — the junction table's PK is typically (user_id, role_id) to enforce uniqueness of each pairing
Payload columns — additional columns on the junction table that describe the relationship itself (e.g., assigned_at, assigned_by)

5 / 5

A schema review reveals a users table with a deleted_at TIMESTAMP NULL column. The column is NULL for active users and set to a timestamp when a user is "deleted". Which statement best describes this design pattern and its primary trade-off?

Soft delete pattern vocabulary and trade-offs:

Option A is correct. Soft delete is a pattern where records are never physically removed — instead, a nullable timestamp (or boolean) marks the logical deletion time.

Aspect	Soft delete	Hard delete
Data recovery	Easy — set deleted_at = NULL	Requires backup restore
Audit history	Preserved in-place	Lost unless separately logged
Query complexity	All queries must filter deleted_at IS NULL	No extra filter needed
Table size	Grows indefinitely	Freed on deletion
FK integrity risk	Deleted rows can still be referenced	FK constraints enforce cleanup

Practical notes for schema reviews: Soft delete often requires a partial index on WHERE deleted_at IS NULL to keep read queries fast as the table grows. ORM frameworks like Rails, Django, and Hibernate have native soft-delete support. The biggest operational risk is forgetting the deleted_at IS NULL filter — a single missing WHERE clause can expose "deleted" data to users.