THE CPA AND THE COMPUTER

July 2002

Designing Relational Database Systems

By Thomas Tribunella

Most accounting information systems and enterprise resource planning systems rely on databases to store and retrieve information. A single database can supply information to accounting, operations, marketing, personnel, and sales. Many of these functional areas may have their own specialized software applications, but pull their data from one general database. Accordingly, this database should be maintained separately and independently from other functional system applications.

The benefits of having one database for all applications are substantial. When different functional areas manage their own specialized databases, the replication of effort is expensive. Data redundancy can lead to data inconsistency. Errors result when obsolete data is used for management decisions. Accordingly, data should be stored and maintained independently, so that updates, manipulations, and deletions occur in only one place.

Relational Database Systems

The relational model structures data into tables that are flexible enough to allow the data to be manipulated into almost any configuration. The relational model decreases data redundancy and increases data integrity. The following terms and definitions are important to remember:

Under the relational paradigm, data are viewed as two-dimensional tables. In relational DB modeling, tables relate to each other by sharing a common field. Properties of relational databases include the following:

In addition to the above properties, relational databases are normalized. Normalization is the process of creating stable data structures from complex groups of data and dividing redundant data into separate tables and establishing their relationships. Normalization has six levels; a DB in the sixth normal form is as close to perfect as one can get. The more normalized the DB is, the more flexible it is, allowing a wider range of queries and manipulations.

Entity Relationship Diagrams

Database consulting, system implementation, and employee training are expensive. An entity relationship (ER) diagram is a useful technique for managing the development of a database information system. ER diagrams systematically model data into logical and easy-to-understand graphical representations. Exhibit 1 contains an explanation of ER diagramming symbols.

The 10-Step Process

Diagramming and modeling a DB is a 10-step process. The first five consist of drawing the ER diagram:

1. Identify DB entities.
2. Map relationships between the entities.
3. Identify attributes of the entities.
4. Reduce all attributes to atomic values.
5. Select attributes for primary keys.
At this point the ER diagram is complete. The next five steps model the data tables so they conform to relational properties:
6. Build data tables (relations) from the ER diagram constructed in steps 1 to 5.
7. Eliminate or modify all attributes (columns) that are sequentially ordered.
8. Eliminate or modify all records (rows) that are sequentially ordered.
9. Eliminate all redundant attributes and records.
10. Construct a data dictionary.

Database Design Example

A small wine distributor needs to keep track of wine suppliers and products. The wine distributor is trying to manage company data with the poorly designed DB in Exhibit 2.

The database in this exhibit has several problems:

Applying an ER diagram (steps 1–5) and a logical data model that adheres to relational properties (steps 6–10) will solve these problems.

The data domain is modeled with the ER diagram in Exhibit 3, which is converted into the data tables in Exhibit 4 and Exhibit 5.
The data tables’ remaining problems can be solved by modeling them to conform to relational properties (steps 6–10).

In Exhibit 6 and Exhibit 7 all of the original data are still present; however, all redundant data have been eliminated. The supplier table relates to the product table through the Supplier Number field.

Data Dictionary

The final step of the design is to construct a data dictionary (DD). The DD states the properties of each field and enables the DB management system to understand how to store, display, receive, and process the data fields (Exhibit 8). The DD prevents input errors by specifying the data type and the range of valid values for each field. Exhibit 8 is an example of a small DD that would define the data fields used in the wine distributor’s supplier DB.


Thomas Tribunella, PhD, CPA, is the director of accounting programs at the State University of New York–Institute of Technology and an information systems consultant.

Editors:
Paul D. Warner, PhD, CPA
Hofstra University

L. Murphy Smith, DBA, CPA
Texas A&M University


This Month | About Us | Archives | Advertise| NYSSCPA


The CPA Journal is broadly recognized as an outstanding, technical-refereed publication aimed at public practitioners, management, educators, and other accounting professionals. It is edited by CPAs for CPAs. Our goal is to provide CPAs and other accounting professionals with the information and news to enable them to be successful accountants, managers, and executives in today's practice environments.


©2002 CPA Journal. Legal Notices

Visit the new cpajournal.com.