Studying Accounting Information Systems

1
Studying Accounting Information Systems
Business Strategy
IT Environment
Business Processes
AIS Applications
Part 2

• Understanding and designing
• Data
• Queries and reports
• Input forms

2
Databases

• Comprehensive collection of related data
• Database Management System (DBMS)
• Enables storage and retrieval of data
• In a relational database, data stored in tables
• Relationships matching attributes

Customer Customer Customer Name Customer
Address Customer Phone
1
m
Rental_Agreement Equipment Rental_Date Customer
3
Data Base Design Options One Uniform Table
Student ID 999999999 999999999 999999999 12345678
9 123456789
Course Unique 00071 00150 00179 00071 00153
Student Name Bill Bixby Bill Bixby Bill
Bixby Lana Turner Lana Turner
Course Description AMIS 531 ENGL 100 HIST
203 AMIS 531 MATH 102
4
Data Base Design Options Vary the Number
of Columns
Course Unique 00071 00071
Course Unique 00150 00153
Course Unique 00179
Student ID 999999999 123456789
Student Name Bill Bixby Lana Turner
5
Data Base Design Options Use Multiple Tables
Enrollment Table
Course Table
Student Table
Course Unique 00071 00150 00153 00179
Course Description AMIS 531 ENGL 100 MATH
102 HIST 203
Student ID 999999999 999999999 999999999 1234567
89 123456789
Course Unique 00071 00150 00179 00071 00153
Student ID 999999999 123456789
Student Name Bill Bixby Lana Turner
6
Identifying the Need for Transactions Tables

• Determine the events in the process (again)
• Exclude events that are not recorded in system
• Exclude query and reporting events
• These data have already been recorded were just
  using them
• Exclude maintenance events
• Usually not relevant for transactions tables for
  reference fields in master table
• There are examples where both a transaction
  record and a master record are created
• e.g. open a bank account with initial deposit

7
Identifying the Need for Master Files

• For each event that produces a transaction file
  identify related goods, services or agents
• Sale
• Who sold it? What did we sell? Who did we sell
  it to?
• Initiate Layaway
• Who placed the item on layaway? What is the
  item? Who started the account?
• Consider master tables to track location of cash
  and effect of events on account balances
• Each master file should be linked to at least one
  transactions file, and vice-versa

8
Events and Master Tables

• Master tables store relatively permanent entity
  data
• Products/services
• Agents
• Cash
• General Ledger
• Benefits of Master Tables
• Save data entry time and storage space
• Make updates in one place only
• We can delete transaction files without losing
  data

9
Data Base Design Options Use Multiple Tables
Enrollment Table
Course Table
Student Table
Course Unique 00071 00150 00153 00179
Course Description AMIS 531 ENGL 100 MATH
102 HIST 203
Student ID 999999999 999999999 999999999 1234567
89 123456789
Course Unique 00071 00150 00179 00071 00153
Student ID 999999999 123456789
Student Name Bill Bixby Lana Turner
10
Attributes and Relationships

• Primary Key
• Uniquely identifies a record
• Candidate key, potential primary key that wasnt
  used
• Foreign Key
• The primary key of another table that is stored
  as an attribute
• Provide the relationship in a relational
  database
• link transactions to master file or sequential
  transactions

Customer File (Master)
Order File (Transaction)
11
Data Base Design Options Use Multiple Tables
Enrollment Table
Course Table
Student Table
Course Unique 00071 00150 00153 00179
Course Description AMIS 531 ENGL 100 MATH
102 HIST 203
Student ID 999999999 999999999 999999999 1234567
89 123456789
Course Unique 00071 00150 00179 00071 00153
Student ID 999999999 123456789
Student Name Bill Bixby Lana Turner
12
Relationships Between Tables

• Cardinality
• How many instances of each entity type
  participate in a relationship
• One to one (11)
• One to many (1m)
• Many to many (mm)
• Software forces you to get rid of these
  (junction table)

1
1
Employee
Office
m
1
Employee
Department
Student
Course
m
m
Student
Course
1
1
Enrollment
m
m
13
Data Base Design Options Use Multiple Tables
Enrollment Table
Course Table
Student Table
Course Unique 00071 00150 00153 00179
Course Description AMIS 531 ENGL 100 MATH
102 HIST 203
Student ID 999999999 999999999 999999999 1234567
89 123456789
Course Unique 00071 00150 00179 00071 00153
Student ID 999999999 123456789
Student Name Bill Bixby Lana Turner
14
Controlling AIS Data Referential Integrity
m
1
Order
Customer

• Cant add an order for Customer 3449
• combine referential integrity with segregation of
  duties and access controls
• one agent for customer table one agent for
  transaction table
• only valid customers can place orders
• Cant delete Customer 3451. How about 3452?

15
Designing Data with UML Class Diagram

• Draw required transaction tables in sequential
  order
• Draw required master tables and link to
  transactions table(s)
• Determine cardinality of relationships
• Determine the required attributes
• Assign a primary key
• 1m add primary key of 1 to m
• mm split with junction table with compound key
• Assign other attributes as needed

16
A UML Example of a Class Diagram
Goods/Services (Resources)
Events
Agents
Sale
Sale SSN
(m,1)
(m,1)
Inventory
Product
(1,m)
Manager
SSN
Sale_Detail
Sale Product
(m,1)
Compound Key
Deposit SSN
Deposit
(m,1)
Primary key
Primary key
Junction Table
Foreign Key
17
A UML Example of a Class Diagram
Other attributes
18
Conceptual Design vs. Implementation
Subschema User A
Subschema User B
Subschema User C
External User/View
Glenn Ted Eric
Managers
Conceptual Design
m
m
m
1
Start here
Sales
Inventory
Customer
Inventory Record Item integer(5),
non-null Description char(15) ...
Sales Record Invoice integer(6)
Customer Record Customer integer(6) Name
char(30) ...
Implementation Issues
19
Implementation Suggestions

• Use one master instead of two when possible
• e.g. employee vs. cook and server
• Same information stored about each?
• One event table instead of two

Implementation choice
20
Implementation Suggestions

• Eliminate Redundant Relationships
• Remove relationships that can be derived from
  earlier relationships
• Makes diagram easier to read and reduces number
  of attributes stored
• Add relationships that do not involve events
• Each tax client is assigned exactly one CPA

Client
m
No associated event
Tax Return Prepared
1
CPA