ISM 4113

1
ISM 4113

• Modeling the Data
• ERDs
• And
• Data Normalization

2
Data versus Process Modeling

• Process modeling focuses on the flow of data
• Where does it come fromwhere does it go?
• Where do we store itwhat do we do with it?
• Remember...we dont care how, just what
• Data modeling focuses on the data we are moving
• definitions, structures, relationships,
  characteristics
• Each plays an essential role in defining the
  system
• One cannot substitute for the other
• One cannot fully define the system without the
  other

3
Six Blind Men and the Elephant
4
Basic ERD Symbology
5
Identifying Entities and Relationships
6
Typical Business Relationships
7
Relationship Syntax
zero or more or one and only one


must be or may be
entity1 relationship entity2
8
Reading Relationships
9
Relationship Complexities
10
Unary Relationship
11
Binary Relationship
12
Ternary Relationship
13
Associative Entity

• Entity whose instance exists only from the
  Matches of instances of multiple entities
• Inherits its concatenated primary key from the
  Parent Entities
• May contain attributes that only describe the
  Associative Entity

14
Associative Entity
15
Characteristics of a Good Data Model
16

• ERDs and Normal Forms
• (A Review)

17
Three Normal Forms
18
Normal Forms

• 1st
• No repeating groups
• 2nd
• No partial functional dependencies
• No non-key field should be determined by part of
  the primary key
• By definition a functional dependency can only
  occur when a table has a concatenated primary key
• 3rd
• No Transitive dependences
• No non-key field should be determined by another
  non-key field

19
Data Normalization Process
20
Fixing Normalizations Problems

• Decompose table in error into one or more new
  tables

21
An Example in table form
22
Same example

• WorkerAssignment(Wid,Bid,start,name,skill,bonus)
• This is in 1NF (no repeating groups)
• Has a concatenated primary key Wid and Bid
• Can anything be determined with only Wid or Bid?

23
Partial Functional Dependencies

• Wid determines Name,Skill,Bonus.
• So decompose table.

24
2NF Tables

• Assignment(Wid,Bid,Start)
• Worker(Wid,Name,Skill,Bonus)
• Can any non-keys determine any other non-keys?

25
Transitive Dependency

• Skill determines bonus
• So decompose table

26
3NF Tables

• Assignment(Wid,Bid,Start)
• Worker(Wid,Name,Skill)
• Bonus(Skill,Bonus)

27
First Normal Form
28
Second Normal Form
29
Third Normal Form
30
De-Normalization
x

• Once the data is in 3NF we must concern ourselves
  with balancing minimum redundancy with maximum
  performance.
• consider the problem of year-to-date sales

Y-T-D Sales S(Daily Sales to date)
Assume Daily Sales approximately 1,000
transactions per day...
If the report is run on January 2 at day end, the
query needs to add approximately 2,000
transactions to reach a total. If each
transaction takes 0.01 seconds to tally, then the
report will take 2,000 transactions x 0.01
seconds 20 seconds
If, however, the report is requested on December
31 at day end, the query needs to add
approximately 365,000 transactions to reach a
total. Now the time to receive a response will
be 365,000 transactions x 0.01 seconds 3,650
seconds 1.014 hours
31
So

• Certain situations may require the designer to
  de-normalize some data elements.
• How can we solve this year-to-date thing?
• De-normalize the data elements to allow for the
  tabulation and storage of month-to-date totals.
• This action will purposely create a file full of
  fields that are calculated and thus, not in 3NF.
• But...

Total time per monthly calculation 5.07
minutes Total time for y-t-d report on December
31 0.12 seconds
32
Denormalization
33
Fully Normalized ERD