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