The CPA Journal

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:

Data: known facts that can be defined and recorded
Database (DB): a collection of related data that should be maintained independent of the applications that use the data
Field: a string of related characters, such as a customer name
Relation: a table in a DB containing a group of related fields
Entity: an object represented in a DB (e.g., suppliers, customers, and products could be modeled as DB entities)
Relationship: an association between entities
Data type: specifies the type of values that can be entered into a field in a DB table
Record: a row in a DB table that is an instance of a data type (e.g., Smith Winery is an instance of the data type Wine Supplier Name)
Attribute: a property that describes an entity (e.g., Supplier Name and Supplier Number are attributes that describe the entity Supplier). Attributes become columns in a DB table.
Atomic: the smallest single value of an attribute that cannot be divided any further without losing meaning
Primary Key: a unique attribute that can identify each record in a DB table.

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:

There are no duplicate records (rows in a DB table).
Records are unordered and identified by a key.
There are no duplicate attributes (columns in a DB table).
Attributes are unordered.
All fields contain atomic data.
Each record has a unique primary key.
Relations (DB tables) do not contain data from multiple entities.

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:

The relation (data table) contains data from multiple entities.
Contact information is not atomic.
There is no primary key that uniquely identifies each row (record) in the table.
Columns (attributes) have sequential letters that are irrelevant to the relational database.
Rows (records) have sequential numbers that are irrelevant to the relational database.
The supplier name, contact information, and supplier number columns contain too much redundant data. For example, updating the contact information for Smith Winery would require four entries, which is inefficient and will lead to inconsistency.

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

Identify entities: Suppliers and products are the entities. The ER diagram entities will be converted into two separate data tables.
Map the relationship between entities (relationship = supply).
Identify attributes of entities: supplier attributes = supplier number, name, contact information; product attributes = product, supplier number, price.
Reduce all attributes to atomic values: The contact information attribute must be split into two attributes, voice and e-mail.
Select attributes for primary keys (PK): Supplier PK = supplier number; Product PK = product.

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).

Build data tables (relations) from ER diagram (see Exhibit 4 and Exhibit 5).
Eliminate or modify attributes (columns) that are sequentially ordered: Delete the column letters.
Eliminate or modify records (rows) that are sequentially ordered: Delete the column containing row numbers.
Eliminate all redundant attributes and records: Delete the redundant Supplier records in Exhibit 4.
Construct the Data Dictionary (see below).

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.

Visit the new cpajournal.com.