Transcript Chapter 9 - Accounting and Information Systems Department
Chapter 9
Database Management Systems
Accounting Information Systems, James A. Hall 5 th edition 1
Flat-File Versus Database Environments
Computer processing involves two components: data and instructions (programs). Conceptually, there are two methods for designing interface between programs and data: file-oriented (flat-file) processing: A specific data file was created for each application [
legacy systems
] data-oriented processing: Create a single data repository to support numerous applications [
current systems
] Disadvantages of file-oriented processing include
redundant data
and
programs
and
varying formats
for storing the redundant data.
File-Oriented (Flat-File) Environment
User 1(
Sales
) Transactions Data Program 1 A,
B
,C User 2 (
A/R
) Transactions Program 2 X,
B
,Y User 3 (
Payroll
) Transactions Program 3 L,
B
,M Where
B
is a customer record. Assume
B
’s address changes. Each user that works with the
B
record must modify the address, increasing chance for error.
Database Approach
Database User 1 (
Sales
) Transactions Program 1 User 2 (
A/R
) Transactions User 3 (
Payroll
) Transactions Program 2 D B M S A,
B
, C, X, Y, L, M Program 3 With the database approach, any User could change
B
’s address - it would be changed for all users. changes. [Assume all users have authority to change the address for this example.]
Data Redundancy & Flat-File Problems
Data Storage
- creates excessive storage costs of paper documents and/or magnetic form
Data Updating
- any changes or additions must be performed multiple times (see example of
B
’s address)
Currency of Information
- potential problem of failing to update all affected files
Task-Data Dependency
- user’s inability to obtain additional information as his or her needs change
Advantages of the Database Approach
Data sharing/centralized database resolves flat-file problems:
No data redundancy -
Data stored only once, eliminating data redundancy and reducing storage costs.
Single update -
Because data are in only one place, requires only single update procedure, reducing time/cost of keeping database current.
Current values -
Change to database made by any (authorized) user yields current data values for all other (authorized) users.
Task-data independence -
As users’ information needs expand beyond immediate domain, new needs can be more easily satisfied than under flat-file approach. 6
Disadvantages of the
Database Approach
Can be costly to implement additional hardware, software, storage, and network resources are required – yet these costs continue to drop… Can only run in certain operating environments may be unsuitable for some existing system configurations (e.g., legacy systems) 7
Internal Controls and DBMS
1 Purpose of DBMS is to
provide controlled access
to database.
DBMS is special software programmed to know which data elements each user is authorized to access and
deny unauthorized requests of data
.
1 DBMS = database management system 8
Query Language
Query capability permits both end users and professional programmers to access data (information) in database without writing conventional programs .
9
Three Steps in Designing a Database
1.
3.
Prepare conceptual model Identify
entities
Identify
relationships
between entities Prepare ER diagram (
ERD
)
Select logical database model (which will always be
relational
nowadays) Transform conceptual data model using logical database model Implement physical design Physical structures Access methods 10
Phase 1
Prepare the Conceptual Model
Draw an ERD to capture the process.
11
Example of a Relationship Linking Two Entities
CUSTOMER
places
ORDER
Cust Number Name Order Number Example of 1:M relationship, using Crow’s Feet notation. We usually don’t cover the “0” relationship.
Date
Phase 2
Create relational tables.
13
Logical Data Structures
Objective is to develop structure efficiently so data can be accessed quickly and easily.
Four types of database structures are: hierarchical (tree structure) network
relational
object-oriented Concentrate on relational DBs. They are the systems being created at the current time.
14
The Relational Model
Relational model portrays data in form of
two dimensional tables (looks like Excel worksheet):
relation
- database table
attributes/fields (data elements)
- columns
tuples (records)
- rows
data
- intersection of rows and columns 15
Properly Designed Relational Tables
No repeating values –
repeat in a table.
Primary Key cannot Attribute values in any column must all be of same class. [can’t put
text
in a
date
field] Each column (field name) in a
table
must be uniquely named. [can’t have 2 address fields both named
Address
– use
Addr1
and
Addr2
] Each row (record) in a
table
must be unique in at least one attribute (primary key) 16
Relational Model Data Linkages (>1 table)
No explicit pointers are present – data are viewed as collection of independent tables.
“Relations” formed by
attribute/field that is common
to both tables in relation. This field is a “foreign key.” How to assign foreign keys: if 1:1 association, either of table’s primary keys may be foreign key.
if 1:M association, primary key of the ONE side is embedded as foreign key in the MANY side.
if M:M (M:N) association, create separate linking table.
17
Three Types of Anomalies
(anomalies are found in Unnormalized tables)
Insertion Anomaly
item.
: New item cannot be added to table until at least one entity uses particular attribute
Deletion Anomaly
is lost.
: If attribute item used by only one entity is deleted, all information about that attribute item
Update Anomaly
: Modification on attribute must be made in each of rows in which attribute appears.
Anomalies can be corrected by creating
relational tables
.
18
Relational Tables
Various items of interest (customers, inventory, sales) stored in SEPARATE tables in database.
Advantages: Removes all three anomalies Efficient use of space.
Flexible. Users can form ad hoc relationships for queries.
19
Normalization Process
Process that
breaks up large, complex tables into smaller tables
that meet two conditions: all nonkey attributes (fields) in table are dependent on primary key (PK) all nonkey attributes (fields) are independent of other nonkey attributes (fields) When unnormalized tables are split and reduced to third normal form , they are linked together by foreign keys (secondary keys).
20
all nonkey attributes (fields) in table are dependent on primary key (PK)
Student ID Lname
80012 Garcia 80012 Garcia 80012 Garcia 80014 Henry 80020 Hernandez 80020 Hernandez 80033 Johnson 80033 Johnson
Fname
Maria Maria Maria Hank Barbara Barbara Kris Kris
Major
ACCT ACCT ACCT MKT CIS CIS ECON ECON
CRN Course
17256 ACCT3321 17535 ACCT3320 17004 MKT3311 17155 MKT3311 17200 CIS2320 17155 MKT3311 16256 ACCT3321 17005 ECON3315
Instructor ID
G001 G003 G005 G005 G010 G005 S001 S005 80033 Johnson Kris ECON 16500 CIS3545 M001 This is an unnormalized table. Only Lname & Fname depend on the PK of Student ID. Other fields belong in another table(s). 21
Steps in Normalization
Unnormalized Table with repeating groups First normal form (1NF)
Does field depend on PK? If no, remove and put in another table.
Second normal form (2NF) Third normal form (3NF)
Only concerns tables with composite PKs. See Fig. 9-38 When field depends on another non-key field in table. See Fig. 9-40
1. Remove repeating groups 2. Remove partial dependencies 3. Remove transitive dependencies
Accountants and Data Normalization
Update anomalies can generate
conflicting and obsolete database values
.
Insertion anomalies can result in
unrecorded transactions and incomplete audit trails
.
Deletion anomalies can cause loss of accounting records and
destruction of audit trails.
Accountants should understand data normalization process and be able to determine whether database is properly normalized.
Phase 3
Implement Physical Design
Decide about software and hardware.
24
Distributed Data Processing
25
CENTRALIZED COMPUTER SERVICES FUNCTION
VP Marketing Systems Development New Systems Development Systems Maintenance President VP Computer Services VP Operations VP Finance Database Administration Data Control Data Processing Data Preparation Computer Operations Data Library
DISTRIBUTED ORGANIZATIONAL STRUCTURE
VP Marketing VP Finance President VP Administration Treasurer Controller Manager Plant X VP Operations Manager Plant Y IPU IPU IPU IPU IPU IPU 26
Characteristics of DDP
1
Advantages:
Cost reductions
in hardware and data entry tasks
Improved cost control
responsibility
Improved user satisfaction
because control is closer to user level
Backup of data
can be improved through use of multiple data storage sites
Disadvantages
Loss of control
(organization-wide)
Mismanagement
of organization-wide resources Hardware/software
incompatibility Redundant
tasks/data
Incompatible tasks
consolidated may be
Lack of standards
27 1 DDP = Distributed Data Processing
Centralized Databases in DDP Environment
Data are retained in
central location
.
Remote
IPUs (workstations) send
requests for data
.
Central
site
services needs
of remote workstations.
Actual data processing is performed at remote workstation 28
Data Currency
Can be an issue in DDP with centralized database During transaction processing, data will
temporarily be inconsistent
as record is being read and updated.
Database lockout procedures are necessary
to keep workstations from reading inconsistent data and/or from writing over a transaction being written by another workstation.
29
Distributed Databases: Partitioning
Splits central database into segments that are distributed to their primary users Advantages: users’ control is increased by having data stored at local sites transaction processing response time is improved volume of transmitted data between workstations is reduced Reduces potential data loss from a disaster
Distributed Databases: Replication
Duplication of entire database for multiple workstations Effective for situations with a high degree of data sharing, but no primary user supports read-only queries.
Data traffic between sites is reduced considerably.
The Deadlock Phenomenon
Especially a problem with
partitioned
databases Occurs when
multiple sites lock each other out
of data that they are currently using Special software needed to analyze and resolve conflicts. 32
The Deadlock Phenomenon
Locked A, waiting for C Locked E, waiting for A A,B E, F C,D Locked C, waiting for E
34