Relational database design is one of those topics that seems simple at first, right up until you put customer names, phone numbers, addresses, vehicles, orders, and three copies of the same information into one giant table. Then things get ugly fast. This quiz will test the core ideas behind relational databases, table relationships, and junction tables.
Give each question a moment before looking at the answer. Keep score if you like. If you get all five correct, congratulations: you may keep your database designer badge. If not, no worries. These are foundational concepts, and getting them right will save you a whole lot of headaches later.
Question 1: What is a relational database?
A) Tables connected by relationships
B) A database with one table
C) A spreadsheet with worksheets
D) A database on the internet
Answer: A) Tables connected by relationships.
A relational database is built from separate tables that are connected using relationships. Instead of cramming customers, vehicles, repairs, invoices, and payments into one mega-table from the depths of database despair, you store each type of information in its own appropriate table.
For example, you might have a Customer table and a Vehicle table. The Customer table stores information about people. The Vehicle table stores information about cars. A relationship tells Access which customer owns which vehicle. That is the basic idea behind a relational database, and it is a huge part of what makes Access useful.
Question 2: What does the Vehicle table use to identify the owner of the vehicle?
A) The customer name
B) The customer ID
C) The customer's address
D) The customer's phone number
Answer: B) The customer ID.
The Vehicle table should store the CustomerID, not the customer's name, address, or phone number. Those details belong in the Customer table.
Why? Because names can be misspelled, changed, duplicated, or entered differently by different people. "Joe Smith," "Joseph Smith," and "J. Smith" might all be the same person, but Access has no magical mind-reading function. A CustomerID is unique and consistent. It gives every customer one reliable identity in your database.
Once the Vehicle table has the CustomerID, you can use a query, form, or report to pull in the customer's name and other details whenever you need them. Store the information once, then look it up. That is the whole point.
Question 3: Joe Smith buys a second car. What is the best design?
A) Add another Joe Smith record
B) Store Joe once and link both cars
C) Create a new customer table
D) Put both cars in one field
Answer: B) Store Joe once and link both cars.
Joe belongs in the Customer table one time. If he owns two cars, each car gets its own record in the Vehicle table, and both vehicle records use Joe's CustomerID.
Creating duplicate customer records is one of the classic beginner mistakes. It works for about five minutes, until Joe changes his phone number and you update one record but forget the other. Now your database has conflicting information, and nobody knows which phone number is correct. That is how databases become haunted.
Likewise, do not put multiple cars in one field. A field should hold one piece of information, not a shopping list. Keep each vehicle in its own record. That makes searching, sorting, reporting, and maintaining the data much easier.
Question 4: Customers and orders usually have what kind of relationship?
A) One-to-one
B) One-to-many
C) Many-to-many
D) A self-join
Answer: B) One-to-many.
A customer can place many orders over time. However, each individual order normally belongs to one customer. That makes the relationship between Customers and Orders a one-to-many relationship.
In Access terms, the Customer table is on the "one" side, and the Order table is on the "many" side. The Order table contains a CustomerID field that points back to the customer who placed that order.
This is probably the most common relationship type you will use in a real-world database. One customer can have many orders. One customer can have many vehicles. One customer can have many appointments, invoices, payments, notes, or whatever else your business needs to track.
Question 5: What relationship uses a junction table?
A) One-to-one
B) One-to-many
C) Many-to-many
D) Parent-to-child
Answer: C) Many-to-many.
A many-to-many relationship exists when records on both sides can relate to multiple records on the other side. For example, suppose one driver can drive several vehicles, but each vehicle can also be driven by several different drivers. You cannot handle that cleanly with just a Customer table and a Vehicle table.
That is where a junction table comes in. You might create a DriverVehicle table containing a DriverID and a VehicleID. Each record in that table represents one specific connection between one driver and one vehicle.
The junction table turns one many-to-many relationship into two one-to-many relationships. One driver can have many records in the junction table, and one vehicle can also have many records in the junction table. It may sound a little strange at first, but once you understand it, junction tables become one of the most useful tools in relational database design.
So, how did you do? If you got all five, you have a solid handle on the basics of relational database design. If you missed a few, that is exactly why quizzes like this are useful. Relationships, primary keys, foreign keys, and junction tables are not just theory. They are what keep your Access database organized, accurate, and much easier to maintain.
Watch the embedded video for the full quiz and a quick walkthrough of each answer.
Live long and prosper,
RR
No comments:
Post a Comment