Transcript Accounting Information Systems: Essential Concepts

Accounting Information Systems:
Essential Concepts and Applications
Fourth Edition by Wilkinson, Cerullo, Raval,
and Wong-On-Wing
Chapter 6: Data-Base
Modeling and Applications
Slides Authored by Somnath Bhattacharya, Ph.D.
Florida Atlantic University
Introduction
We use the term Data Base to mean the
collected data sets that are organized and
stored as an integral part of a firm’s computerbased information system
Data Sets are flexible data structures that
include groupings of data that are logically
related
The Database Approach to
Data Storage
A Database is a set of computer files that
minimizes data redundancy and is accessed by
one or more application programs for data
processing
The database approach to data storage applies
whenever a database is established to serve two
or more applications, organizational units, or
types of users
A Database Management System (DBMS) is a
computer program that enables users to create,
modify, and utilize database information
efficiently
Characteristics of the
Database Approach
Data Independence - the separation of the
data from the various application programs and
other accesses by users
Data Standardization - data elements within a
database have standard definitions, thus stored
data are compatible with every application
program that accesses the data
One-Time Data Entry and Storage individual data values are entered into the
database only once; consequently, redundancy
is reduced and inconsistencies between data
elements are eliminated
Characteristics of the
Database Approach
Data Integration - data sets integrate the data,
which enables all affected data sets to be updated
simultaneously
Shared Data Ownership - all data within a
database are owned in common by the users. The
portion of the database that is of interest to each user
is known as the sub-schema
Centralized Data Management - the database
management system stands guard over the database
and presents the logical view to users and
application programs
Program-Data
Independence
Application
Program A
Database
Management
System
Application
Program B
Figure 6-1
Database
Questions for Database
Design and Construction
 What data management perspective should be adopted?
 What is the proposed system’s initial objective?
 What systems and users will use the data?
 Which existing or future systems will the proposed system
interface with?
 How much data will be stored initially? In the future?
 How many data accesses (reads and updates) will occur on an
hourly, daily, and monthly basis?
 How can the data be organized, both logically and physically,
to best serve the users of the system?
Iterative Phases in Database
Development: Planning & Analysis
Planning
Cost-benefit Analysis
Effective usage Analysis
Analysis
Enterprise Diagram
User Requirements
Data requirements
Firm’s operations and relationships
Development of logical design
Expected output requirements
Inputs
Processes
Appropriate Conceptual Model
Data Modeling through Entity-Relationship Diagrams
Specification of logical view(s)
Designation of Primary and Secondary keys
Development of Data Dictionary
Iterative Phases in Database
Development: Detailed Design
Technical Specifications
Report Layouts
Data Flows
Screen Layouts
DBMS Selection
Data Definition Language (DDL)
Data Manipulation language (DML)
Query language [Structured Query Language (SQL)
and/or Query by Example (QBE)]
Data-base Control System (DBCS)
DBMS
Many DBMS packages allow users to:
Analyze Data
Prepare ad hoc or customized Reports
Create and Display Graphs
Create Customized Applications via
Programming Languages
Import and Export Data
Perform On-line Editing
Purge or Archive Obsolete Data
Backup data
Maintain Security Measures
Interface with Communication Networks
Iterative Phases in Database
Development: Post-Design Phases
Implementation
Testing
Unit Testing
System Testing
User Acceptance Test
Maintenance
Entity-Relationship Model
Relative to the detailed nature of Record layouts and
data dictionaries, Entity-Relationship (E-R) Models
provide a broader and more conceptual view of the
firm’s data
A Data Model documents the key entities in a firm
and the relationships or associations among those
entities
An Entity is an object that exists and is identifiable.
e.g., an agent, event, or a resource
Entity-Relationship Model
Conventions
Rectangles represent entities and diamonds represent
relationships.
Each rectangle is usually denoted by the attributes of
the entity.
E-R Diagrams can easily model the information
needs of the entire enterprise or segments of the
enterprise such as divisions or departments, and even
detailed data issues such as detailed data repositories
such as records and/or tables.
Database Relationships
In a database, relationships occur among data
elements for two reasons:
Because of the nature of the elements themselves.
e.g., the relationship between a customer no. and a
customer name
Because of the need to retrieve
information from a database in
some prescribed manner. e.g.,
customers and invoices.
Kinds of Relationships
No relationship. e.g., student numbers and physical plant
codes
One-to-one relationship. This occurs least frequently,
e.g., reference no. to course description; product no. to
product description; customer no. to customer name
Directed relationships - 2 views:
One-to-many relationship. e.g., advisor to students,
customer to invoices
Many-to-one relationship. e.g., invoice lines to invoice
Many-to-many relationship. e.g., students to courses,
customers to products
A Many-to-Many Relationship:
Variation of Figure 6-6
Customers
April
June
Summers
m
Product A
n
Products
Figure 6-6
Product B
Product C
Product D
Relational Databases
In a relational database, data are perceived by
users to be structured in the form of simple flat
files or tables
Each table consists of records that are
comprised of a key and associated data
elements
In order to lay claim as a relational database, it
must do the following:
Present data to users as tables only
Support the relational algebra functions of
Restrict (Select), Project, and Join without
requiring any definitions of access paths to support
these operations
Relational Algebra Functions in
a Relational Database - Select
Select (Restrict): This function produces a
new table with only rows from a single
source table whose columns meet
prescribed conditions, e.g.,
Customer_Name=Adam Smith;
DOB=2/29/64; Legal Residence=California,
etc
Select
Cust
No.
Cust. Date Credit Legal
Name of
Limit Res.
Birth
1000
Adam
Smith
3-12-62 1000
CA
1010
Lord
2-29-64 2000
Keynes
TX
Relational Algebra Functions in
a Relational Database - Project
This function produces a new table with only
some columns from a single source table. e.g.,
Project Student table on Student_Name and
Student_Major
Student_Name
Student_Major
Estudiante Garcia
French
Madeleine Notallbright
International Relations
Relational Algebra Functions in a
Relational Database - Select & Project
The combination of Select and Project
produces a new table with both fewer
columns and rows than the original
table. e.g., Project on Student_Name
and Student_Major where
Student_Major = Latin
Select & Project
Student_Name
Penny Pasta
Connie Curry
Tony Lama
Student_Major
Latin
Greek
Tibetan
Student_Name Student_Major
Penny Pasta
Latin
Student_Status
Senior
Freshman
Junior
Relational Algebra Functions
in a Relational Database - Join
The Join function produces a new table
from two or more source tables that
have at least one common column
The new table is wider than either of the
two source tables because it contains all
the columns from both source tables
Join
Customer_Name
Customer_Code
John Doe
1001
Customer_Code
Credit_Limit
1001
10,000
+
Customer_ Customer_ Credit_Limit
Name
Code
John Doe 1001
10,000
=
This may get repeated
twice, but then will be
eliminated under the
third normal form.
Query Languages for a
Relational Database
Structured Query Language
(SQL)
SELECT CLIENT_NO, CLIENT_NAME,
PROJECT_NAME
FROM PROJ.TABL
WHERE CLIENT_NO = 531
Query-by-Example (QBE)
Use of Dynasets
Relational Databases:
Advantages & Disadvantages
Advantages
Ease of use for nontechnical users
Flexible structure
English-like commands
or menus
Easy structural changes
Disadvantages
Relative inefficiency
Huge storage space
required
More redundancy than
other data base
structures
Not suitable for highvolume applications
Hierarchical Database
Structure
The hierarchical data structure (or tree
structure) expresses hierarchical relationships
among stored data.
The root node is at the top and for
any two adjacent records, the elder
or higher-level record is called the
parent record.
The younger or lower-level record
is called the child record and any
two records on the same level are called sibling
records.
The Hierarchical Conceptual Model
Customer
Salesperson
Invoice
Salesperson
 In this model all data
deemed necessary must be
defined when the database
Invoice 1 Invoice 2 Invoice 3
is created
 The inverted tree structure
Line
Line
of the database means that
Item 1
Item 2
each node can only have
one parent
 Therefore, the hierarchical
Customer
model only allows for oneto-one and one-to-many
Invoice 1 Invoice 2 Invoice 3
relationships. Many-tomany relationships cannot
be modeled except through
Line
Line
duplication of data elements
Item 1
Item 2
Hierarchical Conceptual
Model - III
 In this model, pointers must
be stored either within the
records or in separate index
files
Customer A
Rec.
Address
Rec.
Content
Forward
Pointer
1
2
3
Cust A
Cust B
Inv 10
4
6
0
4
Inv 9
7
Inv 16
0
Inv 8
3
Inv 12
5
Invoice 9 Invoice 12 Invoice 16 5
Customer B
Invoice 8
6
Invoice 10 7
The Use of Pointers in the
Hierarchical Conceptual Model
Rec.
Address
Rec.
Content
Forward
Pointer
1
2
3
Cust A
Cust B
Inv 10
4
6
0
4
Inv 9
7
5
Inv 16
0
6
Inv 8
3
7
Inv 12
5
The Network Structure
Like the Tree structure, the Network
structure establishes explicit access paths or
links among data nodes
Unlike the Tree Structure, however, the
Network structure:
Allows any data node to be linked to any other
node
Permits entry at more points than a single root
node
Requires at least one subordinate data node to
have two or more owner nodes
Network Database
Structure
The network data structure handles
complex relationships among records by
linking related records together with
“pointer fields”
Pointer fields are embedded in each record
and contain disk addresses of related
records
The pointers maintain the data relationships,
thereby enabling an AIS to prepare familiar
reports
Network Conceptual
Model - I
In this model there is no distinct data hierarchy.
This enables network models to handle all types of
relationships
Along with this ability, though, comes high
inherent complexity
Simple networks contain one-to-many
relationships
Complex networks contain many-to-many
relationships. Usually these are reduced to
numerous one-to-many relationships through
intersection records
Network Conceptual
Model - II
Pointers are also used to link
data elements in network models.
Micro-computer-based network
models are uncommon. These are
more common in large mainframe
environments.
True Network Model Decomposed
to One-to-Many Relationships
Customers
Products
Customer
Product
Cust-Prod
Object-Oriented Database
Structure - I
The object-oriented database
(OODB) is a new type of database
that stores objects with (non-textual)
information in them
These unstructured objects may be
graphic images, still photographs,
animated visual, music and speeches
Objects are grouped into object
classes, with each member of the
class having the same set of
attributes, which can be
manipulated
Object-Oriented Database
Structure - II
Object Classes feature class hierarchies
Super-classes are at the top of the
hierarchies, with classes and sub-classes
linked below
Movement within class hierarchies is
downward from super-class to class to
subclass
Classes may also form sidewise
associations, e.g., association of university
person with university; faculty with
academic dept
Characteristics of Objects
Attributes
Make
Model
Year
Object
Drive
Operations
Engine
Size
Mileage
Car
Park
Lock
Wash
Color
Characteristics of Objects
Attributes
Part No.
Note that Objects possess 2 characteristics:
attributes & operations
Description
Qty
on Hand
Object
Reduce
Operations
Reorder
Point
Order
Qty
Supplier
No.
Inventory
Review
Qty
Reorder
Replace
Sometimes these may change objects
Object-Oriented Database
Structure - III
OODBs feature:
Encapsulation: Storing procedures or operations
called methods with the data to which the methods
relate.
This brings together the data attributes and operations
pertaining to objects and object classes
Because of encapsulation, the application programs that access
the data base can be greatly simplified, thereby reducing
programming errors
Inheritance: This allows subclasses to inherit methods
and/or data from higher classes within a class hierarchy
The major advantage of inheritance is that programmed
instructions (objects) are reusable.
Libraries of commonly used objects (programs) can be
maintained
These standardized programs (fully pre-tested and applied) can
greatly reduce reprogramming efforts
Inheritance Between
Classes
CONTROL
Object
Class
Operations:
Verify key
before Update
*
Other
*
operations
*
Object
Subclasses
ACCT-PAY
ACCT-REC
INVENTORY
Attributes:
Ven-Num (key)
*
Other
*
Attributes
*
Attributes:
Cust-Num (key)
*
Other
*
Attributes
*
Attributes:
Part-Num (key)
*
Other
*
Attributes
*
Accounting Information Systems:
Essential Concepts and Applications
Fourth Edition by Wilkinson, Cerullo,
Raval, and Wong-On-Wing
Copyright © 2000 John Wiley & Sons, Inc. All rights reserved.
Reproduction or translation of this work beyond that permitted in
Section 117 of the 1976 United States Copyright Act without the express
written permission of the copyright owner is unlawful. Request for
further information should be addressed to the Permissions Department,
John Wiley & Sons, Inc. The purchaser may make back-up copies for
his/her own use only and not for distribution or resale. The publisher
assumes no responsibility for errors, omissions, or damages, caused by
the use of these programs or from the use of the information contained
herein.