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?Move the address into a customer table and add a customer key to the orderReplace the address with a single free-text notes fieldKeep the address duplicated to avoid joins and improve performanceStore the address only in a spreadsheet outside the databaseClear my selection单项选择题
A
Move the address into a customer table and add a customer key to the order
B
Replace the address with a single free-text notes field
C
Keep the address duplicated to avoid joins and improve performance
D
Store the address only in a spreadsheet outside the database

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?Move the address into a customer table and add a customer key to the orderReplace the address with a single free-text notes fieldKeep the address duplicated to avoid joins and improve performanceStore the address only in a spreadsheet outside the databaseClear my selection单项选择题

A

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

B

Replace the address with a single free-text notes field

C

Keep the address duplicated to avoid joins and improve performance

D

Store the address only in a spreadsheet outside the database

BlackTom AI · Accepted Answer

Normalisation in relational databases aims to eliminate redundant data, prevent update anomalies, and ensure data integrity—so the best change is to move the address into a customer table and reference it from orders by a customer key.

1. Relevant knowledge points and concepts
- Redundancy and update anomalies: storing the same address in every order record causes inconsistencies when an address changes (multiple rows to update) and can lead to conflicting data.
- Normal forms (especially 2NF/3NF): separate entities (Customer, Order) into their own tables so attributes depend only on the primary key of their entity.
- Referential integrity and foreign keys: Orders should reference Customers via a customer_id to maintain a single authoritative address.

2. Step-by-step reasoning
- Current design: Orders table contains customer address fields for every order. That duplicates the same address across many order rows.
- Decision: Create a Customer table with customer_id, name, address, etc. Modify Orders to include customer_id (foreign key) instead of address fields.
- Resulting schema example:
  - Customers(customer_id PK, name, address, city, postcode, ...)
  - Orders(order_id PK, customer_id FK, order_date, total, ...)
- Now each customer address is stored exactly once; orders refer to it by customer_id.

3. Intermediate steps and effects
- Update: Change address in one Customers row; all orders automatically reflect the new address via the join.
- Storage: Reduces storage for repeated addresses.
- Integrity: DB can enforce foreign key constraints and prevent orphaned orders.

4. Why the correct answer is correct
- Moving the address to a Customer table removes redundancy, avoids update/insert/delete anomalies, and models the real-world one-to-many relationship (one customer, many orders). It follows normalization principles and improves maintainability without significant performance downsides (indexes and optimized joins mitigate join cost).

5. Why other options are incorrect
- Replace with a free-text notes field: still stores the address repeatedly and loses structured data, making queries and validation difficult.
- Keep duplication to avoid joins: premature optimization; modern DBs handle joins efficiently, and the cost of data inconsistency outweighs join overhead.
- Store address in a spreadsheet outside the database: breaks transactional integrity, security, and won’t enforce referential constraints or scale.

6. Conclusion
Moving the address into a customer table and adding a customer key to the order is the correct design change; this matches the provided answer.