In this video, we walk through 6 ISC practice questions on relational database structure and normalization. These questions are from ISC content area 1 on the AICPA CPA exam blueprints: Information Systems and Data Management.
The best way to use this video is to pause each time we get to a new question in the video, and then make your own attempt at the question before watching us go through it.
Relational Database Structure and Normalization
When you examine a relational database, the goal is to see whether it keeps its data accurate, documents its own contents, and organizes data efficiently. That comes down to three areas: data integrity rules, the data dictionary, and normalization.
Data Integrity Rules
Data integrity rules are constraints built into the database that reject changes that would make the data wrong. There are four types.
Entity integrity requires every row to have a primary key that is unique and never blank. A vendor saved without a VendorID couldn’t be identified, and other tables would have no way to point to it.
Referential integrity requires every foreign key to point to a record that actually exists. If a customer is deleted while their orders stay in the Orders table, those orders now point to nothing. A database that enforces referential integrity would either block the deletion or remove the related orders along with the customer.
Domain integrity requires each column to accept only valid values of the right type. A date column set up as a text field, holding entries like “TBD” and “next week,” fails this.
User-defined integrity covers business rules specific to one organization, enforced through triggers or stored procedures. A rule that purchase orders over $10,000 need a second approver can’t be set up through standard column settings, but a trigger can check the amount and block the save when the rule isn’t met.
The Data Dictionary
A data dictionary is a central record of metadata, meaning information about the database itself. It documents each table, column, data type, rule, and relationship. It describes the data but doesn’t hold it, so it would list a Salary column as a numeric field without listing anyone’s salary.
To be useful, a data dictionary has to match the database as it exists today. If it still lists columns that were removed or leaves out ones that were added, people end up guessing what a column means by looking through its values.
Normalization
Normalization organizes tables so each piece of information is stored in only one place. When a fact is stored once, it only has to be updated once, and the data can’t end up contradicting itself. Normalization happens in steps called normal forms, and each one builds on the one before it.
First Normal Form
First normal form means each cell holds only one value. An Orders table with a Products cell reading “Stapler, Printer Paper, Toner” breaks this rule. The fix is a separate table that lists each product on its own row, along with the OrderID it belongs to, so every cell holds a single product.
Second Normal Form
Second normal form means every column that isn’t part of the primary key must depend on the entire key, whether the key is one column or two columns together.
Take a ProjectHours table where EmployeeID and ProjectID together make up the key. HoursWorked needs both: knowing only the project doesn’t tell you whose hours they are, and knowing only the employee doesn’t tell you which project. ProjectName needs only the ProjectID, since a project’s name is the same no matter who works on it. That’s why the name repeats on every row for every employee on the project, and why it belongs in a separate Projects table.
The IDs repeating isn’t a problem. An ID works as a pointer to where the information lives, not as a repeated fact.
Third Normal Form
Third normal form means no column that isn’t the key can depend on another column that isn’t the key.
In an Invoices table with InvoiceID as the key, InvoiceDate and CustomerID both describe the invoice. CustomerName doesn’t. If you know the CustomerID, you know the name without knowing anything about the invoice, so CustomerName belongs in a Customers table listed by CustomerID.
This is where third normal form goes past second. Because InvoiceID is a single column, the table passes second normal form automatically. Third normal form adds a check that second normal form never makes: whether any regular column depends on another regular column.
