Acct 316 Acct 316 Chapter 5 Data Modeling

Acct 316 Acct 316 Chapter 5 Data Modeling and Database Design Acct 316 UAA – ACCT 316 Accounting Information Systems Dr. Fred Barbee

Acct 316 Acct 316 Acct 316 Hmmm? What is data modeling?

What is Data Modeling? The Blind Men and the Elephant ACCT 316

What is Data Modeling? So what! You Say! ACCT 316

What is Data Modeling? 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. ACCT 316

What is Data Modeling? The elephant – a third grader’s view. ACCT 316

What is Data Modeling? You cannot understand (and thus represent) something unless you comprehend it completely – enterprise-wide. ACCT 316

What is Data Modeling? Plato’s “Myth of the Cave” ACCT 316

Acct 316 Acct 316 Acct 316 Hmmm? What is data modeling?

The Text Definition. . . Data modeling is the process of defining a database Acct 316 so that it faithfully represents all aspects of the organization, including its interactions with the external environment.

Other Definitions. . . Acct 316 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. . . Acct 316 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

Acct 316 Acct 316 Chapter 5 Data Modeling and Database Design Acct 316 Database Design Process

Planning Data Modeling Occurs Here Requirements Analysis Design Coding Implementation Figure 5 -1 Operation and Maintenance

The Database Design Process Let’s At each of these steps individually. ACCT 316

Planning Requirements Analysis Design Coding Implementation Operation and Maintenance Initial planning to determine the need for and feasibility of developing a new system.

Planning Requirements Analysis Design Coding Implementation Operation and Maintenance Identifying User Needs

Planning Requirements Analysis Design Coding Implementation Operation and Maintenance Developing the contextualexternal- and internal-level schemas

Planning Requirements Analysis Design Coding Implementation Operation and Maintenance Translating the internal-level schema into the actual database structures that will be implemented in the new system.

Planning Requirements Analysis Design Coding Implementation Operation and Maintenance Transferring all data from the existing system to the new database.

Planning Requirements Analysis Design Coding Implementation Operation and Maintenance Using and maintaining the new system.

Acct 316 Acct 316 Acct 316 The Entity-Relationship (E-R) Diagram

The Entity-Relationship (E-R) Diagram Entity ACCT 316 Relationship ACCT 316

Acct 316 Acct 316 Acct 316 The REA Data Model

The REA Data Model Acct 316 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: Acct 316 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 Give-To. Get Duality Resources ACCT 316 Events ACCT 316 Agents ACCT 316

The REA Data Model Resources: Those things that have economic value to the firm. Resources ACCT 316 Events ACCT 316 Agents ACCT 316

The REA Data Model Events: Various Business Activities Resources ACCT 316 Events ACCT 316 Agents ACCT 316

The REA Data Model Agents: People and Organizations that participate in events. Resources ACCT 316 Events ACCT 316 Agents ACCT 316

Acct 316 Acct 316 Acct 316 Developing an REA Diagram

Step 1: Identify the Economic Exchange Events Acct 316 1 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

Step 2: Identify Resources and Agents Acct 316 2 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.

Step 3: Include commitment Events Acct 316 3 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.

Step 4: Determine Cardinalities of Relationships Acct 316 4 Determine the cardinalities of each relationship.

Determine cardinalities of relationships.

How many sales transactions can be linked to each individual customer? Sales Customer How many customers can be linked to each individual sales transaction?

Cardinalities Minimum (1, N) Maximum ACCT 316

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 Acct 316 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 Acct 316 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 • The minimum cardinality of zero in the (0, N) cardinality pair to the left of the customer entity in the customer-sales relationship. . . Sales • Made to (0, N) Customer . . . indicates that a new customer may be added to the database without being linked to any sales events. ACCT 316

Cardinalities • The minimum cardinality of 1 in the (1, 1) cardinality pair to the right of the sales entity in the customer-sales relationship. . . Sales • (1, 1) Made to (0, N) Customer . . . indicates that a new sales transaction CAN ONLY be added if it is linked to a customer. ACCT 316

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 Acct 316 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 Acct 316 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 • The maximum cardinality of N in the (0, N) cardinality pair to the left of the customer entity in the customer-sales relationship. . . Sales • Made to (0, N) Customer . . . indicates that a given customer MAY be linked to many sales events. ACCT 316

Cardinalities • The maximum cardinality of 1 in the (1, 1) cardinality pair to the right of the sales entity in the customer-sales relationship. . . Sales • (1, 1) Made to (0, N) Customer . . . indicates that a given sales transaction can only be linked to one customer. ACCT 316

Determine Cardinalities are not arbitrarily chosen by the database designer. Acct 316 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 Acct 316 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 (0, 1) ACCT 316 (1, 1) ACCT 316 Cash Receipts ACCT 316

Types of Relationships Panel B: One-to-Many (1: N) Relationship Sales (0, N) ACCT 316 (1, 1) ACCT 316 Cash Receipts ACCT 316

Types of Relationships Panel C: One-to-Many (1: N) Relationship Sales (0, 1) ACCT 316 (1, N) ACCT 316 Cash Receipts ACCT 316

Types of Relationships Panel D: Many-to-Many (M: N) Relationship Sales (0, N) ACCT 316 (1, N) ACCT 316 Cash Receipts ACCT 316

Acct 316 Acct 316 Acct 316 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. Acct 316 A well-structured relational database is one that is not subject to update, insert, and delete anomaly problems.

Three Step Process Acct 316 Create a table for each distinct entity and for each many-to many relationship Assign attributes to appropriate tables Use foreign keys to implement one-to -one and one-to-many relationships

Implementing an REA Diagram ACCT 316

Implementing an REA Diagram ACCT 316

Implementing an REA Diagram ACCT 316