DATABASE DESIGN USING THE REA DATA MODEL

DATABASE DESIGN USING THE REA DATA MODEL Chapter 15

Designing and Implementing a Database System Planning Operation and maintenance Requirements analysis Implementation Design Coding

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

Designing and Implementing a Database System Planning Operation and maintenance Requirements analysis Implementation Data ModelingOccurs Here Design Coding

Data Modeling Tools: Entity-Relationship Diagrams • Entity-relationship (E-R) diagrams – graphical technique for portraying a database schema Enrollment Students Line Items

Data Modeling Tools: Entity-Relationship Diagrams Enrollment Students

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

The REA Data Model • Three Basic Types of Entities • Resources that the organization acquires and uses. • Events in which the organization engages • Agents participating in these events

The REA Data Model: An Example Salesperson Inventory Sales Customer Cashier Cash Accounts Receive Cash

THE REA DATA MODEL • Structuring Relationships: The Basic REA Template • Rule 1: Each event is linked to at least one resource that it affects. • Rule 2: Each event is linked to at least one other event. • Rule 3: Each event is linked to at least two agents.

The Basic REA Template Agent A Resource A Event A Agent B Agent C Resource B Event B

The REA Data Model • Rule 1: Every event entity must be linked to at least one resource entity • Some events affect the quantity of a resource: • “Get” events v. “Give” events • Stockflow relationships affect the quantity of a resource • Commitment event represent promises to engage in some future event

The REA Data Model • Rule 2: Every event entity must be linked to at least one other event entity • Give and get events are linked together in an economic duality relationship

The REA Data Model: A Set of Give &Get Exchanges Get Cash Give Inventory Revenue Cycle Give Cash Get Inventory Expenditure Cycle Give Cash Get Employees’ Time Human Resources/Payroll Cycle Get Cash Give Cash Financing Cycle Give (Use) Raw Materials Production Cycle Give (Use) Employee Time Get Finished Goods Inventory Give (Use) Machinery & Equipment

The REA Data Model • Rule 3: Every event entity must be linked to at least two participating agents • Accountability • Monitor the status of commitments and exchanges with outside parties • Links to at least two participating agents

Developing an REA Diagram • STEP ONE: Identify the events about which management wants to collect information. • STEP TWO: Identify the resources affected by the events and the agents who participated. • STEP THREE: Determine the cardinalities between the relationships.

Developing an REA Diagram • Example: Typical activities in the revenue cycle include: • Take customer order • Fill customer order • Bill customer • Collect payment

Developing an REA Diagram Take Order Sale Receive Cash

Developing an REA Diagram • Step Two: Identifying the resources affected by the events and the agents who participated • Involves determining: • The resource(s) reduced by the give event. • The resource(s) increased by the get event. • The resources that are affected by a commitment event.

Developing an REA Diagram Employee Take Order Inventory Customer Sale Employee Customer Cash Receive Cash

Developing an REA Diagram • Step Three: Determining cardinalities between relationships • A cardinality describes the nature of the relationship between two entities. • It indicates how many instances of one entity can be linked to a 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.

Developing an REA Diagram • Minimum cardinality • Indicates whether a specific a instance of the entity next to the cardinality must be linked to at least one instance of the entity on the opposite side of that relationship • Can be either 0 or 1

Developing an REA Diagram • Maximum cardinality • Indicates whether one instance of that entity can be linked to more than one instance of the other entity participating in that relationship • Can be either 1 or N

Developing an REA Diagram • Using the crow’s feet notation: • The symbol for zero is a circle: O • The symbol for one is a single stroke: | • The symbol for many is the crow’s foot:

Developing an REA Diagram Customer Sale

Developing an REA Diagram • Three Types of Relationships • Relationships depend on the maximum cardinality on each side of a relationship. • A one-to-one relationship (1:1) exists when the maximum cardinality for each entity in the relationship is 1. • A one-to-many (1:N) relationship exists when the maximum cardinality on one side is 1 and the maximum on the other side is many. • A many-to-many (M:N) relationship exists when the maximum on both sides is many.

Developing an REA Diagram Take Order Sale

Developing an REA Diagram Customer Sale

Developing an REA Diagram Sale Inventory

Developing an REA Diagram Sale Cash Receipt

Developing an REA Diagram Employee Take Order Inventory Customer Sale Employee Customer Cash Receive Cash

Developing an REA Diagram • Agent – event cardinalities: • The cardinality between agent and event is typically (0:N) on the agent side • Resource – event cardinalities: • The minimum cardinality is typically one • The maximum could be one or many • Event – resource cardinalities: • The minimum is typically zero

Developing an REA Diagram • Cardinalities between events • When events occur in a sequence, the minimum cardinality between the first event and the second event is always zero • The minimum cardinality between the second event and the first event is typically one • An exception could occur if the first event is not required for the second event to occur • The maximums in the cardinalities between events can be either one or many

Developing an REA Diagram • Uniqueness of REA Diagrams • Each organization will have its own unique REA diagram. • Data modeling is typically complex and repetitive

Summary • We have: • Learned the steps to follow in designing and implementing a database system; • Learned how the REA data model is used to design an AIS database; • Learned how an entity-relationship diagram of an AIS database is drawn; • Learned how to read REA diagrams; • Learned what REA diagrams reveal about the activities and policies of the organization being modeled.

DATABASE DESIGN USING THE REA DATA MODEL