Ready to test your Access normalization knowledge? This expert-level quiz covers five common database design decisions that can make the difference between a clean, maintainable database and a future headache with 47 duplicate fields and no idea which one is correct.
Give yourself a few seconds for each question before revealing the answer. If you get all five, congratulations: your tables are probably less terrifying than most. If not, no worries. These are exactly the kinds of design choices that become much clearer once you understand why normalization matters.
Question 1: What is the main goal of normalizing an Access database?
A. Reduce duplicate data and improve data maintenance
B. Put all information into one large table
C. Replace tables with forms and reports
D. Store every calculation permanently
Answer: A. Reduce duplicate data and improve data maintenance.
Normalization is about organizing your data so that each fact has one proper home. If you store the same customer address, employee name, or product description in a dozen different places, eventually one copy will get changed while the others do not. Then you have conflicting information, confused users, and the usual database gremlins.
A normalized design reduces unnecessary duplication and makes updates easier. Change a driver's phone number once in the Driver table, for example, instead of changing it in every record related to that driver.
Question 2: Which design best follows first normal form when tracking a customer's children?
A. Child One Name, Child Two Name, and Child Three Name fields in the Customer table
B. One Children field containing all children's names
C. A Child table with one child per record linked to the Customer table
D. A separate Customer record for each child
Answer: C. Use a Child table with one child per record linked to the Customer table.
This is one of the classic normalization examples. Do not create fields such as Child1Name, Child2Name, Child3Name, and so on. What happens when someone has four children? Or six? Or sixteen? Do you keep adding columns until the table needs its own zip code?
Instead, create a Child table. Each child gets one record, and each record includes the CustomerID that identifies the parent or customer. A customer with two children has two child records. A customer with sixteen children has sixteen child records. No special fields, no arbitrary limits, and no stuffing multiple names into one field separated by commas.
Question 3: A Car table includes CarID, Make, Model, DriverID, and DriverName. Where should DriverName normally be stored?
A. In the Car table, because every car needs a driver name
B. In a Driver table with the other information about the driver
C. In a calculated field in the Car table
D. In the primary key of the Car table
Answer: B. Store DriverName in the Driver table.
The Car table should store the DriverID, which points to the driver assigned to that car. The driver's name, phone number, address, license information, and other driver-specific details belong in the Driver table.
Why? Because the driver's name is a fact about the driver, not a fact about the car. If a driver changes their name, you should update one Driver record. You should not have to hunt through every car record that happens to reference that person.
Question 4: An order line has Quantity and UnitPrice. What is the best way to handle the line total?
A. Calculate it in a query, form, or report when needed
B. Store it as the primary key of the order line
C. Enter it manually in a text-only field
D. Put all order totals in the Customer table
Answer: A. Calculate it in a query, form, or report when needed.
The line total is simply Quantity multiplied by UnitPrice. Since it can be calculated whenever you need it, it usually should not be stored permanently in the table. Storing calculated values creates another opportunity for bad data. Someone changes the quantity but forgets to update the total, and now the invoice is wrong.
Calculate it in a query, on a form, or in a report. Queries are usually my preferred place for calculations like this because the expression can be reused wherever you need it.
Question 5: When might it be appropriate to store a customer's address with an order, even though the address is also stored in the Customer table?
A. When preserving the shipping address used at the time of the order
B. When Customer table does not have a primary key
C. When an order has only one product
D. When the address is shorter than 20 characters
Answer: A. Store the address with the order when you need to preserve the address used at that time.
This is an important real-world exception. Normally, you do not want to duplicate customer address data everywhere. However, an order is a historical transaction. If the customer moves next year and updates their current address, you still need to know where Order #12345 was shipped last year.
That is why many order systems keep a shipping address, and sometimes a billing address, with the order itself. It is not careless duplication. It is preserving a snapshot of what was true when that transaction occurred.
So, how did you do? If you missed a couple, that is perfectly normal. Normalization takes a little practice because it requires you to think about what each field actually describes and where that information truly belongs. Watch the embedded video for the complete quiz walkthrough and a few more comments on each answer.
Live long and prosper,
RR
No comments:
Post a Comment