REA Model to Logical Model

I have been assigned the Appliance Industry. Based on the generic REA model on the instruction sheet and your knowledge of the industry you’ve been assigned, convert the conceptual model into the logical model for this process. This part of the requirement needs to be typewritten using word processing software, such as Microsoft Word, and uploaded in word file format. Make sure your name and company name is on the face of the document. Use the powerpoint and instruction sheet to help you. There is also a rubric at the bottom of the instruction sheet. The powerpoint provides an example of the format wanted.

Chapter 20
Implementing an REA Model
in a Relational Database
Introduction
• Ch. 19 introduced the topic of REA data modeling and explained how
to develop conceptual REA diagrams
• Ch. 20 shows how to implement an REA diagram in a relational DB,
i.e., convert/map a conceptual REA diagram to a logical (or relational)
REA model by creating tables
▪ REA data modeling can be used to design an object-oriented DB in Ch. 19
Learning Objectives
1. Integrate separate REA diagrams for individual business cycles into
a single, comprehensive, organization-wide REA diagram
2. Build a set of (relational database) tables to implement an REA
model of an AIS in a relational database (i.e., convert the integrated
conceptual REA diagram to logical REA model by creating tables)
3. Use the REA data model to write queries to retrieve information
(e.g., traditional F/Ss and custom performance reports) from an AIS
relational database)
Select *
From employees e left join sales s
On e.employeeid = s. employeeid
Select *
From product p left join order_line o
On p.product_id = o.product_id
Logical REA model
Physical REA model
LO 20-1: Integrate separate REA
diagrams for individual business
cycles into a single, comprehensive,
organization-wide REA diagram
Integrating REA Diagrams Across Cycles1
• For a single, comprehensive, organization-wide REA model,
individual REA diagrams (for revenue, expenditure, and payroll cycle)
can be combined to form an integrated REA diagram
Dotted box because
Time Worked
captures EE time info
Redundant
resources
in red and
blue
+
+
=
Redundant events in yellow
Show how sales were made in
exchange for cash
Show how inventory was initially
acquired by giving up cash
Show how time worked was used in
exchange for cash
Integrating REA Diagrams Across Cycles2
• To combine individual REA diagrams into an integrated REA diagram
▪ Merging redundant (or common) resource entities (e.g., inventory is acquired in expenditure cycle
and reduced in the sales cycle) → one resource entity
o Does not affect any cardinalities
❑ The redundant resource entity (e.g., inventory) can be linked to both cycles (e.g., both expenditure cycle and sales cycle)
▪ Merging redundant (or common) event entities (e.g., “Disburse cash event” appears in the
expenditure cycle and the payroll cycle) → one event entity
o Alter the minimum cardinalities between the merged event (e.g., Disburse cash event) and its associated events
(e.g., Time worked event and Received Inventory event)
Min. cardinality changes
from 1 to 0
❑ The redundant event entity (e.g., Disburse cash event) can be linked to an event of one transaction cycle (e.g., Time
worked event) OR to an event in another transaction cycle (e.g., Received Inventory event) → the event cannot be
simultaneously linked to both events
❖ E.g., The same check (i.e., Disburse cash event) cannot be written to both events, i.e., either Time worked or
Receive inventory event → min. cardinalities (1 → 0) between Disburse cash event and Time worked event & min.
cardinalities (1 → 0) between Disburse cash event and Receive inventory event
o Alter the minimum cardinalities between the merged event (e.g., Disburse cash event) and the agents
participating in that event (e.g., a suppler as payee and an employee as payee)
❑ The redundant event entity (e.g., Disburse cash event) cannot be linked to both agents
❖ E.g., The same check (i.e., Disburse cash event) cannot be written to both agents (e.g., a supplier and an employee),
i.e., one for a suppler and another for an employee, not for both → min. cardinalities (1 → 0) between Disburse
cash event and Employee as payee & min. cardinalities (1 → 0) between Disburse cash event and Suppler as payee
Merging Redundant Resource Entities
• Inventory in expenditure cycle and in sales cycle
• Cash in expenditure cycle and in sales cycle
Get inventory for sales → have expenditure cycle (i.e., base cycle) first and flip the sides!
Agent
(merged cycle)
Redundant
+
Redundant
=
Event
Resource
Event
Agent
Merging Redundant Event Entities
• “Disburse cash event” in expenditure cycle and payroll cycle
Agent
+
Redundant
Event
Resource
Flip the sides up and down
and place it to the bottom!
=
Min. cardinalities (1 → 0)
b/c one check cannot be
written to both a supplier
and an employee
Min. cardinalities (1 → 0)
b/c one check cannot be
both for Time worked and
Receive inventory
Integrated REA Diagram
Agent
Event
Resource
Event
Agent
Expenditure cycle
Revenue cycle
Payroll cycle
Validating Accuracy of Integrated REA
Diagrams: 6 Integrated REA Diagram Rules
• 3 principles in basic REA template (Ch. 19)
1. Every event must be linked to at least one resource
2. Every event must be linked to at least two agents who participate in that event
3. Every event that involves the disposition of a resource must be linked to at least one other
event that involves the acquisition of a resource (i.e., events with give-get exchange)
• 3 principles from merging redundancies
4. Every resource must be linked to at least one event that increments that resource and
to at least one event that decrements that resource i.e., the redundant resource entity (e.g., inventory) can be linked to both cycles
5. If event A can be linked to more than one other event but cannot be linked
Min. cardinality changes simultaneously to all those other events, then the REA diagram should show that
i.e., the redundant event entity (e.g., Disburse cash event)
from 1 to 0
event A is linked to a minimum of 0 of each of the other events cannot be linked to both events
6. If an event can be linked to any one of a set of agents, then the REA diagram should
the redundant event entity (e.g., Disburse cash event)
show that event is linked to a minimum of 0 of each of those agents i.e.,
cannot be linked to both agents
No change in min. cardinality
• 6 rules used to develop intergraded REA diagram and to validate its accuracy
Quiz 20.1: Integrating REA Diagrams
Across Cycles
Because the redundant resource entity (e.g., inventory) can be linked to both cycles
Because the redundant event entity (e.g., Disburse cash event) cannot be linked to
both events or agents
LO 20-2: Build a set of tables to
implement an REA model of an AIS
in a relational database
Implementing REA Diagram in Relational
Database: REA to DB Steps
• Convert/map conceptual REA diagram to logical (or relational) REA model not
subject to update, insert, delete anomalies
• 3 steps
Distinct entity &
(M:N) relationship
1.
2.
Create tables for each distinct entity in the REA diagram and for each many-to-many (M:N)
*It is entirely plausible and sometimes necessary
relationship, i.e., converte.g.,
anSales
entity
&
(M:N)
into
a
table
(Step
1-1
&
1-2)
e.g., Sales-Inventory table
table
to iteratively develop tables while constructing
Assign attributes (i.e., PK, FK, and other) to appropriate tables (Step 2) an REA diagram → a rigid sequence isn’t a
▪ Identify primary keys (PK)
requirement, i.e., creating tables can happen at
various stages during the process.
o PK: Attribute(s) that uniquely identifies each record (e.g., invoice # in Sales table)
o For M:N relationships, the PK consists of 2 attributes that present the PKs of each entity linked in that relationship
(i.e., concatenated (or composite, combined) key, e.g., invoice # + product # in Sales-Inventory table)
(1:1) & (1:N)
relationship
▪ Identify remaining attributes for the table (i.e., FKs and other attributes)
3.
Use foreign keys to implement/convert one-to-one (1:1) & one-to-many (1:N) relationships
▪ FK: Attribute of one entity that is itself the PK of another entity
▪ 1:1 relationship (Step 3-1)
into a table
o If events are sequential (i.e., Take Customer Order event and Sales event), the PK of the 1st event (i.e., sales order
#) should be used as the FK of the 2nd event for merge
o Otherwise (i.e., simultaneous), either entity’s PK can become the FK of the other table for merge
▪ 1:N (or N:1) relationship (Step 3-2)
o The PK (i.e., supplier #) of the entity (i.e., Suppliers event) linked to the Many Entity (e.g., Order Inventory event)
must be used as the FK in the Many Entity for merge
Step 1-1 & 1-2: Create Tables for Each
Distinct Entity and M:N Relationship
1.
Create tables for each distinct entity in
the diagram (Step 1-1) e.g., Sales table
• 13 distinct entities – Employee Time entity =
12 entities → 12 tables in relational DB, i.e.,
7 event tables in red + 2 resource tables in
blue (i.e., Inventory and Cash) + 3 agent
tables in yellow (i.e., Employees,
Customers, and Suppliers)
▪ Master (or maintenance) table: Not change
frequently, i.e., = Resource table + Agent table
▪ Transaction (or event) table: Change
frequently and require time-stamp, i.e., Event
table w/ a date variable
2. Create tables for each many-to-many
(M:N) relationship (Step 1-2) Crow’s feet on both sides
e.g., Sales-Inventory table
Agent
Event
Event
Resource
M:N
Agent
M:N
1:1
M:N
M:N (i.e.,
SalesInventory)
M:N
(Receive-Cash)
• 5 M:N relationships: 5 tables in black arrow
(e.g., Sales-Receive Cash)
(1, N) (0, N)
(M:N)
• 17 tables = 12 tables for each distinct
entity + 5 M:N tables
Our practice REA model for a
revenue cycle is similar (Next)
Step 1-1: Create Tables for Each Distinct
Entity and M:N Relationship
Resource
Event
Agent
Inventory
Item
Sale
Customer
Walk-in Sales only
Cash
Receipt
Cash
Cashier
How Many Tables? 6 tables
4 Master Tables (Resources and Agents)
=Maintenance
2 Event Tables
=Transaction
Format for Step 1-1
Step 1-1: Create Tables for Each Entity
Resource
Event
Agent
Inventory
Item
Sale
Customer
Cash
Cash
Receipt
Cashier
REA
Models
Master
Tables
(=Resource + Agent)
Inventory
Logical
Models
Cash
Customer
Event
/ Transaction Tables
(=Event)
Cashier
Sale
Receipt
Step 1-2: Create Tables for Each Distinct
Entity and M:N Relationship1
2. Create tables for each many-tomany (M:N) relationship e.g., Sales-Inventory table
• 5 M:N relationships: 5 tables in
black arrow (e.g., Sales-Receive
Cash) Crow’s feet on both sides
M:N
1:1
M:N
(1, N)
(0, N)
(M:N)
• In Step 1, 17 tables = 12 distinct
entity tables + 5 M:N tables
M:N
M:N (i.e.,
SalesInventory)
M:N
(Receive-Cash)
Step 1-2: Create Tables for Each Distinct
Entity and M:N Relationship2
Inventory
Item
(1, N)
Sale
Customer
Cash
Receipt
Cashier
(0, N)
M:N (i.e., Sales-Inventory)
Cash
Where is the Many-to-Many Relationship? 1
Step 1-2: Create Tables for Each Distinct
Entity and M:N Relationship3
Inventory
Item
Sale
(1, N)
(0, N)
M:N (i.e., Sales-Inventory)
Why is this complex?
How to Solve?
Putting a foreign key in either direction will lead to
redundancy due to M:N (e.g., 2 Sales #s and 2 Item #s)
Create a relationship table (linking table) with two
foreign keys to create a combined primary key
PK
FK
PK
FK
Item #
Item Description
Sale #
Sale #
Sale Type
Item #
1
Basketball
1, 2
1
In-Store
1
2
Football
2
2
In-Store
1, 2
3
Baseball
two 2s
two 1s
Step 1-2: Create Tables for Each Distinct
Entity and M:N Relationship4
Inventory
Item
Sale
(0, N)
(1, N)
M:N (i.e., Sales-Inventory)
Why is this complex?
How to Solve?
Putting a foreign key in either direction will lead to
redundancy due to M:N (e.g., 2 Sales #s and 2 Item #s)
Create a relationship table (linking table) with two
foreign keys to create a combined primary key
Create 3 Tables
PK
Item Table
PK
Sales Table
Sales_Item Table
Combined Primary Key
Composite/concatenated
Item #
Item Description
Sale #
Sale Type
Sale # FK
Item # FK
1
Basketball
1
In-Store
1
1
2
Football
2
In-Store
2
1
3
Baseball
2
2
Step 2: Assign Attributes to Each Table
2. Assign attributes to appropriate tables
▪ Identify primary keys (PK)
Single PK to distinct tables o PK: Attribute(s) that uniquely identifies each record
(e.g., invoice # in Sales table)
Composite/concatenated
o For M:N relationships, the PK consists of 2 attributes
/combined PK to M:N tables that present the PKs of each entity linked in that
relationship (i.e., concatenated (or composite) key, e.g.,
invoice # + product # in Sales-Inventory table)
▪ Identify remaining attributes for the table (i.e., FKs
and other attributes)
• Cumulative data (e.g., quantity on hand = beg.
+ total purchased – total sold) or calculable
(e.g., total amount of sale = ∑ (quantity sold *
actual sale price)) data are not included in the
table because they are from other attributes
Step 2: Assign Attributes to Each Table1
Inventory
Item
Sale
(0, N)
(1, N)
M:N (i.e., Sales-Inventory)
Why is this complex?
How to Solve?
Putting a foreign key in either direction will lead to
redundancy due to M:N (e.g., 2 Sales #s and 2 Item #s)
Create a relationship table (linking table) with two
foreign keys to create a combined primary key
Assign PK
Sales Table
Item Table
Sales_Item Table
Combined Primary Key
Composite/concatenated
Item #
Item Description
Sale #
Sale Type
1
Basketball
1
In-Store
1
2
Football
2
In-Store
2
1
3
Baseball
2
2
(PK)
(PK)
Sale # (FK)
Item # (FK)
(composite PK)
1
Format for Assigning Attributes to Each
Table: Underline for PK
Step 1-2: Create Tables for M:N Relationship
Step 2: Assign Attributes to Each Table
Inventory
Item
Sale
Customer
Cash
Cash
Receipt
Cashier
REA
Models
(Refer to Table 20-1 for PK, FK, and other attributes)
Master Tables
Item
Logical
Models
Cash
Acct # (PK)
Item # (PK)
Bank Name
Description
Acct Type
Std. Cost
BegBal
List Price
BegBal Dt
BQOH
=Beginning quantity on hand
BQOH Date
(Time-stamp optional)
Customer
Event / Transactional Tables
Cashier
Customer #(PK) Cashier # (PK)
First Name
First Name
Last Name
Last Name
Address
Address
Email
Date of Hire
(Time-stamp required)
Sale
Sale # (PK)
Date
Receipt
Sale_Item
Receipt # (PK) Sale #
(composite PK)
Date
Item #
Quantity Sold
(Time-stamp (i.e., date var.) required)
Step 3: Use Foreign Keys to Implement 1:1
and 1:N Relationships
i.e., Use FK to convert (1:1) and (1:N) into a table in a relational DB
3. Use foreign keys to implement one-to-one (1:1) and one-to-many
(1:N) relationships
▪ FK: Attribute of one entity that is itself the PK of another entity
▪ 1:1 relationship (Step 3-1)
o If events are sequential (i.e., Take Customer Order event and then Sales event), the PK of
the 1st event (i.e., sales order #) should be used as the FK of the 2nd event for merge
o Otherwise (or simultaneous), either entity’s PK can become the FK of the other table for
merge
▪ 1:N (or N:1) relationship (Step 3-2)
o The PK (i.e., supplier #) of the entity (i.e., Suppliers event) linked to the Many Entity
(e.g., Order Inventory event) must be used as the FK in the Many Entity for merge
Step 3-1: Use Foreign Keys to Implement 1:1
and 1:N Relationships: Managing Sequential or
Simultaneous Events1
Inventory
Item
Sale
Customer
(1:1)
Cash
Cash
Receipt
Where are the sequential (or simultaneous) events?
Cashier
Step 3-1: Use Foreign Keys to Implement 1:1
and 1:N Relationships: Managing Sequential or
Simultaneous Events2
Sale
Consider whether the two entities are conceptually separate or whether they
should be combined
Two options:
(1:1)
Sequential → Do not combine them into one table
• The PK of the 1st event should be used as the FK of the 2nd event
Cash
Receipt
Simultaneous → Combine into one table, i.e., DO NOT POST BOTH (will result in
redundancy)
• Either entity’s PK can become the FK of the other table
Step 3-1: Use Foreign Keys to Implement 1:1
and 1:N Relationships: Managing Sequential
Events3
Sales Table
SaleID
Sale
(1:1)
Date
Amount
S1
6/12
$10
S2
6/12
$15
S3
6/13
$12
(PK)
sequential
Sequential → separate table
Cash
Receipt
No FK
Use PK in 1st event as FK in 2nd event
Receive Cash Table
CashReceiptID
Date
Amount
SaleID
CR1
6/15
$10
S1
CR2
6/15
$15
S2
CR3
6/16
$12
S3
(PK)
(FK)
Step 3-1: Use Foreign Keys to Implement 1:1
and 1:N Relationships: Managing
Simultaneous Events4
Sales Table
SaleID
Sale
(1:1)
Date
(PK)
simultaneous
Cash
Receipt
Amount
CashReceiptID
(FK)
S1
6/12
$10
S2
6/12
$15
CR2
S3
6/13
$12
CR3
Receive Cash Table
CashReceiptID
Simultaneous →
combined table
CR1
Use either entity’s PK as FK
in the other table
OR
Date
Amount
SaleID
CR1
6/12
$10
S1
CR2
6/12
$15
S2
CR3
6/13
$12
S3
(PK)
Choose one of these options, but not both
(FK)
Format for Step 3-1
Step 3-1: Manage Sequential (or Simultaneous) Events (1:1)
Inventory
Item
Sale
Customer
REA
Models
sequential
Cash
Receipt
Cash
Master Tables
Logical
Models
Inventory
Cash
Item # (PK)
Description
Std. Cost
List Price
BQOH
BQOH Date
Acct # (PK)
Bank Name
Acct Type
BegBal
BegBal Dt
Customer
Cashier
Event / Transaction Tables
Cashier
Customer #(PK) Cashier # (PK)
First Name
First Name
Last Name
Last Name
Address
Address
Email
Date of Hire
Sale
Sale # (PK)
Date
Payment
Receipt
Sale_Item
Receipt
Pmt # # (PK) Sale #
(composite PK)
Date
Item #
Sale # (FK)
Quantity Sold
Step 3-2: Use Foreign Keys to Implement
1:1 and 1:N Relationships1
i.e., Use FK to convert (1:N) into a table in a relational DB
Inventory
Item
Sale
Customer
Cash
Cash
Receipt
Cashier
Step 3-2: Use Foreign Keys to Implement
1:1 and 1:N Relationships2
Sale
Customer
(0:N)
(1:1)
(N:1) or (1:N)
The PK (e.g., CustomerID) of the entity (e.g., Customer) linked to the Many
Entity (e.g., Sale) must be used as the FK in the Many Entity (e.g., Sale)
Customer Table
CustomerID
Name
SaleID
C1
Steve
S1, S2
(PK)
C2
Sales Table
SaleID
Ariana
Use PK as FK in many event
Date
Amount
CustomerID
S1
6/12
$10
C1
S2
6/12
$15
C1
(FK)
Format for Step 3-2
Step 3-2: Manage 1:N Relationships
Inventory
Item
Sale
Customer
Cash
Cash
Receipt
Cashier
REA
Models
Master Tables
Logical
Models
Event / Transactional Tables
Inventory
Cash
Customer
Cashier
Item #
Description
Std. Cost
List Price
BQOH
BQOH Date
Acct #
Bank Name
Acct Type
BegBal
BegBal Dt
Customer #
First Name
Last Name
Address
Email
Cashier #
First Name
Last Name
Address
Date of Hire
No FK
No FK
No FK
No FK
Sale
Payment
Receipt
Sale_Item
Pmt # #
Sale #
Receipt
Sale #
(composite PK)
Item #
Date
Date
Quantity Sold
Customer # (FK) Sale # (FK)
Acct # (FK)
Cashier #
No FK
(FK)
Cashier # (FK)
Customer # (FK)
Implement All Steps1
Step 1-1: Create Tables for Each Entity
Inventory
Item
Sale
Customer
Cash
Cash
Receipt
Cashier
REA
Models
Master Tables
Inventory
Logical
Models
Cash
Customer
Event / Transactional Tables
Cashier
Sale
Receipt
Implement All Steps2
Step 1-2: Create Tables for M:N Relationship
Step 2: Assign Attributes to Each Table
Inventory
Item
Sale
Customer
Cash
Cash
Receipt
Cashier
REA
Models
Master Tables
Logical
Models
Inventory
Cash
Item # (PK)
Description
Std. Cost
List Price
BQOH
BQOH Date
Acct # (PK)
Bank Name
Acct Type
BegBal
BegBal Dt
Customer
Event / Transaction Tables
Cashier
Customer #(PK) Cashier # (PK)
First Name
First Name
Last Name
Last Name
Address
Address
Email
Date of Hire
Sale
Sale # (PK)
Date
Receipt
Sale_Item
Receipt # (PK) Sale #
(composite PK)
Date
Item #
Quantity Sold
Implement All Steps3
Step 3-1: Manage Sequential (or Simultaneous) Events (1:1)
Inventory
Item
Sale
Customer
REA
Models
sequential
Cash
Receipt
Cash
Master Tables
Logical
Models
Inventory
Cash
Item # (PK)
Description
Std. Cost
List Price
BQOH
BQOH Date
Acct # (PK)
Bank Name
Acct Type
BegBal
BegBal Dt
Customer
Cashier
Event / Transaction Tables
Cashier
Customer #(PK) Cashier # (PK)
First Name
First Name
Last Name
Last Name
Address
Address
Email
Date of Hire
Sale
Sale # (PK)
Date
Payment
Receipt
Sale_Item
Receipt
Pmt # # (PK) Sale #
(composite PK)
Date
Item #
Sale # (FK)
Quantity
Implement All Steps4
Step 3-2: Manage 1:N Relationships
Inventory
Item
Sale
Customer
Cash
Cash
Receipt
Cashier
REA
Models
Master Tables
Logical
Models
Event / Transactional Tables
Inventory
Cash
Customer
Cashier
Item #
Description
Std. Cost
List Price
BQOH
BQOH Date
Acct #
Bank Name
Acct Type
BegBal
BegBal Dt
Customer #
First Name
Last Name
Address
Email
Cashier #
First Name
Last Name
Address
Date of Hire
Sale
Payment
Receipt
Sale_Item
Pmt # #
Sale #
Receipt
Sale #
(composite PK)
Item #
Date
Date
Quantity Sold
Customer # (FK) Sale # (FK)
Acct # (FK)
Cashier #
(FK)
Cashier # (FK)
Customer # (FK)
Implement All Steps5
(Lastly, assign every attribute in the narrative to the appropriate table(s) w/ notations)
Inventory
Item
Sale
Customer
Cash
Cash
Receipt
Cashier
REA
Models
Master Tables
Inventory: Item#, Description, StandardCost, ListPrice, BQOH, BQOHDate *Note: A space in a variable name (e.g.,
First Name) is recognized %20 in coding
Cash: Acct#, BankName, AccountType, BegBal, BegBalDate
Customer: CustID, FirstName, LastName, StreetAddress, City, State, ZipCode, Email
Logical
Cashier: Casher#, FirstName, LastName, StreetAddress, City, State, ZipCode, DateofHire
Models
Use /, not Event/Transaction Tables
This is the format that we will
You can use tbl as a
Can use , between 2 FKs
Sale:
Sales#,
[CustID],
[Casher#],
Date
prefix of table names
use in this course!
if you use MS Access, Sale/Inventory: [Sales#] [Item#], QtySold
(Use an underline for PK, a box bracket for FK,
e.g., tblInventory
Receipt: Receipt#, [CustID], [SalesID], [Acct#], [Sales#], Date
and both for composite PK (=FK+FK))
*Similar to Assignment #2: REA Model to Logical Model
17 tables in a table list
17 Tables in List
No FK
1:N
1:1
sequential
Concatenated keys
Tables from M:N relationships use – for
relationship
Financial info (beg. balance)
and nonfinancial info (cash on
hand, cash in the bank, and
petty cash)
Name should be FirstName and LastName
e.g., GE
Slide #36/#37
& Table 20-1
4. (1:N) or (N:1)
Take purchase
1. Each distinct entity
2. (M:N)
e.g., Order
3. (1:1)
(as a check list)
2. (M:N)
and your company name
e.g., Ship
Template for Logical REA Model
Please rename entities in this generic
conceptual REA model according to
your industry- or company-specific
convention before you convert it into
a logical one in MS Access.
Your name
XXX company in XXX industry
Master Tables
*Note: You should rename tables (keep tbl for the AIS project) and attributes which represent your company and
industry classification. You should reorder non-key attributes in more intuitive way, e.g.,. SalesClerkSSN# and then
SalesClerkDateofHire
tblInventory: Item#, Description, StandardCost, ListPrice, BQOH, BQOHDate (Please add at least one more field specific to your industry)
tblSalesClerk: SalesClerk#, SalesClerkFirstName, SalesClerkLastName, SalesClerkStreetAddress, SalesClerkCity, SalesClerkState, SalesClerkZipCode,
SalesClerkDateofHire, SalesClerkSSN#
(different prefix)
tblCustomer: CustID, CustomerFirstName, CustomerLastName, CustomerStreetAddress, CustomerCity,
CustomerEmail, CustomerPhone#, CustomerSegment (Please add at least one more field specific to your industry)
CustomerState,
CustomerZipCode,
tblShipClerk: ShipClerk#, ShipClerkFirstName, ShipClerkLastName, ShipClerkStreetAddress, ShipClerkCity, ShipClerkState, ShipClerkZipCode,
ShipClerkDateofHire, ShipClerkSSN#
Event Tables
tblOrder: Order#, [SalesClerk #], [CustID], OrderDate
tblOrder/Inventory: [Order#] [Item#], QtyOrdered (different prefix)
tblShip: Ship#, [Order#], [CustID], [ShipClerk#], ShipDate, SHFee
tblShip/Inventory: [Ship#] [Item#], QtyShipped
(different suffix)
Sample Answers in Narrative w/ Notations
These samples are not perfect, but the second one is better for the AIS project in MS Access
Show your name and company name
When table names are in an MS Access style for your AIS project, e.g., tblInventory
Show your name and company name
Show your name and company name
Note: Please make sure that your table and attribute (or column) names (e.g., CaddieID) should represent your company!
Preparation for Assignment #2:
REA Model to Logical Model
• Study “Chapter 20: Comprehensive Problem” for Assignment #2
Conceptual REA model
Table list for logical REA model
(For an AIS project, you need to use notations, e.g.,
an underline for PK and a box bracket for FK)
Solutions
Conceptual REA model
Table list for logical REA model
Completeness Check and Accuracy Check
• Completeness Check: Check the table list to see any missing attributes
▪ For missing table, modify REA diagram
• Accuracy check using 3 basic design requirements for relational DB
(Similar with basic requirements (or rules, constraints) of relational
DB design in Ch. 4)
1. Every table must have a PK
2. Other non-key attributes in each table must describe a fact (or characteristic)
about the thing (or object) designated (or identified) by either the PK or FKs
used to link that table to another table
▪ Non-key attributes are items in a table that are neither a primary key nor a foreign key
3. Every attribute in every table is single-value (i.e., each table is a flat file)
Quiz 20.2: Implementing an REA Diagram
in a Relational Database
If the question is about true
*It is entirely plausible and
sometimes necessary to iteratively
develop tables while constructing an
REA diagram → a rigid sequence
isn’t a requirement, i.e., creating
tables can happen at various stages
during the process.
LO 20-3: Use the REA data model to
write queries to retrieve information
from an AIS relational database
Using REA Diagrams to Retrieve
Information from Database
• We learned how to use integrated REA diagram to design AIS in
relational DB
• Let’s learn how to use integrated REA diagram and resulting tables to
retrieve information to evaluate performance
Creating Journals and Ledgers
• Queries help you extract information from the data tables in relational DB
designed by an REA diagram → create (or produce) journals, ledgers, F/Ss,
and managerial reports
• Journals-queries
▪ Journal: Chronological listing of transactions stored in(Time-stamp
event tables
required)
▪ E.g., to query sales transactions in a chronological order and produce a sales journal
→ use Sales, Sales-Inventory, and Cash-Receipts tables (w/ time-stamp = date var.)
• Ledgers-queries
e.g., asset accounts
▪ Ledger: Master file that contain cumulative information about specific accounts (e.g.,
Cash and Inventory) stored in both resource tables and event tables which affects
change in resources
o Information about an organization’s assets that is posted in ledgers is stored in Resource tables
▪ E.g., to query ledger information → use both a resource table and the event table that
affects the resource
Cash ledger
Generating Financial Statements
• F/Ss-queries
• To query information on F/Ss
• Information on F/S
accounts stored in resource tables, e.g., cash and
e.g., assets
inventory
▪ To query, use resource tables
• Information on F/S accounts not stored in resource tables, e.g.,
accounts receivable (= beg. + new sales – cash receipts)
▪ E.g., to query accounts receivable → use a set of queries on resource and event
tables, i.e., 1st query is increase in credit sales, 2nd query is decrease in cash,
Receive
st query
and 3rd query subtracts 2nd query
from
1
Sales event
Inventory resource Sales-Inventory event Sales event cash event
Cash
A/R
Receipt Balance
Generating Managerial Reports
• Management reports-queries
▪ To query financial and nonfinancial information found in management reports
to monitor the business performance → use resource, event, and agent tables
o E.g., of financial information: Amount of Beg. balance in cash table
o E.g., of nonfinancial information: Types of cash in cash table, e.g., cash on hand, cash in
the bank, and petty cash OR key performance indicators (KPIs)
Quiz 20.3: Using REA Diagrams to
Retrieve Information from a Database
Event table with time-stamp (i.e., date var.)
A/R xxx
Sales xxx
Cash xxx
A/R xxx
COGS xxx Inventory xxx
Note: A/R is the difference between the sales and Receive Cash events
→ not event, resource, or agent → not on REA model
Chapter 20: Summary and Case
Conclusion
Key Terms
• Concatenated keys
Chapter 20 Quiz: Implementing an REA
Diagram in a Relational Database
• 10 questions for 10 points
ACCT 361 Accounting Analytics – Spring 2024
Assignment #2 – Converting from the REA Model to the Logical Model
Background
You have been assigned to build an accounting information system for a company in the following
industry below:
• Last names starting with letters A – E: Sporting Goods / Outdoor Equipment
• Last names starting with letters F – J: Food and Drink
• Last names starting with letters K – O: Apparel
• Last names starting with letters P – T: Appliances
• Last names starting with letters U – Z: Office Supplies
The company has developed the following conceptual (REA) model in relation to its revenue process.
Instructions:
Based on the generic REA model above and your knowledge of the industry you’ve been assigned,
convert the conceptual model into the logical model for this process. This part of the requirement needs
to be typewritten using word processing software, such as Microsoft Word, and uploaded to our course
site in PDF file format. Make sure your name is on the face of the document. You will not complete this
assignment in Microsoft Access or other database software although this logical model will form the basis
for the AIS that you will build in your AIS Project.
Instructions continued on the next page.
ACCT 361 Accounting Analytics – Spring 2024
Instructions (Cont.):
1) List each database table required to implement the logical model complete with required primary keys,
foreign keys and non-key attributes to track the following data elements on Resource and Agent tables:
a. Inventory Items: Item Description, Price, BQOH, BQOH Date, Standard Cost, [At Least One
More Field Specific to Your Industry]
b. Sales Clerk: Sales Clerk Name, Sales Clerk SSN#
c. Ship Clerk: Ship Clerk Name, Ship Clerk SSN#
d. Customer: Customer Name, Customer Street Address, Customer City, Customer State,
Customer Zip, Customer Phone #, Customer Segment, [At Least One More Field Specific to
Your Industry]
Also, make sure you put Date and Quantity fields properly on Event tables. Also, make sure you add
an SH Fee (Shipping and Handling Fee) field to the proper table.
2) Use proper notation as instructed during the lessons.
3) Organize your model based on maintenance/master tables and operating event/transaction tables.
4) Keep all event tables together. That is, keep all Event #1 tables together, Event #2 tables together,
etc.
Rubric:
This assignment is worth 40 points. Points will be deducted for any of the following issues:
Tables and Fields
• 5 points off for each missing table
• 2 points off for each missing/misplaced primary or foreign key; 1 point off for each key included when
not needed
• 2 points off for missing/misplaced Date, Quantity, and SH Fee fields; 1 point off for cases in which
each of these fields are included when not needed
• 1 point off for other missing/misplaced non-key attributes
Formatting and Presentation
• 2 points are deducted for poor notation or for not organizing the tables into master/maintenance and
event files or for not keeping each event set of tables together.
• 2 points are deduced for not submitting in PDF format
• Other points deducted for miscellaneous errors / issues