Transcript Gelinas-Dull_8e_Chapter_05_Revised_September_2009

Chapter 5 – Database Management
Systems
Accounting Information Systems 8e
Ulric J. Gelinas and Richard Dull
© 2010 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated,
in whole or in part, except for use as permitted in a license distributed with a certain product
or service or otherwise on a password-protected website for classroom use
Learning Objectives
• Describe the limitations of traditional application
approaches to managing data.
• Analyze the advantages gained by using the database
approach to managing data.
• Create normalized tables in a relational database.
• Use entity-relationship diagrams in database design
and implementation.
• Explain the importance of advanced database
applications in decision support and knowledge
management.
2
Introduction to Databases
• The enterprise database is at the heart of Accounting
Information Systems (AIS).
• The chapter discusses the major types of databases
available and how organizations undertake database
design for AIS.
• Larger organizations store information in data warehouses
in ways that let managers analyze it to gain important
insights.
• Many companies combine their data resources with
decision support systems, executive information systems,
group decision systems, and other advanced technologybased systems to improve decision making and operations.
3
Two Approaches To Business Event
Processing
Applications Approach
•
•
•
•
•
•
Concentrates on the process
being performed.
Data support the role of the
programs that run in each
application system.
Each application collects and
manages its own data in separate,
distinguishable files.
Data redundancy can cause
inconsistencies among the same
data in different files when
information is updated in one file
but not the others.
Storage costs are increased
because the system must store
and maintain multiple versions of
the same data in different files.
Data residing in separate files are
not shareable among applications.
Database Approach
•
•
•
•
•
Facts about events are stored in
relational database tables instead
of separate files.
Solves many of the problems
caused by data redundancy.
Improves the efficiency of
processing data by eliminating data
redundancies and improving data
integrity.
Enables the creation of integrated
business information systems that
include data about all of a
company’s operations in one
massive collection of relational
tables.
Multiple users from throughout the
organization can view and
aggregate event data in a manner
most conducive to their needs.
4
Two Approaches To Event Processing
5
Record Layouts under the Applications
Approach to Business Event Processing
6
Problems with Applications
Approach
• Data redundancy - files stored may
include redundant information
increasing storage requirements and
the risk of inconsistency.
• Data in files is not shareable across
applications because applications
depend on a fixed record layout in data
files.
7
Record Layout – Applications
Approach
• Each application stores all the data
required for analysis.
• Shows many redundancies across
applications/files.
• Data lacks integrity when the data
stored by one application is inconsistent
with data stored by another application.
8
Database Management Systems
• A set of integrated programs designed to simplify the tasks of
creating, accessing, and managing data.
• Integrates a collection of files that are independent of
application programs and are available to satisfy a number of
different processing needs.
• Database contains data related to all the organization’s
applications in one place.
• Supports normal data processing needs and enhances the
organization’s management activities by providing data useful
to managers.
• Data is independent of the application that created the data
(i.e., can be changed/used by other applications).
• The term enterprise database is synonymously with database
management system or DBMS.
9
Schemas and Subschemas
10
Relational Database Tables
• The next slide shows a database with data stored in a
relational structure.
• This is most common type of database structure used in
businesses today.
• The data from three files are now stored in four tables:
–
–
–
–
CUSTOMERS (instead of the customer master data file)
INVENTORY_ITEMS (instead of the inventory master data file)
SALES_ORDERS
SALES_LINES (replace the sales order master data file)
• The logical database view is how the data appear to the
user to be stored.
– This view represents the structure that the user must use to extract
data from the database.
11
Record
Layouts
as Tables
12
Formulating a Query in SQL
Users can access the data in the tables
by:
1. Formulating a query.
2. Preparing a report using a report
writer.
3. Including a request for data within
an application program.
13
SQL – Query Example
A query that uses the SQL SELECT command can return to
the customers assigned to salesperson Garcia.
– SELECT Cust_Code Cust_Name Cust_City
– FROM CUSTOMERS
– WHERE Salesperson = ‘Garcia’
There are two customers, STANS and WHEEL, who are
assigned to salesperson Garcia.
14
Disadvantages of DBMS
1. Expensive to implement.
2. If the DBMS fails, all the organization’s information processing halts.
3. Database recovery and contingency planning are more important
than in the applications approach.
4. When more than one user attempts to access data at the same time,
the database can face “contention” or “concurrency” problems.
– Record locking can help mitigate such problems but are beyond
the scope of this course.
5. Territorial disputes over who “owns” the data, such as who is
responsible for data maintenance (additions/deletions/changes) to
customer data.
6. Most companies that have adopted this approach have found it
necessary to create a database administrator function to cope with
these and other problems.
15
Logical Database Models
• Hierarchical databases
– Child records may only have one parent record.
– Cannot sustain complex data structures.
• Network databases
– Overcomes problems of hierarchical.
– Child record can have more than one parent
record.
– Eclipsed by relational databases.
16
Logical Database Models
• Relational databases
– Data logically organized into two dimensional tables.
– Vast improvement over hierarchical or network database models.
– Able to handle complex queries.
– Allows only text and numerical data to be stored.
– Does not allow the inclusion of complex object types such as
graphics, audio, video, or geographic information.
• Object oriented databases
– Overcomes the limitations of relational databases.
– Allow the storage of complex objects such as video clips.
– An object can store attributes and instructions for actions that can
be performed on the object or its attributes.
17
Object-Oriented Database Model
18
Elements/Parts of a Relational
Database
• Tables - place to store data
• Queries - retrieve data
• Forms - on-screen presentations of
data collected by queries
• Reports - printed lists and data
summaries collected by queries
19
Comparison of Database and
Spreadsheet
• Database
– Cell can contain only
one data type.
– Each row in the
database must be
unique and include a
unique identifier
(primary key or
composite key).
– Columns store one
attribute.
• Spreadsheet
– Cell can contain text,
numbers, formula or
graphic.
– Rows need not be
unique.
– Columns often store
dissimilar attributes.
20
Classifying and Coding Data
Types of coding
1. Sequential
2. Block
3. Significant digit
4. Hierarchical
5. Mnemonic
21
Sequential Coding
• Assigns numbers in chronological sequence.
• Limited flexibility
– Additions can be made only at the end of a sequence.
– Deletions result in unused numbers unless the numbers
are recycled.
– Codes tell nothing about the object’s attributes.
• Examples include
– Student ID numbers
• Example based on employee ID codes:
– 001 - 1st hired
– 002 - 2nd hired
22
Block Coding
• Groups of numbers are dedicated to particular
characteristics of the objects being identified.
• Universal product code example:
73805
Mfg
Code
80248
Product
Code
• Employee ID code example:
001-100 fabrication department
101-200 assembly department
23
Significant Digit Coding
• Assigns meanings to specific digits.
• Significant digit coding works well for
inventory items.
• Also works well for employee ID codes.
• The following slide shows examples
using inventory and employee ID codes.
24
Significant Digit Coding
Example based on an Inventory item
16
2
17
4389
Product
group
Part or
subassembly
Warehouse
Unique item
identifier
Example based on employee ID codes
2
0
4
623
Work
center
Exempt or
nonexempt
Pay rate
code
Unique
employee
identifier
25
Hierarchical Data Coding
• Like significant digit codes, hierarchical codes
attach specific meaning to particular
character positions.
• Items are ordered in descending order where
each successive rank order is a subset of the
rank above it.
• Reading from left to right in a hierarchical
code, each digit is a subcategory of the digit
to its left.
• A 5 digit postal code is an example of
hierarchical data coding.
26
Hierarchical Data Coding
Example based on postal zip code
0
18
90
Section of
country
Region within
section
Locality (town
within region)
Example based on employee ID codes
01
3
9
623
Company
division
Plant
Department
Unique
employee ID
27
Mnemonic Data Coding
College course numbering:
AC340 - Accounting Information Systems
EN101 - English Composition
Example based on employee ID codes:
F
Female
M
Married
C
623
Caucasian
Unique
employee ID
28
Coding Examples
29
Database Normalization
• Rules for database normalization based on set theory.
• Failure to normalize results in anomalies/errors that
otherwise might occur when adding, changing, or
deleting data stored in the database.
– There are 6 normal forms, but the first 3 are generally
considered sufficient.
– To function properly, a database should obtain the third
normal form.
– Normal forms are inclusive, which means that each
higher normal form includes all lower normal forms.
• That is, a table in 3NF is in 1NF and in 2NF.
30
Unnormalized Relation
• Table contains repeating groups
– Sales order line items repeat
31
Functional Dependence and Primary Keys
• An attribute (column in a table) is functionally dependent on a
second attribute (or a collection of other attributes), if a value for
the first attribute determines a single value for the second
attribute at any time.
• If functional dependence exists, the first attribute determines the
second attribute.
• A primary key contains a value that uniquely identifies a specific
row in a table.
• A candidate attribute (a column or collection of columns) in a
table is that table’s primary key if:
– All attributes in the table are functionally dependent on the
candidate attribute.
– No collection of other columns in the table, taken together,
has the first property.
32
Relation in First Normal Form (1NF)
• A table is in first normal form (1NF) if it doesn’t contain repeating groups.
• Rows are now key dependent, uniquely identified by a primary key.
• The primary key for the table is a combination of SO_Number and
Item_Number.
• A primary key formed by the combination of two or more columns is
called a composite primary key.
33
Problems with First Normal Form (1NF)
Includes the following functional dependencies:
1. Item_Number functionally determines Item_Name.
Therefore, item names are repeated several times.
This data redundancy should be eliminated.
2. Cust_Code functionally determines Cust_Name.
3. The combination of SO_Number and Item_Number
together functionally determine Item_Name,
Qty_Ordered, Cust_Code, and Cust_Name.
These functional dependencies cause several
problems called update anomalies.
34
Update Anomalies
•
Update. Updating an item name requires updating multiple
records. Each row in which any item appears must be changed if
the description is updated.
•
Inconsistent data. An item can have several different names in
different rows of the table.
•
Additions. Adding a new inventory item to the database is
problematic because a sales order number is required before an
item can be inventoried. This is impossible for a business, that
wants information about inventory in its database before
accepting sales orders.
•
Deletions. Deleting an inventory item from the database (by
deleting its row) could cause the table to lose sales order
information.
•
Because the attribute, Item_Name is dependent on a portion of the
primary key, Item_Number, not on the entire key, a problem called
a partial dependency exists. Second normal form eliminates
partial dependencies.
35
Two steps to get from 1NF to 2NF
1. Create a new table for each subset of the table that is partially
dependent on a part of the composite primary key.
– This step results in two new tables, one with SO_Number as
its primary key (SALES_ORDERS table) and another with
Item_Number as its primary key (INVENTORY_ITEMS
table).
2. Place each of the non-key attributes that are dependent on a
part of the composite primary key into the table that now has a
primary key that is the field on which the non-key attribute is
partially dependent.
– The Item_Name field is partially dependent on the
Item_Number field portion of the composite primary key, so it
would be moved into the new INVENTORY_ITEMS table.
36
Relations in Second Normal Form (2NF)
To obtain second normal form, partial dependencies must be
eliminated by creating new tables.
37
Third Normal Form (3NF)
• To obtain third normal form, transitive
dependencies must be eliminated.
• A transitive dependency exists when a nonkey field depends on another non-key field
which in turn depends on the key. For
example, customer name depends on
customer code which depends on sales a
order number.
• A table is in third normal form if it is in second
normal form and transitive dependencies are
eliminated.
38
Transitive Dependencies
Some customer names are repeated several times. This
transitive dependency causes update anomalies:
1. Update. Changing any customer name could require multiple
changes. The user would have to change each row in which
any customer appears. Changing Wheelaway’s name would
require changing three rows in the SALES_ORDERS table.
2. Inconsistent data. Nothing in this design prevents users from
entering several different names for a single customer.
3. Additions. A new customer can’t be added unless the
customer already has a sales order. Internal control dictates
that an authorized customer should exist before a sales order
can be created for that customer.
4. Deletions. If a user deletes a sales order, the name of a
customer might be erased from the database.
39
Relations in Third Normal Form (3NF)
Customer
information
moved to
“Customers”
table
40
Entity-Relationship Models (REA)
• Popular data modeling approach.
• Entities and relationships are
determined through systems analysis.
• Common accounting entities include:
– Resources: thing the company owns.
– Events: occurrences related to resources.
– Agents: people or organizations that
participate in events.
41
Entity-relationship diagram
Entity-Relationship Models (REA)
42
Entity Relationship Models (REA)
• Identify Entities
– Categories of entities
• Resources
• Events
• Agents
• Locations
• Identify relationships that connect the entities
– shown in the E-R diagram as connecting lines with
diamonds that describe the nature of the relationship.
• Create tables and relationships
– the analyst continues the data modeling process
transforming the data model into a logical design
for the database.
43
Characteristics of
Relationships
• Relationships between entities are
determined by analyzing the
system.
• The cardinality is the degree to
which each entity participates in the
relationship, e.g., 1:N (pronounced
one-to-many).
44
Create the E-R Diagram in 5 Steps
Create a logical model of the database:
1. Create tables for each entity.
2. Determine primary keys.
3. Determine attributes.
4. Implement relationships through primary keys
and relationships.
5. Define relationship tables.
Implement the database using an available
DBMS.
45
Relational
Database
46
Decision Support Systems: DSS
• Information systems that assist managers
with unstructured decisions by retrieving data
and generating information.
• Possesses interactive capabilities.
• Can answer ad-hoc inquires.
• Provide data modeling facilities such as
spreadsheets.
• Supports non-recurring, relatively
unstructured decision making.
47
Executive Information System:
ESS
• Information systems, often considered
a subset of DSS, that combine
information from the organization
and the environment.
• Organizes and analyzes the information
in a form suitable for managers to make
decisions.
48
Decision Support Systems and
Executive Information Systems
49
Group Support Systems: GSS
• Computer based systems that support collaborative
intellectual work such as
–
–
–
–
–
Idea generation
Elaboration
Analysis
Synthesis
Decision making
• GSS use technology to solve the time and place
dimension problems associated with group work.
• Also known as GDSS or Group Decision Support
Systems.
50
Expert Systems
• Expert systems (ES): information system
that emulates the problem solving
techniques of human experts.
• Neural networks (NN): computer hardware
and software systems that mimic the
human brain’s ability to recognize patterns
or predict outcomes using less-than
complete information.
51
Excel Decision Model Example
52
Intelligent Agents
• An intelligent agent is a software
program that may be integrated into
DSS, EIS or other software tools (such
as word processing, spreadsheet, or
database packages).
• Sometimes called “bots,” short for
“robots.”
53
Data Warehouses
• Data warehousing is the use of information
systems to collect, organize, integrate, and
store entity-wide data to provide users with easy
access to large quantities of varied data from
across the organization.
• Data mining is the exploration, aggregation, and
analysis of data in the data warehouse using
analytical tools and exploratory techniques.
54
Knowledge Management
Systems
• Knowledge management is the process of
capturing, storing, retrieving, and distributing
the knowledge of the individuals in an
organization for use by others in the
organization to improve the quality and
efficiency of decision making across the firm.
• Knowledge refers to information that has
been formatted and distributed in accordance
with an organization’s standards.
55