Chapter 1314 Databases and Data Modeling for Accounting Information Systems

1
Chapter 13-14Databases and Data Modeling for
Accounting Information Systems

• Introduction
• An Overview of Databases
• Database Management Systems
• Creating a Database Using REA Model
• Normalization

2
AN OVERVIEW OF DATABASES

• A database is a collection of data that is stored
  in related files.
• Reasons why databases are important are
• Valuable information
• Volume
• Complexity
• Privacy
• Irreplaceable data
• Need for accuracy

Which of these does not characterize a typical
database? a. large number of records b.
irreplaceable data c. high need for
accuracy d. simple systems
3
File Processing Systems vs. Database Management
Systems
File A
File B
Application Program 1
File C
File A
File D
Application Program 2
Users
File E
File F
File E
Application Program 3
File B
4
File Processing Systemsvs. Database Management
Systems
Application Program 1
Data Sets A,B,C, D,E,F
Database Management System (DBMS)
Application Program 2
Users
Application Program 3

• Organizations use database management systems
  (DBMS) for database construction and use.
• Data definition language (DDL) of a DBMS enables
  users to define the record structure of any
  particular database table.
• The data manipulation language (DML) enables
  users to perform tasks such as querying, changing
  records, and deleting records. Many relational
  databases support structured query language (SQL).

5
Advantages of Data Warehouse

• Data are clean of errors and defined uniformly.
• Data are stored in several databases, not just
  one.
• Data Warehouses span a longer time horizon than
  the companys transaction systems.
• The data relations are optimized for answering
  complex questions.
• Make organizational information available on a
  corporate-wide basis.
• Facilitates data mining and enables users to
  identify
• target markets or its most desirable customers.

6
Administration and Documentation

• A database administrator
• supervises the design, development, and
  installation of alarge database system, and
• is responsible for its maintenance, security and
  revision
• Documentation involves descriptions of
• database structures,
• contents,
• security features,
• E-R diagrams, and
• password policies
• A data dictionary is a data file about data.

7
User Views Schemas and Subschemas

• The totality of information in a database and the
  relationships of its tables (records) is called
  the database schema.
• Any particular user or application program will
  be interested in only a subset of the schema,
  called the subschema.
• A database must be flexible enough to satisfy the
  subschema uses required.

• Schema for a Customer Record
• ACCOUNT-NUMBER
• CUSTOMER-NAME
• CUSTOMER ADDRESS
• SALES-DIVISION
• CREDIT-LIMIT
• BALANCE
• CREDIT-TERMS
• TOT-YEARS-SALE
• DATE-RECENT-SALE

• Subschema for a Sales Order Entry Application
• ACCOUNT-NUMBER
• CUSTOMER-NAME
• CUSTOMER-ADDRESS
• CREDIT-LIMIT
• BALANCE
• CREDIT-TERMS
• Subschema for a Sales Analysis Application
• ACCOUNT-NUMBER
• CUSTOMER-NAME
• CUSTOMER-ADDRESS
• SALES-DIVISION
• TOT-YEARS-SALES

8
Documentation Data Dictionary

• A data dictionary is a computer file that
  maintains descriptive information about the items
  in a database.
• Each computer record of the data dictionary
  contains information about a single data item
  used in an AIS.
• Examples of information that might be stored in a
  data dictionary are source document(s) used to
  create the data item, programs that update the
  data item, and classification information about
  the items length and data type.

9
Data Storage Concepts

• Storing accounting data in computer files
  involves organizing the data into a data
  hierarchy.
• A bit shows a yes or no state a yes state,
  a no state
• The lowest level of information in a file is a
  character, or eight bits (byte). 11010011 An
  8-bit byte representing an L
• A data field combines several characters.
• A 15 byte field with the data name of
  CUSTOMER-NAME
• A record combines related data fields.
• A record with CUSTOMER-NMUBER as its key and
  CUSTOMER-NAME as one of its fields.
• A set of records forms a file.
• A database is a collection of files that contain
  all the information for an application.
• A data warehouse is a large, common body of
  information.

1
0
YINING CHEN
YINING CHEN
00001
YINING CHEN
00001
00002
10
Types of Data Files

• A master file comes in two subtypes
• One type contains records that do not change very
  often, such as a vendor master file.
• The other type of master file is one that is
  regularly updated to reflect ongoing activities,
  such as a general ledger file.
• A transaction file contains the data that reflect
  ongoing business activities, such as individual
  purchases from vendors or general journal
  entries.

11
Record Structures
Which of these is not part of the data
hierarchy (within the context of databases)? a.
record b. bit c. Character d. data type

• The specific data fields in each record of a
  computer file are part of the record structure.
• Keys are identifiers for records
• Primary keys are unique to each record
• Secondary keys are non-unique identifiers for
  records
• Foreign keys enable database records to reference
  one or more records in other files.

YINING CHEN
00001
00001
03-09-04-01
12
Organizing Database Records Database Structures

• A particular method used to organize records in a
  database is called the databases structure.
• The objective is to develop this structure
  efficiently so that data can be accessed quickly
  and easily.
• Three types of structures are
• 1) hierarchical,
• 2) network and
• 3) relational.

13
1) Hierarchical Structure

• Accounting data are often organized in a
  hierarchy.
• A hierarchical structure has successive levels of
  data in an inverted treelike pattern.
• Higher level records are parent records and lower
  level records are child records.
• Two records on same level are sibling records.

Line Item
Order
Customer
14
2) Network Structure

• Databases can use a network data structure to
  link related records together and capture
  many-to-many relationships.
• The linking is accomplished with pointer fields.
• The pointers maintain the data relationships.

Line Item
Order
Customer
15
3) Relational Structure

• Relational databases are more flexible.
• Users can define relationships at the time the
  database is created or at later points in time.
• Data are stored in tables with columns
  (attributes) and rows (tuples)

16
Operate Relational Database Using Query Language
(DML)

• The SELECT Operation
• Query language commend SELECT ABBOTT OF
  EMPLOYEE DISPLAY EMP NAME AND JOB

The PROJECT Operation Query language commend
PROJECT EMP NO AND PAY RATE OF PAY-RATE
PROJECT
SELECT
Display EMP NAME JOB Abbott,G. 3
17
Operate Relational Database Using Query Language
(DML)

• The JOIN Operation
• Query language commend JOIN EMPLOYEE WITH
  JOB/DEPT DISPLAY DEPT AND NAME

JOIN
18
CREATING A DATABASE USING REA MODEL

• The REA is a tool for designing databases.
• Using this model, an AIS captures data about an
  organizations resources, events and agents
  (REA).
• Resources are an organizations assets.
• Events are identifiable activities associated
  with a business processes.
• Agents are the people associated with business
  activities.

In the REA model, the A stands for a.
agents b. additions c. accounts d.
associations
19
Entity-Relationship Diagram

• An entity-relationship diagram graphically
  depicts a databases contents.
• Depicts entities being modeled and the
  relationships among them (flowcharts do not show
  this).
• Entities are rectangles and relationships are
  shown with lines between.
• Entities are resources, events and agents. The
  E-R model uses diamond symbols to represent
  relationships.
• E-R diagrams include symbols (1 or M) that
  provide information on cardinality, which show
  the nature of the relationship between entities.
• The cardinality of a relationship describes the
  number of occurrences of one entity that may be
  associated with a single occurrence of the other
  entity.
• Referential integrity means that for a record to
  be entered in a given table, there must already
  be a record in some other table(s). For example,
  the Order Table cannot contain a record where the
  part number is not already present in the Parts
  Table

Which of these is not a cardinality between two
database entities? a. one-to-one b.
none-to-none c. one-to-many d. many-to-many
20
Cardinality Relationships
Hourly Pay Rate
Date of Hire

• One-to-one (11)
• One-to-many or Many-to-one
• (1M), (M1)
• Many-to-many (MM)
• Cardinalities provide useful information about
  the nature of the company being modeled and the
  business policies that it follows.
• E-R models are useful in designing the structure
  of a database.

Social Security Number
Employee
Ovals denote attributes rectangles denote entity
has
Line items
Invoice
Enroll in
Students
Classes
21
A E-R Diagram for the Sales Process
Inventory
Customer Order
Salesperson
orders
takes
makes
Sale (Ship Goods)
Customer
contains
Ships to
ships
Shipping Clerk
makes
Cash Account
Receive Payment
A/R Clerk
Is deposit into
Receives Records
22
Database Tables for the Sales Process(underline
signifies a primary key and brackets denote
foreign key)
ENTITY TABLES
RELATIONSHIP TABLES
INVENTORY Item Description Unit Cost Sales
Price Quantity on Hand
EMPLOYEE Employee Employee Name Address Date of
Birth Date Hired Department Job Code
CUSTOMER ORDER Order Order Date Customer Empl
oyee
INVENTORY/ORDER Item Order Quantity Ordered
INVENTORY/SHIP Item Shipping Quantity Shipped
SALES (SHIP GOODS) Shipping Ship
Date Customer Employee Order
CASH Account Account Type Bank Balance
CUSTOMER Customer Company Name Address Contact
Person Credit Limit
SHIP/PAYMENT Shipping Cash Receipt
RECEIVE PAYMENT Cash Receipt Amount
Received Date Received Employee Account Cus
tomer
23
NORMALIZATION

• Flat files are files with no sequence or order to
  them, except perhaps chronological.
• Flat files make it almost impossible to find a
  particular record easily or use file data
  productively.
• Normalization is a process of examining and
  arranging file data in a way that enables
  designers to avoid problems when these files are
  used or modified later.

Un-normalized Parking Ticket Data (0 Normal Form)
24
First Normal Form

• A database is in first normal form (1NF) if all
  the records attributes (data fields) are well
  defined and the information can thus be stored in
  a flat file.
• Problems
• data redundancy
• insertion anomaly
• deletion anomaly

25
Second Normal Form

• A database is in second normal form (2NF) if it
  is in first normal form and all the data items in
  each record depend on the records primary record
  key.
• This approach results in a more efficient design
  and eliminates much of the first files data
  redundancy.

Car Registration File
Ticket File
26
Third Normal Form

• Our goal is to create a database that is
  minimally in third normal form (3NF).
• A database is in third normal form if it is in
  second normal form and contains no transitive
  dependencies - i.e., no relationships in which
  data field A determines data field B.

Car Registration File
Ticket File
Violation Code File
27
Bensons Sports Supplies (Normalizing Data)
Un-normalized Table
First Normal Form
28
Data Normalization for Relational DBMS