CHAPTER 4

SECURITY PART II: AUDITING DATABASE SYSTEMS

REVIEW QUESTIONS

1. What is a legacy system?

Response: Legacy systems are large mainframe systems that were implemented from the
late 1960s through the 1980s. Organizations today still make extensive use of these
systems.

2. What is the flat-file model?

Response: Flat files are data files that contain records with no structured relationships to other
files. The flat-file model describes an environment in which individual data files are not
related to other files. End users in this environment own their data files rather than share
them with other users. Data files are therefore structured, formatted, and arranged to suit the
specific needs of the owner or primary user of the data. Such structuring, however, may
exclude data attributes that are useful to other users, thus preventing successful integration of data
across the organization.

3. What are the key elements of the database environment?

Response: The database management system (DBMS), users, the database administrator, the
physical database, and DBMS models..

4. What types of problems does data redundancy cause?

Response:
a. data storage problems
b. data updating problems
c. currency of information problems

5. What flat-file data management problems are solved as a result of using the
database concept?
Response:
a. data storage problem
b. data update problem
c. currency problem
d. task-data dependency problem

6. What is DML?
Response: DML is the proprietary programming language that a particular DBMS
uses to retrieve, process, and store data.

7. What is the purpose of the data definition language?

Response: (DDL) is a programming language used to define the database to the DBMS.
The DDL identifies the names and the relationship of all data elements, records, and files
that constitute the database. This definition has three levels, called views: the physical
internal view, the conceptual view (schema), and the user view (subschema).
8. What is the internal view of a database?
Response: The internal view of a database is the physical arrangement of the records. It
describes the data structure, the linkages between files, and the physical arrangement of
the records.

9. What is SQL?
Response: Structured Query Language (SQL) is a fourth-generation, nonprocedural
language that allows users to easily input, retrieve, and modify data. It is the standard
query language for both mainframe and microcomputer DBMSs.

10. What is a data dictionary, and what purpose does it serve?

Response: The data dictionary describes every data element in the database. It enables all
users (and programmers) to share a common view of the data resource, thus greatly
facilitating the analysis of user needs. The data dictionary may be in both paper form and
online. Most DBMSs employ special software for managing the data dictionary.

11. What are the two fundamental components of data structures?

Response: Organization and access method.

12. What are the criteria that influence the selection of the data structure?
Response:
a. rapid file access and data retrieval
b. efficient use of disk storage space
c. high throughput for transaction processing
d. protection from data loss
e. ease of recovery from system failure
f. accommodation of file growth

13. What is a data attribute?

Response: A data attribute, or field, is a single item of data, such as customer name,
account balance, or address.

14. What is a record type?

Responses: A Record Type is a physical database representation of an entity. Database
designers group together into tables (files) the record types that pertain to specific entities.
For example, records of sales to customers would form the sales order record type, which
physically represents the Sales Order entity

15. What is a record association?

Response: Record types exist in relation to other record types. This is called a record
association. There are three basic record associations: one-to-one, one-to-many, and
many-to-many.

16. What is a database?

Response: A database is the set of record types that an organization needs to support its
 business processes.

17. What is an enterprise database?

Response: An enterprise database is a common set of data files or tables for the entire
organization, or enterprise. The most recent application development systems, such as
Oracle’s latest package or Microsoft’s dot-net, focus on the ability to use an enterprise
database as the foundation for applications that interface across the entire enterprise. The
Enterprise Resource Planning (ERP) software so popular in the 1990s is also based on the
principle of an enterprise database.

18. What is time-stamping, and why is it useful?

Response: The second part of the concurrency control process is to time-stamp each
transaction. A system-wide clock is used to keep all sites, some of which may be in
different time zones, on the same logical time. Each time stamp is made unique by
incorporating the site’s ID number. Time-stamping is used in distributed data processing
environments to help ensure the presence of complete and accurate data by avoiding the
processing of conflicting transactions.

19. Explain the grandfather-father-son backup technique. Is it used for sequential files
or direct access techniques? Why? How many generations can be backed up?
Response: This procedure is when the current master file (the father) is processed against
the transaction file to produce a new master file (the son). With the next batch of
transactions, the son becomes the current master file (the father), and the original father
becomes the backup (the grandfather) file. The new master file that emerges from the
update process is the son. This procedure is continued with each new batch of
transactions, creating generations of backup files. When the desired number of backup
copies is reached, the oldest backup file is erased (scratched). This process is used for
sequential files and batch direct access systems. The updates for online, real-time, direct
access systems occur continuously throughout the day. The master files are backed up at
certain intervals. The number of backed up master files is a management and auditor
decision.

20. What are inference controls? Why are they needed?

Response: Inference controls are implemented to preserve the confidentiality and
integrity of the database from users who try to infer specific data values by using query
features. The query feature might not allow certain types of direct questions to be asked,
but a clever user may determine a way to ask multiple questions, which together give the
desired answer to the question.

21. What are the four basic backup and recovery features necessary in a DBMS? Briefly
explain each.
Response:
a. Backup. This feature makes a periodic backup of the entire database.
b. Transaction Log (Journal). This feature tracks all transactions in a transaction log.
c. Checkpoint Feature. This feature suspends all data processing while the system
reconciles the transaction log and the database change log against the database.
d. Recovery Module. This feature uses the logs and backup files to restart the system after
a failure.
22. What is data encryption?
Response: Data encryption uses an algorithm to scramble selected data, thus making
them unreadable to an intruder browsing the database. In addition to protecting stored
data, encryption is used for protecting data that are transmitted over communications
lines.

23. What are biometric devices?

Response: Biometric devices measure various personal characteristics, such as
fingerprints, voice prints, retina prints, or signature characteristics. These user
characteristics are digitized and stored permanently in a database security file or on an
identification card that the user carries. When an individual attempts to access the
database, a special scanning device captures his or her biometric characteristics, which it
compares with the profile data stored on file or the ID card. If the data do not match,
access is denied.

24. What is a user-defined procedure?

Response: A user-defined procedure allows the user to create a personal security
program or routine to provide more positive user identification than a single password.
Thus, in addition to a password, the security procedure asks a series of personal questions
(such as the user’s mother’s maiden name), which only the legitimate user should know.

Discussion Questions

1. In the flat-file data management environment, users are said to own their data files.
What is meant by this ownership concept?
Response: In the traditional data management environment, applications are developed
with data and program dependency. Typically, these programs are application specific.
Thus, the users of the application data tend to be proprietary about the data in “their”
applications and may not be amenable to sharing such data.

2. Why is a hierarchical data model considered to be a navigational database?

Response: The hierarchical database model is considered to be a navigational database
because traversing through it requires predefined linkages between related records that
start at the root. A limitation of this model is that a parent record may own one or more
child, but no child record may have more than one parent. If a child logically needs to be
linked to one or more parents, duplication of files with different predefined linkages is
required.

3. Discuss why control procedures over access to the database become more crucial
under the database concept than in the flat-file data management environment.
What role does the DBMS play in helping to control the database environment?
Response: Under the database concept, the data is centrally stored with many different
users accessing the database. However, each user should not have access to the whole
database. Under the flat-file data management environment where the data and programs
 were linked, the user access problem was not as great a threat. The DBMS is a special
software system that is programmed to know which data each user is authorized to
access. This controlled authorization is crucial in centrally stored DBMSs.

4. What is the relationship between a schema and a subschema?

Response: The schema represents the physical storage of the entire database. A
subschema is a subset of the entire database; it represents a user’s view of the database.
Numerous sub- schemas exist for every schema.

5. Discuss the two ways in which users can access the database files in a database
environment.
Response: The first way the user may access the database is through user programs
prepared by systems professionals. These programs send data access requests to the
DBMS. The DBMS then validates the requests and retrieves the data for processing. The
presence of the DBMS is transparent to the user. The second method involves direct
inquiry on the part of the user. DBMSs have built-in data inquiries that allow authorized
users to retrieve and manipulate data without the assistance of the professional
programmers.

6. Discuss the limitations of the hierarchical database model?

Response: The primary limitation of this model is that a parent record may own one or
more child, but no child record may have more than one parent. If a child logically needs
to be linked to more than one parents, which is common in business applications,
duplication of files with different predefined linkages is required.

7. What is a partitioned database and what are its advantages?

Response: The partitioned database approach splits the central database into segments or
partitions that are distributed to their primary users. The advantages of this approach
follow: having data stored at local sites increases users’ control, transaction processing
response time is improved by permitting local access to data and reducing the volume of
data that must be transmitted between IT units. Also, partitioned databases can reduce
the potential effects of a disaster. By locating data at several sites, the loss of a single IT
unit does not eliminate all data processing by the organization.

8. SQL has been said to place power in the hands of the user. What is meant by this
statement?
Response: SQL allows users to retrieve data from many different files without the
assistance of programming professionals. Thus, if the user has access to data files and
knows the SQL, which is very user friendly, the user may retrieve the data
instantaneously.

9. Discuss the importance of the role of the database administrator. Why wasn’t such
a role necessary in the flat-file environment? What tasks are performed by the
DBA?
Response: In the flat-file environment, the data were not centrally stored for many
different applications to use. Because the data are centrally stored and shared by many
 users in a database environment, the need for an independent individual to care for and
control these files arose. The database administrator is responsible for database planning,
developing the data requirements and data dictionary, database design and controls,
database implementation and access controls, operation and maintenance, and
establishing and reviewing the standards and procedures.

10. As users determine new computer application needs, requests must be sent to both
the system programmers and the DBA. Why is it important that these two groups
perform separate functions, and what are these functions?
Response: The system programmers program the manner in which the data will be
retrieved, manipulated, reported, and stored. They do not need, and should not have,
access to the data, except perhaps temporarily to test the programs. The database
administrator controls access to the data. If one person has the authority to write
programs and access data, then control issues become a concern. The potential to commit
fraud or embezzlement or destroy or alter the company’s records becomes too great.

11. Discuss disadvantages of a partitioned database.

Response:
1) The partitioned approach works best for organizations that require minimal data
sharing among their distributed IT units. The primary user manages data requests from
other sites. To minimize data access from remote users, the organization needs to
carefully select the host location. Identifying the optimum host requires an in-depth
analysis of user data needs.
2) In a portioned environment, it is possible for multiple sites to lock out each other from
the database, thus preventing each from processing its transactions.

12. In a distributed data processing system, why can temporary inconsistencies result in
permanent damage to accounting records? Explain with an example.

Response: In between the time a processing application (a) updates a subsidiary account
and (b) updates the control account, a temporary inconsistency exists. Permanent damage
may result between the time an account value is read and the time it is written or updated.
Consider the following example where a payment for sub account 1 is received for $500
and a payment is received for sub account 2 for $800.
AR
Control
Time Site Instruction Sub Account 1 Sub Account 2 Account
1:00:001 A Read sub acct 1 1,000
1:00:001 B Read sub acct 2 3,000
1:00:002 A Update sub acct 1 500
1:00:002 B Update sub acct 2 2,200
1:00:003 A Read control acct 20,000
1:00:003 B Read control acct 20,000
1:00:004 A Update control acct 19,500
1:00:005 B Update control acct 19,200

Thus, the data never reflect the second to last instruction indicated. The AR control
 account should reflect payments received of $1,300 and have a value of $18,700, but
reflect payments of only $800. Thus, the transaction of $500 is lost, and the control and
subsidiary ledgers are out of balance.

13. What is a replicated database, and what control issues exist?

Response: Replicated databases are effective in companies where there exists a high
degree of data sharing but no primary user. Since common data are replicated at each IT
unit site, the data traffic between sites is reduced considerably. Figure 4.18 in the text
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 due to data traffic are minimized. The
problem with this approach is maintaining current versions of the database at each site.
Since each IT unit processes only its transactions, common data replicated at each site are
affected by different transactions and reflect different values.

14. Which database method would be most appropriate for ticket sales at thirty
different outlets to an assigned seating concert? Why?
Response: Partitioned databases would not be appropriate because the seats received by
the customer would be a function of the ticket outlet where the purchase is made.
Replicated databases would not be appropriate because duplicate tickets would be sold.
Centralized databases with local processing are the appropriate method. The seat data
would be centrally stored. Only the data for the seat being purchased would be locked and
sent to the appropriate node. Once the seat is sold, the record is processed locally and
then the data are transmitted back to the central computer.

15. Why is it risky to allow programmers to create user subschemas and assign access
authority to users?
Response: Such a subschema is a subset of the total database that defines the user’s
access authority. The database administrator has primary responsibility for designing
these authorization tables. The programmers should not perform this task because the
concept of authorization could be circumvented. The tasks of programming and the
creation of user authorization tables are incompatible tasks and should be performed by
different individuals.

16. Is access control of greater concern in the flat-file or database file environment?
Response: In the flat-file environment, data and users linked and are separated through
ownership. The physical data files are accessed only by the owner of the data. In the
database environment, data are centrally stored and shared by many users. The
assignment of user privileges via authorization tables determines who accesses the data
and access control thus becomes a greater concern.

17 Discuss the relationship between attributes and entities.

Response: Entities are defined by attributes. For example, an Employee entity may be
defined by the following set of attributes: Name, Address, Job Skill, Years of Service, and
Hourly Rate of Pay. Each occurrence in an entity consists of the same types of attributes,
but values of each attribute type will vary among occurrences. Because attributes
logically define a particular entity, they are unique to it and the same attribute type should
not be used to define two different entities.
18. In a database environment, individual users may be granted summary and
statistical query access to confidential data to which they normally are denied direct
access. Describe how security can be preserved through inference controls.
Response: Inference controls are used to prevent users from inferring, through query
features, specific data values that they otherwise are unauthorized to access. Inference
controls attempt to prevent three types of compromises to the database.
a. Positive compromise. The user determines the specific value of a data item.
b. Negative compromise. The user determines that a data item does not have a specific
value.
c. Approximate compromise. The user is unable to determine the exact value of an item
but is able to estimate it with sufficient accuracy to violate the confidentiality of the data.

19. Discuss and give an example of one-to-one, one-to-many, and many-to-many record
associations.
Response: A one-to-one association means that for every occurrence in record type X,
either zero or one occurrence exists of record type Y. An example would be that for every
student, only one social security number exists.
A one-to-many association means that for every occurrence in record type X, either zero,
one, or many occurrences exist of record type Y. An example would be buyers of
assigned seating at concerts. Each potential buyer would leave the sales box office with
zero, one, or many seats.
A many-to-many association is a two-way relationship. For each occurrence of record
types X and Y, zero, one, or many occurrences exist of record type Y and X, respectively.
An example would be a student-professor relationship. Each student has multiple
professors each semester, and each professor has multiple students each semester.

20. Explain the deadlock phenomenon. Discuss how it could occur with a phone-in mail
order system that locks the inventory records until the order is complete.
Response: A deadlock occurs when multiple sites lock out each other from the data they
need to process transactions. Phone-in mail-order Example. Customer 1 wants to order
two items—Item A and Item B. The customer informs the phone clerk that he or she
wants Item A, and the record for Item A is locked until the order is complete in case any
changes are made. Customer 1 then requests Item B, but it is locked by another
customer’s order. The phone clerk will apologize for the delay and say the system is slow
today. Meanwhile, Customer 2 who has just ordered Item B and locked it requests Item
A, which is locked because of Customer 1’s order. The phone clerk who is helping
Customer 2 will apologize for the delay and say the system is slow today. Unfortunately,
neither record can be unlocked until the competing orders are complete, which results in
deadlock. This condition will require system intervention to resolve it.
MULTIPLE CHOICE QUESTIONS

1. c
2. a
3. a
4. c
5. d
6. d
7. c
8. c
9. b
10. c
11. b
12. e

PROBLEMS

1. Converting from Legacy System to DBMS

The Johnson Manufacturing Corporation employs a legacy flat-file data processing system that
does not support data sharing between key business processes including sales order processing,
production planning, inventory management, procurement, payroll, and marketing. It has long
been though that upgrading the system would be too disruptive to operations. Recently corporate
management has changed its thinking on this matter and has hired your consulting firm to
investigate the possibility of switching to a relational database management system. Prepare a
memo to the top management at Johnson explaining the advantages of a DBMS. Comment in
your memo on the necessity of hiring a database administrator and the role this person would
play.

Response:
Responses to this problem will vary. The sample memo below covers the key issues that should
be discussed:

June 23, 20XX

MEMO TO: Robert Johnson, CEO
Ryan Lindquist, CFO
Mike Alogna, COO
Bee N. Counter, Controller
Aaron York, CIO

FROM: Technical Solutions, Inc.

SUBJ: Conversion to a DBMS

After examining Johnson Manufacturing operations our consulting team feels confident that your
organization would benefit greatly from a relational database management system. Such a system
will allow data sharing among all functional areas and thus greatly enhance communications
between departments and improve business operations. An operational problem that has
repeatedly come to light is the lack of intra-organizational communication. This has resulted in
out-of –stock situations, over stock situations, shipping delays, and shipping errors. In addition to
adding considerable cost to your operations, these problems have had an adverse effect on your
customer and vendor relationships
A relational database management system will provide a central data source whereby all
authorized users may gain access to the data they need for decisions and to support day-to-day
operations. Also, a database system will greatly simplify you data processing burden. Currently,
you are transcribing data multiple times as it is moved from one user’s system to another. This
error-prone activity will be eliminated in a database environment. Once data are entered into the
system, they are available to all legitimate users.

We propose that you begin a conversion to a DBMS at your earliest convenience. The
process will take approximately one year to complete, but you will begin to experience some of
the benefits within six months as key systems are cut over to the central database.

This conversion will require the permanent appointment of a database administrator. A

centralized DBMS requires that the shared data be designed, managed, and maintained by an
independent administrator. This individual would also be responsible for assigning user access
privileges. The key responsibilities of a database administrator are summarized below:

Database Planning: Implementation:

Develop organization’s database strategy Determine access policy

Define database environment Implement security controls

Define data requirements Specify tests procedures

Develop data dictionary Establish programming standards

Design Operation and Maintenance:

Logical database (schema) Evaluate database performance

External users’ views (subschemas) Reorganize database as user needs demand

Internal view of databases

Database controls Review standards and procedures

Change and Growth:

 Plan for change and growth Evaluate new

technology

2. Database Authorization
Some relevant database tables and attributes are presented in the figure designated Problem 4-2 in
the chapter.

Required:
A) Create a database authorization table for an Accounts payable clerk. Structure your response
similar to the example below:

Table 1 Name Table 2 Name Table …..

Authority Level
Read Y Y Y
Insert Y N N
Modify Y
Delete

Indicate the name of each table the AP clerk may access and the degree of access privilege such
as:
Read data from the table
Insert data into the table,
Modify or edit data in the table
Delete data (attribute values or entire records) from the table

B) Explain your answers to part (A) above.

Response:
A) The table below illustrates the appropriate access privileges for the AP clerk whose job is to
review the supplier’s invoice and set up a liability, which will later be paid.

Database Table Purchase Order Receiving Report Vendor Invoice

Authority Level

Read Y Y Y

Insert N N Y
 Modify N N N

Delete N N N

B) The process involves performing a three-way-match of the PO, receiving report, and the
Vendor Invoice. Before setting up an account payable the clerk will verify the items invoiced
were ordered (PO), received in good condition (receiving report), and that the invoice charges are
correct. To do this the clerk needs “Read” access to the PO and receiving report tables and
“Read” and “Insert” access to the Vendor Invoice table. Once the three-way-match is complete,
the clerk transcribes the details from the supplier’s hard copy invoice to the Vendor Invoice table
and assigns a due date. This establishes the liability. The unpaid invoices at any point in time
constitute the accounts payable.

The AP clerk normally would not have “modify” or “delete” access to the Vendor Invoice table.
Editing and deleting existing records in a table should be a supervisor function.

Also, the clerk should not have insert, modify, or delete access to the PO and receiving report
tables. Such access would allow the AP clerk to enter fraudulent PO and receiving report records.
The clerk’s legitimate “insert” privileges would then allow him to create a fraudulent supplier’s
invoice (on behalf of himself or a conspirator) and set up a false liability, which would later be
paid.

3. Database Authorization Table

Required:
A) Using the database table and attribute structures presented in Problem 4-2 create a database
authorization table for a Sales Clerk. Structure your response similar to the example provided in
Problem 4-2.

B) Explain your answer to (A) above.

Response:

The table below illustrates the appropriate access privileges for the sales clerk whose typical job
is to review customer credit, check inventory availability, and create a sales invoice record.

Database Table Customer Table Inventory Table Sales Invoice

Authority Level

Read Y Y Y

Insert N N Y

Modify N N N

Delete N N N

B) The clerk needs “Read” access to the Customer Table attributes (including “available credit”)
and to the “Quantity on Hand” and “Sales Price” attributes in the Inventory Table. Once credit
worthiness and inventory availability are established, the clerk transcribes the customer order
details to the Sales Invoice table. The unpaid sales invoices at any point in time constitute the
accounts receivable.

The Sales clerk normally would not have “modify” or “delete” access to the Sales Invoice table.
Editing and deleting existing records in a table should be a supervisor function.

Also, the clerk should not have “insert”, “modify”, or “delete” access to the Customer Table or
the Inventory Table. Such access would allow the sales clerk to alter a customer’s available credit
and create a sales invoice for a customer who lacks adequate credit. It would also permit the sales
clerk to modify the Sales Price attribute in the Inventory Table and thus discount the price of sales
to certain customers.

4. Access Privileges
Mary Johnson, the Ajax Construction Co. cash disbursement clerk, has the database access
privileges presented in the table below.

Required:
Discuss the appropriateness of the access privileges assigned. What, if any, internal control
problems may result.

Database Table Purchase Order Receiving Vendor Invoice Cash

Report Disbursements
Authority Level
Read Y Y Y Y
Insert Y Y Y Y
Modify Y N N Y
Delete N N N Y

Response:
The Cash disbursement clerk should have “read” only access to the PO, Receiving Report, and
Vendor Invoice tables above. These documents provide evidence of a liability, which the clerk
discharges by writing a check. As a final control prior to writing the check, Mary may review the
PO, receiving report, and invoice.

A problem lies in the degree of access Mary has. Her “Insert” capability would allow her to
create a purchase order, receiving report, and supplier’s invoice (for herself or an associate) thus
creating the illusion of a transaction. Mary’s role as CD clerk allows her to write a check to
discharge the phony liability that she established. On the surface the transition looks valid
because it has the necessary supporting documents.

5. Distributed Databases
The XYZ Company is a geographically distributed organization with several sites around the
country. Users at these sites need rapid access to common data for read-only purposes. Which
distributed database method is best under these circumstances? Explain your reasoning.
Response: The best distributed approach is a replicated database.

Reasoning:
● The users are distributed around the country and need rapid access to data. A centralized
model may result in long delays because of network traffic and database lockout.
● User data needs are common, not unique. Because there are no identifiable primary users,
partitioning the database will accomplish nothing.
● Because usage is read-only, changes will not occur and database concurrency is not a
problem.

6. Distributed Databases
The ABC Company is a geographically distributed organization with several sites around the
country. Users at these sites need rapid access to data for transaction processing purposes. The
sites are autonomous; they do not share the same customers, products, or suppliers. Which
distributed database method is best under these circumstances? Explain your reasoning.

Response: The best distributed approach is a partitioned database.

Reasoning:
● The users are distributed around the country and need rapid access to data. A centralized
 model may result in long delays because of network traffic and database lockout.
● User data needs are unique with identifiable primary users. There is no need to replicate
the entire database.
● Because users are unique, changes to the database will not cause database concurrency
problems.
7. Distributed Databases

Mega wants to improve response time by distributing some parts of the corporate database
while keeping other parts of it centralized.
A) Develop a schema for distributing Mega Supply Corporation’s database. Add new
tables and attributes as needed.
B) Briefly explain how the new system will operate.

Response:

Commercial Division Database

Customer Table (Commercial customers only. Partitioned by Customer Class)
Customer Number
Billing Address
Shipping Address
Line of Credit
Sales Invoice (Replicated at Corporate Office)
Sales Invoice Number
Customer Number
Product Number
Invoice Amount
Invoice Date
Inventory Table (Partitioned on Inventory Class)
Product Number
Quantity on hand
Unit Cost
Vendor Number
Vendor Table (Partitioned based on products sold by division)
Vendor Number
Vendor Address
Tel Number
Purchase Order Table (Replicated at Corporate Office)
PO Number
Vendor Number
Product Number
Quantity
 Order Date
Receiving Report Table (Replicated at Corporate Office)
PO Number
Product Number
Quantity Received
Condition Code
Received Date

Consumer Division Database

Same structure as the Commercial Division, but for consumer customers

Corporate Division Database

Sales Invoice (AR) (Replicated at Division)
Sales Invoice Number
Customer Number
Product Number
Invoice Amount
Invoice Date
Cash Receipts Table (Centralized at Corporate)
Cash Receipts Number
Customer Number
Check Number
Sales Invoice Number
Check Amount
Date
Purchase Order Table (Replicated at Division)
PO Number
Vendor Number
Product Number
Quantity
Order Date
Receiving Report Table (Replicated at Division)
PO Number
Product Number
 Quantity Received
Condition Code
Received Date

Vendor Invoice Table (AP) (Centralized at Corporate Office)

Vendor Invoice Num
Vendor Number
Product Number
Invoice Amount
Invoice Date
Cash Disbursement Table (Centralized at Corporate Office)
Check number
Vendor Invoice Num
Check Amount
Date

Description of New Process

The revised system functions as follows:
● Commercial and Consumer divisions receive sales orders from customers
● Clerks at divisions check credit using partitioned Customer table.
● Clerks at divisions determine inventory availability from partitioned Inventory table and
update Inventory records to reflect the sale.
● Clerk at division prepares a customer invoice record.
● At end of day Customer Invoice Table is replicated at the Corporate office
● Corporate office bills customer, maintains AR accounts, and receives cash payments from
customer.
● When inventories falls to reorder point, division sends PO to vendor
● When inventories arrive division prepares receiving report and updates Inventory table.
● At end of day PO table and Receiving Report Table are replicated at Corporate office.
● Corporate office receives Vendor Invoice and sets up AP to be paid.
● Corporate office makes payment to vendor.
This system of replicated, centralized and partitioned database tables allows Mega to
process sales locally and quickly and manage billing, AR, Cash receipts, AP and Cash
disbursements centrally, efficiently, and with adequate control.