Transcript Accounting Information Systems 9th Edition

Accounting
Information
Systems
9th Edition
Marshall B. Romney
Paul John Steinbart
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-1
Relational Databases
Chapter 4
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-2
Learning Objectives
1.
2.
3.
4.
Explain the difference between database
and file-based legacy systems.
Describe what a relational database is and
how it organizes data.
Explain the difference between logical and
physical views of a database.
Create a set of well-structured tables to
properly store data in a relational
database.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-3
Introduction
Ashton Fleming, the accountant for
S&S, believes that the best way to
provide Susan Gonzalez and Scott
Parry with easy access to the
information they need to run their
business is to build S&S’s new AIS as
a database system.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-4
Introduction
Ashton decides to prepare a brief
report for them addressing the
following questions:
 What is a database system?
 What is a relational database system?
 How do you design a relational
database?

©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-5
Introduction
This chapter explains what a
database is and how it differs from a
file-oriented system.
 It also describes the structure of a
relational database system.
 The chapter concludes by discussing
the basic steps involved in designing
a database.

©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-6
Learning Objective 1

Explain the difference between
database and file-based legacy
systems.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-7
Types of Files

1
2
Two basic types of files are used to
store data.
The master file, which is conceptually
similar to a ledger in a manual
system.
The transaction file, which is
conceptually similar to a journal in a
manual system.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-8
File Approach


1
2
For many years, companies created new
files and programs each time an information
need arose.
This proliferation of master files created
problems:
Often the same data was stored in two or
more separate files.
The specific data values stored in the
different files were not always consistent.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-9
File-Oriented Approach
File 1
Fact A
Fact B
Fact C
Sales
Program
File 2
Fact B
Fact D
Fact E
Shipping
Program
File 3
Fact A
Fact G
Fact E
Billing
Program
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-10
Databases



The database approach views data as an
organizational resource that should be used
by, and managed for, the entire organization,
not just the originating department or
function.
Its focus is data integration and data sharing.
Integration is achieved by combining master
files into larger pools of data that can be
accessed by many application programs.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-11
Databases
Database management system
(DBMS) is the program that manages
and controls access to the database.
 Database system is the combination
of the database, the DBMS, and the
application program that uses the
database.
 Database administrator (DBA) is the
person responsible for the database.

©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-12
Database Approach
Database
Sales Program
Fact A
Fact B
Fact C
Database
management
system
Shipping
Program
Fact D
Billing
Program
Fact E
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-13
Learning Objective 2
Describe what a relational
database is and how it
organizes data.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-14
Relational Databases
A data model is an abstract
representation of the contents of a
database.
 The relational data model represents
everything in the database as being
stored in the form of tables.
 Technically, these tables are called
relations.

©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-15
Relational Databases

Each row in a relation, called a tuple,
contains data about a specific
occurrence of the type of entity
represented by that table.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-16
Learning Objective 3

Explain the difference between logical
and physical views of a database.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-17
Logical and Physical
Views of Data
A major advantage of database
systems over file-oriented systems is
that the database systems separate
the logical and physical view of data.
 What is the logical view?


It is how the user or programmer
conceptually organizes and
understands the data.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-18
Logical and Physical
Views of Data

What is the physical view?


It refers to how and where the data
are physically arranged and stored on
disk, tape, CD-ROM, or other media.
The DBMS controls the database so
that users can access, query, or
update it without reference to how or
where the data are physically stored.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-19
Logical and Physical
Views of Data
Logical View User A
Past Due Accounts
Name Balance Days
Jackson 2145
Houston 1595
Logical View User B
October Sales by Region
48
65
Database
DBMS
Operating
system
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-20
Logical and Physical
Views of Data
Program-data independence is
the separation of the logical and
physical views of data.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-21
Schemas
A schema describes the logical
structure of a database.
 There are three levels of schemas:

1
2
3
Conceptual-level schema
External-level schema
Internal-level schema
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-22
Schemas
The conceptual-level schema is an
organization-wide view of the entire
database.
 The external-level schema consists of
a set of individual user views of
portions of the database, also referred
to as a subschema.
 The internal-level schema provides a
low-level view of the database.

©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-23
Schemas
Subschema A
Jackson 210
Houston 100
Subschema B
r
Subschema C
r
xxxxxxx
xxxxxxx
Mapping external level views to conceptual level schema
Inventory
Sales
Customer
Cash receipt
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-24
Schemas
Inventory
Sales
Customer
Cash receipt
Mapping conceptual level facts to internal level descriptions
Inventory Record
Item number – integer (5), non-null, index =
itemx Description – character (15)
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-25
The Data Dictionary
The data dictionary contains
information about the structure of the
database.
 For each data element stored in the
database, such as the customer
number, there is a corresponding
record in the data dictionary
describing it.

©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-26
The Data Dictionary
The data dictionary is often one of the
first applications of a newly
implemented database system.
 What are some inputs to the data
dictionary?

–
–
records of any new or deleted data
elements
changes in names, descriptions, or
uses of existing data elements
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-27
The Data Dictionary

What are some outputs of the data
dictionary?
–

reports useful to programmers,
database designers, and users of the
information system
What are some sample reports?
–
–
lists of programs in which a data item
is used
lists of all synonyms for the data
elements in a particular file
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-28
DBMS Languages

Every DBMS must provide a means of
performing the three basic functions:
1
2
3

Creating the database
Changing the database
Querying the database
The sets of commands used to
perform these functions are referred
to as the data definition, data
manipulation, and data query
languages.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-29
DDL Language

The data definition language (DDL) is
used to...
–
–
–
–
build the data dictionary.
initialize or create the database.
describe the logical views for each
individual user or programmer.
specify any limitations or constraints
on security imposed on database
record or fields.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-30
DML Language
The data manipulation language
(DML) is used for data maintenance.
 What does it include?

–
–
–
updating portions of the database
inserting portions of the database
deleting portions of the database
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-31
DQL Language
The data query language (DQL) is
used to interrogate the database.
 The DQL retrieves, sorts, orders, and
presents subsets of the database in
response to user queries.

©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-32
Learning Objective 4

Create a set of well-structured tables
to properly store data in a relational
database.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-33
Basic Requirements of the
Relational Data Model
1.
2.
3.
4.
Each column in a row must be single
valued.
Primary keys cannot be null.
Foreign keys, if not null, must have values
that correspond to the value of a primary
key in an other relation.
All non-key attributes in a table should
describe a characteristic about the object
identified by the primary key.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-34
Anomalies That May Occur in
Non-Normalized Relational
Tables

Update Anomaly: When changes
(updates) to data values are not
correctly recorded.

Instead of having to update once,
each record in the single table has to
be updated individually in order to
avoid inconsistencies in the database.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-35
Anomalies That May Occur in
Non-Normalized Relational
Tables

Insert Anomaly: There is no way to
store information about one entity in
the database without it being
associated with another entity

In the text, we would not be able to
store information on new customers
without their being associated with
transactions first!
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-36
Anomalies That May Occur in
Non-Normalized Relational Tables

Delete Anomaly: Unintended results
arising from deleting a row of data
pertaining to one entity and resulting in
the deletion of data regarding another
entity as well.

In the text, if a particular Inventory item were
discontinued and hence removed from the
database table, we would lose information
on the customer associated with that
inventory item as well.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-37
Approaches to
Database Design

Normalization



Starts with the assumption that all data is
initially stored in a large non-normalized
table.
This table is then decomposed using a set of
normalization rules to create a set of tables
in the Third Normal Form.
Semantic Data Modeling

The database designer uses his/her
knowledge about the business structure to
create a set of relational tables.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-38
Database Systems and the
Future of Accounting
Database systems have the potential
to significantly alter the nature of
external reporting.
 Perhaps the most significant effect of
database systems will be in the way
that accounting information is used in
decision making.

©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-39
End of Chapter 4
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
4-40