Question 1
A school tuck shop stores all sales data in a single flat-file table: `SaleDraft(SaleID, StudentID, StudentName, StudentEmail, ItemCode, ItemName, ItemPrice, Quantity, SaleDate)`. A student changes their email address. What problem does this flat-file design cause, and what is the correct solution?No clue? Show me the answer
Correct answerCorrect!
IncorrectStep-by-step walkthrough
Choose a solution method
Method #1Approach 1Step 1: Identify the scenario
A student's email appears in every row of the flat-file table for every item they have purchased. When the email changes, every one of those rows needs to be updated.
Step 2: Apply the definition of update anomaly
An update anomaly occurs when a single real-world change (one student's email) requires updates to multiple rows. If even one row is missed, the database contains contradictory data about the same student.
Step 3: Apply the relational solution
The fix is normalisation: move student data (StudentID, StudentName, StudentEmail) into its own
Studenttable. TheSaleDrafttable retainsStudentIDas a foreign key, so each student's email is stored exactly once.Step 4: Select the correct answer
The answer that correctly names the update anomaly and proposes a separate Student table with a foreign key is the correct choice.
Method #2Approach 2Step 1: Identify what is being asked
The question asks which database anomaly is caused by changing an email stored in a flat-file table, and what the correct fix is.
Step 2: Eliminate 'No problem occurs'
'No problem occurs because SaleID uniquely identifies each sale row' is incorrect. SaleID makes rows unique but does not prevent the same email from appearing in many rows — the anomaly still exists.
Step 3: Eliminate the deletion anomaly option
'A deletion anomaly occurs — deleting the student record removes all sale records' describes a real anomaly type, but it is not triggered by changing an email. Deletion anomalies arise when removing a record, not updating one.
Step 4: Eliminate the insertion anomaly option
'An insertion anomaly occurs — the email cannot be stored until a sale is made' is also a real anomaly, but again it describes adding new data, not modifying existing data. The scenario specifically involves updating an email.
Step 5: Select the correct answer
The remaining option correctly identifies this as an update anomaly and provides the correct solution: a separate Student table with a foreign key reference.
Question 2
An online bookshop database includes the following tables: `Customer(CustomerID, Name, Email)`, `Order(OrderID, CustomerID, OrderDate)`, `OrderLine(OrderID, BookID, Quantity)`, and `Book(BookID, Title, Price)`. The `OrderLine` table has a composite primary key of `(OrderID, BookID)`. Which statement about the `OrderLine` table is correct?No clue? Show me the answer
Correct answerCorrect!IncorrectStep-by-step walkthrough
Choose a solution method
Method #1Approach 1Step 1: Recall the purpose of a composite primary key
A composite primary key is used when no single column is sufficient to uniquely identify a row. It combines two or more columns whose values together are unique for every record.
Step 2: Apply the concept to OrderLine
In
OrderLine, a singleOrderIDcan appear many times (one row per book in the order). A singleBookIDcan also appear many times (the same book purchased in many orders). Neither column is unique on its own.Step 3: Confirm the composite key works
The combination
(OrderID, BookID)is unique: a specific order can only contain any specific book once as a line item. This makes the combination a valid composite primary key.Step 4: Select the correct answer
The option stating that
(OrderID, BookID)is correct because the same book can appear in different orders and an order can have many books correctly explains why a composite key is needed.Method #2Approach 2Step 1: Identify what is being asked
The question tests understanding of why a composite primary key is used in a junction/line-items table.
Step 2: Eliminate 'OrderID alone is sufficient'
'OrderID alone can uniquely identify every row' is incorrect. One order contains multiple books, so
OrderIDrepeats for each book — it is not unique withinOrderLine.Step 3: Eliminate 'BookID should be the sole primary key'
'BookID uniquely identifies each book' is true in the
Booktable, but inOrderLine, the same book can appear in many orders —BookIDis not unique in this table.Step 4: Eliminate 'no primary key needed'
'OrderLine does not need a primary key' violates entity integrity — every table must have a primary key. This option is incorrect on a fundamental design principle.
Step 5: Select the correct answer
The option explaining that
(OrderID, BookID)together uniquely identify each line item is the only logically sound explanation.