Access Video Quiz X2.1
By Richard Rost

8 hours ago

How to Properly Normalize Customer Child Records

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

What is the main goal of normalizing an Access database? Welcome to another Microsoft Access Video Quiz. You will be asked five expert-level questions covering normalization, first normal form, related tables, calculated totals, and preserving order addresses. Answer each question, check your score, and see how well you know Microsoft Access.

Recommended Courses

Access Expert 2, Lesson 1

Want More?

Access Video Quizzes

Keywords

Access Video Quiz, database normalization, first normal form, relational database design, one-to-many relationship, foreign keys, child table design, driver table design, calculated fields, order line total, shipping address history, duplicate data reduction

Comments for Access Video Quiz X2.1

Age	Subject	From
17 hours	Post Your Quiz Score Here!	Richard Rost

More Information

Transcript

It's time for another video quiz from Access Learning Zone.

Today's quiz is for expert-level users. Five questions coming at you fast. Only gonna give you three seconds to answer each one. Feel free to hit the pause button if you need to. Keep track of your score. We'll see how you do at the end. Are you ready? Here we go.

Question one: What is the main goal of normalizing an Access database? To reduce duplicate data and improve data maintenance, to put all information into one large table, to replace tables at forms and reports, or to store every calculation permanently.

That's going to be A: to reduce duplicate data and improve data maintenance.

Question two: Which design best follows first normal form when tracking a customer's children?

A: You want Child One Name, Child Two Name, Child Three Name fields in the customer table.

B: You want one Children field containing all the children's names.

C: A Child T with one child per record linked to the customer table.

Or D: A separate customer record for each child.

That's gonna be C: a Child T with one child per record linked to the customer table. You got two kids, two child records. You got 16 kids, you got 16 child records.

Question three: A Car T car table includes Car ID, Make, Model, Driver ID, and Driver Name. Where should Driver Name normally be stored?

A: In Car T, because every car needs a driver name.

B: In Driver T with the other information about the driver.

C: In a calculated field in Car T.

Or D: In the primary key of Car T.

That's gonna be B: in Driver T with the other information about the driver. You make a driver table with all the information about the driver, you link the Driver ID back to the car table.

Question four: An order line has quantity and unit price. What is the best way to handle the line total?

Calculated in the query, form, or report when needed, store it as the primary key of Car T. You make a driver table with all the information about the driver, you link the Driver ID back to the car table. You can use the Driver ID to store it as the primary key of the order line, enter it manually in a text-only field, or put all order totals in the customer table.

That's gonna be calculated in a query, form, or report when needed. I'd like to do it in queries.

And question five: When might it be appropriate to store a customer's address with an order, even though the address is also in the Customer T?

When preserving the shipping address used at the time of the order, when Customer T does not have a primary key, when an order has only one product, or when the address is shorter than 20 characters.

That's A: when preserving the shipping address used at the time of the order, so you know where it went.

All right, so how'd you do? Did you get all five? Did you boot the whole LAN from one floppy disk, or was your C prompt waiting patiently?

If you missed any of these questions, I cover this in Access Expert Level 2, Lesson 1 on Normalization.

Thanks for playing. Be sure to post your score in the comments, and until next time, live long and prosper.

Intro

Quiz

Q1. What is the main goal of normalizing an Access database?
A. To reduce duplicate data and improve data maintenance
B. To put all information into one large table
C. To replace tables with forms and reports
D. To store every calculation permanently

Q2. 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

Q3. A Car table includes Car ID, Make, Model, Driver ID, and Driver Name. Where should Driver Name normally be stored?
A. In the Car table, because every car needs a driver name
B. In the 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

Q4. An order line has Quantity and Unit Price. 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

Q5. When might it be appropriate to store a customer's address with an order, even though the address is also in the Customer table?
A. When preserving the shipping address used at the time of the order
B. When the 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

Answers: 1-A; 2-C; 3-B; 4-A; 5-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

In today's Video Quiz, I have five expert-level Microsoft Access questions focused on database normalization and proper table design. Give yourself a few seconds to consider each question before reviewing the answer, and keep track of your score as you go.

Question 1: What is the main purpose of normalizing an Access database?

The correct answer is to reduce duplicate data and make the database easier to maintain. Normalization organizes information so that each fact is stored in the appropriate place, rather than repeated unnecessarily throughout multiple records. This helps prevent errors and makes updates more consistent.

Question 2: What is the best first normal form design for tracking a customer's children?

The correct answer is to create a separate ChildT table with one child per record, linked back to the customer using the customer ID. For example, if a customer has two children, that customer would have two related child records. If the customer has sixteen children, there would be sixteen child records.

You should not create fields such as Child1Name, Child2Name, and Child3Name in the customer table. You also should not place all of the children's names in a single field. Each child should be represented by an individual record in a related table.

Question 3: A CarT table contains CarID, Make, Model, DriverID, and DriverName. Where should DriverName normally be stored?

The correct answer is DriverT, along with the other information about that driver. The car table should store the DriverID as a foreign key that identifies the related driver record. The DriverT table should contain the driver's name, address, phone number, and other driver-specific details.

Storing DriverName in the car table would duplicate information. If a driver changes their name, you would have to update every car record associated with that driver. Keeping driver information in DriverT avoids that problem.

Question 4: An order line contains a quantity and unit price. What is the best way to handle the line total?

The correct answer is to calculate it when needed in a query, form, or report. The line total is derived from the quantity multiplied by the unit price, so it does not normally need to be stored permanently in the order line table.

I generally prefer calculating these values in queries. Storing calculated data can lead to errors if one of the underlying values changes and the stored total is not updated.

Question 5: When might it be appropriate to store a customer's address with an order, even though the address is also stored in CustomerT?

The correct answer is when you need to preserve the shipping address that was used at the time the order was placed. A customer may move or update their address later, but historical orders should still show the address where the shipment was originally sent.

This is an example of a situation where storing a copy of information is appropriate because the historical value matters. The order's shipping address is not simply a duplicate of the customer's current address. It represents the address associated with that specific transaction.

If you got all five questions correct, excellent work. If you missed any, I cover these normalization principles in Access Expert Level 2, Lesson 1 on Normalization.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Database normalization goals
First normal form for child records
Separating driver data into related tables
Calculating order line totals when needed
Storing order shipping address history

Article

This is a video quiz. No article needed.

Primary Topics

database normalization, reducing duplicate data, first normal form, one-to-many relationships, foreign keys, calculated fields, historical address storage

Secondary Topics

customer-child records, car-driver relationship, order line totals, shipping address snapshots