Transcript Blue Red-Lines Background - University of Alaska system

Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Chapter
4
Relational
Databases
Acct 316 Acct 316 Acct 316
UAA – ACCT 316
Accounting Information Systems
Dr. Fred Barbee
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316
Creating a database can be like
creating a universe, only more
complicated. At least when the
universe was created, there was
no one around to complain.
Michael J. Hernandez
Database Design for Mere Mortals
Addison Wesley, Boston, 1997
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
An Out-of-the-Text
Experience!
Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Chapter
Acct 316 Acct 316 Acct 316
Types of
Databases
4
Types of Databases
Operational Database
Acct 316 Acct 316 Acct 316
Used whenever there is a need to
collect, maintain, and modify data.
Stores dynamic data (i.e., the data
changes constantly and reflects upto-the-minute information).
Types of Databases
Analytical Database
Acct 316 Acct 316 Acct 316
Used to store and track historical
and time-dependent data..
Stores static data that is never (or
rarely) modified. Information is
applicable to a specific point in
time.
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Chapter
Acct 316 Acct 316 Acct 316
Early Database
Models
4
Hierarchical Database
Model
Data is structured hierarchically.
Acct 316 Acct 316 Acct 316
Visualize as an inverted tree.
A single table acts as the “root” of
the tree.
Other tables act as branches
flowing from the root.
Hierarchical Database Model
Agents
Entertainers
Schedule
Clients
Engagements
Payments
Network Database Model
Visualize as an inverted tree.
Acct 316 Acct 316 Acct 316
Several inverted trees may share
branches,
All of which are part of the same
database structure.
The Network Database Model
Agents
Represent
Manage
Clients
Make
Payments
Entertainers
Schedule
Perform
Engagements
Play
Musical
Styles
A Basic Set Structure
Owner Table
Represent
Relationship
Agents
Set Structure
Clients
Member Table
Enter . . . The Relational
Database Model
Relational Database
Acct 316 Acct 316 Acct 316
Relational Database Model
Acct 316 Acct 316 Acct 316
Data is stored in relations, which
are perceived by the user as
tables.
Each relation is composed of
tuples (records) and attributes
(fields).
Fundamental Data Storage
Concepts and Definitions
Accounts Receivable File
Attributes
Customer Customer
Credit
number name Address limit Balance
301
ABC Co. Box 5 1,000 400
555
XYZ Co. Box 9 6,000 2,000
2 Entities
2 Records
Individual fields
Data
values
Relational Database Model
Acct 316 Acct 316 Acct 316
The physical order of the
records of fields in a table is
completely immaterial.
Each record in the table is
identified by a field that contains
a unique value.
A Relational Database Model
Agents
Clients
Entertainers
Engagements
Musical
Styles
Entertainer
Styles
Agents
Agent ID
First Name
Last Name
100
Mike
Hernandez
05/16/02
333-4444
101
Greg
Piercy
10/15/02
444-3333
Clients
Client ID
Agent ID
Phone
First Name
Last Name
…..
9001
100
Stewart
Jamison
…..
9002
101
Shannon
McClain
…..
Entertainers
Ent ID
Hire Date
Agent ID
First Name
Last Name
…..
3000
100
John
Slade
…..
3001
101
Mark
Weiser
…..
Engagements
Client ID
Ent ID
Eng. Date
Time
…..
9001
3001
04/01/02
1-3 PM
…..
9002
3002
04/12/02
7-9 PM
…..
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Now, Back to the
Text
Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316
Double-Entry Vs.
Database Accounting
Double-Entry Versus
Database Accounting
Luca Pacioli and double-entry
accounting.
Acct 316 Acct 316 Acct 316
Captures and stores key
attributes of transactions in a
highly aggregated form.
Double-Entry Versus
Database Accounting
Acct 316 Acct 316 Acct 316
The beloved debit/credit
convention provided an important
internal control feature in manual
systems.
Enter computerized accounting.
Double-Entry Versus
Database Accounting
The ability to quickly capture lots
of data quickly . . .
Acct 316 Acct 316 Acct 316
Date and time of purchase
Identity of item purchased
Store location
Checkout station number
Cashier number
Double-Entry Versus
Database Accounting
Consider a simple transaction:
Acct 316 Acct 316 Acct 316
On November 18, 2002 we made a
sale to Jones Enterprises for
$10,000 on credit.
Terms 2/10, n/30
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316
How would we handle this
transaction in a double-entry
system?
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Chapter
Acct 316 Acct 316 Acct 316
Files Vs.
Databases
4
Fundamental Data Storage
Concepts and Definitions
Accounts Receivable File
Attributes
Customer Customer
Credit
number name Address limit Balance
301
ABC Co. Box 5 1,000 400
555
XYZ Co. Box 9 6,000 2,000
2 Entities
2 Records
Individual fields
Data
values
Basic File Types
Acct 316 Acct 316 Acct 316
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.
File-Oriented Approach
File # 1
Item A
Item B
Item C
Application
program #1
File # 2
Item B
Item D
Item E
Application
program #2
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316
How would we handle this
transaction in a relational
database system.
Databases
Acct 316 Acct 316 Acct 316
The database approach views data as
an organizational resource that
should be . . .
used by, and
managed for,
the entire organization
Databases
A database is a set of interrelated,
centrally coordinated files.
Acct 316 Acct 316 Acct 316
Focus is on
Data integration
Data sharing
Database Approach
Application
program #1
Database
Item A
Item B
Item C
Item D
Item E
Database
management
system
Application
program #2
Application
program #3
Database System
Database
Item A
Item B
Item C
Item D
Item E
DBMS refers to programs that
control the data and interfaces
Application
between the data and
the
program #1
application programs.
Database
management
system
Application
program #2
Application
The Data Base Administrator
(DBA) is responsibleprogram
for the #3
database.
A Database Accounting System
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316
Advantages & Disadvantages
of Relational Database
Systems
Advantages
Reduce data storage costs
Acct 316 Acct 316 Acct 316
Eliminate data redundancy
Eliminate data inconsistencies
Avoid duplicate processing
Advantages
Ease add, delete, and update data
maintenance tasks
Acct 316 Acct 316 Acct 316
Make data independent of
applications
Centralize data management
Centralize data security
Advantages
Ease report modifications and
updats
Acct 316 Acct 316 Acct 316
Provide ad hoc query capabilities
Facilitate cross-functional data
analysis
Advantages
Permit multiple users
simultaneous data access.
Acct 316 Acct 316 Acct 316
Disadvantages
Greater hardware requirements
Acct 316 Acct 316 Acct 316
The database software itself
Employing a database
administrator.
Disadvantages
Centralization of data . . .
Acct 316 Acct 316 Acct 316
System operation becomes critical
Incorrect data entry corrupts many
users’ work
Territorial disputes over data
ownership may arise
One Last Disadvantage
Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
It’s Your Turn!
Acct 316 Acct 316 Acct 316
Discussion Question
4.3 p. 99
The text explained how database technology
may eliminate the need for double-entry
accounting. This creates three possibilities:
The double-entry model will be abandoned,
The double-entry model will not be used directly,
but an external-level schema based on the doubleentry model will be defined for accountant’s use;
or
The double entry model will be retained in
database systems.
Which alternative do you think is most likely
to occur? Why?
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Logical View
Acct 316 Acct 316 Acct 316
Vs.
Physical View
Physical View
Acct 316 Acct 316 Acct 316
Physical View
Acct 316 Acct 316 Acct 316
The physical view refers to how
and where the data are physically
arranged and stored on disk,
tape, CD-ROM, or other media.
Logical View
Acct 316 Acct 316 Acct 316
The logical view is how the user
or programmer conceptually
organizes and understands the
data.
Logical View Vs. Physical View
Logical View
Data
Physical View
Credit Report
Customer Number
Credit Limit
Balance
Monthly Statement
Customer Name
Address
Balance
Customer Number
Customer Name
Address
Credit Limit
Balance
How data
are stored
on disk.
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Chapter
Acct 316 Acct 316 Acct 316
Schemas
4
Schemas
Acct 316 Acct 316 Acct 316
A schema describes the logical
structure of a database.
There are three levels:
Conceptual-Level
External-Level
Internal-level
Conceptual-Level Schema
An organization-wide view of
the entire database.
External-Level Schema
Consists of a set of individual user
views of portions of the database,
also referred to as a subschema.
Internal-Level Schema
The internal-level schema provides
a low-level view of the database.
Database Management System
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Chapter
Acct 316 Acct 316 Acct 316
DBMS Languages
4
DBMS Languages
Acct 316 Acct 316 Acct 316
Every DBMS must provide a
means of performing the three
basic functions:
Creating the database
Changing the database
Querying the database
Data Definition Language (DDL)
Build the data dictionary.
Initialize or create the database.
Acct 316 Acct 316 Acct 316
Describe the logical views for each
individual user or programmer.
Specify any limitations or constraints
on security imposed on database
record or fields.
Data Manipulation Language (DML)
Used for data maintenance.
Acct 316 Acct 316 Acct 316
updating portions of the database
inserting portions of the database
deleting portions of the database
Data Query Language (DQL)
The data query language (DQL) is
used to interrogate the database.
Acct 316 Acct 316 Acct 316
The DQL retrieves, sorts,
orders, and presents subsets of
the database in response to user
queries.
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316
More on the
Relational Data
Model
Chapter
4
Table in a Relational Data Model
Basic Requirements of the
Relational Data Model
Primary keys must be unique.
Acct 316 Acct 316 Acct 316
The primary key cannot be null
(blank).
Why?
Known as the entity integrity rule.
What is the entity integrity
rule?
Acct 316 Acct 316 Acct 316
The entity integrity rule ensures
that every row in every relation
(table) must represent data
about some specific object in the
real world.
Basic Requirements of the
Relational Data Model
Acct 316 Acct 316 Acct 316
Every foreign key must either be
null or have a value corresponding
to the value of a primary key in
another relation.
Why?
The referential integrity rule.
What is the referential
integrity rule?
Acct 316 Acct 316 Acct 316
The referential integrity rule
ensures that links exist among
the tables of a database.
Basic Relational Data Model Stuff
Relation = Table = File
Some terminology
Table in a Relational Data Model
Tuple = Row = Record
Some terminology
Table in a Relational Data Model
Attribute = Column = Field
Some terminology
Attribute = Column = Field
Each column in a table must describe
a characteristic of the object
identified by the primary key.
Attribute = Column = Field
Each attribute (column) in a table has
a distinct name, called the attribute
name.
Attribute = Column = Field
Every entry in a column contains a
value for that column only, and the
values are of the same data type.
Attribute = Column = Field
The order (position) of the columns in
relation to each other is unimportant.
Tuple = Row = Record
The entries in each column of any row
must be single valued.
Tuple = Row = Record
The order of the rows is unimportant.
Tuple = Row = Record
Each row is unique; it differs from all
other rows in the table.
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316
How does a
database
differ from a
spreadsheet?
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316
How might an
accountant’s
familiarity with the
tabular
representation of
spreadsheets
facilitate or hinder
learning how to use a
relational DBMS?
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316
Importance of a WellStructured Relational
Database
Chapter
4
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316
Anomalies that may
occur in nonnormalized relational
tables.
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Acct 316 Acct 316 Acct 316
Normalization!
Anomalies! WOW!
You accountants are
an odd lot!
What is
normalization? Is
anything normal
around here?
Normalization
Acct 316 Acct 316 Acct 316
A technique used to make
complex databases more
efficient by removing undesirable
redundancy.
Break one large table into several
smaller tables.
Normalization
Normalization starts with the
assumption that everything is initially
stored in one large table.
A set of rules is then followed to
decompose that initial table into a set
of normalized tables.
The objective is to produce a set of
tables that are in what is called third
normal form (3NF)
Normalization
Problems caused by redundancy:
Acct 316 Acct 316 Acct 316
Storage inefficiencies
Processing inefficiencies
Errors
Update Anomaly
Acct 316 Acct 316 Acct 316
When changes (updates) to data
values are not correctly
recorded.
Insert Anomaly
Acct 316 Acct 316 Acct 316
There is no way to store
information about one entity in
the database without it being
associated with another entity
Delete Anomaly
Acct 316 Acct 316 Acct 316
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.
Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316
Database Design
Acct 316 Acct 316 Acct 316
Chapter
4
Approaches to Database Design
Normalization
Acct 316 Acct 316 Acct 316
Starts with the assumption that all data
is initially stored in a large nonnormalized table.
This table is then decomposed using a
set of normalization rules to create a
set of tables in the Third Normal Form.
Approaches to Database Design
Semantic Data Modeling
Acct 316 Acct 316 Acct 316
The database designer uses his/her
knowledge about the business structure
to create a set of relational tables.
Database Design Objectives
Completeness
Acct 316 Acct 316 Acct 316
Relevance
Accessibility
Up-to-dateness flexibility
Database Design Objectives
Efficiency
Acct 316 Acct 316 Acct 316
Cost-effectiveness
Integrity
Security
Database Systems and the
Future of Accounting
Acct 316 Acct 316 Acct 316
Database systems have the
potential to significantly alter
the nature of external reporting.
Database Systems and the
Future of Accounting
Acct 316 Acct 316 Acct 316
Perhaps the most significant
effect of database systems will
be in the way that accounting
information is used in decision
making.