IMS 5024

1
IMS 5024

• Data Modelling

2
Content

• Individual assignment
• Pitfall revisited
• Group assignment
• Class assignment
• Nature of data modelling
• Tools/Techniques used in data modelling
• Place in ISD
• Evaluation of data modelling
• Reading list

3
Individual assignment

• Date due 29 August 2002
• Difference between social and technical
• Show understanding of the subject matter
• Questions e-mail Bahar directly

4
Teaching Assistant

• Bahar Jamshidi
• E-mail
• bahar.jamshidi_at_infotech.monash.edu.au
• Queries about marks
• Other queries

5
Pitfalls

• Not starting early
• Reading, more reading and then some reading.
• Plagiarism !!!!!

6
Data modelling describe
7
Data modelling describe

• Structure
• Meaning
• Relationship
• Of Data

8
Data modelling help us to grasp

• Static Data in the organisation
• Fundamental building block of the system
• Two perspectives (Process and Data)

9
Techniques used in Data modelling

• Entity relationship diagrams
• Normalisation
• Data Dictionary
• What difference?
• Use both?

10
Entity

• Entity things of interest to the business
• Identification of an entity is subjective
• Entities can be
• Real eg product
• Abstract eg Quota
• Event remembered eg sale
• Role played eg employee

Employee
11
Relationship

• Relationship Between entities
• Cardinality (eg. One to many, one to one ect.)
• Degree of relationship (Unary, Binary, Ternary)

Department
Employee
12
Examples of Cardinalities
PATIENT
EMPLOYEE
Is assigned to
Has
Is married to
PERSON
PATIENT HISTORY
PROJECT
Mandatory cardinalities
Optional and mandatory cardinalities
Optional cardinalities
13
Relationship Cardinality Summary
Mandatory 1 cardinality
Many cardinality (1,2 m)
Optional (0 or 1) cardinality
Optional (0 or many) cardinality
14
Unary Relationship

• Also called a recursive relationship

Manages
Is married to
PERSON
EMPLOYEE
One to many
One to one
15
Binary Relationship

• A binary relationship is a relationship between
  instances of two entity types.

EMPLOYEE
CUSTOMER
SUPPLIER
Leads
Supplies
Places
PROJECT
SALES ORDER
ITEM
One to one
One to many
Many to many
16
Ternary Relationship

• A ternary relationship is a relationship between
  instances of three entity types.

PART
supplies
VENDOR
CUSTOMER
17
Attributes

• What we want to know about the entity or a
  relationship
• Types
• Derived,
• multi-valued,
• Composite,
• Simple

18
Example of attributes
Name
Address
Emp-no
Skill
EMPLOYEE
19
Why normalisation?

• Remove redundancy and incompleteness
• Bottom up process
• Rely on Maths well researched

20
Determining columns

• One fact per column
• Hidden data
• Derivable data
• Determining the key

21
Steps in Basic Normalisation

• Basic Normalisation is most often accomplished in
  three stages (these are the three basic normal
  forms)

Unnormalised table
Remove repeating groups
First Normal Form
Remove partial dependencies
Second Normal Form
Remove transitive dependencies
Third Normal Form
22
First normal form

• Step 1 Remove the repeating group
• Why is repeating groups a problem?
• Determine the key for the new group.
• Order-Item (Order, Customer, (Item, Desc,
  Qty))
• Order-Item (Order, Item, Desc, Qty)
• Order (Order, Customer)

23
Second and Third normal forms

• Eliminate redundancy
• Determinates one or more columns which
  determines other column values

24
Second and third normal form procedure

• Identify any determinates (other than the key)
• Establish a separate table for each determinate
  and the columns it determines
• Name the new tables
• Remove the determined columns from the original
  table

25
Third normal form

• A table is in third normal form if the only
  determinates of nonkey columns are candidate keys

26
Advanced normalisation

• A set of tables can be in 3NF and still not be
  fully normalised
• Further stages of normalisation are BCNF, 4NF, 5
  NF and Domain key NF

27
Higher Normal forms

• Occur infrequently
• Most tables in 3 NF is already in BCNF, 4NF and 5
  NF
• Data in 3NF but not in 5NF has
• Redundancy
• Insert/update/delete anomalies
• Difficulty in storing facts independently

28
BC NF

• Example
• Branch-customer relationship (customer no, branch
  no, visitng frequency, date relationship
  established, salesperson no)

BRANCH
CUSTOMER
BRANCH CUSTOMER RELATIONSHIP
SALES- PERSON
29
Problem

• Salesperson branch no
• Overlapping and candidate keys
• Customer no and branch no
• Customer no and salesperson
• Branch-customer relationship (customer no,
  salesperson no, branch no, visiting frequency,
  date relationship established)
• Customer salesperson relationship (customer no,
  salesperson no, visiting frequency, date
  relationship established)
• Salesperson (Salesperson No, branch no)

30
B-C NF

• Every determinant must be a candidate key (must
  have overlapping keys)

BRANCH
SALES- PERSON
CUSTOMER
BRANCH CUSTOMER RELATIONSHIP
31
Fourth and Fifth NF

• Apply to all-key tables, degree gt 2

INSTRUMENT
DEALER
LOCATION
32
Problems are the result of multi-valued
dependencies
33
Fifth NF

• Keep splitting tables until
• Any further splitting would lead to tables unable
  to be joined to produce the original table
• The only splits left are trivial

34
Dealing authority problem
35
Many to many relationships resolved
36
Combined table
37
Combined table which cannot be split
38
Other normalisation issues

• Normalisation and redundancy
• Overlapping classifications
• Derivable data
• Selecting primary keys

39
Thinking in Data modelling

• Hard Vs Soft ??
• Perspective
• Objective vs Subjective
• Nature of the organisation

40
Evaluation of Data modelling
Problem oriented Product oriented
Concep-tual Structured analysis Entity relationship modelling Logical construction of systems Modern structured analysis Object oriented analysis Structured design Object oriented design
Formal PSL/PSA JSD VDM Levels of abstraction Stepwise refinement Proof of correctness Data abstraction JSP Object oriented programming
41
Advantages of Data modelling

• Data model is not computer oriented (agree??)
• Model understandable by technologist and users
• Does not show bias
• UoD can vary (whole organisation or department)
• Readily transformable into other models
• Different data analysis techniques
• Data modelling is rule-based

42
Disadvantages

• Does not encourage/support user participation
• Your view on the organisation people or data
• The idea that the model is THE model
• Subjective view
• One-side ito data
• Others??

43
Advantages of Normalisation

• Rid the data of redundancy and other problems
• Very well researched
• Math basis for normalisation

44
Disadvantages of normalisation

• Does not encourage/support user participation
• Your view on the organisation people or data
• One-side ito data
• Can be done mechanistically without thought
• Others??

45
Process modelling view of ISD
Objectives
Development group
Object system
Object system
Change process
Environment
Hirschheim et al see reading list
46
Reading for next week

• Johnstone, M.N., McDermid, D.C. (1999). Extending
  and validating the business rules diagram method.
  Proceedings of the 10 th Australian Conference on
  Information Systems.
• Chapter 2 of Curran, Keller, Ladd (1998). SAP R/3
  the business blueprint Understanding the
  business process reference model. Prentice Hall.