Chapter 5 UNDERSTANDING AND DESIGNING ACCOUNTING DATA

1
Chapter 5 UNDERSTANDING AND DESIGNINGACCOUNTING
DATA
2
Identifying and Documenting Files

• Transaction files
• Used to record information about events in a
  business process

3
Identifying and Documenting Files

• Transaction files
• Attributes include
• Transaction date
• Agents associated with transaction
• Description of products/services associated with
  event

4
Identifying and Documenting Files

• Master files
• Store reference data
• Store summary data

5
Identifying and Documenting Files

• Events and transaction files
• First, identify the events in the business
  process
• Then, identify the need for transaction files in
  the AIS

6
Documenting Transaction Files

• UML class diagram
• Shows relationships between transaction and
  master files
• Each box represents a file
• Connecting lines between files indicate file
  relationships

7
Documenting Transaction Files

• UML class diagram
• Can be used to document
• Tables in an AIS
• Relationships between tables
• Attributes of tables

8
Documenting Transaction Files

• Guidelines for identifying need for transaction
  tables
• 1 Determine the events in the process
• 2 Exclude events that do not need to be recorded
  in the computer system

9
Documenting Transaction Files

• Guidelines for identifying need for transaction
  tables
• 3 Exclude query and reporting events because
  they involve using data that have already been
  recorded in the AIS
• 4 Exclude maintenance events

10
Documenting Transaction Files

• Events and master tables
• Typical master tables
• Products/services - master tables
• Describe products/services offered
• Identify costs and/or prices of products/services
• Agents - master tables describe
• External agents
• Internal agents

11
Documenting Transaction Files

• Events and master tables
• Typical master tables
• Cash - master file describes where cash is stored
• General ledger master file - needed if general
  ledger system is
• Automated and
• Integrated with the revenue or acquisition cycle

12
Documenting Transaction Files

• Events and master tables
• Generally, master tables are used to store
  relatively permanent data about an entity

13
Documenting Transaction Files

• Benefits of master tables
• Save data entry time
• Save storage space
• Simplify making changes to data
• Simplify deleting transaction records

14
Attributes and Relationships

• 3 important concepts
• Primary keys
• Linking attributes (foreign keys)
• Relationship cardinalities

15
Attributes and Relationships

• Primary key
• Attribute(s) that uniquely identifies a record in
  a table

16
Attributes and Relationships

• Foreign key
• A field in a table that is the primary key in
  some other table
• Used to link one table to another
• Link event records to master records
• Link two events that occur in a sequence

17
Attributes and Relationships

• Cardinality of the relationship
• (covered in the text)
• One-to-one relationships (1,1) - not nearly as
  common as one-to-many relationships
• One-to-many relationships (1,m) - common in
  accounting systems
• Many-to-many relationships (m,m) - can be
  converted into two one-to-many relationships by
  adding a junction table

18
Attributes and Relationships

• Cardinality of the relationship
• Important in designing a database
• Represents how many occurrences of one type of
  entity are associated with another type of entity

19
Attributes and Relationships

• Significance of concepts for database
  applications
• Implementing documents and reports

20
Attributes and Relationships

• Significance of concepts for database
  applications
• Implementing input forms
• Input forms are used to make data entry more
  accurate/efficient
• Form designs rely on primary and foreign keys
  and relationships between tables

21
Attributes and Relationships

• Significance of concepts for database
  applications
• Controlling AIS data referential integrity
• For one-to-many relationships
• Can specify if want referential integrity
  enforced on relationship
• Control most effective with two other controls
• Segregation of duties and
• Access controls

22
Designing Data with a UML Class Diagram

• Developing a data design using a UML class
  diagram
• Four basic steps
• Step 1 Place the required transaction tables
  (files) on the UML class diagram.
• Identify events in a business process.
• Decide which events will need transaction tables.
  
• Start the UML class diagram by showing a box for
  each event requiring transaction tables.

23
Designing Data with a UML Class Diagram

• Developing a data design using a UML class
  diagram
• Four basic steps
• Step 2 Place required master tables (files) on
  UML class diagram
• For each event on the diagram (from Step 1),
  determine related goods, services, or agent
  entities
• Determine which identified entities require
  master tables

24
Designing Data with a UML Class Diagram

• Developing a data design using a UML class
  diagram
• Four basic steps
• Step 2 (continued)
• Consider using master tables to track location of
  cash and effect of events on account balances in
  the general ledger
• Add required master tables to appropriate side of
  the UML class diagram

25
Designing Data with a UML Class Diagram

• Developing a data design using a UML class
  diagram
• Four basic steps
• Step 3 Determine required relationship between
  tables
• For each connecting lines, determine cardinality
  of the relationship between tables

26
Designing Data with a UML Class Diagram

• Developing a data design using a UML class
  diagram
• Four basic steps
• Step 3 (continued)
• Write cardinalities next to line between entities
• If there are any many-to-many relationships,
  convert them to one-to-many relationships by
  adding junction table

27
Designing Data with a UML Class Diagram

• Developing a data design using a UML class
  diagram
• Four basic steps
• Step 4 Determine required attributes by
• Assigning a primary key to each tables
• Linking related tables by adding a foreign key to
  one of the pair in the relationship
• Linkage depends on cardinality of the
  relationship

28
Designing Data with a UML Class Diagram

• Additional data design implementation issues
• Suggestions
• One master table instead of two
• One event table instead of two
• Option A Two records in two tables
• Option B One record in one table
• Eliminate redundant relationships
• Add relationships not involving event records

29
Designing Data with a UML Class Diagram

• Communicating the data design
• Guidelines in preparing documentation
• Be consistent in naming entities
• Name boxes so can easily correlate UML diagram
  with preceding documentation
• Help reader understand how each part of the
  documentation relates to other parts

30
Designing Data with a UML Class Diagram

• Communicating the data design
• Guidelines in preparing documentation -
• Proper layout can also enhance readability
• Start each part on a separate page
• Clearly label each part
• Write a brief explanation of the information
  obtainable by reviewing diagram
• Use bulleted lists to explain linkages between
  diagrams
• Use same style throughout

31
Keyterms

• Attributes
• Cardinality
• Database
• Database management system
• Foreign key
• Primary key
• Referential integrity
• Relational database
• UML class diagram