Question at position 9 A student designs a customer database in which the customer’s address is duplicated in every order record. Based on understanding keys and normalisation, which design change would most improve this structure?Store the address only in a spreadsheet outside the databaseKeep the address duplicated to avoid joins and improve performanceMove the address into a customer table and add a customer key to the orderReplace the address with a single free-text notes fieldClear my selection单项选择题
A
Store the address only in a spreadsheet outside the database
B
Keep the address duplicated to avoid joins and improve performance
C
Move the address into a customer table and add a customer key to the order
D
Replace the address with a single free-text notes field

Question

Question at position 9 A student designs a customer database in which the customer’s address is duplicated in every order record. Based on understanding keys and normalisation, which design change would most improve this structure?Store the address only in a spreadsheet outside the databaseKeep the address duplicated to avoid joins and improve performanceMove the address into a customer table and add a customer key to the orderReplace the address with a single free-text notes fieldClear my selection单项选择题

A

Store the address only in a spreadsheet outside the database

B

Keep the address duplicated to avoid joins and improve performance

C

Move the address into a customer table and add a customer key to the order

D

Replace the address with a single free-text notes field

BlackTom AI · Accepted Answer

Normalization principles require removing repeated facts into their own tables so each real-world entity is stored once. A customer’s address is an attribute of the customer entity, not of each order, so it should be stored in a customer table and referenced by orders via a customer key (foreign key). This design uses primary keys and foreign keys to enforce referential integrity and eliminates update, insertion and deletion anomalies.

1. Relevant concepts
- Primary key: unique identifier for a record (e.g., CustomerID).
- Foreign key: a field in one table that refers to the primary key of another (e.g., Order.CustomerID → Customer.CustomerID).
- Normalization (1NF–3NF): reduce redundancy, ensure each fact is stored once, and avoid anomalies.
- Anomalies: if address duplicated in orders, updating an address requires changing many rows (update anomaly); deleting all orders could lose customer address (deletion anomaly); adding a new customer without an order may be awkward (insertion anomaly).

2. Step-by-step reasoning
- Current design: address duplicated in every order row → redundancy.
- Problems: extra storage, inconsistent addresses if one order is updated but others are not, complex updates.
- Normalized design: create Customer table with CustomerID and Address fields. In Order table, replace address fields with CustomerID.
- Result: single place to update addresses, referential integrity ensures orders link to existing customers, easier queries about customers, clearer schema.

3. Intermediate logical steps
- Identify repeated attribute (address) → determine owning entity (customer) → create Customer(CustomerID, Address, ...) → modify Order(OrderID, CustomerID, OrderData...) → enforce foreign key constraint.

4. Why the correct answer is correct
Moving the address into a Customer table and adding a customer key to Order conforms to normalization rules and relational design best practices. It prevents inconsistencies and reduces redundancy while keeping data structured for queries.

5. Why other options are incorrect
- Storing the address in a spreadsheet outside the database breaks integrity, transactional consistency and makes joins/manual sync necessary.
- Keeping duplicated addresses to avoid joins sacrifices data integrity and causes anomalies.
- Replacing address with a free-text notes field removes structured data, preventing reliable querying, validation, and format consistency.

Conclusion: The best design change is “Move the address into a customer table and add a customer key to the order,” which matches the provided answer.