Data Modelling and Data Base Design

1
Data Modelling andData Base Design

• Lecture 8

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?

5
The REA Data Model

• Data modelling is the process of defining a data
  base so that it faithfully represents all aspects
  of the organisation, including its interactions
  with the external environment.

6
The REA Data Model
Data Modelling in the Data Base Design Process
Operation and maintenance
Planning
Implementation
Requirements analysis
Data modelling occurs here
Coding
Design
7
The REA Data Model

• The REA data model is a conceptual modelling 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

8
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 organisation
• Events engaged in by the organisation
• Agents participating in these events

9
Types of Entities

• Resources are defined as those things that have
  economic value to the organisation.
• What are some examples?
• cash
• stock
• equipment

10
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

11
Types of Entities

• Agents are the third type of entity in the REA
  model.
• Agents are the people and organisations that
  participate in events and about whom information
  is desired.
• What are some examples?
• employees
• customers

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

13
Structured Relationships
Get resource A
Resource A
Give up resource B
Resource B
14
Structured Relationships
Internal Agent
Get resource A
External Agent
Internal Agent
Give up resource B
External Agent
15
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 modelled and the important
  relationships among them.
• In an E-R diagram, entities appear as rectangles,
  and relationships between entities are
  represented as diamonds.

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

19
Create Tables

• Stock
• Sales
• Salesperson
• Customer
• Cashier

• Cash collections
• Cash
• Sales-stock
• Sales-cash collections

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

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

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

23
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 organisations business
  practices

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

25
Information AboutBusiness Practices

• The cardinalities in REA diagrams provide useful
  information about the nature of the company being
  modelled and the business policies that it
  follows.

Events
Resources
Sales
Stock
(0, N)
(1, N)
26
Information AboutBusiness Practices

• The relationship between sales and stock 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.

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

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