Deck 2 Accounting Information Systems Romney and Steinbart

1. Deck 2 Accounting Information SystemsRomney and Steinbart Linda Batch May 2012

2. Contents • Learning Objectives • Diagramming Organizational Data Flows • Why, types, constructing data flow diagrams • Relational vs. Flat File Structures • Semantic Data Modeling • Components of a transaction • Data Cardinality • One to one, one to many, many to many

3. Week 2 – Learning Objectives • Diagramming Organizational Data Flows • Importance of system documentation • Types of system documentation • Composing Data Flow Diagrams (DFD) – Context, Logical, and Physical • Creating tables in Access 2010

4. Week 2 – Learning Objectives • Relational Database Management Systems • Relational vs. flat file data structures • Introduction to semantic data modeling (entities and attributes) • Relationship cardinalities • Implementing relational database design in Access2010 and • Key Fields and establishing relationships

5. What is and Why Document • Documentation encompasses the narratives, flowcharts, diagrams, and other written material that explains how a system or process works. The who, what, where, when, why, and how of data entry, processing, storage, information output, and system controls Process Output Input Storage

6. Types of Documentation - Controls • Data Flow Diagrams • Document Flow Charts • System Flow Charts • Program Flow Charts • Supplemented by a process narrative • Step by step explanation of system components and interactions • Read documentation • Evaluate controls systems for strengths and weaknesses • Prepare documentation • Systems are usually evaluated on • Control design • Control effectiveness

7. Data Flow Diagrams • Graphically describes the flow of data within an organization for new or existing systems

8. Components and Symbols of a DFD Data sources and destinations entities Data Flows movement of data between entities, data stores, and processes word descriptions are used except into storage Transformation Processes transformation of data use action words Data Stores Data at rest

9. Data Flows

10. Levels of DFD’s • Context Diagram • Shows inputs and outputs into a system • One process symbol • No data stores • Level 0 • Shows all major activity steps of a system • Processes are labelled 1.0, 2.0 etc.

11. Problem 3.14 b pg.73 Prepare a context diagram and a level 0 data flow diagram for a customer who pays an invoice with a cheque. AR is updated to reflect the payment, the cheque is recorded and deposited to the bank. Processes Record payment from customer Record cash receipt Prepare deposit to bank Data Flows Payment to cash team from customer Record cash deposit Payment data from cash team to AR team Update AR subledger Data to banking team Cheque to Bank Storage AR Subledger Cash receipts Entities Customer Bank

12. Problem 3.14 b - Context DiagramDepicts system boundaries Notice: • Only one process is identified • Boundaries (customer and bank are identified) • Storage is not identified • Control processes are not identified • Timing is not identified

13. Name all DFD elements • Give each process a sequential number • Several processes are identified and are labeled with action words • The flow to storage is not labeled • Control processes are not identified • Timing is not identified

14. Example The registrar’s office of a small college receives paper enrollment forms from students. They sort these records alphabetically and then update the student record file to show the new classes. They also prepare class lists from the same data. The sorted enrollment forms are forwarded to the bursar’s office for billing purposes. Class lists are mailed to faculty members. Processes Update student records Prepare Class Lists Sort enrollment forms Data Flows Enrollment forms to registrar Sorted forms to update student records Sorted enrollment forms to Bursar Class lists sent to faculty members Storage Student records Entities Registrar’s Office Students Bursar’s Office Faculty Members

15. Level 0 DFD Bursar Enrollment Forms Enrollment Forms 1.0 Update Student Records 2.0 Prepare Class Lists Students Faculty Enrollment Forms Class Lists Student Records

16. For Homework • Complete Problem 3.6 (a) • Payroll Processing Context Diagram and Level 0 DFD • Hand in at the beginning of the next class

17. Contents • Learning Objectives • Diagramming Organizational Data Flows • Why, types, constructing data flow diagrams • Relational vs. Flat File Structures • Introduction to Semantic Data Modeling • Components of a transaction • Semantic Data Modeling • Table Cardinality • One to one, one to many, many to many

18. Database Structures – from week 1 • Hierarchical database structure • Data is arranged in subordinate files • Items in each file cannot be easily cross referenced with that in another path

19. Files vs. Databases

20. Master Files vs. One Central Database • Relational databases allowed an organization to have one set of centrally coordinated files

21. Database Structures – from week 1 • Relational Database structure • Use one piece of information to search and access more details • Data organized in tables • Tables are cross referenced Advantages: • Files are logically combined • Data is easily shared across processes allowing for cross functional analysis • Minimize data redundancy and data inconsistency • Data is separate from programs that access it so change to the program does not require change to data

22. Checkpoint • Give me an example of hierarchical data storage • Any paper system • Telephone book • Paper payroll files • Give me an example of cross functional analysis • Manufacturing doing demand planning by sales district to predict production levels • What is the point: • Identify and organize data for an organization into tables for good design of relational databases • Terminology • Entity, attributes • Resources, events, agents • (REA)

23. Contents • Learning Objectives • Diagramming Organizational Data Flows • Why, types, constructing data flow diagrams • Relational vs. Flat File Structures • Introduction to Semantic Data Modeling • Table Cardinality • One to one, one to many, many to many

24. Two Approaches to DB Design (ch 4 p 97) • Normalization Data Modeling • Assumed that everything is initially stored in one table and then rules are followed to decompose it into as set of tables • The new set of tables is called the third normalized form (3NF) • Semantic Data Modeling • The designer uses knowledge of the business processes and information needs to create a diagram of what to include in the database • The outcome is a set of tables in the 3NF form • Designer knowledge results in an efficient design • Graphical model helps to ensure the design meets the users needs

25. Components of a Transaction • Resource • What was exchanged? • Data might include type of product/service, special features, quantity, quality etc. • Event • The occurrence of the exchange • Data might include date & time and place • Agent • Internal agent: the company's representative • External agent: buyer, seller, broker etc. For each transaction the company saves information on the events, resources, and agents

26. Semantic Data Modeling • Entities • Attributes generalized class of people, places, things or activities for which data is stored. • Can be an resource, event, or agent • Inventory, sale, customer, vendor, sales person characteristics of an entity • Customer number, address, material number, material quality, pay rate Data is grouped according to entities

27. Semantic Data Modeling Attribute Types Primary keys and Foreign keys are used to link tables – primary key uniquely identifies a specific row in a table (customer number) – foreign key is an attribute in one table that is a primary key in another table – other non key attributes in a table that store important information about that entity

28. Database Terminology Database Management System • Interface between software applications and the data in files Database Administrator • Person responsible for maintaining the database Data Dictionary • Information about the structure of the database Database Languages • DDL – Data definition language (IT) • DML – Data manipulation language (IT) • DQL – Contains powerful easy to use commands that enable users to retrieve, sort, order and display data

29. Logical and Physical Views of Data p.90) Why? This separates the use of the data from the storage of the data • The logical view is how people conceptually organize and understand the data. • The physical view refers to how and where there data are arranged and stored in the computer

30. Logical and Physical Views of Data p.90) The DBMS links the logical view with the physical view of the data • Users can change their logical view of the data and it does not affect where and how the data is stored • IT people can change where and how the data is stored and it does not impact the user.

31. Checkpoint • The two key components in semantic data modeling • Entity • Attribute • In MS Access what are these represented by? • Tables • Fields • Types of Entities • Keep in mind the generic nature of the definition • Resources • Inventory • Intellectual property • Events • Sales • Payments • Agents (generic) • Sales people • Suppliers (Vendors) • Employees

32. Checkpoint • What are the types of attributes? • Primary Key • Foreign Key • Other non key attributes

33. Contents • Learning Objectives • Diagramming Organizational Data Flows • Why, types, constructing data flow diagrams • Relational vs. Flat File Structures • Introduction to Semantic Data Modeling • Table Cardinality • One to one, one to many, many to many

34. Data Cardinalities (Relationships between Tables)One-to-One Each value (one) in the primary key of the first table is the same as one and only one value in the foreign key of the other table. Foreign Key Primary Key Orders Shipping Customer ID Sales Rep ID Shipping ID Order ID Date Date Order ID AAAA April 3 C01 PP6 SS01 April 5 AAAA BBBB April 3 PP9 SS02 April 5 BBBB C03 SS03 CCCC CCCC April 3 C02 PP9 April 8 DDDD April 4 C01 PP9 SS04 DDDD April 8 EEEE April 4 C02 PP6 SS05 April 8 EEEE

35. Data Cardinalities (Relationships between Tables)One-to-One These relationships are almost always inefficient. The proper solution is to add the Shipping ID and Shipping Date as fields to the original table Primary Key Orders Order Date Customer ID Sales Rep ID Shipping ID Shipping Date Order ID Now, there are unique values in the primary key. AAAA April 3 C01 PP6 April 5 SS01 BBBB April 3 PP9 C03 SS02 April 5 CCCC April 3 C02 PP9 SS03 April 8 DDDD April 4 C01 PP9 SS04 April 8 EEEE April 4 C02 PP6 SS05 April 8

36. Data Cardinalities (Relationships between Tables)One-to-Many But, what happens if the entire order is not shipped at the same time? That means that for every one order there may be many shipments. A single table will violate the rule which states that for every record, the value in the primary key must be unique. Primary Key Orders Order Date Customer ID Sales Rep ID Shipping ID Shipping Date Order ID AAAA April 3 C01 PP6 April 5 SS01 AAAA April 3 PP9 C01 SS04 April 8 BBBB April 3 C03 PP9 SS02 April 5 BBBB April 4 C03 PP9 SS03 April 8 BBBB April 4 C03 PP9 SS05 April 10

37. One-to-Many However, two related tables model the relationship between orders and shipping very well. Both have unique values in the primary key. The foreign key in the shipping table captures the multiple shipments related to each order. Foreign Key Primary Key Orders Shipping Customer ID Sales Rep ID Shipping ID Order ID Date Date Order ID AAAA April 3 C01 PP6 SS01 April 5 AAAA BBBB April 3 PP9 SS02 April 5 BBBB C03 SS03 BBBB CCCC April 3 C02 PP9 April 8 DDDD April 4 C01 PP9 SS04 AAAA April 8 EEEE April 4 C02 PP6 SS05 April 10 BBBB

38. Data Cardinalities (Relationships between Tables)Many-to-Many • A very common relationship • Many orders for each product and many products for each order. • There are two major problems with the table design below. What are they? Primary Key Orders Unit Price Customer ID Product ID Product Type Order ID Quantity Date Answer: Primary Key Violation $125 AAAA April 3 C01 661 Bottle 450 $55 AAAA April 3 662 Can 1,012 C01 Drum Data Redundancy $250 AAAA April 3 C01 663 144 April 4 C03 661 Bottle BBBB $125 65 April 4 BBBB $250 C03 662 Can 73

39. Data Cardinalities (Relationships between Tables)Many-to-Many • Problem: Two entities are modeled in one table, orders and products • Solution: An intermediary (middle) table between products and orders. • Primary Keys have unique values. • Data Redundancy only exists to link tables. Primary Key Order Details Products Orders Customer ID Product ID Product Type Unit Price Order Line ID Product ID Order ID Date Order ID Quantity AAAA CO1 April 3 661 Bottle $125 661 A901 AAAA 450 BBBB C03 April 3 Can $55 662 662 A902 AAAA 1,012 C02 CCCC April 3 663 Drum $250 663 A903 AAAA 144 DDDD C01 April 4 661 B333 BBBB 65 EEEE C02 April 4 B334 BBBB 662 173

40. Completing the Database • Tables (entities) have been determined. • Relationships (cardinalities) have been established. Next… • Fields (attributes) have to be chosen • The first field is the primary key. Provide a unique identifier for each record. • Be sure to include the foreign keys necessary to link the tables. • What other data needs to be recorded for each type of agent, event and product/service modeled in the tables?

41. Homework • Homework • Problem 3.6 (a) DFD Context and Level 0 • Chapter 4 pp. 86 to 97 • Related Chapter 4 quick study questions • Chapter 17 pp. 492 to 502 • Related Chapter 17 quick study questions • Prepare for in class Quiz #1 (3% of your grade).