Data Modeling and Data Base Design - PowerPoint PPT Presentation

1 / 50
About This Presentation
Title:

Data Modeling and Data Base Design

Description:

The cash collections event involves the acquisition of cash. 6 - 26 ... Cardinalities are not arbitrarily chosen by the data base designer. ... – PowerPoint PPT presentation

Number of Views:110
Avg rating:3.0/5.0
Slides: 51
Provided by: olga113
Category:
Tags: base | data | design | modeling

less

Transcript and Presenter's Notes

Title: Data Modeling and Data Base Design


1
Data Modeling andData Base Design
  • Chapter 6

2
Introduction
  • 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.

3
Introduction
  • 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?

4
Introduction
  • 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.

5
Introduction
  • 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.

6
Learning 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.

7
Learning Objective 1
  • Use the REA data model to design an AIS data
    base.

8
The 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.

9
The REA Data Model
Data Modeling in the Data Base Design Process
Operation and maintenance
Planning
Implementation
Requirements analysis
Data modeling occurs here
Coding
Design
10
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 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

11
Types 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

12
Types of Entities
  • Resources are defined as those things that have
    economic value to the organization.
  • What are some examples?
  • cash
  • inventory
  • equipment

13
Types 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

14
Types 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

15
Structured 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.

16
Structured Relationships
Get resource A
Resource A
Give up resource B
Resource B
17
Structured Relationships
Internal Agent
Get resource A
External Agent
Internal Agent
Give up resource B
External Agent
18
Learning Objective 2
  • Draw an entity-relationship (E-R) diagram of
    an AIS data base.

19
Entity-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.

20
Entity-Relationship Diagram
Resources
Events
Sales
Inventory
(0, N)
(1, N)
(0, N)
Cash collections
Cash
21
Entity-Relationship Diagram
Events
Agents
(1, 1)
Sales
(1, N)
Salesperson
(1, 1)
(0, N)
(0, N)
Customer
Cash collections
Cashier
22
Developing 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.

23
Identify 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.

24
Identify 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.

25
Identify 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.

26
Identify 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).

27
Specify 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.

28
Specify 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.

29
Specify 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
30
Specify 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.

31
Specify 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)
32
Specify 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.

33
Specify 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

34
Specify 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.

35
Learning Objective 3
  • Build a set of tables to implement an REA model
    of an AIS in a relational data base.

36
Implementing 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.

37
Create 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.

38
Create Tables
  • Inventory
  • Sales
  • Salesperson
  • Customer
  • Cashier
  • Cash collections
  • Cash
  • Sales-inventory
  • Sales-cash collections

39
Identify 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.

40
Implement 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.

41
Implement 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.

42
Learning Objective 4
  • Read an E-R diagram and explain what it reveals
    about the business activities and policies of the
    organization being modeled.

43
Documentation
  • 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

44
Information 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.

45
Information 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)
46
Information 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.

47
Organization 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.

48
Extracting 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
49
Extracting 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.

50
End of Chapter 6
Write a Comment
User Comments (0)
About PowerShow.com