Technology ReviewII Professor Martin Professor Xiong CSUS This

Technology Review-II Professor Martin Professor Xiong CSUS This lecture is based primarily on Romney & Steinbart(2003). It also draws on Martin (2002). Updated on: Monday, September 9, 2003

Agenda § § REA Developing a REA Diagram Implementing an REA Diagram in a Relational Database The Purpose of REA

The REA Data Model t 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 Data Model The REA data model is a conceptual modeling tool specifically designed to provide structure for designing AIS databases. t The REA data model provides structure in two ways: 1 By identifying what entities should be included in the AIS database 2 By prescribing how to structure relationships among the entities in the t

Sample REA Diagram Inventory Stock flow Sales Person Participates in Sales Participates in Customer Economic Duality Participate s in Cash Stock flow Cashier Receive Cash Participates in

Basic REA Template Resource A inflow Internal Participates Agent in Get Resource A Participates in External Economic Agent Duality Participate s in Internal Agent Resource B outflow Give up Resource B Participates in External Agent

An AIS Viewed as a Set of Giveto-Get Exchanges Give Inventory Get Cash Revenue Cycle Give Cash Get Inventory Expenditure Cycle Give Cash Get Employee’s Time/Service Get Cash Human Resources/Payroll Cycle Production Cycle Get Employee’s Time/Service Give/Use Raw Materials Give (Use) Machine Time & Services Get Finished Goods Inventory Financing Cycle

Types of Entities An entity is any class of objects about which data is collected. t 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 t

Types of Entities Resources are defined as those things that have economic value to the organization. t What are some examples? – cash – inventory – equipment t

Types of Entities Events are the various business activities about which management wants to collect information for planning or control purposes. t What are some examples? – sales events – taking customer orders t

Types of Entities Agents are third type of entity in the REA model. t Agents are the people and organizations that participate in events and about whom information is desired. t What are some examples? – employees – customers t

Structured Relationships t The REA data model prescribes a basic pattern for how the three types of entities (resources, events, and agents) should relate to one another.

Structured Relationships Resource A Inflow Get resource A Economic duality Resource B Outflow Give up resource B

Structured Relationships Get resource A Participates in Internal Agent Participates in External Agent Economic duality Give up resource B

Agenda § § REA Developing a REA Diagram Implementing an REA Diagram in a Relational Database The Purpose of REA

Developing an REA Diagram t 1 2 3 Developing an REA diagram for a specific transaction cycle consists of three steps: Identify the pair of events that reflect the basic economic exchange. Identify the resources affected by each event and the agents who participate in those events. Determine the cardinalities of each relationship.

Identify Economic Exchange Events t t 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&S’s revenue cycle shows the drawing of sales and cash collections events entities as rectangles and the relationship between them as a diamond.

Identify Economic Exchange Events t t 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.

Identify Resources and Agents t Once the events of interest have been specified, the resources that are affected by those events need to be identified. t The sales event involves the disposal of inventory. t The cash collections event involves the acquisition of cash.

Identify Resources and Agents t After specifying the resources affected by each event, the next step is to identify the agents who participate in those events. t There will always be at least one internal agent (employee) and, in most cases, an external agent (customer).

Specify Cardinalities t The cardinality of a relationship indicates how many occurrences of one entity in the relationship can be linked to a single occurrence of the other entity in the relationship. t Cardinalities are often expressed as a pair of numbers.

Specify Cardinalities t The minimum cardinality of a t t relationship indicates the fewest number of rows that can be involved in that relationship. Minimum cardinalities can be either 0 or 1. A minimum cardinality of zero means that each occurrence of the entity on the other side of the relationship need not be linked to any occurrences of the entity on

Specify Cardinalities t 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 given customer need not be linked to any sales events. Sales Made to (0, N) Customer

Specify Cardinalities t Minimum cardinalities of zero are t t common for relationships between two temporarily linked events. Why? Because at any given time the second event in the pair may not yet have occurred. A minimum cardinality of 1 indicates that each instance of that entity must be associated with at least one instance of the

Specify Cardinalities t The minimum cardinality below the sales entity in the sales-customer relationship reflects the general business rule that each sales event must be associated with some specific customer. Sales (1, 1) Party to Customer

Specify Cardinalities t The maximum cardinality of a relationship indicates the largest number of rows that can be involved in that relationship. t Maximum cardinalities can be either 1 or N. t The N indicates that each row in the table may be linked to many rows in the other table.

Specify Cardinalities t Three basic types of 1 2 3 relationships between entities are possible, depending on the maximum cardinality associated with each entity. One-to-one relationship One-to-many relationship Many-to-many relationship

Specify Cardinalities t Cardinalities are not arbitrarily chosen by the database designer. t They reflect facts about the organization being modeled and its business practices obtained during the requirements analysis stage of the database design process.

Examples Sales ) Sales (0, 1) (0, N) (1, 1) Sales (0, N) Cash Receipts (1, N) Cash Receipts Sales (0, 1) Cash Receipts

Agenda § REA § Developing a REA Diagram § Implementing an REA Diagram in a Relational Database § The Purpose of REA

Implementing an REA Diagram in a Relational Database An REA diagram can be used to design a well-structured relational database. t A well-structured relational database is one that is not subject to update, insert, and delete anomaly problems. t

Create Tables A properly normalized relational database has a table for each entity and each manyto-many relationship. t 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. t

Create Tables Inventory t Salesperson t Customer t Cashier t Cash collections t Cash t Salesinventory t Sales-cash collections t

Identify Attributes for Each Table t Primary keys: Usually, the primary key of a table representing an entity is a single attribute. t Other Attributes: t Additional attributes are included in each table to satisfy transaction processing requirements. t

Implement One-to-One and One-to-Many Relationships One-to-One Relationships: t 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. t

Implement One-to-One and One-to-Many Relationships: t In a relational database, one-to -many relationships can be also implemented in relation to databases by means of foreign keys. t

Agenda § REA § Developing a REA Diagram § Implementing an REA Diagram in a Relational Database § The Purpose of REA

Documentation t t REA diagrams are especially useful for documenting an advanced AIS built using databases. REA diagrams provide two important types of information about a database: 1 Information about the relationships among data items 2 Information about the organization’s business

Information About Business Practices t The cardinalities in REA diagrams provide useful information about the nature of the company being modeled and the business policies that it follows. Events Resources Inventory (0, N) Line items (1, N) Sales

Information About Business Practices t t t The relationship between sales and inventory is called line items and represents the fact that each sale consists of one or more items of merchandise. Each item appears as a separate line item on the sales invoice. The quantity sold indicates that a customer may buy more than one of a given item.

Extracting Information From the AIS A complete REA diagram serves as a useful guide for querying an AIS database. t Queries can be used to generate journals and ledgers from a relational database built on the (0, 1)REA model. (1, N) Cash t Sales collections

Extracting Information From the AIS t t In a one-to-many relationship between cash collection and sales, remittance number is a foreign key in the sales table. Each sales transaction is paid in full by a cash collection event. What is the query logic? Total accounts receivable is the sum of all sales for which there is no remittance number.

Topics Discussed § § § REA-Introduction Developing a REA Diagram Implementing a REA Diagram in a Relational Database

Review 1. a. b. c. d. Which of the following is not considered a resource in an REA model? Cash Accounts Receivable Inventory Equipment

Review 2. Which of the following is not a type of entity in the REA data model? a. Customers b. Sales c. Invoices d. Delivery trucks

Review 3. Which type of relationship cardinality must be implemented in a relational database as a separate table? a. One-to-one relationship b. One-to-many relationship c. Many-to-many relationship d. all of the above

Review 4. In a company pays for each purchase it makes with a separate check and does not make installment payments on any purchases, then the relationship between cash disbursement and purchases would be modeled as being with of the following? a. One-to-one relationship b. one-to-many relationship

Review 5. The key of a many-to-many relationship between the sales and inventory events would be: a. invoice number b. item number c. both invoice number and item number d. either invoice number or item number

Review 6. Which of the following elements of the REA data model must be implemented as tables in a relational database? a. Resources b. events c. agents d. all of the above

Review 7. Which set of cardinality pairs most accurately models the sales of low-cost, massproduced items by a retail store? a. inventory(0, N) – (0, N) Sales b. inventory(0, N) – (1, N) Sales c. Inventory (1, N) – (1, N) Sales d. inventory(1, N) – (1, N) Sales

Review 8. A company wants to store information about both currently used and alternative suppliers? a. Purchases(0, N) – (0, 1) suppliers b. Purchases(1, N) – (1, 1) suppliers c. Purchases(0, 1) – (1, N) suppliers

Example Draw an REA Diagram , complete cardinalities, for Joe’s revenue cycle Joe’s is a small ice cream shop located near the local university’s baseball field. Joe’s serves walk-in customers only. The shop carries 26 flavors of ice cream. Customers can buy cones, sundaes, or shakes. When a customer pays for an individual purchase, a sale transaction usually includes just one item. When a customer pays for a family or a group purchase, however, a single sale may include many different items. All sales must be paid for at the time the ice creams is served.