FIS 431/631Financial Information Systems: Analysis and Design

ERD & NormalizationJoe Callaghan

Oakland UniversityDepartment of Accounting &

Finance

Data Modeling

• What information is required by the business

• Logical view vs. physical view

• Carried out in parallel with activity analysis

• We already know much because of REA

Analysis Steps

• Activity Models– FHD– ADD

• REA– Events, RALs

• Transform Events to E-Ts necessary to capture event information, i.e. verb to noun

• Data Model: ERD– Entity Types, Attributes and Relationships

Business Process: Simple MSC

Process and Events

Receive cashSell goods

A REA Interaction Model for MSC Function

Sell Goods

CustomerInventory

SalespersonDepartment

Receive Cash

CashierCash

Store

Sell Goods Transformation

• To SALE

• Header and Detail for multi-product firm

• To Sale Header

• To Sale Detail

• Now, rearrange RAL relationships

• Look for special relationships (i.e. business rules)

Data model: ERD with Normalization

Entity Types

• A collection of business objects• An Entity is a uniquely identifiable object of

the collection, a manifestation of the entity type, downstream is a tuple (or row) in a database table

• Uniquely Identifiable mechanism is the Identifier (downstream it is the Primary Key (PK)), which enforces Entity Integrity

• Have attributes

Attributes

• Information that describes the entity type

• Has a data type

• Is mandatory or optional

• Has a Domain

• Downstream are columns in Relational tables

• Foreign keys are not attributes!

Relationships

• Business rule aspects:– Cardinality: pairing– Optionality: requiring (type of referential

integrity inferred)

• REA Types:– From “Event” to RAL– From “Event” to “Event”– Among the RAL

Different Notations to Represent Relationship Cardinalities

(1,1)

(1,*)

(0,1)

(0,*)

Entity Subtypes

• An entity subtype is a subdivision of the entity type

• Each subtype must have one or more special attribute of its own and/or participate in one or more special relationship of its own

• The special attributes and relationships are not common to all entities of the entity type

Partitioning

• A classifying attribute is the basis for partitioning, e.g. EMPLOYEE - Name, Number, Type where Type can be Full-Time or Part-Time

• Each subtype must be associated with a permitted value of the classifying attribute

• Each subtype inherits properties ( attributes, identifiers, relationships) of its parent entity type

Partitioning can be

• Fully enumerated

• Not fully enumerated

• There can be multiple partitionings

EmployeeManager

Staff

Full Time

Part Time

NAMEADDRESSNUMBERTYPEJOB STATUS

Rules for Partitioning and Subtypes

• Each subtype belongs to exactly one partitioning• Each partitioning must be associated with a

classifying attribute• The subtype to which a given entity occurrence

belongs must be identifiable by a classifying attribute value

• A fully enumerated partitioning must divide the entity type into two or more subtypes

Refining the Data Model

• Developing a data model is iterative, aiming at an accurate portrayal of the business

• A set of techniques can be applied to identify and reduce likely errors

• Each component of the data model is subject to refinement

Identifiers (to be Primary Keys (PKs))

• Each identifier of an entity type consists of one or more attributes and/or relationships that uniquely identify an entity

• A single attribute may be an identifier, e.g. STUDENT - Name, Address, Number

Composite Identifier Using an Attribute and Relationship

OrderItem OrderI

is included on

includes

AttributesNUMBERPRICEQUANTITY

Identifier is a combination of:NUMBER and the fact thatEach ORDER ITEM is includedon one ORDER

Composite Identifier Using Relationships

Product Warehouse

StockItemI o o I

holds

is held for

is stored instores

Identifier is a combination of:Each STOCK ITEM is stored in one WAREHOUSEand each STOCK ITEM holds one PRODUCT

Identifiers - Summary

• Each value of an identifier uniquely identifies one entity of a given type

• Identifiers may be– Simple (one attribute)– Composite (two or more attributes and/or

relationships

Identifiers - Summary (continued)

• Identifiers may consist of an entity type’s own:

– Attributes

– Relationship memberships

– Mixture of both

• An entity type must have at least one identifier

• An entity type may have more than one identifier

Relationship Membership Creation and Deletion Rules

Customer

Order

delete - if relationship within circleis mandatory, when customer is deleted, whatever customer order is related is also deleted

create - if the relationship outsidethe circle is mandatory, a create of customer creates the other entity

Refining 1:1 Relationships

• Merge entity types where both relationships are mandatory

• Leave alone if both relationships are optional

Refining M:N Relationships

• An M:N relationship may contain hidden data

Order Product

Order

Purchasing Agent

Product

o o

specifies specifiedby

approves

approved by

specifies

specifiedby