Transcript Objectives At the end of this chapter you should be able to:  Discuss the use and features of a data model  Define the.

1
Objectives
At the end of this chapter you should be able
to:
 Discuss the use and features of a data
model
 Define the terms entity and attribute and
reference them to corresponding terms in
table design
 Select suitable entities and attributes for a
given case study
 Describe and select suitable primary keys
2
 We
need to hold data to meet user
requirements.
e.g. names, addresses, stock quantities,
appointment times etc.
 We could hold all this data in a single table
 What would be the disadvantages ?
 A data model provides us with a way of
visually deciding how we might break up the
data into chunks so that the data items in a
chunk all relate to the same object or entity
 A data model may be produced using a
technique known as entity modelling
3
 The
relationships between these entities are
then shown on a diagram and we can see the
structure of the proposed database
 The diagram is often called an entity
relationship diagram (ERD)
 The data model consists of the ERD and the
accompanying documentation
 A model in this sense is a selection of
features that describe a certain aspect of a
system
4
 An
entity is anything of significance
about which we wish to hold information
 Entities can be
• real world objects such as Employee, Chair and
Car
• intangible objects such as Purchase Order,
Current Account, and Seat Reservation. These
entities should contain all the information that
we need to hold in the database
5
 The
requirements of the system are first
analysed to find what is significant and
what is not
 Entities are described by a set of
attributes
 An attribute is a data item that can be used
to describe the state of an entity
6
entity
name
attributes
Example of an Entity
7
An entity will allow us to generate an
occurrence or a particular example of an
entity.
36285
Brenda
Wooltorton
Yes
01453 658372
8
Every customer would have values for
most, if not all, of these attributes
Each entity can potentially give rise to any
number of occurrences
9



Entity becomes Table
Attribute becomes Field
Occurrence becomes Record
10
11
12
13
Understand the problem domain
• Conduct an analysis of the user’s present system
and future requirements
• Use interviews, existing documentation and
observation
14
 Be
clear about the context
 In this case the context is the selection
and booking of courses for staff
members
 Names of the possible entities must be
• expressed in the singular
• start with an upper case letter
 Picking
out the nouns is one way of
making up a list of possible entities
15
Staffordshire
Free University employs a
large number of staff who need to keep up to
date with a whole variety of subjects. The
staff may work in different departments
within the university. A system is required
that will keep track of bookings made by staff
members to go on various training courses.
The courses may be internal or external.
16

Each course, whether internal or external, occurs on a
number of occasions and staff members are free to
choose which offering they wish to take. Each course has
a title and it is required to know when they start. Each
course offering for the same course will last for the same
time and this must be recorded. The course offerings
may be at any number of different locations and will only
run if there are a minimum number of students booked
onto the course offering. Each course offering also has a
maximum number of students who are allowed to be
booked onto it.
17



The staff are allowed to browse the different courses
available and then select the particular course they wish
to go on and the offering that suits them best. When a
staff member makes a booking the system should record
the date on which it was made.
Detail of the staff member booking a course offering
include the name, job title, date of birth, date appointed
and department name.
The database should allow the training officer in SFU to
see which members of staff are booked onto which
courses
18
The list here would be:
Staffordshire Free University
Department
occasions
Course Offering
Booking
Minimum Student Number
Booking Date
Date of Birth
Name
Training Officer
Staff
Training Course
Subject
Number of
Course Title
Start Date
Course Duration
Location
Maximum Student Number
Student
Staff Name
Job Title
Date Appointed
Department
19
 Must
be something about which we wish
to store information in the context of the
case study
 There must be more than one
occurrence of each entity
 Make sure you have an entity and not an
attribute. If it is an entity there will be
more than one attribute you will want to
have for it. It is very common to need to
decide if you have an attribute or an
entity.
20
 Consider
if the entity name is vague. It should
be specific
 Call an entity by one name
 Think of two or three attributes it might
contain that we need to operate the system
 Don’t include the name of the company for
which you are performing the data model.
The entire E-R diagram is representing the
company
 Don’t expect to get a full list of entities
immediately
21
 Staff
Member
 Course
 Booking
 Course Offering
We need to remember that we may add
to this list of entities later.
22
 Each
attribute can take on only one value
for any one occurrence of that entity
 If you are putting in an attribute with an
‘s’ on the end – stop and think. This may
be an entity in its own right
23
Each attribute name should be in the
singular
 If attribute is plural then you must create
another entity where you can place that
attribute in its singular form
The chosen name should be specific
 e.g. choose quantity in stock or order quantity
or re-order quantity rather than quantity
24
Check that you can choose a suitable
value for that attribute for a given occurrence

An attribute should be placed in one
entity only

Choose the most natural home for its
placement

Resist the temptation to put an attribute in
more than one place

•e.g. customer ID could be placed in the
Customer or Order entity
25

Don’t include attributes whose values
may be determined from other attribute
values
 e.g.For example don’t include Total Value if it is
simply the sum of a number of Sub-totals that
you are already keeping


Never use the term ‘details’
Check against the case study for
omissions
26
Entities for Staffordshire Free University example
27


a course offering apparently has no
details of the course title
a booking does not know for which
person or course it is made!
28
 Uniquely
identify each occurrence of
an entity by reference to the values of
its attributes
 Choose
one or more attributes such
that if we know their value we can
uniquely identify that occurrence
 One
or more attributes that perform
such a function are known as the
primary key of that entity
29

Sometimes a primary key already exists
 Choose
the minimum number of
attributes possible to form the primary
key
 If Customer ID is a unique number such
that no two customers can have identical
numbers then Customer ID could be a
primary key
30
 Combinations
of other attributes would
not guarantee a way of uniquely
identifying a customer
 Underline the attribute(s) forming the
primary key
31
Entities for SFU with attributes and primary keys
32
We may relax our rule of placing an
attribute in only one entity if we need it
in another entity to form part of a
primary key
See Course ID for the Course entity
and Course ID for Course Offering
33
 Can
we uniquely identify some small
items ?
 We need to identify the name of the
entity as a type of object
• e.g. we might call an entity Stock Item Type and
have attributes of Item #, Item Description and
Quantity in Stock
• Two occurrences then might be:
Item #
237654
421889
Item Description
2” steel nail
4mm bolt
Quantity in Stock
4000
43
34
 Entities
are often chosen from nouns in
requirements specification
 Test to see if an entity has two attributes
(or more) that we need to know to make
our system work
 Don’t put any single attribute in more
than one place
35
 Software
System Development – A Gentle
Introduction 3rd Edition, Britton & Doake,
2003, McGraw Hill (Essential reading)
ISBN 0077099745
36