data modeling and database design

Acct 316 Acct 316 Acct 316 Acct 316 Acct 316 Acct 316

Acct 316 Acct 316 Acct 316

Data Modeling and Database Design5

UAA – ACCT 316Accounting Information

SystemsDr. Fred Barbee

Chapter



Hmmm? What is

data modeling?

What is Data Modeling?

ACCT 316 ACCT 316 ACCT 316

The Blind Men and the Elephant



So what! You Say!



Without a model of what we are building, we are like these blind men: we may be partly right, but we are probably mostly wrong.



The elephant – a third grader’s view.



You cannot understand (and thus represent) something unless you comprehend it completely – enterprise-wide.



Plato’s “Myth of the Cave”



Hmmm? What is

data modeling?


The Text Definition . . .

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.


Other Definitions . . .

Data modeling is the task of formalizing the data requirements of the business process as a conceptual model.

Hall, James A. Accounting Information Systems

Southwestern Publishing, 2001


Other Definitions . . .

Data modeling is the process of defining what data you want to capture in your database and the relationships between data.

Database Services University of Michigan http://www.umich.edu/~dbsvcs/services/modeling.html



Data Modeling and Database Design5

Database Design Process

Chapter

Planning

Requirements Analysis

Design

Coding

Implementation

Operation andMaintenance

Data Modeling Occurs Here

Figure 5-1

Let’s

At each of these steps individually.

The Database Design Process


Planning


Design

Coding

Implementation


Initial planning to

determine the need for and feasibility of developing a new system.

Planning


Design

Coding

Implementation


Identifying User Needs

Planning


Design

Coding

Implementation


Developing the

contextual-external- and internal-level

schemas

Planning


Design

Coding

Implementation


Translating the internal-level

schema into the actual database structures that

will be implemented in the new system.

Planning


Design

Coding

Implementation


Transferring all data from the

existing system to the new database.

Planning


Design

Coding

Implementation


Using and maintaining

the new system.



The Entity-Relationship (E-R) Diagram

The Entity-Relationship (E-R) Diagram


Entity Relations

hip



The REA Data Model


The 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

The REA data model provides structure in two ways:

By identifying what entities should be included in the AIS databaseBy prescribing how to structure relationships among the entities in the AIS database

The REA Data Model


Resources

Events Agents

Give-To- Get Duality

The REA Data Model


Resources

Events Agents

Resources: Those

things that have

economic value to the

firm.

The REA Data Model


Resources

Events Agents

Events: Various

Business Activities

The REA Data Model


Resources

Events Agents

Agents: People and Organizatio

ns that participate

in events.



Developing an REA Diagram

Acct 316 Acct 316 Acct 316 1Step 1: Identify the Economic Exchange Events

Identify the pair of events that reflect the basic economic exchange (give-to-get duality relationship) in that cycle.

Identify the PAIR of events•One GET•One GIVE

Acct 316 Acct 316 Acct 316 2Step 2: Identify Resources and Agents

Identify the Resources affected by each event and the agents who participate in those events.

Identify . . .•RESOURCES affected by each event.•AGENTS who participate in the events.

Acct 316 Acct 316 Acct 316 3Step 3: Include commitment Events

Analyze each economic exchange event to determine whether it should be decomposed into a combination of one or more commitment events and an economic exchange event.

Include commitment events.

Acct 316 Acct 316 Acct 316 4Determine the cardinalities of each relationship.

Step 4: Determine Cardinalities of Relationships

Determine cardinalities of relationships.

Sales Customer

How many sales transactions can be linked to each individual customer?

How many customers can be linked to each individual sales transaction?

Cardinalities


(1,N)Minimu

m

Maximum

The first number is the minimum cardinality. It

indicates whether a row in this table must be linked to at

least one row in the table on the opposite side of that

relationship.


Minimum Cardinality

The minimum cardinality of a relationship indicates whether each row in that entity MUST be linked to a row in the entity on the other side of the relationship.Minimum cardinalities can be either 0 or 1.


Minimum Cardinalities

A minimum cardinality of zero means that a new row can be added to that table without being linked to any rows in the other table.A minimum cardinality of one means that each row in that table MUST be linked to at least one row in the other table

Cardinalities


Sales Made to Customer(0, N)

– 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 new customer may be added to the database without being linked to any sales events.

Cardinalities


– The minimum cardinality of 1 in the (1,1) cardinality pair to the right of the sales entity in the customer-sales relationship . . .

– . . . indicates that a new sales transaction CAN ONLY be added if it is linked to a customer.

Sales Made to Customer(0, N)(1,1)

The second number is the maximum cardinality. It

indicates whether one row in that table can be linked to more than one row in the

other table.


Maximum Cardinalities

The maximum cardinality of a relationship indicates whether each row in that entity CAN be linked to more than one row in the entity on the other side of the relationship.

Maximum cardinalities can be either 1 or N.


Maximum Cardinalities

A maximum cardinality of 1 means that each row in that table can be linked to at most only 1 row in the other table.A maximum cardinality of N means that each row in that table MAY be linked to more than one row in the other table.

Cardinalities


Sales Made to Customer(0, N)

– The maximum cardinality of N in the (0,N) cardinality pair to the left of the customer entity in the customer-sales relationship . . .

– . . . indicates that a given customer MAY be linked to many sales events.

Cardinalities


– The maximum cardinality of 1 in the (1,1) cardinality pair to the right of the sales entity in the customer-sales relationship . . .

– . . . indicates that a given sales transaction can only be linked to one customer.

Sales Made to Customer(0, N)(1,1)


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


Cardinalities: Types of Relationships

Three basic types - depending on the maximum cardinality associated with each entity.

A one-to-one relationship (1:1)A one-to-many relationship (1:N)A many-to-many relationship (M:N)

Types of Relationships


Panel A: One-to-One (1:1) Relationship

Sales Cash Receipts

(0,1) (1,1)



Panel B: One-to-Many (1:N) Relationship

Sales Cash Receipts

(0,N)

(1,1)



Panel C: One-to-Many (1:N) Relationship

Sales Cash Receipts

(0,1) (1,N)



Panel D: Many-to-Many (M:N) Relationship

Sales Cash Receipts

(0,N)

(1,N)



Build a Set of Tables to Implement an REA Model of an AIS in a Relational Database


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.


Three Step Process

Create a table for each distinct entity and for each many-to many relationshipAssign attributes to appropriate tablesUse foreign keys to implement one-to-one and one-to-many relationships

Implementing an REA Diagram


data modeling and database design

Documents

data requirements

ais data bases

rea diagramstep

new database

conceptual modeling

new system

database designuaa acct

actual database structures