Entity relationship diagrams

1
Data modeling using ER

• Entity relationship diagrams

2
Why analyze data?

• Insight in data - and structures of data.
• Create a structure in unstructured data
• Prior to designing a database
• A part of the systems development process.

3
ER modeling

• ER modeling is a widely used technique for data
  analysis.
• Many notations (syntax) for ER diagrams
• Different book ? different notations
• Diagrams can be drawn using Visio or a similar
  tool.

4
Entities with attributes

• Characteristics of an entity
• An important thing in the world we model.
• Entities have attributes (data)
• Similar to a class in OOA, but has no methods /
  functions.
• Notation (this book)
• Entity Rectangle
• Attribute Oval

5
Types of attributes

• Simple or composite attributes
• simple (atomic) e.g. age
• Notation Oval
• composite
• address street number ZIP code town
• Notation Oval connected to several sub ovals.

6
Types of attributes II

• Single valued or multi-valued
• Single valued e.g. CPR
• Multi-valued e.g. phone number
• One person may have several phones
• Notation Double oval
• An attribute may be composite and multi-valued!

7
Types of attributes III

• Derived Calculated from other attributes e.g.
  sum or average
• Notation Oval with a dashed border
• An attribute with no value has value NULL
• Values does not exist.
• Value unknown.

8
Entities

• An entity type is like a class
• Naming convention Substantive, singular
• Every instance of the entity must be unique
• Identified by a key, one or more attributes
• Notation for keys Underline the name of the
  attribute(s)

9
Relationships

• Relationships between entities.
• Notation Rhomb
• Similar to association in OOA.
• Relationships may have attributes
• Notation Oval

10
Relationships II

• Binary relationship
• Relationship between 2 entities (normal)
• Relationships, higher degree
• Relationship between more entities (rare)
• Recursive relationships
• Relationship of 2 entities belonging to the same
  entity type e.g. an employee with a relation to
  another to a boss (another employee)

11
Cardinality

• 1-1 very rare
• 1 entity of type A has a relation to at most 1
  entity of type B and vice versa
• 1 husband 1 wife
• 1-many, 1-N very common
• 1 entity of type A may have relation to many
  entities of type B, not vice versa.
• 1 department many employees

12
Cardinality II

• Many - many, N-M common
• 1 relation of type A may have relations to many
  entities of type B and vice versa.
• 1 employee many projects
• 1 project many employees

13
Cardinality notation

• Step into the entity type and look at the other
  entity through the relation.
• How many entities of the other type do you see?
• Write the number on far end of the relation.

14
(min, max) cardinality

• Often youre only concerned with max.
  cardinalities.
• Sometimes you want both (min, max) cardinality.
• Note Cardinalities placed in the other end of
  the relation!!!

15
History

• History More generations of relations
• Cardinality goes up
• 1 man many wives not simultaneously
• Put a timestamp / period on the relation
  (attribute on the relation)
• Relation marriage start_date, end_date
• end_date may be NULL -)

16
Total participation

• Ordinary relation
• An entity may be related to another entity.
• Total participation
• En entity must be related to another entity.
• Min. cardinality 1 (not 0)
• Notation Double line on the relation

17
Weak entities

• An entity that is dependent on some other entity.
• If the strong entity is deleted so is the weak
  entity.
• Notation
• Weak entity Rectangle with double borders
• Identifying relationship Rhomb with double
  borders (shows the strong entity)

18
Weak entities II

• A weak entity has a partial key
• Not unique
• Unique in combination with the key from the
  strong entity.
• Notation Dashed line under the partial key.

19
Diagramming tools

• Drawing
• Microsoft Visio now a part of MS Office
• Diagramming to generate tables
• You draw ER, the tool generates tables
• Reverse engineering
• You have the tables, the tools generates ER.
• Dont use tools early in the process - use pen
  and paper!!!

20
Best practice

• Procedure (best practice)
• find entities and easy attributes
• relationships
• cardinalities
• keys
• weak entities
• total participation