Transcript Conceptual Modelling

ER Models
ISSUES
and Examples
CS263 Lecture 9
ER Issues - Fan Trap
Where a model represents a relationship between entity types,
but the pathway between certain entity occurrences is
ambiguous.
Staff
works for
has
Division
operates
reports to
Branch
Q. At which branch does employee Fred Bloggs (001) work?
Branch
Staff
Division
001
D1
B03
002
003
D2
B02
B04
A. Fred Bloggs (001) works at Branch B03, or is it B02?
ER Issues - Fan Trap Resolution
We resolve the Fan Trap by restructuring the original ER
model to represent the correct association between the entities
concerned.
Division
operates
reports to
Branch
has
work for
Staff
Q. At which branch does employee Fred Bloggs (001) work?
Division
Branch
Staff
D1
B03
002
D2
B02
B04
001
003
A. Fred Bloggs (001) works at Branch B02
Developing an ER Diagram
Step 1
Identify Entities
 Step 2
Work out relationships
 Step 3
Identify Attributes
 Step 4
Identify optionality
 Step 5
Identify KEY attributes
(Resolve any assumptions)

Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any one
author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any one
author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any one
author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any one
author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any one
author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any
one author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any
one author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any
one author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any
one author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any
one author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any
one author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any
one author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Example Problem 1
A college library holds books for its members to borrow.
Each book may be written by more than one author. Any
one author may have written several books. If no copies of a
wanted book are currently in stock, a member may make a
reservation for the title until it is available. If books are not
returned on time a fine is imposed and if the fine is not paid
the member is barred from loaning any other books until the
fine is paid.
Member
Book
Author
Title
Member
Book
Author
Title
Member
Book
• Serial Number.
• Condition.
• Date Purchased.
Author
Title
EACH
Written
by
Writes
Borrows
Book
Member
On loan
Author
Title
Written
by
RESERVES
Writes
Borrows
Book
Member
On loan
BARRED
FINE
Author
Title
Written
by
Reserves
Reserved
By
Writes
Borrows
Book
Member
Author
On loan
MUST
Fine
MAY
ER Diagram One
Title
Reserves
Reserved Has Has
By
Writes
Written
by
Borrows
Book
Member
On loan
Has
For
Results in
Imposed on
Fine
Author
ER Diagram Two
Title
T_A
Reserve
Member
M_B
Fine
Book
Author
T#
Identify Keys
Title
T_A
Reserve
M#
B#
Member
M_B
F#
Fine
Book
A#
Author
Foreign
KeysTwo
ER Diagram
T#
Title
T#,A#
T_A
M#,T#
Reserve
M#
Member
M#,B#
Borrow
F# M#,B#
Fine
B#
T#
Book
A#
Author
Example Problem 2
A global enterprise has three companies, and each company
has between two and six departments. Each department
employs one or more employees (although certain company
employees may not currently be attached to a department),
who, as well as possibly having a number of dependants, may
well have an associated set of employment history notes.
State any assumptions you make.
Company
Belongs to
Operates
Employs
Department
Employee
Belongs to
Works for
Belongs to
Has
Dependent
Has
Employment
Note
Example Problem 3
A local authority wishes to keep a database of all its schools
and the school children that are attending each school. The
system should also be able to record teachers available to be
employed at a school and be able to show which teachers
teach which children. Each school has one head teacher
who’s responsibility it is to manage their individual school,
this should also be modelled. State any assumptions you
make.
School
Teacher
Teaches
Taught by
Child