Data Modelling

1
Data Modelling
Entities, Attributes and Relationships
2
Data Modelling

• Technique for describing information structures
• Information models represent
• things - entities
• properties of things - attributes
• associations between things - relationships

3
Entities

• Abstractions of real world things
• e.g. CUSTOMER
• does not relate to specific customers
• ...any distinguishable person, place, thing,
  event or concept about which information is kept.
  (Bruce 1992)

Specific customer
4
Attributes

• The elements of data belonging to an entity are
  known as its attributes

A/C No. Name Address Tel No. Credit limit
Customer
5
Relationships

• Imagine two entities
• Lecturer and Student
• Lecturers teach students
• Teaching is the relationship between the two
  abstract entities

6
Logical Data Model
An Entity Type has a set of attributes e.g.
Customer has attributes of Account Number Name
Address Telephone Number Credit Limit
7
Logical Data Model
An Entity Type may have a number of
occurrences. Each Entity Occurrence has a unique
set of values for the attributes.
A/C No. Name Address Tel No. Credit limit
Customer Entity Type
Customer Entity Occurrences
8
Logical Data Model
Customer Entity Attribute Value Account
Number BL032 Name Bloggs Son Address 117
Acacia Rd Birmingham 7 Telephone Number 0121
345678 Credit Limit 2500
9
A Table or Relation
Each row of the table is unique.
10
Entities, Tables Relations

• An Entity Type is represented as a Table
  (Relation)
• Each Row (Tuple) of the Table is an Occurrence of
  the Entity
• Each Column (Domain) of the Table contains the
  Values of one Attribute of the Entity

11
Physical Data Organisation

• An Entity Type is usually implemented as a File
  in the Physical Storage Medium
• Each Entity Occurrence is a Record in the File
• The Value of an Attribute of the Entity
  Occurrence is stored in a Field within the Record

12
Physical Organisation
physical medium
file
field
record
13
Repeating Attributes / Fields
Order File / Entity
Are repeating attributes (fields) really
attributes of this entity?
14
Attributes / Fields of an Order
15
Entities or Attributes?
When is data an Attribute of an Entity and when
is it a separate Entity? Can one Entity ever be
considered to be simply Attributes of another?
16
Diagrammatic Representation
17
Diagrammatic Representation
Relationship between entities
master
one order can be for many items
crows foot
detail
18
Degrees of Relationship
19
Optional Relationships
An Order must be for a Customer
but a Customer may not have any orders
optional at the customer end
20
Consultant System

• A Client has an Account
• Consultants have a Grade and a number of Skills
• Consultants are active on various Projects
• Each Project is for one Account

21
Possible Entities for Consultant System
22
Initial Attempt at Relationships
23
Resolving One to One Relationships
A Client can only have one Account.
Client
Account
The account is an attribute of the client and NOT
a separate entity. Simply merge the entities
which have a one to one relationship.
24
One to one relationships resolved
25
Resolving Many to Many Relationships
Difficult to implement so how can they be
replaced?
What is the nature of the relationship between an
Actor and a Scene?
26
Resolving Many to Many Relationships
Create a linking entity which is a detail to both
the original entities.
Actor and Scene are both masters to the new
linking entity of Appearance.
27
Resolving Many to Many Relationships
What would make suitable entities and what
attributes might they have?
28
Many to many relationships resolved
Avoid crossed relationships Resuscitate dead
crows!
29
Developing a Logical Data Structure

• identify possible entities
• draw initial entity relationship diagram
• resolve 11 and manymany relationships check
  for further entities and relationships
• remove redundant relationships
• show optionality

30
Cross-checking the LDS
The LDS is derived using a Top Down
approach. It can be cross-checked by using a
Bottom Up approach, building up the entities
from their attributes. This technique is called
Normalisation which is the subject of the next
lecture.
31
Relational Data Analysis

• Normalisation

With thanks to Codd Date.
32
Attributes / Fields of an Order
33
Normalisationobjectives

• to reduce data redundancy
• to hold each data item (attribute) with as few
  occurrences as possible
• to identify and remove any dependencies between
  data items stored together (in the same table)

34
A RelationA two-dimensional table
35
A Compound Key
36
Attributes Listed
37
Un-Normalised Data
38
Assumptions

• Consultant No., Skill Code and Grade are unique.
• A consultant can have many skills each identified
  by a Skill Code.
• For each skill only the consultants highest
  Qualification is recorded.
• Other consultants may have the same skills (and
  Skill Code) but not necessarily the same
  Qualification.
• Each Skill Code has one Description.
• Each Grade belongs to one Salary Scale.

39
Un-Normalised Form (UNF)
40
UNF

• list all data attributes
• allocate primary key
• identify repeating group(s)
• (optional)

41
First Normal Form (1 NF)Rule
remove repeating data
Consultant No. Name Address Grade Salary
Scale Skill Code Description Qualification
42
First Normal Form (1 NF)
43
1NF

• separate repeating group
• copy non-repeating group unchanged
• add initial primary key to repeating group
  identify compound key

44
Second Normal Form (2 NF)Rule
remove part-key dependencies
Consultant No. Skill Code Description Qualificatio
n
45
Second Normal Form (2 NF)
46
2NF

• separate part-key dependencies
• all other groups are copied across unchanged
• do not omit key only groups

47
Third Normal Form (3 NF)Rule
remove inter-data (and inter-key) dependencies
Consultant No. Name Address Grade Salary Scale
48
Inter-Data Dependency
49
Third Normal Form (3 NF)
50
3NF

• separate inter-data (non-key) dependencies
• identify foreign keys

51
Normalisation of Consultant Data
52
Normalisation Stages

• UNF - list attributes allocate primary key
• 1NF - remove repeating groups
• 2NF - remove part key dependencies
• 3NF - remove inter-data dependencies

53
Third Normal Form Tests

• Given a value for the key of a 3NF relation, is
  there only one possible occurrence of the
  associated data (row)?
• Is each attribute (column) of the relation
  dependent on the key, the whole key and nothing
  but the key?

54
Relation (Table) Names
55
Summary of the Normalisation
56
3NF Entity-Relationship Diagram

• 3NF Relations are Entities
• Entities / Relations are linked by their common
  attributes
• Relational Model

57
Relationships from 3NF

• Entities are linked by their keys
• Keys and Foreign Keys are the common attributes.
• An Entity / Relation with a Compound Key is a
  Detail
• Its Masters are Relations that have as their Key
  a part of the Compound Key
• An Entity / Relation with a Foreign Key is a
  Detail
• Its Master is the Relation which has the Foreign
  Key as its whole Key

58
3NF Relations are Entities
59
Compound Keys are Details
60
Foreign Keys are Details
61
Cross-checking the LDS
The LDS is derived using a Top Down
approach. Normalisation is used to cross-check
the LDS by using a Bottom Up approach, building
up the entities and relationships from their
attributes.
62
Compare to LDS
63
LDS versus 3NF Entity Relationship Diagram

• Why are the diagrams not the same?
• What do they have in common?
• What are the differences? Why?
• What do we need to resolve the differences?
• Normalise other documents / data sources.

64
What is the key?
Activity No
Time (days)
Activity Type
Consultant No
Project No
write letter draw DFD interview plan
project visit site
003 001 004 004 002
0.1 1.5 0.3 2.0 1.0
1 2 1 2 1
C232 C232 C979 C979 A176
Activity No. is only unique within one project.
65
A Composite Key
66
Data Modelling

• Derive an LDS - top down
• Normalise all relevant data (documents etc.)
• apply 3NF tests
• Merge relations with the same key
• check for synonyms and homonyms
• apply 3NF tests again
• Give each Relation (Entity) a name
• names should be consistent with LDS
• Draw a 3NF Entity Relationship Diagram
• Compare LDS 3NF ERD and resolve differences

67
Another Normalisation
68
Normalisation of Purchase Order
69
Purchase Order Document 3NF

• apply 3NF tests
• name entities (relations)

70
Sales Order

Date Required
Part No
Item
1
19/2/99
41235
2
19/2/99
23467
3
19/2/99
89965
assembly
4
23467
3.20
32.00
26/3/99
Order Total 58.00
Assumptions Customer No, Order No and Part No
are unique but Item is only unique within one
order. Price each depends on Part No.. The same
Part No can be used for more then one Item on the
same order (but the Date Required will vary).
71
Normalisation of Sales Order
3NF
Sales Order No. Date Customer No. Order
Total Customer No. Name Sales Order
No. Item Part No. Quantity Value Date
Required Part No. Description Price each

)
(

72
Sales Order Document 3NF
73
Merged 3NFs
74
3NF Relations are Entities
75
Compound Keys are Details
master Part No.
master Purchase Order No.
76
Foreign Keys are Details
Sales Order No. is foreign key
77
LDS from Tutorial Example
78
LDS versus 3NF Entity Relationship Diagram

• What are the differences?
• What do we need to resolve these?
• Normalise a despatch document.