Title: Data Modeling and Data Base Design
1Data Modeling andData Base Design
2Introduction
- Ashton Fleming, the accountant for SS, is
learning that designing a relational data base
for SS 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 data base.
3Introduction
- 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 data base? - When creating a relational data base, how exactly
do you decide which attributes belong in which
tables?
4Introduction
- How can you document an AIS that is implemented
as a relational data base? - This chapter explains how to design and document
a relational data base for an accounting
information system. - It focuses on one of the aspects of data base
design, data modeling.
5Introduction
- This chapter also introduces the REA accounting
model and Entity-Relationship (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 data base.
6Learning Objectives
- Use the REA data model to design an AIS data
base. - Draw an Entity-Relationship (E-R) diagram of an
AIS data base. - Build a set of tables to implement an REA model
of an AIS in a relational data base. - Read an E-R diagram and explain what it reveals
about the business activities and policies of the
organization being modeled.
7Learning Objective 1
- Use the REA data model to design an AIS data
base.
8The REA Data Model
- Data modeling is the process of defining a data
base so that it faithfully represents all aspects
of the organization, including its interactions
with the external environment.
9The REA Data Model
Data Modeling in the Data Base Design Process
Operation and maintenance
Planning
Implementation
Requirements analysis
Data modeling occurs here
Coding
Design
10The REA Data Model
- The REA data model is a conceptual modeling tool
specifically designed to provide structure for
designing AIS data bases. - The REA data model provides structure in two
ways - By identifying what entities should be included
in the AIS data base - By prescribing how to structure relationships
among the entities in the AIS data base
11Types 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
12Types of Entities
- Resources are defined as those things that have
economic value to the organization. - What are some examples?
- cash
- inventory
- equipment
13Types 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
14Types 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
15Structured 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.
16Structured Relationships
Get resource A
Resource A
Give up resource B
Resource B
17Structured Relationships
Internal Agent
Get resource A
External Agent
Internal Agent
Give up resource B
External Agent
18Learning Objective 2
- Draw an entity-relationship (E-R) diagram of
an AIS data base.
19Entity-Relationship Diagram
- An Entity-Relationship (E-R) diagram is one
method for portraying a data base 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.
20Entity-Relationship Diagram
Resources
Events
Sales
Inventory
(0, N)
(1, N)
(0, N)
Cash collections
Cash
21Entity-Relationship Diagram
Events
Agents
(1, 1)
Sales
(1, N)
Salesperson
(1, 1)
(0, N)
(0, N)
Customer
Cash collections
Cashier
22Developing 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.
23Identify 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.
24Identify 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.
25Identify 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.
26Identify 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).
27Specify 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.
28Specify 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.
29Specify 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
30Specify 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.
31Specify 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
Salesperson
(1, 1)
32Specify 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.
33Specify 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
34Specify Cardinalities
- Cardinalities are not arbitrarily chosen by the
data base designer. - They reflect facts about the organization being
modeled and its business practices obtained
during the requirements analysis stage of the
data base design process.
35Learning Objective 3
- Build a set of tables to implement an REA model
of an AIS in a relational data base.
36Implementing an REA Diagram in a Relational Data
Base
- An REA diagram can be used to design a
well-structured relational data base. - A well-structured relational data base is one
that is not subject to update, insert, and delete
anomaly problems.
37Create Tables
- A properly normalized relational data base 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.
38Create Tables
- Inventory
- Sales
- Salesperson
- Customer
- Cashier
- Cash collections
- Cash
- Sales-inventory
- Sales-cash collections
39Identify Attributesfor 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.
40Implement One-to-One and One-to-Many
Relationships
- One-to-One Relationships
- In a relational data base, 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.
41Implement One-to-One and One-to-Many
Relationships
- One-to-Many Relationships
- In a relational data base, one-to-many
relationships can be also implemented in relation
to data bases by means of foreign keys.
42Learning Objective 4
- Read an E-R diagram and explain what it reveals
about the business activities and policies of the
organization being modeled.
43Documentation
- REA diagrams are especially useful for
documenting an advanced AIS built using data
bases. - REA diagrams provide two important types of
information about a data base - Information about the relationships among data
items - Information about the organizations business
practices
44Information AboutData Base Content
- REA diagrams explicitly depict the relationships
among the various data items that are stored in
the accounting data base. - Flowcharts only show which files exist and
describe their basic characteristics. - Data flow diagrams (DFD) describe the contents of
each file in the system, but do not explicitly
show how those files relate to one another.
45Information AboutBusiness 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)
46Information AboutBusiness 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.
47Organization Specificityof REA Diagrams
- Due to the fact that SS sells mass-produced
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-to-many.
48Extracting InformationFrom the AIS
- A complete REA diagram serves as a useful guide
for querying an AIS data base. - Queries can be used to generate journals and
ledgers from a relational data base built on the
REA model.
(0, 1)
(1, N)
Cash collections
Sales
49Extracting 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.
50End of Chapter 6