Contents
Contents 1
Entity Relationship Diagrams – An Introduction 2
Overview 2
Reading the ERD 3
Key Terms in an Entity Relationship Diagram 4
Entity 4
Entity Occurrence 4
Attributes 4
Primary Key 4
Relationships between Entities 5
One-to-one 5
One-to-many 5
Many-to-many 5
Entity Relationship Diagrams (Cardinality and Optionality) 6
Cardinality 6
Optionality 7
Entity Relationship Diagrams – Many to Many Relationships 8
Entity Relationship Diagrams – Selecting Attributes 9
Entity Relationship Diagrams – An Introduction
Entity relationship diagrams (ERDs) describe data and the relationships between data items – its semantics or meaning. The construction of these diagrams is known as data modelling.
Overview
A company has a number of projects on-going at any time. Each project will be run by a single engineer and over time that engineer will have a number of projects.
What entities do we need to keep information on?
· Engineer
· Project
What attributes (fields) will be needed for each entity?
· Engineer - each engineer has a unique identifier (e.g. 100), and a name
· Project – a project identifier (e.g. Eng01) and a project title
We need to look at the information that we have and come up with suitable names for these attributes and types:
When an attribute is underlined – it is a primary key. A primary key is a unique identifier for an instance of an entity.
Reading the ERD
The description that we give to the relationships between each entity (and we need to read that relationship both ways:
Engineer -> Project:
· An engineer MAY work on one or more projects (dotted line indicates an optional relationship)
· An engineer has a one-to-many (many is indicated by the crows foot) relationship with a project
Project -> Engineer:
· A project will have an engineer (solid line indicates a mandatory relationship)
Key Terms in an Entity Relationship Diagram
· Entity
· Entity Occurrence
· Attributes
· Primary Key
Entity
Media
1 / Source Code / 12 / £13.99 / £3.00
2 / Despicable Me / U / £6.99 / £1.50
3 / Mars Attacks / 12 / £10.99 / £2.00
Entity Occurrence
MEDIA - Represents the name of the entity which is transformed into a table.
This is an example of a record also known as an entity occurrence or instance - these are represented in the table as rows.
Media_Number / Media_Title / Media_Rating / Media_Cost_Price / Media_Rental_Price2 / Despicable Me / U / £6.99 / £1.50
Attributes
Media_Title (below) is an example of an attribute/field name which is part of an entity.
Attributes are represented in the table as columns.
Source Code
Despicable Me
Mars Attacks
Each of these (Source Code, Despicable Me, Mars Attacks) is an attribute occurrence.
Primary Key
Media_No - the attribute/field name is underlined indicating that it is a primary key. This provides each row with a unique identifier.
Relationships between Entities
· Relationships are verbs (whereas entities are nouns)
· Relationships are described by their degree (cardinality)
o One-to-one 1:1
o One-to-many 1:m
o Many-to-many m:n
One-to-one
The DVLA should have one tax disc allocated to a car and each car should have a tax disc
One-to-many
A mother may have many children but a child can only have one mother
Many-to-many
Many items supplied by a supplier and a supplier may/will offer many items
Questions
Describe the following degrees of relationship (explain the relationship in both directions and what:
MP / Represents / Constituency
Department / Works in / Secretary
Councillor / Attends / Committee
Warehouse / Contains / Product
Entity Relationship Diagrams(Cardinality and Optionality)
Note: Each of these is considered from the direction of A to B.
Cardinality
One to One (1:1)
One to Many (1:m)
Many to One (m:1)
Many to Many (m:n)
Optionality
Mandatory to Mandatory
Mandatory to Optional
Optional to Mandatory
Optional to Optional
Entity Relationship Diagrams – Many to Many Relationships
Previously
A company has a number of projects on-going at any time. Each project will be run by a single engineer and over time that engineer will have a number of projects.
· An engineer MAY work on one or more projects (dotted line indicates an optional relationship)
· A project will have an engineer
In reality we would more likely have the following situation:
· An engineer MAY work on one or more projects (dotted line indicates an optional relationship)
· A project will be run by one or more engineers
Many-to-many relationships are a problem and need to be changed into two one-to-many relationships
In practice the relationship between the two entities becomes in itself an entity – the name (noun selected) of this may be something obvious or it may be more abstract (below the relationship entity is called assignment).
Entity Relationship Diagrams – Selecting Attributes
In the diagram above the following attributes are recorded for Engineer:
· Engineer_ID
· Engineer_FirstName
· Engineer_Surname
For Project:
· Project_ID
· Project_Title
For Assignment:
· Assignment_No
As stated last week each entity (table) must have a primary key which uniquely identifies each instance (row) when data is entered.
Normally a relationship between two entities is signified not only by a line between the two entities showing cardinality and optionality but also by the inclusion of a foreign key. A foreign key is a field that holds the same value held in the table with the primary key enabling the two tables to be linked and the relationship maintained.
Engineer_ID / Engineer_FirstName / Engineer_Surname1 / Fred / Bloggs
2 / John / Smith
Project_ID / Project_Title
C12 / Cardboard boxes
P01 / Plastic Ducks
Assignment_No / Engineer_ID / Project_ID
1 / 1 / C12
2 / 1 / P01
3 / 2 / P01
Even though the QSEE diagram does not show foreign keys it does maintain them and when the design is converted to SQL it includes these fields – you just cannot see them!
1 / © Phil Jameswww.philjameswebsite.com