Accounting Information Systems 9th Edition

Accounting Information Systems 9th Edition

Accounting Information Systems 9th Edition Marshall B. Romney Paul John Steinbart 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-1 Data Modeling and Database Design Chapter 5 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-2 Learning Objectives 1.

2. 3. 4. 5. Discuss the steps for designing and implementing a database system. Use the REA data model to design an AIS database. Draw an Entity-Relationship (E-R) diagram of an AIS database. Build a set of tables to implement an REA model of an AIS in a relational database. Read an E-R diagram and explain what it reveals about the business activities and policies of the organization being modeled. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-3 Introduction Ashton Fleming, the accountant for

S&S, is learning that designing a relational database for S&S is not as easy as the computer store salesperson made it seem. He is planning to attend a seminar to teach accountants the basics on how to design a relational database. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-4 Introduction Ashton hopes to have answers for the following questions by the end of the seminar: What are the basic steps to follow

when designing a database? When creating a relational database, how exactly do you decide which attributes belong in which tables? 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-5 Introduction How can you document an AIS that is implemented as a relational database? This chapter explains how to design and document a relational database for an accounting information system. It focuses on one of the aspects of database design, data modeling.

2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-6 Introduction This chapter also introduces the REA accounting model and EntityRelationship (E-R) diagrams. It shows how to use these tools to build a data model of an AIS. Finally, it describes how to implement the resulting data model in a relational database. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-7 Learning Objective 1 Discuss

the steps for designing and implementing a database system. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-8 Designing and Implementing a Database System Six basic steps in designing and implementing a database system: 1. 2. 3. Initial planning to determine the

need for and feasibility of developing a new system (planning stage). Identifying user needs (requirements analysis stage). Developing the contextual-, externaland internal- level schemas (design stage). 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-9 Designing and Implementing a Database System 4. 5. 6. Translating the internal-level schema into the actual database structures that will be implemented in the new system (coding stage).

Transferring all data from the existing system to the new database (implementation stage). Using and maintaining the new system (operation and maintenance stage). 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-10 Learning Objective 2 Use the REA data model to design an AIS database. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-11 The REA Data Model

Data modeling is the process of defining a database so that it faithfully represents all aspects of the organization, including its interactions with the external environment. The REA (Resources, Data, Events) data model is a conceptual modeling tool that focuses on the business semantics underlying an organizations value chain activities. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-12 The REA Data Model Data Modeling in the database Design Process Data modelin g occurs

here Planning Operation and maintenance Requirements analysis Implementation Design Coding 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-13 The REA Data Model

1 2 The REA data model provides structure in two ways: By identifying what entities should be included in the AIS database By prescribing how to structure relationships among the entities in the AIS database 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-14 Types of Entities An entity is any class of objects about which data is collected. The REA data model classifies entities into three distinct categories:

1 Resources acquired and used by an organization 2 Events engaged in by the organization 3 Agents participating in these events 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-15 Types of Entities Resources are defined as those things that have economic value to the organization. What are some examples?

cash inventory equipment 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-16 Types of Entities Events are the various business activities about which management wants to collect information for planning or control purposes. What are some examples? sales events taking customer orders

2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-17 Types of Entities Agents are the third type of entity in the REA model. Agents are the people and organizations that participate in events and about whom information is desired. What are some examples? employees customers 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

5-18 Developing an REA Diagram 1 2 Developing an REA diagram for a specific transaction cycle consists of four steps: Identify the pair of events that represent the basic give-to-get economic duality relationship in that cycle. Identify the resources affected by each event and the agents who participate in those events. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-19

Developing an REA Diagram Four steps (continued): 3. 4. Analyze each economic exchange event to determine whether it should be decomposed into a combination of one or more commitment events and an economic exchange event. If necessary, replace the original economic exchange event with the resulting set of commitment and economic exchange events. Determine the cardinalities of each relationship. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

5-20 Basic REA template Resource A GET Resource A Inflow Participant Internal Agent Participant External Agent Participant External Agent Economic

Duality Resource B Outflow GIVE Resource B Participant 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart Internal Agent 5-21 Sample REA diagram Inventory Stock-flow

Sales Participant Salesperson Participant Economic Duality Customer Participant Cash Stock-flow Cash Receipts 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Participant Cashier 5-22 REA Diagram, Step 1: Identify Economic Exchange Events In drawing an REA diagram for an individual cycle, it is useful to divide the paper into three columns, one for each type of entity. Left column should be used for resources. Middle column should be used for events. Right column should be used for agents. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

5-23 REA Diagram, Step 1: Identify Economic Exchange Events The basic economic exchange in the revenue cycle involves the sale of goods or services and the subsequent receipt of cash in payment for those sales. The REA diagram for S&Ss revenue cycle shows the drawing of sales and cash receipts events entities as rectangles and the relationship between them as a diamond. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-24 REA Diagram, Step 2:

Identify Resources and Agents Once the events of interest have been specified, the resources that are affected by those events need to be identified. The sales event involves the disposal of inventory. The cash receipts event involves the acquisition of cash. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-25 REA Diagram, Step 2: Identify Resources and Agents After specifying the resources affected by each event, the next step is to identify the agents who participate in those events. There will always be at least one

internal agent (employee) and, in most cases, an external agent (customer). 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-26 REA Diagram, Step 3: Include Commitment Events The third step in drawing an REA diagram is analyzing each economic exchange event to determine whether it can be decomposed into a combination of one or more commitment exchange events. Example: The sales event may be decomposed into the take order

commitment event and the deliver order economic exchange event 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-27 Decomposing Sales into Orders and Sales InventoryOrders (1,N) Customer Orders (0,N) (1,1) Participant (0,N)

Customer (1,1) Participant (0,1) (0,N) Inventory (0,N) InventorySales Leads to Salesperson (0,N) (0,1) (1,N)

Participant (1,1) Sales (1,1) Participant 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart (0,N) Customer 5-28 REA Diagram, Step 4: Determine Cardinalities Cardinalities indicate how many instances of one entity can be linked to one specific instance of another entity.

Cardinalities are often expressed as a pair of numbers. The first number is the minimum, and the second number is the maximum. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-29 REA Diagram, Step 4: Determine Cardinalities The minimum cardinality of a relationship indicates whether each row in that entity MUST be linked to a row in the entity on the

other side of the relationship. Minimum cardinalities can be either 0 or 1. A minimum cardinality of zero means that a new row can be added to that table without being linked to any rows in the other table. A minimum cardinality of 1 means that each row in that table MUST be linked to at least one row in the other table 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-30 REA Diagram, Step 4: Determine Cardinalities Sales The minimum cardinality of zero in the (0, N) cardinality pair to the left of the customer entity in the customer-sales relationship indicates that a new

customer may be added to the database without being linked to any sales events. Made to (0, N) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart Customer 5-31 REA Diagram, Step 4: Determine Cardinalities Sales The minimum cardinality of 1 in the (1, 1) cardinality pair to the right of the sales entity in the customer-sales

relationship indicates that a new sales transaction CAN ONLY be added if it is linked to a customer. (1,1) Made to (0, N) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart Customer 5-32 REA Diagram, Step 4: Determine Cardinalities

The maximum cardinality of a relationship indicates whether each row in that entity CAN be linked to more than one row in the entity on the other side of the relationship. Maximum cardinalities can be either 1 or N. A minimum cardinality of 1 means that each row in that table can be linked to at most only 1 row in the other table. A maximum cardinality of N means that each row in that table MAY be linked to more than one row in the other table. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-33 REA Diagram, Step 4: Determine Cardinalities Sales

The maximum cardinality of N in the (0, N) cardinality pair to the left of the customer entity in the customer-sales relationship indicates that a given customer MAY be linked to many sales events. Made to (0, N) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart Customer 5-34 REA Diagram, Step 4: Determine Cardinalities Sales

The maximum cardinality of 1 in the (1, 1) cardinality pair to the right of the sales entity in the customer-sales relationship indicates that a given sales transaction can only be linked to one customer. (1,1) Made to (0, N) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart Customer 5-35 REA Diagram, Step 4: Determine Cardinalities Cardinalities are not arbitrarily chosen by the database designer. They reflect facts about the

organization being modeled and its business practices obtained during the requirements analysis stage of the database design process. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-36 Relationships between Entities 1. 2. 3. Three basic types of relationships between entities are possible, depending on the maximum cardinality associated with each

entity. They are: A one-to-one relationship (1:1) A one-to-many relationship (1:N) A many-to-many relationship (M:N) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-37 Different types of relationships Panel A: One-to-One (1:1) Relationship Sales (0,1) (1,1) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Cash Receipts 5-38 Different types of relationships Panel B: One-to-Many (1:N) Relationship Sales (0,N) (1,1) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart Cash Receipts 5-39

Different types of relationships Panel C: One-to-Many (1:N) Relationship Sales (0,1) (1,N) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart Cash Receipts 5-40 Different types of relationships Panel D: Many-to-Many (M:N) Relationship

Sales (0,N) (1,N) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart Cash Receipts 5-41 Learning Objective 3 Draw an EntityRelationship (E-R) diagram of an AIS database. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

5-42 Entity-Relationship Diagram An Entity-Relationship (E-R) diagram is one method for portraying a database schema. It is called an E-R diagram because it shows the various entities being modeled and the important relationships among them. In an E-R diagram, entities appear as rectangles, and relationships between entities are represented as diamonds. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-43

Sample E-R Diagrams Employers Managed By Part of Supervisors Manages Departments Customer Orders Part of Sales Part of

Cash Receipts Players Part of Teams Part of League 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-44 Sample E-R Diagram based on REA model Inventory (0,N)

InventoryPurchases (1,N) Purchases (0,N) (1,1) Participant (0,N) Buyer (Purchasing Agent) (1,1) Participant (0,N) PurchasesCash

Disbursements (1,N) Cash (0,N) Stockflow (1,1) Cash Disbursement Vendor Participant (0,N) Participant

(0,N) (1,1) (1,1) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart Cashier 5-45 Learning Objective 4 Build a set of tables to implement an REA model of an AIS in a relational database. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

5-46 Implementing an REA Diagram in a Relational Database An REA diagram can be used to design a well-structured relational database. A well-structured relational database is one that is not subject to update, insert, and delete anomaly problems. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-47 Implementing an REA Diagram in a Relational Database Implementing an REA diagram in a relational database is a three-step process:

1. 2. 3. Create a table for each distinct entity and for each many-to many relationship Assign attributes to appropriate tables Use foreign keys to implement one-to-one and one-to-many relationships 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-48 Implementing an REA model Inventory (0,N) InventoryPurchases (1,N)

Purchases (0,N) (1,1) Participant (0,N) Buyer (Purchasing Agent) (1,1) Participant (0,N) PurchasesCash Disbursements (1,N)

Cash (0,N) Stockflow (1,1) Cash Disbursement Vendor Participant (0,N) Participant (0,N) (1,1)

(1,1) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart Cashier 5-49 Create Tables From the previously discussed REA diagram, nine tables would be created: one for each of the seven entities and one for each of the many-to-many relationships. 1. 2. 3. 4. 5. Inventory Purchases Employees

Vendors Cashier 6. 7. 8. 9. Cash disbursements Cash Purchasesinventory Purchases-cash disbursements 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-50 Assign Attributes for Each Table Primary keys: Usually, the primary key

of a table representing an entity is a single attribute. Other Attributes: Additional attributes are included in each table to satisfy transaction processing requirements. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-51 Implement One-to-One and One-to-Many Relationships One-to-One Relationships: In a relational database, one-to-one relationships between entities can be implemented by including the primary key of one entity as a foreign key in the table representing the other entity. No examples of 1:1 relationships in the sample diagram

2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-52 Implement One-to-One and One-to-Many Relationships One-to-Many Relationships: In a relational database, one-to-many relationships can be also implemented in relation to databases by means of foreign keys. The primary key of the entity with the maximum cardinality of N becomes a foreign key in the entity with a maximum cardinality of 1 Examples: Employee number and vendor

number are foreign keys in the purchases event and in the cash disbursement event 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-53 Learning Objective 5 Read an E-R diagram and explain what it reveals about the business activities and policies of the organization being modeled. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-54 Documentation of Business Practices REA diagrams are especially useful for documenting an advanced AIS

built using databases. REA diagrams provide information about the organizations business practices 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-55 Documentation of Business Practices The zero minimum for the sales event indicates that credit sales are made The N maximum for the sales event means that customers may make installment payments Cash Receipts

(1, N) SalesCash Receipts 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart (0, N) Sales 5-56 Documentation of Business Practices Cash Receipts The one minimum for the cash receipts event indicates that cash is not received prior to delivering the merchandise

The N maximum for the cash receipts event means that customers may pay for several sales with one check (1, N) SalesCash Receipts 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart (0, N) Sales 5-57 Organization Specificity of REA Diagrams Due to the fact that S&S sells massproduced goods, its REA diagram models the relationship between sales and inventory as being many-to-many. An REA diagram for a rare art dealer would depict the relationship between sales and inventory as being one-tomany.

2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-58 Extracting Information From the AIS A complete REA diagram serves as a useful guide for querying an AIS database. Queries can be used to generate journals and ledgers from a relational database built on the REA model. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-59 Extracting Information

From the AIS Sales (0, 1) (1, N) Cash collections Each sales transaction is paid in full by a cash collection event. Each customer payment may be for more than one sale. What is the query logic? Total accounts receivable is the sum of all sales for which there is no remittance number.

2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-60 Extracting Information From the AIS Sales (0, N) (1, 1) Cash collections Each sales transaction can be paid in installments.

Each customer payment is for just one sale. What is the query logic? (1) sum all sales; (2) sum cash collections; then A/R = (1)-(2) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-61 Extracting Information From the AIS Sales (0, 1) (1, 1) Cash

collections Each sales transaction is paid in full by a cash collection event. Each customer payment is for one sale. What is the query logic? Total accounts receivable is the sum of all sales for which there is no remittance number. 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-62 Extracting Information From the AIS Sales

(0, N) (1, N) Cash collections Each sales transaction may be paid for in installments. Each customer payment may be for more than one sale. What is the query logic? (1) Sum all sales; (2) Sum all cash collections; Then A/R = (1)-(2) 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5-63 End of Chapter 5 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

5-64

Recently Viewed Presentations

  • NGA Power Point Template

    NGA Power Point Template

    Albert Wat, Senior Policy Analyst, Education Division National Smart Start Conference ... Dorothy Strickland Bobbie Burnham, Kathy Christie, Linda Fandel, Theresa Hawley, Rich Long, Ashley Parrot, Barbara O'Brien, Jada Rupley, Christine Scanlan, and Emily Workman Our consultant Anna Lovejoy ...
  • Tofiii - Ihep

    Tofiii - Ihep

    It takes about two hours to test Four PMTs, so we can measure all the PMTs in about one month. Dark box PMT HV Signal housing LED fiber head oscilloscope shield Mass Tests by IHEP (3) The rising time of...
  • Managing Electronic Documents Effectively

    Managing Electronic Documents Effectively

    Nigel Fort. HR Assistant. Gillian McCutcheon. Louise Hodgson. Blackpool PCT. Harry Clarke. Executive Director of Operations. Information & Performance Manager . James Goddall. Surgical . Dr Dhruv Sharda. Doctor. Lancahire Care. Dr Maisa Saghairoun. Emma Dawkins. Acting Deputy Director of...
  • Photosynthesis & Respiration

    Photosynthesis & Respiration

    Describe Photosynthesis The process of changing light energy to chemical energy Energy stored as sugar Occurs in plants and some algae Plants need light energy, CO2, and H2O Takes place in the chloroplasts, using chlorophyll, the green pigment in plants...
  • Send Information Exchange

    Send Information Exchange

    Orrets Meadow OutreachSOCIAL MEDIA - updates, videos of strategies and links to SEN news. good place to signpost parents of pupils with SpLD, dyslexia and dyscalculia.We are also looking to see if there are people out there to join our...
  • לוקחים אחריות נקייה - iClean

    לוקחים אחריות נקייה - iClean

    טלפונים חכמים עם מצלמות ו-GPS מאפשרים דיווח מהשטח בזמן אמת. רשתות חברתיות לפרסום וקידום ... לוקחים אחריות נקייה - iClean Last modified by:
  • Powerpoint template for scientific posters (Swarthmore College)

    Powerpoint template for scientific posters (Swarthmore College)

    Suggestions and gripes to: [email protected] Randy Dettmers1, Mitch Hartley1, and Tom Hodgman2 1U.S. Fish & Wildlife Service, Hadley, MA and 2Maine Dept. Inland Fisheries & Wildlife, Bangor, ME
  • CSC 252a: Algorithms Pallavi Moorthy 252a-av Smith College

    CSC 252a: Algorithms Pallavi Moorthy 252a-av Smith College

    Pallavi Moorthy 252a-av Smith College December 14, 2000 Outline What are disjoint sets? What are disjoint set data structures? Explanation of data structure An application - determining connected components Two representations of disjoint sets: (1) linked lists (2) disjoint set...