In this video, we walk through 5 AUD practice questions teaching about the components of a relational database. These questions are from AUD content area 3 on the AICPA CPA exam blueprints: Performing Further Procedures and Obtaining Evidence
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.
Also be sure to watch one of our free webinars on the 6 “key ingredients” to an extremely effective & efficient CPA study process here…
The Components of a Relational Database
A relational database is a structured system that organizes data into tables with clearly defined relationships between them. This structure ensures efficient data storage, retrieval, and integrity. Relational databases are widely used in businesses, finance, healthcare, and many other industries to manage structured data efficiently. Each component plays a specific role in maintaining the organization and reliability of the data stored. Below is a detailed explanation of these components with multiple real-world examples.
Tables and Records
Tables are the foundation of a relational database. Each table represents a specific category of data, such as customers, products, or orders. Within these tables, records (or rows) store individual pieces of data. Every row in a table represents a single entity, and every table focuses on a specific subject.
A table consists of multiple rows and columns. Rows, also called records, contain the actual data entries, while columns define what type of data can be stored.
Example 1: In an Employee table, each row represents a different employee, storing details like EmployeeID, Name, Department, and Salary. Each employee is a unique record within this table.
Example 2: A Sales table in a retail business could store individual sales transactions, each represented by a row with SaleID, Date, CustomerID, and TotalAmount. Every new sale creates a new row in the table.
Example 3: A Library Books table might include records for each book with details like BookID, Title, Author, and PublicationYear. Every new book added to the library is a separate row.
Fields (Attributes)
Fields, also known as attributes, define the columns in a table. Each field holds a specific type of information and determines the kind of data that can be stored in that column. Every column in a table represents a unique field and contributes to the complete information for each record.
Example 1: In a Students table, fields might include StudentID, FirstName, LastName, Email, and EnrollmentDate. Each of these fields provides specific details about a student.
Example 2: In an Inventory table, fields could include ItemID, ItemName, Category, Quantity, and Price, allowing a business to track stock levels and costs.
Example 3: A Vehicle Registration table may have fields like LicensePlate, OwnerName, VehicleMake, Model, and Year, helping a DMV manage vehicle records efficiently. Each field holds unique details about the registered vehicles.
Primary and Foreign Keys
A primary key is a unique identifier for each record in a table. It ensures that no two rows have the same key value, preventing duplicate records.
- Example 1: OrderID in an Orders table serves as the primary key to distinguish one order from another.
- Example 2: StudentID in a Students table uniquely identifies each student in an academic system.
- Example 3: InvoiceNumber in an Invoices table ensures each invoice is uniquely recorded.
A foreign key is a field that links to a primary key in another table, creating a relationship between tables.
- Example 1: In a Payments table, OrderID can act as a foreign key to link payments to their respective orders in the Orders table.
- Example 2: A ClassEnrollments table that links students to classes might have StudentID (foreign key) referencing the Students table and ClassID (foreign key) referencing the Classes table.
- Example 3: A Flight Reservations table might use PassengerID as a foreign key to reference the Passengers table and FlightID to reference the Flights table.
These relationships help maintain data integrity by preventing orphaned records. For instance, an order record should not exist without an associated customer, and a student enrollment should not exist without a valid student and class.
Normalization
Normalization is the process of organizing data to reduce redundancy and improve integrity. It breaks large tables into smaller, related tables and links them using keys. This structure prevents duplicate data and makes updates more efficient.
Normalization follows different levels (normal forms) to achieve a more structured database design. The first normal form (1NF) ensures that each column contains atomic values, meaning data is stored in the smallest possible units. The second normal form (2NF) removes partial dependencies, ensuring that all non-key attributes depend entirely on the primary key. The third normal form (3NF) removes transitive dependencies, ensuring that non-key attributes are independent of each other.
Example 1: Instead of storing supplier details in every product entry, a database can have a separate Suppliers table and reference it in the Products table using a SupplierID foreign key. This ensures that if supplier details change, only one record needs to be updated rather than multiple product entries.
Example 2: A company’s Employee table may originally contain department details for each employee. Instead of repeating department names, the database can have a separate Departments table where DepartmentID is referenced in the Employee table.
Example 3: Splitting an Orders table into an Orders table (storing general order details) and an OrderDetails table (storing individual items within each order) avoids repeating order-level data for each item purchased.
Why Relational Databases Matter
Relational databases provide a reliable way to store and manage structured data. They ensure data consistency, minimize redundancy, and allow efficient data retrieval through relationships between tables.
Example 1: A hospital database can store patient details in a Patients table, doctor details in a Doctors table, and appointment records in an Appointments table. Linking them using PatientID and DoctorID ensures accurate record-keeping and quick retrieval of information.
Example 2: In an e-commerce database, tables like Users, Orders, Products, and Payments work together to track customer transactions without storing redundant data.
Example 3: A university’s Courses table, Professors table, and Students table work together to ensure that course enrollments are properly linked to instructors and students, preventing duplicate records and maintaining consistency.
By understanding these components, businesses and professionals can maintain well-organized and scalable databases that support efficient decision-making and operations.
