Entity Relationship Modelling
Optional
Relationships & representation
Employee Department
Is in
Mandatory Many One
Entities are shown on the diagrams as rectangles. They
must always have a name.
Relationships are shown as diamonds and must also have
a name.
The degree of association (or cardinality) must be shown
(i.e. using m &/or 1)
Optionality must be shown (optional – spot outside,
mandatory – spot inside)
Entity Occurrence Diagrams
These can be constructed for any instance of a valid
relationship
e.g.
Employee Works Project
on
01 P01
P02
02 P03
P04
03 P05
P06
04
Note: Occurrence diagrams depend on the nature of the
relationship, and should therefore illustrate examples of
all of the possibilities allowed by the relationship.
Emp Proj
01 P01
02 P02
03 P03
04 P04
P05
P06
Emp Proj
01 P01
02 P02
03 P03
04 P04
P05
P06
What is wrong with the above example?
Valid Relationships
Many to |One
Mandatory to A B
optional
Optional to A B
optional
Mandatory to A B
mandatory
Optional to A B
mandatory
Many to many relationships
These should be decomposed:
A B
Becomes
A B
*
*
A/B
One to One relationships
1.
A B
Mandatory to optional
2.
A B
Optional to optional
3.
B
A
Mandatory to mandatory (usually becomes one entity)
Enterprise Rules
Employee Department
Works
in
An employee works in exactly ONE department
A department has 0, one, or many employees
Additional Relationships/features
1. Recursive (pigs ear) relationship
Employee
An employee has a supervisor, but an employee can also
be a supervisor.
2. Exclusive Relationships
An either/or relationship
Branch
Empoyee
Department
An employee may be assigned to a Branch OR a
Department
Table design
Emp Proj
works
A 1:m relationship with the spot on the inside (i.e.
obligatory) at the many end gives two tables, e.g.
Employee
EmpNo LName FName Address Tel
001 Bloggs Bill 1 Tree Rd 2673256
002 Smith Pat 6 Big Hill 3673236
003 Brown Jill 3 The St. 2236723
Etc.
Proj
ProjNo Title Start_date EmpNo
P1 Big Project 12/6/00/ 003
P2 Posh Project 3/3/99 001
Etc.
Notice that the employee number (EmpNo) has been
posted into the project table. This is how the tables are
related together. Always post from the 1 end to the M
end in such a relation ship. EmpNo in the Project table is
known as a foreign key.
Emp Proj
works
M:M relationships are always broken down to give an
extra entity. Sometimes this might become a meaningful
entity with a new name. The cardinality is always 1:m
m:1, with spots on inside (i.e. obligatory) at the 3rd
entity.
Proj
Emp
Emp/Proj
This gives three tables:
Employee (EmpNo, Lname, Fname, Address, etc…)
Project (ProjNo, Title,….)
Emp_Proj (EmpNo, ProjNo, Startdate, Finishdate..)
Notice this time, that the key fields from Employee &
Project have been posted into Emp_Proj. These
combined make a composite key field for this table.
Emp Proj
works
A 1:M relationship that is non-obligatory at the M end
(i.e. spot outside) gives three tables (this eliminates null
values). A bit like with a M:M except we don’t get an
additional entity, just the extra table & it only has the two
posted foreign keys as fields in it. E.g.
Employee (EmpNo, Lname, Fname, Address …)
Proj (ProjNo, Title, Startdate,…)
Emp_Proj (EmpNo, ProjNo)
Notice, only the EmpNo needs to be underlined as the
key field here. We don’t need a composite key.
1:1 relationships are quite rare & are handled in different
ways depending on the participation of each entity in the
relationship (i.e. where the spots are)
1. Spots inside (obligatory at both ends)
Emp Proj
works
This is very rare & often becomes 1 entity. Only one
table would be defined for this e.g.
Emp_Proj (EmpNo, Lname, Fname, Address, ProjNo, Title….)
2. Both ends non-obligatory
Emp Proj
works
This gives THREE tables: Emp Proj Emp_Proj
3. One end obligatory, one end non-obligatory
Emp Proj
works
This gives TWO tables, & we post the key field from the
table at the non-obligatory end, e.g.
Emp (EmpNo, Lname, Fname, Address, ProjNo…)
Proj (ProjNo, Title..)
Connection Traps
Fan traps & chasm traps lead to problems when
selecting information.
Fan traps can occur when we have m:1/1:m relationships.
e.g.
Emp Dept Section
Employee Department Section
01
02 01 S1
03 S2
04 02 S3
05 S4
06 03
Can’t tell which section employees 1 and 2 work in.
Employee is really a subset of section in the ‘natural
hierarchy’ of the type diagram.
SOLUTION: Rework the model!
Employee Section Department
Employee Section Department
01 S1 01
02 S2 02
03 S3 03
04 S4
05
06
So employee 01 works in section S1 and Dept 01
Chasm Traps
What if an employee was a department head & not linked
to any one section? This would cause a chasm trap!
Obligatory/non-obligatory nature of relationship is key.
e.g. an employee is linked to 0,1 sections.
Emp Section Dept
To resolve this, we would need to add a relationship
between Dept and Emp.
Tutorial Exercise
For the scenario below,
Draw the E-R diagram
Write a set of enterprise rules for each relationship
Derive fully normalised table headings, indicating
the key fields.
A veterinary practise is to have its records computerised and you have been given the
job of designing and implementing a database for this purpose. The following
scenario gives details of the organisation of the practise and its information
requirements that are available to you so far.
The practise has four branches: London Rd., Loughborough Rd., Newark St. and
Marsden Lane. Details of equipment available at the branches, along with full postal
addresses and phone numbers are filed by the secretary of the practise.
A number of staff are employed by the surgery, these include veterinary surgeons,
veterinary nurses, a secretary and a number of cleaners. The nurses, cleaners and
secretary are based at particular branches, and always work there, but the veterinary
surgeons can work at any of the branches.
Details of the different jobs and job descriptions, salary ranges etc. are filed as well as
names, addresses, phone numbers, start dates etc. of all staff.
The owners of animals that use the practise always use the same branch. Their names,
addresses, phone numbers and pet details are currently kept on record at that branch.
Every time an animal visits the branch, details of the visit are recorded. These include:
date and time of appointments, the attending veterinary surgeon, any diagnoses,
advised treatment and any drugs prescribed.
Each time a new drug is used by the practise it is added to the ‘drug file’ along with
relevant information to enable the secretary to re-order suitable quantities when
necessary.