The example problems in this set give practice toward the following module outcome:
Each problem presents a poorly structured (unnormalized or partially normalized) relation and asks you to identify and classify the anomalies present. Because this outcome is pitched at the Analyze level of Bloom's Taxonomy, the problems require you to break down the schema, examine the data, and discriminate among the three anomaly types — not simply recall their definitions.
Outcome practised: MO2 [Analyze]
Problem Statement
A university stores all enrollment information in a single table called ENROLLMENT. A sample of the data is shown below.
| StudentID | StudentName | AdvisorID | AdvisorName | CourseID | CourseName | Grade |
|---|---|---|---|---|---|---|
| 101 | Alice | A01 | Dr. Smith | CS101 | Intro to CS | A |
| 101 | Alice | A01 | Dr. Smith | CS202 | Data Structures | B |
| 102 | Bob | A02 | Dr. Jones | CS101 | Intro to CS | C |
| 103 | Carol | A01 | Dr. Smith | CS303 | Databases | A |
The primary key of this table is the combination (StudentID, CourseID). Analyze the table and identify one concrete example of each of the following anomaly types: insertion anomaly, update anomaly, and deletion anomaly. For each, explain precisely what the problem is and why it occurs.
Solution
The primary key is (StudentID, CourseID). Notice that several attributes depend on only part of this composite key:
These partial dependencies mean the table is not in Second Normal Form (2NF), which is the root cause of all three anomaly types.
Definition check: An insertion anomaly occurs when you cannot add certain data to the database without also supplying unrelated, possibly unavailable data.
Concrete example: Suppose a new advisor, Dr. Lee (AdvisorID = A03), is hired but has not yet been assigned any students. You cannot insert Dr. Lee's information into ENROLLMENT because every row requires a valid (StudentID, CourseID) primary key — there is no student or course to associate with Dr. Lee yet. The advisor's existence simply cannot be recorded.
Why it occurs: Advisor data is stored inside a table whose primary key is about enrollments. Advisor existence is a separate fact that should live in its own relation.
Definition check: An update anomaly occurs when changing a single real-world fact requires updating multiple rows, creating a risk of inconsistency if some rows are missed.
Concrete example: Dr. Smith (AdvisorID = A01) changes her name to Dr. Smith-Brown. Looking at the table, AdvisorName = "Dr. Smith" appears in rows for (101, CS101), (101, CS202), and (103, CS303) — three separate rows. If even one row is not updated, the database will contain contradictory advisor names for the same AdvisorID, leaving the data in an inconsistent state.
Why it occurs: The same advisor fact (AdvisorID → AdvisorName) is replicated across every enrollment row for every student advised by that person. A single logical fact is stored in multiple physical places.
Definition check: A deletion anomaly occurs when deleting one piece of data unintentionally destroys other, unrelated data.
Concrete example: Carol (StudentID = 103) decides to drop her only course, CS303 (Databases). Deleting the row (103, CS303) is the correct action for removing that enrollment. However, that row is also the only record that stores the fact that CS303 is named "Databases." Once the row is deleted, all knowledge that the course CS303 exists and is called "Databases" is permanently lost from the database.
Why it occurs: Course information is entangled with enrollment information in the same row. Deleting an enrollment fact inadvertently destroys course facts that should be stored independently.
| Anomaly Type | Concrete Example in ENROLLMENT | Root Cause |
|---|---|---|
| Insertion | Cannot add a new advisor (Dr. Lee) without an enrollment row | Advisor data requires a (StudentID, CourseID) key to exist |
| Update | Changing Dr. Smith's name must be done in 3 rows; missing one causes inconsistency | Advisor facts are replicated across all enrollment rows for her students |
| Deletion | Dropping Carol's only enrollment deletes all record of course CS303 | Course facts are stored inside enrollment rows rather than separately |
Outcome practised: MO2 [Analyze]
Problem Statement
A company tracks employee project assignments in a single table called ASSIGNMENT. A sample of the data is shown below.
| EmpID | EmpName | DeptID | DeptLocation | ProjectID | ProjectBudget | HoursWorked |
|---|---|---|---|---|---|---|
| E01 | Maria | D10 | New York | P001 | 50000 | 120 |
| E01 | Maria | D10 | New York | P002 | 30000 | 80 |
| E02 | James | D20 | Chicago | P001 | 50000 | 200 |
| E03 | Nina | D10 | New York | P003 | 75000 | 95 |
The primary key is (EmpID, ProjectID). Your task is to: (a) analyze the functional dependencies present, (b) identify which anomaly type each of the following scenarios represents, and (c) explain why each is classified the way it is.
Solution
Inspect each non-key attribute against the composite primary key (EmpID, ProjectID):
Both partial and transitive dependencies exist, meaning the schema violates 2NF and 3NF. These violations are the structural root of all three anomalies.
Classification: Insertion Anomaly.
Project P004 cannot be inserted into ASSIGNMENT because a row requires a valid (EmpID, ProjectID) primary key. Since no employee is yet assigned to P004, there is no EmpID to supply, and inserting a row with a NULL EmpID would violate the primary key constraint (primary key attributes cannot be NULL). The project's budget information simply cannot exist in the database until at least one employee is assigned.
Distinguishing feature of an insertion anomaly: You are trying to add a new, legitimate fact (a new project) but the schema forces you to also supply unrelated data (an employee assignment) that does not yet exist.
Classification: Update Anomaly.
The location of department D10 ("New York") appears in three separate rows — (E01, P001), (E01, P002), and (E03, P003). Relocating D10 to Boston is a single real-world change, but it requires updating three rows. If the (E03, P003) row is not updated, the database simultaneously records D10 as being in "New York" and "Boston," which is a contradiction — the data is inconsistent.
Distinguishing feature of an update anomaly: You are trying to change one fact, but because it is stored redundantly across multiple rows, failing to change every occurrence breaks consistency.
Classification: Deletion Anomaly.
The row (E02, P001) is the only row containing information about department D20 and its location (Chicago). When James is correctly removed from project P001 by deleting this row, the system also erases the only record that D20 exists and is located in Chicago. This is unintended data loss — a fact about a department is destroyed by an action that was only meant to remove an employee-project assignment.
Distinguishing feature of a deletion anomaly: You are removing one piece of data, but the schema forces unrelated data to be deleted along with it because both facts are stored in the same row.
| Scenario | Anomaly Type | Operation Attempted | Unintended Consequence |
|---|---|---|---|
| X | Insertion | Adding new project P004 | Cannot insert without a (non-existent) employee assignment |
| Y | Update | Changing D10's location to Boston | Must update 3 rows; missing one causes contradictory location data |
| Z | Deletion | Removing James from project P001 | All knowledge of D20's location (Chicago) is permanently lost |
Outcome practised: MO2 [Analyze]
Problem Statement
A public library stores all information about books and loans in one table called LOAN. The data sample is shown below.
| LoanID | MemberID | MemberName | MemberPhone | BookISBN | BookTitle | AuthorName | LoanDate | DueDate |
|---|---|---|---|---|---|---|---|---|
| L001 | M01 | Sara | 555-1234 | 978-1 | Database Design | Codd | 2024-01-05 | 2024-01-19 |
| L002 | M02 | Tom | 555-5678 | 978-2 | SQL Mastery | Date | 2024-01-10 | 2024-01-24 |
| L003 | M01 | Sara | 555-1234 | 978-3 | Normalization | Codd | 2024-01-12 | 2024-01-26 |
| L004 | M03 | Priya | 555-9999 | 978-1 | Database Design | Codd | 2024-02-01 | 2024-02-15 |
The primary key is LoanID. Read the following three statements and determine whether each describes an insertion anomaly, an update anomaly, or a deletion anomaly. Justify each classification by referencing the specific data in the table.
Solution
The primary key is the single-column LoanID, so there are no partial dependencies. However, several non-key attributes depend on other non-key attributes:
These transitive dependencies mean the schema violates Third Normal Form (3NF) and is prone to all three anomaly types.
Classification: Insertion Anomaly. Statement 1 is correct.
To insert a row into LOAN, a LoanID is required, and a LoanID only exists when a loan event occurs. Because ISBN 978-4 has never been borrowed, there is no loan event and therefore no row can be created. The book's existence, title, and author cannot be stored until someone actually borrows it. This is a classic insertion anomaly: a valid, real-world fact (a book in the collection) cannot be recorded because the schema requires it to be bundled with an unrelated fact (a loan transaction).
Classification: The statement is INCORRECT — an update anomaly does exist, just not for Tom right now.
It is true that updating Tom's phone in L002 alone is currently safe because Tom appears in only one row. However, this does not mean the schema is free of update anomalies. Consider Sara (MemberID M01): she appears in rows L001 and L003. If Sara's phone number changed, both rows would need to be updated. Missing one would create an inconsistency. The potential for inconsistency is a structural property of the schema, not a property of the specific current data. The schema suffers from an update anomaly by design — the fact that it is not triggered for Tom at this moment does not eliminate the anomaly.
Key analytical insight: Anomalies are assessed at the schema level (what the structure allows or prevents), not just at the instance level (what the current data happens to look like).
Classification: Deletion Anomaly. Statement 3 is correct.
Row L002 is the only row in the table that contains information about BookISBN 978-2 (SQL Mastery by Date). Deleting L002 to record Tom's return of the book is a legitimate and necessary operation. However, because book facts are stored within loan rows rather than in a separate books table, this deletion permanently removes all knowledge that the book exists. The library's catalog effectively loses a title as a side effect of a routine loan-return operation. This is a deletion anomaly: deleting one kind of data (a loan record) unintentionally destroys a different kind of data (a catalog entry).
| Statement | Correct Classification | Is the Statement's Claim Accurate? | Key Reason |
|---|---|---|---|
| 1 | Insertion Anomaly | Yes | New book cannot be stored without a loan transaction to attach it to |
| 2 | Update Anomaly (schema-level) | No — anomaly exists at schema level even if not triggered for Tom now | Sara's duplicate phone data shows the structural vulnerability; anomalies are schema properties |
| 3 | Deletion Anomaly | Yes | Deleting Tom's loan row destroys the only record of book ISBN 978-2 |