Relational Databases in Accounting Information Systems

Relational Databases in Accounting Information Systems

Introduction to Databases

Databases are essential tools in modern accounting information systems, enabling efficient storage, retrieval, and management of large volumes of data. Understanding how databases function and are structured is crucial for accountants and financial professionals.

• Database: A collection of related files that efficiently and centrally coordinates information.

• File: A related group of records.

• Record: A related group of fields.

• Field: A specific attribute of interest for the entity (record).

Advantages of Databases

Databases offer several advantages over traditional file-based systems, particularly in accounting environments where data integrity and accessibility are critical.

• Data Integration: Centralizes data, reducing duplication and improving consistency.

• Data Sharing: Multiple users can access and use the same data simultaneously.

• Minimized Redundancy: Reduces data repetition and inconsistencies.

• Program Independence: Data is independent of the programs that use it, allowing for flexible reporting and analysis.

• Easy Access: Facilitates reporting and cross-functional analysis.

Database Users and Designers

Different stakeholders interact with databases at various levels, each with distinct perspectives and requirements.

• Logical View (External Level): How users perceive and interact with the data.

• Physical View (Internal Level): How data is actually stored in the system.

• Conceptual Level: The overall organization-wide view, essential for database designers to understand user needs and system structure.

Schemas

Schemas define the structure and organization of a database at different levels.

• Conceptual-level schema: Organization-wide view of the entire database.

• External-level schema: Individual user's view of the data.

• Internal-level schema: Low-level view detailing how data is physically stored.

Database Design

Effective database design is foundational for accurate and efficient data management in accounting systems.

• Conceptual View: Illustrates different files and their relationships.

• Data Dictionary: A blueprint of the database structure, including data elements, field types, programs using the data, and outputs.

DBMS Languages

Database Management Systems (DBMS) use specialized languages to define, manipulate, and query data.

• Data Definition Language (DDL):

  • Builds the data dictionary

  • Creates the database

  • Describes logical views for each user

  • Specifies record or field security constraints

• Data Manipulation Language (DML):

  • Changes the content in the database (create, update, insert, delete)

• Data Query Language (DQL):

  • Enables users to retrieve, sort, and display specific data from the database

Relational Databases

Relational databases are the most common type used in accounting information systems, organizing data into related tables.

• Conceptual and External Schema: Represented as if all data is in one table, but actually stored in multiple related tables.

• Relational Model: Data is organized into tables (relations) that are linked by key fields.

Example: Conceptual View Table

Customer Name	Sales Invoice #	Invoice Total
D. Ainge	101	$1,447
G. Kite	102	$4,394
D. Ainge	103	$898
G. Kite	104	$789
F. Redetts	105	$3,994

Relational Data Tables Example

Instead of one large table, data is split into related tables such as Customers, Sales, and Inventory. These tables are linked by key fields (e.g., Customer #, Sales Invoice #).

Primary and Foreign Keys

• Primary Key: A unique identifier for each record in a table (e.g., Customer # in the Customers table).

• Foreign Key: A field in one table that links to the primary key in another table, establishing relationships between tables (e.g., Customer # in the Sales table).

Why Use Related Tables?

Storing all data in one large table can lead to inefficiencies and anomalies:

• Update Anomaly: Inconsistencies when updating data.

• Insert Anomaly: Difficulty adding new data due to missing information.

• Delete Anomaly: Unintended loss of data when deleting records.

Relational Database Design Rules

To ensure data integrity and efficiency, relational databases must follow certain rules:

• Every column in a row must be single valued.

• Primary key cannot be null (entity integrity).

• If a foreign key is not null, it must correspond to a primary key in another table (referential integrity).

• All other attributes in the table must describe characteristics of the object identified by the primary key.

Following these rules allows databases to be normalized, solving update, insert, and delete anomalies.

Queries

Queries allow users to extract specific information from a relational database without manually searching through all files.

• Example Query: What are the invoices of customer D. Ainge and who was the salesperson for those invoices?

Sample Query Result Table

Sales Invoice #	Salesperson	Customer Name
101	J. Buck	D. Ainge
103	S. Knight	D. Ainge

Key Terms

• Database Management System (DBMS): Software for creating, managing, and using databases.

• Data Dictionary: A detailed description of all data elements in the database.

• Normalization: The process of organizing data to minimize redundancy.

• Entity Integrity Rule: Ensures each record has a unique, non-null primary key.

• Referential Integrity Rule: Ensures foreign keys correspond to valid primary keys in related tables.

• Update/Insert/Delete Anomalies: Problems that arise from poor database design.

Additional info: Understanding relational databases is essential for accountants, as these systems underpin most modern accounting information systems and support accurate financial reporting, data analysis, and decision-making.