Technology Review-II

1
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

2
Agenda

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

3
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.

4
The REA Data Model

• The REA data model is a conceptual modeling tool
  specifically designed to provide structure for
  designing AIS databases.
• 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

5
Sample REA Diagram
Sales Person
Participates in
Stock flow
Inventory
Sales
Participates in
Economic Duality
Customer
Participates in
Stock flow
Cashier
Cash
Receive Cash
Participates in
6
Basic REA Template
Internal Agent
Participates in
inflow
Resource A
Get Resource A
Participates in
External Agent
Economic Duality
Participates in
Internal Agent
outflow
Resource B
Give up Resource B
Participates in
External Agent
7
An AIS Viewed as a Set of Give-to-Get Exchanges
Revenue Cycle
Give Cash
Get Inventory
Expenditure Cycle
Give Cash
Get Employees Time/Service
Human Resources/Payroll Cycle
Give Cash
Production Cycle
Get Cash
Get Employees Time/Service
Financing Cycle
Get Finished Goods Inventory
Give/Use Raw Materials
Give (Use) Machine Time Services
8
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
• Resources acquired and used by an organization
• Events engaged in by the organization
• Agents participating in these events

9
Types of Entities

• Resources are defined as those things that have
  economic value to the organization.
• What are some examples?
• cash
• inventory
• equipment

10
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

11
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

12
Structured Relationships

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

13
Structured Relationships
Get resource A
Resource A
Give up resource B
Resource B
14
Structured Relationships
Internal Agent
Get resource A
External Agent
Internal Agent
Give up resource B
External Agent
15
Agenda

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

16
Developing an REA Diagram

• 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.

17
Identify EconomicExchange 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 SSs revenue cycle shows the
  drawing of sales and cash collections events
  entities as rectangles and the relationship
  between them as a diamond.

18
Identify EconomicExchange 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.

19
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 collections event involves the
  acquisition of cash.

20
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).

21
Specify Cardinalities

• 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.
• Cardinalities are often expressed as a pair of
  numbers.
• The first number is the minimum, and the second
  number is the maximum.

22
Specify Cardinalities

• The minimum cardinality of a 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 this side of the
  relationship.

23
Specify Cardinalities

• 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
(0, N)
Customer
24
Specify Cardinalities

• Minimum cardinalities of zero are 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 other entity.

25
Specify Cardinalities

• 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
Customer
(1, 1)
26
Specify Cardinalities

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

27
Specify Cardinalities

• Three basic types of 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

28
Specify 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.

29
Examples
Cash Receipts
Sales

• )

(1, 1)
(0, 1)
Sales
Cash Receipts
(1, 1)
(0, N)
Sales
Cash Receipts
(0, 1)
(1, N)
Sales
Cash Receipts
(0, N)
(1, N)
30
Agenda

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

31
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.

32
Create Tables

• A properly normalized relational database has a
  table for each entity and each many-to-many
  relationship.
• 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.

33
Create Tables

• Inventory
• Sales
• Salesperson
• Customer
• Cashier

• Cash collections
• Cash
• Sales-inventory
• Sales-cash collections

34
Identify 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.

35
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.

36
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.

37
Agenda

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

38
Documentation

• REA diagrams are especially useful for
  documenting an advanced AIS built using
  databases.
• REA diagrams provide two important types of
  information about a database
• Information about the relationships among data
  items
• Information about the organizations business
  practices

39
Information About Business Practices

• 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
Sales
Inventory
(0, N)
(1, N)
40
Information About Business Practices

• 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.

41
Extracting InformationFrom 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.

(0, 1)
(1, N)
Cash collections
Sales
42
Extracting InformationFrom the AIS

• 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.

43
Topics Discussed

• REA-Introduction
• Developing a REA Diagram
• Implementing a REA Diagram in a Relational
  Database

44
Review

• Which of the following is not considered a
  resource in an REA model?
• Cash
• Accounts Receivable
• Inventory
• Equipment

45
Review

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

46
Review

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

47
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?
• One-to-one relationship
• one-to-many relationship
• many-to-many relationship
• paid-in-full relationship

48
Review

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

49
Review

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

50
Review

• 7. Which set of cardinality pairs most accurately
  models the sales of low-cost, mass-produced items
  by a retail store?
• inventory(0,N) (0,N) Sales
• inventory(0,N) (1,N) Sales
• Inventory (1,N) (1,N) Sales
• inventory(1,N) (1,N) Sales

51
Review

• 8. A company wants to store information about
  both currently used and alternative suppliers?
• Purchases(0,N) (0,1) suppliers
• Purchases(1,N) (1,1) suppliers
• Purchases(0,1) (1,N) suppliers
• Purchases(1,1) (0,N) suppliers

52
Example

• Draw an REA Diagram , complete cardinalities, for
  Joes revenue cycle
• Joes is a small ice cream shop located near the
  local universitys baseball field. Joes 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.