CHAPTER 9 THE DATABASE APPROACH

DATABASE MANAGEMENT SYSTEMS Figure 9-2(a) presents a simple overview of the database approach with the
same users and data requirements. The most obvious change from the flat-
Overview of the Flat-File versus Database Approach file model is the pooling of data into a common database that is shared by
Many so-called legacy systems are characterized by the flat-file approach all the users.
to data management. In this environment, users own their data files.
Exclusive ownership of data is a natural consequence of two problems
associated with the legacy-system era. The first problem is a business
culture that erects barriers between organizational units that inhibit
entity-wide integration of data. The second problem stems from
limitations in flat-file management technology, which require data files to
be structured to the unique needs of the primary user.

FLAT-FILE PROBLEMS SOLVED

Data sharing (the absence of ownership) is the central concept of
the database approach. Let’s see how this resolves the problems
identified.
 No data redundancy. Each data element is stored only once,
thereby eliminating data redundancy and reducing storage
costs.
 Single update. Because each data element exists only in one
In the figure, the file contents are represented conceptually with letters. place, it requires only a single update procedure. This reduces
Each letter could signify a single data attribute (field), a record, or an entire
the time and cost of keeping the database current.
file. Note also that data element B is present in all user files. This is called
data redundancy and is the cause of three types of data management
 Current values. A change any user makes to the database
problems: data storage, data updating, and currency of information. Each yields current data values for all other users.
of these, as well as a fourth problem—task-data dependency, which is not  Task-data independence. Users have access to the full domain
directly related to data redundancy—is examined next. of data available to the firm. As users’ information needs
expand beyond their immediate domain, the new needs can
 DATA STORAGE. An efficient information system captures and be more easily satisfied than under the flat-file approach.
stores data only once and makes this single source available to Only the limitations of the data available to the firm (the
all users who need it. This is not possible in the flat-file entire database), and the legitimacy of their need to access it,
environment. To meet the private data needs of users, constrain users.
organizations must incur the costs of both multiple collection
and multiple storage procedures. Indeed, some commonly CONTROLLING ACCESS TO THE DATABASE
used data may be duplicated dozens, hundreds, or even The database approach places all the firm’s information eggs in one basket.
thousands of times, creating excessive storage costs. It is essential, therefore, to take very good care of the basket. The example
in Figure 9-2(a) has no provision for controlling to the database. Assume
Data X contain sensitive, confidential, or secret information that only User
 DATA UPDATING. Organizations have a great deal of data 3 is authorized to access. How can the organization prevent others from
stored on master files and reference files that require periodic gaining unauthorized access to it?
updating to reflect operational and economic changes. For
example, a change in a customer’s name or address must be THE DATABASE MANAGEMENT SYSTEM
reflected in the appropriate master files. This piece of
information may be important to several user departments in
the organization, such as sales, billing, credit, customer
services, sales promotion, and catalog sales. When users
maintain separate files, any such change must be made
separately for each user. This adds significantly to the cost of
data management.

 CURRENCY OF INFORMATION. In contrast to the problem of

performing multiple updates is the problem of failing to
update the files of all users affected by a change. If update
messages are not properly disseminated, then some users Figure 9-2(b) adds a new element to Figure 9-2(a). Standing between the
may not record the change and will perform their duties and users’ programs and the physical database is the database management
system (DBMS). The purpose of the DBMS is to provide controlled access
make decisions based on out- dated data.
to the database. The DBMS is a special software system that is
programmed to know which data elements each user is authorized to
 TASK-DATA DEPENDENCY. Another problem with the flat-file access. The user’s program sends requests for data to the DBMS, which
approach is the user’s inability to obtain additional validates and authorizes access to the database in accordance with the
information as his or her needs change. This problem is called user’s level of authority. The DBMS will deny requests for data that the user
task-data dependency. The user’s information set is is unauthorized to access. As one might imagine, the organization’s criteria,
constrained by the data that he or she possesses and rules, and procedures for assigning user authority are important control
controls. issues for accountants to consider.
THREE CONCEPTUAL MODELS constitute the database. The DDL defines the database on
Early database models are as different from modern database three levels called views: the internal view, the conceptual
models as they were from traditional flat files. The most common view (schema), and the user view (subschema).
database approaches used for business information systems are
the hierarchical model, users, and relational model. Because of  INTERNAL VIEW. The internal view presents the physical
certain conceptual similarities, the hierarchical and network arrangement of records in the database. This is the lowest
databases are termed navigational models or internal view. The level of representation, which is one step removed from the
way that data are organized in these early database systems forces physical database. The internal view describes the structure of
users to navigate between data elements using predefined records, the linkages between them, and the physical
structured paths. The relational model is far more flexible by arrangement and sequence of records in a file. There is only
allowing users to create new and unique paths through the one internal view of the database.
database to solve a wider range of business problems. Although
their limitations are severe and their ultimate demise is inevitable,  CONCEPTUAL VIEW (SCHEMA). The conceptual view or
hierarchical and network models still exist as legacy systems that schema represents the database logically and abstractly,
support mission-critical functions in some companies. Most rather than the way it is physically stored. This view allows
modern systems, however, employ relational databases. users’ programs to call for data without knowing or needing to
specify how the data are arranged or where the data reside in
Elements of the Database Environment the physical database. There is only one conceptual view for
1. USERS. Figure 9-3 shows how users access the database in two a database.
ways. The first is via user application programs that systems
professionals prepare. These programs send data access requests  USER VIEW (SUBSCHEMA). The user view defines how a
(calls) to the DBMS, which validates the requests and retrieves the particular user sees the portion of the database that he or
data for processing. Under this mode of access, the presence of the she is authorized to access. To the user, the user view is the
DBMS is transparent to the users. Data-processing procedures database. Unlike the internal and conceptual views, many
(both batch and real time) for transactions such as sales, cash distinct user views exist. For example, a user in the personnel
receipts, and purchases are essentially the same as they would be department may view the database as a collection of
in the flat-file environment. The second method of database access employee records and is unaware of the supplier and
is via direct query, which requires no formal user programs. The inventory records seen by the users in the inventory control
DBMS has a built-in query facility that allows authorized users to department.
process data independent of professional programmers. The
query facility provides a friendly environment for integrating and DBMS OPERATION. To illustrate the roles of these views, let’s look at the
retrieving data to produce ad hoc management reports. This typical sequence of events that occurs in accessing data through a DBMS.
feature has been an attractive incentive for users to adopt the The following description is hypothetical, and certain technical details are
database approach. omitted.
1. A user program sends a request (call) for data to the DBMS. The call is
written in a special data manipulation language that is embedded in the
2. THE DATABASE MANAGEMENT SYSTEM. The second element of
user program.
the database approach depicted in Figure 9-3 is the DBMS. The 2. The DBMS analyzes the request by matching the called data elements
DBMS provides a controlled environment to assist (or prevent) against the user view and the conceptual view. If the data request matches,
user access to the database and to efficiently manage the data it is authorized, and processing proceeds to Step 3. If it does not match the
resource. Each DBMS model accomplishes these objectives views, access is denied.
differently, but some typical features include: 3. The DBMS determines the data structure parameters from the internal
 Program development. The DBMS contains application view and passes them to the operating system, which performs the actual
development software. Both programmers and end users may data retrieval. Data structure parameters describe the organization and
employ this feature to create applications to access the access method (an operating system utility program) for retrieving the
database. requested data.
 Backup and recovery. During processing, the DBMS periodically 4. Using the appropriate access method, the operating system interacts
makes backup copies of the physical database. In the event of with the disk storage device to retrieve the data from the physical database.
a disaster (e.g., disk failure, program error, and malicious act), 5. The operating system then stores the data in a main memory buffer area
which renders the database unusable, the DBMS can recover an managed by the DBMS.
earlier version that is known to be correct. Although some data 6. The DBMS transfers the data to the user’s work location in main memory.
loss may occur, without the backup and recovery feature, the At this point, the user’s program is free to access and manipulate the data.
database would be vulnerable to total destruction. 7. When processing is complete, Steps 4, 5, and 6 are reversed to restore
 Database usage reporting. This feature captures statistics on the processed data to the database.
what data are being used, when they are used, and who uses
them. The database administrator uses this information to  Data Manipulation Language. Data manipulation language
assign user authorization and to maintain the database. (DML) is the proprietary programming language, which a
 Database access. The most important feature of a DBMS is to particular DBMS uses to retrieve, process, and store data.
permit authorized user access to the database. Figure 9-3 Entire user programs may be written in the DML, or
shows the three software modules that facilitate this task.
alternatively, selected DML commands can be inserted into
These are the data definition language, data manipulation
programs that are written in universal languages, such as PL/1,
language, and the query language.
COBOL, and FORTRAN. Inserting DML commands enables
THREE SOFTWARE MODULES legacy application programs, which were originally written
 Data Definition Language. Data definition language (DDL) is a for the flat-file environment or earlier types of DBMSs, to be
programming language used to define the physical database easily converted to work in the current database environment.
to the DBMS. The definition includes the names and the The use of standard language programs also provides the
relationship of all data elements, records, and files that organization with a degree of independence from the DBMS
 vendor. If the organization decides to switch its vendors to The Data Dictionary
one that uses a different DML, it will not be necessary to Another important function of the DBA is the creation and
rewrite all the user programs. By replacing the old DML maintenance of the data dictionary. The data dictionary describes
commands with the new commands, user programs can be every data element in the database. This enables all users (and
modified to function in the new environment. programmers) to share a common view of the data resource and
greatly facilitates the analysis of user needs.
 Query Language. The query capability of the DBMS permits
end users and professional programmers to access data in 4. THE PHYSICAL DATABASE
the database directly without the need for conventional The fourth major element of the database approach is the physical
programs. IBM’s indexed sequential file (SQL, pronounced database. This is the lowest level of the database. The physical
sequel) has emerged as the standard query language for both database consists of magnetic spots on magnetic disks. The other
mainframe and microcomputer DBMSs. SQL is a fourth- levels of the database (e.g., the user view, conceptual view, and
generation, nonprocedural language with many commands internal view) are abstract representations of the physical level.
that allow users to input, retrieve, and modify data easily. The
SELECT command is a powerful tool for retrieving data. The At the physical level, the database is a collection of records and files.
example in Figure 9-5 illustrates the use of the SELECT Relational databases are based on the indexed sequential file structure
command to produce a user report from a database called which uses an index in conjunction with a sequential file organization. It
facilitates both direct access to individual records and batch processing of
Inventory. SQL is an efficient data processing tool. Although
the entire file. Multiple indexes can be used to create a cross- reference,
not a natural English language, SQL requires far less training in called an inverted list, which allows even more flexible access to data.
computer concepts and fewer programming skills than many
other programming languages. In fact, many database query The Relational Database Model
systems require no SQL knowledge at all. Users select data E. F. Codd originally proposed the principles of the relational model in the
visually by pointing and clicking at the desired attributes. The late 1960s. The formal model has its foundations in relational algebra and
visual user interface then generates the necessary SQL set theory, which provide the theoretical basis for most of the data
commands automatically. This feature places ad hoc reporting manipulation operations used. Other theorists have, therefore, proposed
and data processing capability in the hands of the less rigid requirements for assessing the relational standing of a system.
user/manager. By reducing reliance on professional Accordingly, a system is relational if it:
1. Represents data in the form of two-dimensional tables such as the
programmers, managers are better able to deal with problems
database table, called Customer.
that arise. 2. Supports the relational algebra functions of restrict, project, and join:
 Restrict: Extracts specified rows from a specified table. This
3. THE DATABASE ADMINISTRATOR operation creates a virtual table (one that does not physically
The administrative position of database administrator (DBA) does exist) that is a subset of the original table.
not exist in the flat-file environment. The DBA is responsible for  Project: Extracts specified attributes (columns) from a table to
managing the database resource. Having multiple users share a create a virtual table.
common database requires organization, coordination, rules, and  Join: Builds a new physical table from two tables consisting of
guidelines to protect the integrity of the database. In large all concatenated pairs of rows, from each table.
organizations, the DBA function may consist of an entire
department of technical personnel under the DBA. In smaller Although not the complete set of relational functions, the subset of restrict,
organizations, someone within the computer services group may project, and join satisfies most business information needs.
assume DBA responsibility. The duties of the DBA fall into the
following areas: database planning, database design, database DATA MODELING CONCEPTS
implementation, database operation and maintenance, and In this section, we review data modeling concepts. A data model is
database change and growth. Table 9-1 presents a breakdown of a visual representation of an organization’s data. The model
specific tasks within these broad areas. represents the nature of the data and the business rules that
dictate how they are used. A data model is similar to a blueprint
(plan) for a house. Database developers use the completed data
model, often in the form of an entity relationship diagram, to
construct the physical database tables and the coding needed to
make it function as prescribed. In theory, data models are designed
in a top-down approach, which requires a detailed analysis of the
organization’s information needs. In practice, commercial DBMSs
provide a predefined data model and tables that are based on
business best-practices. The client organization’s database
developers may then modify the model to suit the company’s
specific needs. This is often called the bottom-up approach.

As information needs arise, users send formal requests for computer Accountants and auditors work extensively with databases as part of their
applications to the systems professionals (programmers) of the day-to-day activities. Whether preparing financial statements, tagging data
organization. The requests are handled through formal systems attributes for XBRL reporting, or extracting financial data from tables during
development procedures, which produce the programmed applications. By the conduct of audit tests, accountants and auditors need to understand
keeping access authority separate from systems development (application how the data are structured. The purpose of this section, therefore, is to
programming), the organization is better able to control and protect the explain the basics of data modeling, which will serve to guide the
database. Intentional and unintentional attempts at unauthorized access accountant when performing database- related tasks. Toward this end, the
are more likely to be discovered when these two groups work subsections that follow introduce relational concepts, database
independently. terminology, and database design techniques. The final subsection employs
the top-down approach to develop a data model from scratch.
Entity, Relation, Occurrence, and Attributes ANOMALIES, STRUCTURAL DEPENDENCIES, AND DATA
An entity is anything about which the organization wishes to capture data. NORMALIZATION
Entities may be physical, such as inventories, customers, and employees. This section deals with why database tables need to be normalized. In other
They may also be conceptual, such as accounts receivable and accounts words, why is it necessary for the organization’s database to form an
payable. Database developers identify entities and prepare a model of elaborate network of normalized tables linked together? Why, instead, can
them. The graphical technique used to depict the model is called an entity we not simply consolidate the views of one user (or several) into a single
relationship (ER) diagram. Each entity that is represented in an ER diagram common table from which all data needs may be met?
corresponds to a table in the physical database. This is a high-level
perspective of the overall schema, which presents only the key entities and Database Anomalies
their relationships; it does not show the data types and keys contained
The answer to the questions asked in previous section is that
within the entities. The term relation describes how data in one entity are
related to data in another entity.
improperly normalized tables can cause DBMS processing
As a matter of convention, entities are named in the singular noun form, problems that restrict, or even deny, users’ access to the
such as Customer rather than Customers. The term occurrence (or instance) information they need. Such tables exhibit negative operational
is used to describe the number of items associated with an entity. symptoms called anomalies. Specifically, these are the update
Attributes are the data elements that define an entity. anomaly, the insertion anomaly, and the deletion anomaly.

Associations and Cardinality  UPDATE ANOMALY. The update anomaly is the result of data
The term association describes the nature of the functional connection redundancy in an unnormalized table.
between two entities in a relation. This association is represented in an ER
diagram by a verb label on the connecting line such as ships, requests, and
 INSERTION ANOMALY. To demonstrate the effects of the
receives. Cardinality is the degree of association between two entities.
Simply stated, cardinality describes the number of possible occurrences in
insertion anomaly, assume that a new vendor has entered
one table that is associated with a single occurrence in a related table. the marketplace. The organization does not yet purchase
Four basic forms of cardinality are possible: zero or one (0,1), one and only from the vendor but may wish to do so in the future. In the
one (1,1), zero or many (0,M), and one or many (1,M). These are combined meantime, the organization wants to add the vendor to the
to describe the cardinality between entities in a relation. database.

The Physical Database Tables  DELETION ANOMALY. The deletion anomaly involves the
The data model is the blueprint for the physical database tables that unintentional deletion of data from a table.
constitute the database. The tables are constructed based on the entity
specifications in the model. The attributes form columns with the attribute The presence of the deletion anomaly is less conspicuous but potentially
name at the top of each column. Occurrences form rows that cut across the more serious than the update and insertion anomalies. A flawed database
columns and at their intersection is an attribute value that pertains to the design that prevents the insertion of records or requires the user to perform
occurrence. The term tuple is the formal name for a row in the table, Codd excessive updates attracts attention quickly. The deletion anomaly,
gave a precise definition when he first introduced this term. Accordingly, however, may go undetected, leaving the user unaware of the loss of
properly designed tables possess the following four characteristics: important data until it is too late. This can result in the unintentional loss
1. The value of at least one attribute in each tuple must be unique. of critical accounting records and the destruction of audit trails. Proper
This attribute is the primary key. The values of the other table design, therefore, is not just an operational efficiency issue; it carries
(nonkey) attributes in the row need not be unique. internal control significance that accountants need to recognize.
2. All attribute values in any column must be of the same class.
3. Each column in a given table must be uniquely named. However,
Normalizing Tables in a Relational Database
different tables may contain columns with the same name.
The database anomalies described earlier in the text are symptoms of
4. Tables must conform to the rules of normalization. This means structural problems within tables called dependencies. Specifically, these
they must be free from structural dependencies, including
are known as repeating groups, partial dependencies, and transitive
repeating groups, partial dependencies, and transitive
dependencies. The normalization process involves identifying and
dependencies.
removing structural dependencies from the table(s) being modeled such
that the resulting 3NF table designs will meet two conditions:
Linkages between Relational Tables 1. All nonkey (data) attributes in the table are dependent on (defined by)
Logically related tables need to be physically connected to achieve the the primary key.
associations described in the data model. This is accomplished by using 2. All nonkey attributes are independent of the other nonkey attributes.
foreign keys. The foreign keys are embedded in the related table but not
always. The degree of cardinality between the related tables determines In other words, a 3NF table is one in which the primary key of a table wholly
the method used for assigning foreign keys. With foreign keys in place, a and uniquely defines each attribute in the table. Furthermore, none of the
computer program can be written to navigate among the tables of the table attributes is defined by an attribute other than the primary key.
database and provide users with the data they need to support their day-
to-day tasks and decision-making responsibilities. REPRESENT THE VIEW AS A SINGLE TABLE. The next step is to
represent the view as a single table that contains all of the view
User Views attributes.
A user view was defined earlier as the set of data that a particular user
sees. Examples of user views are computer screens for entering or viewing REMOVE REPEATING GROUP DATA. The first step in correcting
data, management reports, or source documents such as an invoice and a structural dependencies is to determine if the table under review
purchase order. Views may be digital or hard copy, but in all cases, they contains repeating groups. Repeating group data occur when
derive from underlying database tables. Simple views may be constructed multiple values for a particular attribute exist in a specific tuple.
from a single table, while more complex views may require several tables.
The task of identifying all views and translating them into normalized tables
REMOVE TRANSITIVE DEPENDENCIES. The final step in resolving
is an important responsibility of database designers as it has internal control
implications. The issues and techniques related to this task are examined structural dependencies is to remove transitive dependencies. A
next. transitive dependency occurs in a table where nonkey attributes
are dependent on another nonkey attribute and independent of
the table’s primary key.
REMOVE PARTIAL DEPENDENCIES. Next we check to see if the 2. NORMALIZE DATA MODEL AND ADD PRIMARY KEYS. Figure 9-
resulting tables contain partial dependencies. A partial 27 presents a normalized data model. The following section
dependency occurs when one or more nonkey attributes are describes the normalization procedures that produced this model:
dependent on (defined by) only part of the primary key, rather than 1. Repeating Group Data in Status Report Entity. The nonkey attributes of
the whole key. This can occur only in tables that have composite Supplier Number, Supplier Name, Supplier Address, and Supplier Tel
Number are repeating group data in the Status Report. These data were
(two or more attributes) primary keys.
removed and placed in a new table called Supplier and assigned a primary
key of Supplier number. The remaining entity, which now contains only
LINKING THE NORMALIZED TABLES. At this point, the original inventory attributes was renamed Inventory.
single-table structure has been reduced to the four normalized but 2. Repeating Group Data in Purchase Order Entity. The attributes of Part
independent tables. The tables contain the sample data used in the Number, Description, Order Quantity, and Unit Cost are repeating group
original single- table structure. Data redundancy in the original data. To resolve this, these attributes data were removed to a new entity
single-table structure has been eliminated from the more efficient called PO Item Detail, which was assigned a primary key that is a composite
structure. To work together in the physical database, however, the of Part Number and PO Number.
3. Partial Dependency in PO Item Detail Entity. An analysis of the PO Item
tables in this data model need to be linked via foreign keys. This
Detail entity reveals that its composite primary key uniquely defines only
requires first determining the cardinality between the tables and Order Quantity. The attributes of Description and Unit Cost are partially
then assigning foreign keys. dependent on Part Number and therefore removed from the table leaving
Order Quantity as the only nonkey attribute. Unit Cost was added to the
Accountants and Data Modeling Inventory entity. Description was already a listed attribute of the Inventory
Although most accountants will not be directly responsible for normalizing entity.
an organization’s databases, they should have an understanding of the 4. Transitive Dependency in Purchase Order Entity. The attributes of
process and be able to determine whether financial data are normalized Supplier Name, Supplier Address, and Supplier Tel Number are transitive
properly to avoid anomalies. For example, the update anomaly can dependencies, which are defined by Supplier Number. These attributes
generate conflicting and/or obsolete database values in accounts, the were therefore removed from the Purchase Order entity.
insertion anomaly can result in unrecorded transactions and incomplete 5. Repeating Group Data in Receiving Report Entity. The attributes Part
audit trails, and the deletion anomaly can cause the loss of accounting Number, Quantity Received, and Condition Code are repeating groups in
records and the destruction of audit trails. Also, the conduct of many the Receiving Report entity and were removed to a new entity called Rec
financial audit procedures involves accessing data stored in normalized base Report Item Detail. A composite key composed of Part number and Rec Rept
tables. An organization’s financial database may consist of thousands of Number was assigned.
normalized tables and navigating such a network requires an understanding 6. Transitive Dependency in Receiving Report Entity. The attributes
of data structures. Supplier Name, Supplier Address, and Supplier Tel Number are defined by
Supplier Number and not defined by the primary key PO Number. They are
Top-Down Approach to Designing Relational Databases Supplier entity attributes and therefore removed from the Purchase Order
This section examines the steps involved in creating a relational entity.
database using a top-down approach. Keep in mind that database
design is an element of a much larger systems development 3. DETERMINE CARDINALITIES AND ADD FOREIGN KEYS. The next
process that involves extensive analysis of user needs. The focus step in view modeling is to determine the cardinality between
will be on the following phases of database design, which are entities and link the entities with foreign keys. Recall that
known collectively as view modeling: cardinalities represent business rules. Sometimes, the rules are
1. Identify the views to be modeled. obvious and are the same for all organizations. For example, the
2. Normalize the data model and add primary keys. normal cardinality between a Customer entity and a Sales Order
3. Determine cardinalities and add foreign keys. entity is 1:M. This signifies that one customer may place many
4. Construct the physical database. orders during a sales period. The cardinality would never be 1:1.
This would mean that the organization restricts each customer to a
5. Prepare the physical user views.
single sale, which is illogical. Sometimes, the cardinality between
entities is not apparent because different rules may apply in
1. IDENTIFY THE VIEWS TO BE MODELED. View modeling begins by
different organizations. If the database is to function properly, its
identifying the relevant views of the business function in question.
designers need to understand the organization’s business rules as
This involves user interviews, and other techniques, to determine
well as the specific needs of individual users.
what users do and what information they need to support their
tasks. To demonstrate view identification, we will analyze the
The view modeling process described previously in the chapter
following key features of a simplified purchasing function:
1. The purchasing agent reviews the inventory status report for items that involved only one business function— the purchases system—and
need to be reordered. the example entities that resulted constitute only a small portion
2. The agent selects a supplier and prepares a digital purchase order from of that system. A modern company would employ thousands of
his terminal. views. Combining the data needs of all users into a single entity-
3. The agent prints a copy of the purchase order and sends it to the supplier. wide schema is called view integration. This involves the
4. The supplier ships the inventory to the company. Upon its arrival, the consolidation of the attributes from all user views into a common
receiving clerk inspects the inventory and prepares the digital receiving set of normalized entities that meet the following conditions:
report. The computer system automatically updates the inventory records.
1. An entity must consist of two or more occurrences.
2. No two entities may have the same primary key. The exceptions to this
This description identifies three views: (1) Inventory Status Report, (2) are entities with composite keys that comprise the primary keys of other
Purchase Order, and (3) Receiving Report. The primary key should logically entities.
define the nonkey attributes and uniquely identify each occurrence in the 3. No nonkey attribute may be associated with more than one entity. For
entity. Sometimes this is accomplished by using a simple sequential code example, if Customer Address is an attribute of the Customer entity, it
such as an invoice number, check number, and purchase order number. cannot also be an attribute of another entity. All views that use Customer
Sequential codes, however, are not always appropriate keys. Block codes, Address will retrieve it from the Customer entity.
group codes, alphabetic codes, and mnemonic codes can also be assigned
as primary keys.
 that are free of the update, insert, and deletion anomalies and sufficiently
4. CONSTRUCT THE PHYSICAL DATABASE. Figure 9-28 illustrates rich to serve the needs of all users.
the 3NF table structures for the physical database. The dotted lines Databases in a Distributed Environment
represent the primary and foreign keys linking the tables. The next Chapter 1 introduced the concept of distributed data processing
step is to create the physical tables and populate them with data. (DDP) as an alternative to the centralized approach. Most modern
This is an involved step that must be carefully planned and organizations use some form of distributed processing and
executed, and may take many months in a large installation. networking to process their transactions. Some companies process
Programs will need to be written to transfer organization data all of their transactions in this way. An important consideration in
currently stored in flat files or legacy databases to the new planning a distributed system is the location of the organization’s
relational tables. Data currently stored on paper documents will be database. In addressing this issue, the planner has two basic
entered into database tables manually. Once this is done, the options: databases can be centralized, or they can be distributed.
physical user views can be produced. Distributed databases fall into two categories: partitioned and
replicated.
5. PREPARE THE PHYSICAL USER VIEWS. The normalized tables
should be rich enough to support the views of all users of the  CENTRALIZED DATABASES. Under the centralized database
system being modeled. The fields in the user view are cross- approach, remote users send requests via terminals for data
referenced via circled numbers to the attributes in the supporting to the central site, which processes the requests and
tables. Keep in mind that these tables may also provide data for transmits the data back to the user. The central site performs
many other views not shown here, such as the receiving report, the functions of a file manager that services the data needs of
purchase requisition listing, inventory status report, and vendor the remote users. Earlier in the chapter, three primary
purchases activity report. Database developers use SQL to identify advantages of the database approach were presented: the
which tables to use and which attributes to select to create the reduction of data storage costs, the elimination of multiple
user view. SQL commands needed to produce the inventory status update procedures, and the establishment of data currency
report (i.e., the firm’s data files reflect accurately the effects of its
 The SELECT command identifies all of the attributes to be transactions). Achieving data currency is critical to database
contained in the view. integrity and reliability. However, in the DDP environment,
 The FROM command identifies the tables used in creating the this can be a challenging task.
view.
 The WHERE command specifies how rows in the Inventory, Data Currency in a DDP Environment
During data processing, account balances pass through a state of temporary
Part-Supplier, and Supplier tables are to be matched to create
inconsistency in which their values are incorrectly stated. This occurs during
the view. the execution of any accounting transaction.
 Multiple expressions may be linked with the AND, OR, and
NOT operators. Only records whose quantities on hand have Database Lockout
fallen to or below their reorder points will be selected for the To achieve data currency, simultaneous access to individual data elements
view and the user will not see the many thousands of other by multiple sites needs to be prevented. The solution to this problem is to
inventory items that have adequate quantities available. use a database lockout, which is a software control (usually a function of
the DBMS) that prevents multiple simultaneous accesses to data.
These SQL commands will be saved in a user program called a query. To
view the Inventory Status Report, the purchasing agent executes the query  DISTRIBUTED DATABASES. Distributed databases can be
program. Each time this is done, the query builds a new view with current distributed using either the partitioned or replicated
data from the Inventory and Vendor tables. By providing the user with his technique.
or her personal query, rather than permitting access to the underlying base
tables, the user is limited only to authorized data. A report program is used
Partitioned Databases
to make the view visually attractive and easy to read. Column headings can
The partitioned database approach splits the central database into
be added to produce a hard-copy or computer screen report. The report
segments or partitions that are distributed to their primary users. The
program will suppress unnecessary data from the view, such as duplicated
advantages of this approach are:
fields and the key values in the Inventory/Vendor link table. These keys are
 Storing data at local sites increases users’ control.
necessary to build the view, but are not needed in the actual report.
 Permitting local access to data and reducing the volume of data
that must be transmitted between sites improve transaction
COMMERCIAL DATABASE SYSTEM processing response time.
Modeling the data needs of thousands of user views is a daunting  Partitioned databases can reduce the potential for disaster. By
undertaking when creating an entity-wide database from scratch. having data located at several sites, the loss of a single site
To facilitate this task, modern commercial database systems come cannot terminate all data processing by the organization.
equipped with a core schema, normalized tables, and templates
for thousands of views. Commercial systems are designed to The partitioned approach works best for organizations that require
comply with proven industry best practices and to satisfy the minimal data sharing among users at remote sites. To the extent that
remote users share data, the problems associated with the centralized
most common needs of different client organizations.
approach still apply. The primary user must now manage requests for data
from other sites. Selecting the optimum host location for the partitions will
Working from a commercial database package, developers can thus focus
minimize data access problems. This requires an in-depth analysis of end-
on configuring predefined views to accommodate specific user needs within
user data needs.
their organizations rather than starting from scratch.
Database vendors cannot, however, anticipate the information needs of all
users in advance. Therefore, new entities and new attributes may need to THE DEADLOCK PHENOMENON. In a distributed environment, it is
be added to the core schema. Although configuring the core database in possible that multiple sites will lock out each other, thus preventing
this fashion is far more efficient than working from scratch, the objective is each from processing its transactions. A deadlock occurs here
the same. The database designer must produce a set of integrated tables because there is mutual exclusion to data, and the transactions
are in a wait state until the locks are removed. This can result in to use the same data and do not conflict. However, multiple sales order
transactions being incompletely processed and corruption of the transactions involving both read and write operations will potentially
database. A deadlock is a permanent condition that must be conflict.
The second part of the control process is to time stamp each transaction. A
resolved by special software that analyzes each deadlock condition
system-wide clock is used to keep all sites, some of which may be in
to determine the best solution. Because of the implications for different time zones, on the same logical time. Each time stamp is made
transaction processing, accountants should be aware of the issues unique by incorporating the site’s identification number. When
pertaining to deadlock resolutions. transactions are received at each site, they are examined first for potential
conflicts. If conflicts exist, the transactions are entered into a serialization
DEADLOCK RESOLUTION. Resolving a deadlock usually involves schedule. An algorithm is used to schedule updates to the database based
sacrificing one or more transactions. These must be terminated to on the transaction time stamp and class. This method permits multiple
complete the processing of the other transactions in the deadlock. inter- leaved transactions to be processed at each site as if they were serial
events.
The preempted transactions must then be reinitiated. In
preempting transactions, the deadlock resolution software
Distributed Databases and the Accountant
attempts to minimize the total cost of breaking the deadlock.
The decision to distribute databases is one that should be entered
Although not an easy task to automate, some of the factors that
into thoughtfully. There are many issues and trade-offs to consider.
influence this decision are as follows:
1. The resources currently invested in the transaction. This may be
Some of the most basic questions to be addressed are as follows:
measured by the number of updates that the transaction has already  Should the organization’s data be centralized or distributed?
performed and that must be repeated if the transaction is terminated.  If data distribution is desirable, should the databases be
2. The transaction’s stage of completion. In general, deadlock resolution replicated or partitioned?
software will avoid terminating transactions that are close to completion.  If replicated, should the databases be totally replicated or
3. The number of deadlocks associated with the transaction. Because partially replicated?
terminating the transaction breaks all deadlock involvement, the software
 If the database is to be partitioned, how should the data
should attempt to terminate transactions that are part of more than one
deadlock. segments be allocated among the sites?

Replicated Databases The choices involved in each of these questions impact the
In some organizations, the entire database is replicated at each site. organization’s ability to maintain database integrity. The
Replicated databases are effective in companies in which there preservation of audit trails and the accuracy of accounting records
exists a high degree of data sharing but no primary user. Because are key concerns. Clearly, these are decisions that the modern
common data are replicated at each site, the data traffic between accountant should understand and influence intelligently.
sites is reduced considerably.

Figure 9-32 illustrates the replicated database model. The primary

justification for a replicated database is to support read-only queries. With
data replicated at every site, data access for query purposes is ensured,
and lockouts and delays because of network traffic are minimized. A
problem arises, however, when local sites also need to update the
replicated database with transactions.
Because each site processes only its local transactions, different
transactions will update the common data attributes that are replicated at
each site, and thus, each site will possess uniquely different values after the
respective updates.

Concurrency Control
Database concurrency is the presence of complete and accurate
data at all remote sites. System designers need to employ methods
to ensure that transactions processed at each site are accurately
reflected in the databases at all other sites. This task, while
problematic, has implications for accounting records and is a
matter of concern for accountants. A commonly used method for
concurrency control is to serialize transactions. This involves
labeling each transaction by two criteria.

First, special software groups transactions into classes to identify potential

conflicts. For example, read-only (query) transactions do not conflict with
other classes of transactions. Similarly, AP and AR transactions are not likely