SAINT COLUMBAN COLLEGE

COLLEGE OF BUSINESS EDUCATION

ACCOUNTING PROGRAM
ACCOUNTING INFORMATION SYSTEM
2ND Semester, S.Y. 2025-2026

JOHN KHELVIN A. ORBETA, CPA, CTT

HANDOUT 2604: Analysis of Relational Database Systems in Accounting
Information Systems

Foundations of Database Systems

Database systems were developed to resolve the inefficiencies of legacy file-

oriented systems. In traditional environments, organizations created new master
files for every new information need, leading to a proliferation of redundant data.

From Files to Databases

Computer systems store data in a hierarchical structure:

 Entity: Anything about which the organization wishes to store data (e.g.,
"Students").
 Attributes: Specific characteristics of an entity (e.g., Student ID, Last
Name).
 Fields: The physical storage location for attributes.
 Record: All fields containing data about a specific occurrence of an entity
(e.g., data for one specific student).
 File: A set of all related records.
 Database: A set of interrelated, centrally coordinated files.

The Database System Framework

A complete database system consists of three integrated components:

1. The Database: The physical collection of interrelated files.

2. Database Management System (DBMS): The software interface between
the database and application programs.
3. Application Programs: Programs that access the database through the
DBMS to perform business functions.

The Database Administrator (DBA) is the individual responsible for coordinating,

controlling, and managing the database. Large-scale implementations used for
historical analysis are referred to as data warehouses.

Advantages of the Database Approach

The transition to database technology provides several key benefits to

organizations:

 Data Integration: Combining master files into larger pools accessible by

many programs.
 Data Sharing: Integrated data is more easily shared among authorized
users.
 Minimal Redundancy and Inconsistency: Data items are typically stored
only once, reducing the risk that different files will contain conflicting
information.
 Data Independence: Data is independent of the programs that use it,
allowing for easier programming and simplified data management.

1
  Reporting Flexibility: Reports can be easily revised, and the database can
be browsed to research specific problems.
 Cross-Functional Analysis: Relationships between data points (e.g., selling
costs vs. promotional campaigns) can be explicitly defined for management
reporting.

Logical and Physical Views of Data

A hallmark of modern database systems is the separation of how data is stored from
how it is used.

The Two Views

 Logical View: How the user or programmer conceptually organizes and

understands the data.
 Physical View: How and where the data are physically arranged and stored
on disks.

The DBMS handles the mapping between these views, allowing users to query data
without needing to know its physical location or record layout.

Database Schemas

A schema describes the logical structure of a database across three levels: |

Schema Level | Description | | :--- | :--- | | Conceptual Level | The organization-
wide, "big picture" view of the entire database, listing all data elements and their
relationships. | | External Level | A set of individual user views of portions of the
database, often referred to as subschemas. | | Internal Level | A low-level view
describing how data are actually stored and accessed (record layouts, addresses,
indexes). |

Accountants frequently participate in developing conceptual- and external-level

schemas to ensure data access is limited to what is relevant for specific job
functions.

Key DBMS Components and Languages

The DBMS utilizes specific tools and languages to manage and interrogate the data.

The Data Dictionary

The data dictionary is a fundamental component containing information about the

structure of the database. For every data element, it stores:

 Descriptions and explanations.

 Sources and authorized users.
 Programs and outputs where the element is used.
 Field lengths and types.

DBMS Languages

Every DBMS provides specialized commands to perform three basic functions:

1. Data Definition Language (DDL): Used to build the data dictionary,

initialize the database, and specify security constraints.
2. Data Manipulation Language (DML): Used for data maintenance,
including updating, inserting, and deleting records. Access is typically
restricted to administrators and programmers.
3. Data Query Language (DQL): Provides powerful, easy-to-use commands
for retrieving, sorting, and ordering records to satisfy user information needs.

2
Additionally, many packages include a Report Writer, which simplifies the
extraction and formatting of data into printed reports.

The Relational Database Model

Developed by E.F. Codd in 1970, the relational model represents all data in the form
of tables, also known as relations.

Structural Elements

 Rows (Tuples): Each row represents a specific occurrence of an entity.

 Columns (Attributes): Each column contains information about a specific
characteristic of the entity.

Primary and Foreign Keys

Tables are linked together through the use of keys:

 Primary Key: The attribute (or combination of attributes) that uniquely

identifies a specific row in a table.
 Foreign Key: An attribute in one table that serves as a primary key in
another table. These are used to create links between entities.

Database Integrity and Design

To be well-structured and free from anomalies, a relational database must follow

specific rules.

Basic Requirements (The Normalization Rules)

1. Single-Valued Cells: Every column in a row must be single-valued (no

repeating groups).
2. Entity Integrity Rule: The primary key cannot be null and must be unique
for every record.
3. Referential Integrity Rule: A foreign key must either be null or correspond
to a value of a primary key in another table.
4. Descriptive Non-Key Attributes: All non-key attributes must describe a
characteristic of the object identified by the primary key.

Avoiding Design Anomalies

Improperly designed tables (such as storing all data in one large uniform table) lead
to three types of anomalies:

 Update Anomaly: When a data change (like a phone number update) must
be made in multiple places, leading to potential inconsistencies.
 Insert Anomaly: When a new record cannot be added because only part of
the data is available (e.g., a student who hasn't registered for classes yet).
 Delete Anomaly: When deleting a row results in the unintended loss of all
information about an entity (e.g., losing all student data when a student
drops their last class).

Design Methodologies

There are two primary ways to design a relational database:

 Normalization: Starting with a large table and following rules to decompose

it into a set of tables in third-normal form (3NF).

3
  Semantic Data Modeling: Using knowledge of business processes to create
a graphical representation of the database, which is then used to generate
3NF tables.

The Future of Accounting and Database Systems

The integration of database technology is poised to fundamentally alter the

accounting profession:

 Redundancy Reduction: The inherent efficiency of databases may lead to

the abandonment of traditional double-entry accounting, as the redundancy
of double entries is not required for computer processing.
 External Reporting: External users may eventually gain direct access to
company databases to manipulate data for their own specific reporting
needs.
 Enhanced Decision Making: The ability to integrate financial and
operational data with powerful query capabilities will improve the speed and
quality of business insights.

Accountants must remain involved in the development of these systems to ensure

the inclusion of adequate internal controls to safeguard organizational data.