Intermediate
Database & SQL
#schema-design
#normalization
#data-modelling
#constraints
Schema Design Vocabulary
5 exercises — practise the English vocabulary for schema design discussions: normalization normal forms, denormalization trade-offs, foreign key constraint actions, relationship cardinality, and the soft delete pattern.
0 / 22 completed
1 / 22
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.
Key vocabulary for normalization discussions:
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 / 22
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.
Write anomaly types to mention in reviews:
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 / 22
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.
Referential integrity 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 / 22
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.,
Junction table 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 / 22
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.
Practical notes for schema reviews: Soft delete often requires a partial index on
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.6 / 22
PR Description:
During a code review of the new `customer_orders` API endpoint, Sarah flags a potential performance issue. She writes in the PR description:
"I've optimized the query to fetch order details directly from the `orders` table instead of joining with the `customers` table. This should significantly reduce latency for common use cases where customers are frequently associated with their orders. However, I'm concerned about potential data consistency issues if customer information is updated independently in the `customers` table without corresponding updates to the order records."This scenario highlights the trade-off between query performance and data consistency. Sarah's decision to denormalize—directly fetching customer information with orders—is prioritizing speed. The explanation correctly identifies that this introduces a potential for inconsistencies if customer updates aren't reflected in order records; it avoids simply stating 'denormalization is good'. Options A, C, and D misrepresent the core concept of performance optimization versus data integrity.
7 / 22
Alex: 'Okay team, I've just received this API response from the new `inventory_tracking` service. It's returning a lot of null values for product attributes – particularly things like 'color' and 'size'. This is impacting our ability to accurately display product details on the storefront. I think we need to reconsider how we're storing these attributes.'
Which of the following approaches would MOST effectively address Alex's concern regarding null values in the `inventory_tracking` database?The core issue is that storing multiple product attributes (like color and size) within a single database column leads to frequent null values when products have varying characteristics. Normalization—specifically, *decomposing* the `inventory_tracking` table into separate tables for each attribute category (e.g., a `product_colors` table linked by a foreign key—likely product ID—to the main `inventory_tracking` table)—is the correct strategy. This minimizes redundancy and avoids having to represent multiple attributes as null values, improving data integrity and query efficiency.
8 / 22
Alex is experiencing issues with null values in the `inventory_tracking` database. He suspects this is impacting product display on the storefront. Which of the following strategies would MOST effectively resolve this issue by preventing future null value introduction?
Consider the trade-offs between data normalization and potential complexities in updating product information.
The correct answer addresses the root cause: preventing null values from being entered into the database in the first place. Implementing `NOT NULL` constraints ensures data integrity by enforcing that these fields *must* have a value when a new product is created or an existing one is updated. Options A and D represent band-aid solutions that don't prevent the problem; option C misdiagnoses the issue, focusing on application logic rather than database design, and option B directly solves the core problem by enforcing data constraints.
9 / 22
PR Description:
During a code review of the new `customer_orders` API endpoint, Sarah flags a potential performance issue. She writes in the PR description:
"I've optimized the query to fetch order details directly from the `orders` table instead of joining with the `customers` table. This should significantly reduce latency for common use cases where customers are frequently associated with their orders. However, I'm concerned about potential data consistency issues if customer information is updated independently in the `customers` table without corresponding updates to the order records."This scenario highlights the trade-off between query performance and data consistency. Sarah's decision to denormalize—directly fetching customer information with orders—is prioritizing speed. The explanation correctly identifies that this introduces a potential for inconsistencies if customer updates aren't reflected in order records; it avoids simply stating 'denormalization is good'. Options A, C, and D misrepresent the core concept of performance optimization versus data integrity.
10 / 22
Alex: 'Okay team, I've just received this API response from the new `inventory_tracking` service. It's returning a lot of null values for product attributes – particularly things like 'color' and 'size'. This is impacting our ability to accurately display product details on the storefront. I think we need to reconsider how we're storing these attributes.'
Which of the following approaches would MOST effectively address Alex's concern regarding null values in the `inventory_tracking` database?The core issue is that storing multiple product attributes (like color and size) within a single database column leads to frequent null values when products have varying characteristics. Normalization—specifically, *decomposing* the `inventory_tracking` table into separate tables for each attribute category (e.g., a `product_colors` table linked by a foreign key—likely product ID—to the main `inventory_tracking` table)—is the correct strategy. This minimizes redundancy and avoids having to represent multiple attributes as null values, improving data integrity and query efficiency.
11 / 22
Alex is experiencing issues with null values in the `inventory_tracking` database. He suspects this is impacting product display on the storefront. Which of the following strategies would MOST effectively resolve this issue by preventing future null value introduction?
Consider the trade-offs between data normalization and potential complexities in updating product information.
The correct answer addresses the root cause: preventing null values from being entered into the database in the first place. Implementing `NOT NULL` constraints ensures data integrity by enforcing that these fields *must* have a value when a new product is created or an existing one is updated. Options A and D represent band-aid solutions that don't prevent the problem; option C misdiagnoses the issue, focusing on application logic rather than database design, and option B directly solves the core problem by enforcing data constraints.
12 / 22
PR Description:
During a code review of the new `customer_orders` API endpoint, Sarah flags a potential performance issue. She writes in the PR description:
"I've optimized the query to fetch order details directly from the `orders` table instead of joining with the `customers` table. This should significantly reduce latency for common use cases where customers are frequently associated with their orders. However, I'm concerned about potential data consistency issues if customer information is updated independently in the `customers` table without corresponding updates to the order records."This scenario highlights the trade-off between query performance and data consistency. Sarah's decision to denormalize—directly fetching customer information with orders—is prioritizing speed. The explanation correctly identifies that this introduces a potential for inconsistencies if customer updates aren't reflected in order records; it avoids simply stating 'denormalization is good'. Options A, C, and D misrepresent the core concept of performance optimization versus data integrity.
13 / 22
Alex: 'Okay team, I've just received this API response from the new `inventory_tracking` service. It's returning a lot of null values for product attributes – particularly things like 'color' and 'size'. This is impacting our ability to accurately display product details on the storefront. I think we need to reconsider how we're storing these attributes.'
Which of the following approaches would MOST effectively address Alex's concern regarding null values in the `inventory_tracking` database?The core issue is that storing multiple product attributes (like color and size) within a single database column leads to frequent null values when products have varying characteristics. Normalization—specifically, *decomposing* the `inventory_tracking` table into separate tables for each attribute category (e.g., a `product_colors` table linked by a foreign key—likely product ID—to the main `inventory_tracking` table)—is the correct strategy. This minimizes redundancy and avoids having to represent multiple attributes as null values, improving data integrity and query efficiency.
14 / 22
Alex is experiencing issues with null values in the `inventory_tracking` database. He suspects this is impacting product display on the storefront. Which of the following strategies would MOST effectively resolve this issue by preventing future null value introduction?
Consider the trade-offs between data normalization and potential complexities in updating product information.
The correct answer addresses the root cause: preventing null values from being entered into the database in the first place. Implementing `NOT NULL` constraints ensures data integrity by enforcing that these fields *must* have a value when a new product is created or an existing one is updated. Options A and D represent band-aid solutions that don't prevent the problem; option C misdiagnoses the issue, focusing on application logic rather than database design, and option B directly solves the core problem by enforcing data constraints.
15 / 22
PR Description:
During a code review of the new `customer_orders` API endpoint, Sarah flags a potential performance issue. She writes in the PR description:
"I've optimized the query to fetch order details directly from the `orders` table instead of joining with the `customers` table. This should significantly reduce latency for common use cases where customers are frequently associated with their orders. However, I'm concerned about potential data consistency issues if customer information is updated independently in the `customers` table without corresponding updates to the order records."This scenario highlights the trade-off between query performance and data consistency. Sarah's decision to denormalize—directly fetching customer information with orders—is prioritizing speed. The explanation correctly identifies that this introduces a potential for inconsistencies if customer updates aren't reflected in order records; it avoids simply stating 'denormalization is good'. Options A, C, and D misrepresent the core concept of performance optimization versus data integrity.
16 / 22
Alex: 'Okay team, I've just received this API response from the new `inventory_tracking` service. It's returning a lot of null values for product attributes – particularly things like 'color' and 'size'. This is impacting our ability to accurately display product details on the storefront. I think we need to reconsider how we're storing these attributes.'
Which of the following approaches would MOST effectively address Alex's concern regarding null values in the `inventory_tracking` database?The core issue is that storing multiple product attributes (like color and size) within a single database column leads to frequent null values when products have varying characteristics. Normalization—specifically, *decomposing* the `inventory_tracking` table into separate tables for each attribute category (e.g., a `product_colors` table linked by a foreign key—likely product ID—to the main `inventory_tracking` table)—is the correct strategy. This minimizes redundancy and avoids having to represent multiple attributes as null values, improving data integrity and query efficiency.
17 / 22
Alex is experiencing issues with null values in the `inventory_tracking` database. He suspects this is impacting product display on the storefront. Which of the following strategies would MOST effectively resolve this issue by preventing future null value introduction?
Consider the trade-offs between data normalization and potential complexities in updating product information.
The correct answer addresses the root cause: preventing null values from being entered into the database in the first place. Implementing `NOT NULL` constraints ensures data integrity by enforcing that these fields *must* have a value when a new product is created or an existing one is updated. Options A and D represent band-aid solutions that don't prevent the problem; option C misdiagnoses the issue, focusing on application logic rather than database design, and option B directly solves the core problem by enforcing data constraints.
18 / 22
During a Slack conversation with the database team about redesigning the `products` table, Ben says: 'We need to ensure that each product has a unique identifier. We'll use a primary key column named `product_id`.' Which of the following best describes Ben's statement regarding primary keys?
A primary key's core function is to uniquely identify each record within a table. It's crucial for data integrity and efficient querying. Options A, B, and D accurately describe the role of a primary key – it enforces uniqueness and often includes indexing. Option C describes a *unique* constraint instead.
19 / 22
Maria is reviewing a PR that implements a new feature for tracking customer shipments. The developer has used the `updated_at` timestamp column in the `shipments` table. During the review, another team member asks: 'How does this design handle concurrent updates to the same shipment record?' Which of the following approaches would MOST effectively address this concern?
Database transactions are essential for managing concurrent access. They guarantee that either all operations within the transaction succeed or none do, preventing data corruption due to race conditions. Option B correctly identifies the use of a transaction as the best practice; options A, C and D are inadequate solutions.
20 / 22
During a standup meeting, David explains that he's designing a new table to store user activity logs. He states: 'I need to capture every action a user takes on the website – logins, purchases, page views.' Which of the following database design choices would be MOST suitable for this scenario?
A normalized schema is generally preferred for relational databases because it reduces redundancy and improves data integrity. Using separate tables for users, events, and their relationships allows for efficient querying and scalability. Options C and D would lead to significant data duplication and potential inconsistencies.
21 / 22
You receive the following API response from a new microservice responsible for managing product inventory:
```json
{
"product_id": null,
"name": "Laptop Pro",
"quantity": 10
}
```
What is the MOST likely reason for the `null` value in the `product_id` field, and what action should you take to address this?
Null values in APIs often represent intentional design choices – particularly when dealing with evolving schemas. While reporting an issue is important, it's more likely that the null represents a deliberate decision about the product ID's role at this stage. Implementing validation to prevent further null entries is crucial.
22 / 22
Sarah is designing a database for an e-commerce platform and needs to represent the many-to-many relationship between customers and products. Which of the following approaches would be MOST appropriate?
A junction (or bridge) table is the standard approach for implementing many-to-many relationships. The `customer_products` table will have foreign keys referencing both the `customers` and `products` tables, representing each instance of a customer purchasing a product. Options A & B are incorrect due to redundancy; option D isn't suitable for relational databases.
Frequently Asked Questions
What does the "Schema Design Vocabulary" exercise practise?
Practice English for database schema design discussions: normalization normal forms, denormalization trade-offs, foreign key constraint behaviour, relationship cardinalities, and soft delete patterns. 5 exercises.
How many questions are in this exercise?
This exercise has 22 questions, each multiple-choice with a full explanation shown after you answer.
What English level is this exercise for?
This exercise is tagged Intermediate. If the vocabulary feels difficult, browse the Database & SQL category page for an easier module to start with.
Is this exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is free with no account, sign-up, or paywall.
Do I get feedback if I answer incorrectly?
Yes — whichever option you choose, right or wrong, you'll immediately see an explanation clarifying the correct term and why the other options don't fit.
Can I retry this exercise?
Yes — once you finish all the questions, a "Try again" button on the results screen resets the exercise so you can practise as many times as you like.
Do I need an account to track my progress?
No account is required. Your progress bar and score for this session are tracked in the browser as you go, but nothing is saved once you leave the page.
Is "Schema Design Vocabulary" part of a larger series?
Yes — it's one exercise in the Database & SQL category on CoderSlingo. See the category page for the full list of related exercises on similar terminology.
Can I link directly to this exercise?
Yes — this exercise has its own permanent URL, so you can bookmark it or share the link directly with a colleague or study partner.
Where can I find more exercises like this one?
See the Database & SQL category page for related exercises, or browse the main Exercises hub for other IT English topics.