Transcript Lecture Note 05-2

ITN 170 MySQL Database
Programming
Lecture 3 :Database Analysis and Design (II)
ITN 170 - MySQL Database
Programming
1
Topics covering:
Identify and Model Entities
Entity definition
Attribute definition
ERD and ERM
Relationships
ITN 170 - MySQL Database
Programming
2
Entities Definition
Entity
An entity is something that can be identified in the user’s work
environment; something that the users want to track.
Example: Mary Doe, Peter Chen, etc (They are instructors at
WTCC.)
Entity Classes (also called entity)
a collection of the same type of entities.
Attribute
Attributes, within an entity, describe the entity’s characteristics.
E-RM / E-RD
Entity-relational diagram is a set of concepts and graphical
symbols that can be used to create database. The E-R model
was first published by Peter Chen in 1976.
ITN 170 - MySQL Database
Programming
3
Identify and model entities from a set of
interview notes.
 Examine the nouns. Are they things of significance?
 Name each entity.
 Is there information of interest about the entity that the
business needs to hold?
 Is each instance of the entity uniquely identifiable? Which
attribute or attributes could serve as its UID?
 Write a description of it. “An EMPLOYEE has significance
as a paid worker at the company. For example, John Brown
and Mary Smith are EMPLOYEEs”.
 Diagram each entity and all of its attributes.
ITN 170 - MySQL Database
Programming
4
Example:
Identify the entities in the following set of information
“I’m the manager of a training company that provides instructor-led
courses in management techniques. We teach many courses, each of
which has a code, a name, and a fee. Introduction to UNIX and C
Programming are two of our more popular courses. Courses vary in
length from one day to four days.
An instructor can teach several courses. Paul Rogers and Maria
Gonzales are two of our best teachers. We track each instructor’s
name and phone number. Each course is taught by only one
instructor. We create a course and then line up an instructor.
The students can take several courses over time, and many of them
do this. Jamie Brown from AT&T took every course we offer! We
track each student’s name and phone number. Some of our students
and instructors do not give us their phone numbers.”
ITN 170 - MySQL Database
Programming
5
Example Result:
“I’m the manager of a training company that provides instructorled courses in management techniques. We teach many
courses, each of which has a code, a name, and a fee.
Introduction to UNIX and C Programming are two of our more
popular courses. Courses vary in length from one day to four
days.
An instructor can teach several courses. Paul Rogers and
Maria Gonzales are two of our best teachers. We track each
instructor’s name and phone number. Each course is taught by
only one instructor. We create a course and then line up an
instructor.
The students can take several courses over time, and many
of them do this. Jamie Brown from AT&T took every course we
offer! We track each student’s name and phone number.
Some of our students and instructors do not give us their phone
numbers.”
ITN 170 - MySQL Database
6
Programming
Solution
The following entities model the Training Company's
information requirements.
COURSE
code
name
fee
duration
INSTRUCTOR
(TEACHER)
name
phone number
STUDENT
name
phone number
ITN 170 - MySQL Database
Programming
7
Entity Descriptions
A COURSE has significance as a training event
offered by the Training Company. For Example,
Introduction to UNIX and C Programming.
A STUDENT has significance as a participant in
one or more COURSEs. For example, Jamie
Brown.
An INSTRUCTOR has significance as a teacher
of one or more COURSEs. For example, Paul
Rogers and Maria Gonzales.
ITN 170 - MySQL Database
Programming
8
Relationships
A relationship is a two-directional, significant association between two
entities, or between an entity and itself (e.g. employee & manager).
Relationship Syntax:
Each entity1
{
Must be
or
may be
}
relationship
name
{
One or more
or
one and only one
ITN 170 - MySQL Database
Programming
} each entity2
9
Relationships
Example:
The relationship between INSTRUCTER and COURSE
is:
Each COURSE may be taught by one and only one
INSTRUCTOR
Each INSTRUCTOR may be assigned to one or
more COURSEs.
ITN 170 - MySQL Database
Programming
10
Relationships
Each direction of relationship has:
Each direction of relationship has:
a name – e.g. taught by or assigned to.
an optionality – either must be or may be.
a degree – either one and only one, or one or more.
Quick Notes
Cardinality is a synonym for the term degree.
A degree of 0 is addressed by may be.
ITN 170 - MySQL Database
Programming
11
Relationships
Diagramming Conventions
A line between two entities
Lower case relationship names
Optionality
- - - - - - - - - - Optional (may be)
___________ Mandatory (must be)
Degree
___________
___________
One and only one
One or more
ITN 170 - MySQL Database
Programming
12
Relationships
Example:
many
mandatory
one
optional
ITN 170 - MySQL Database
Programming
13
Relationships
First read a relationship in one direction, and then read the
relationship in the other direction:
Example:
Read the relationship between EMPLOYEE and DEPARTMENT.
EMPLOYEE
assigned
to
DEPARTMENT
responsible
for
Read this relationship first from left to right, and then from right to left.
ITN 170 - MySQL Database
Programming
14
Relationships
Relationship from Left to Right (partial diagram)
EMPLOYEE
assigned
to
DEPARTMENT
Each EMPLOYEE must be assigned to one and only one
DEPARTMENT
ITN 170 - MySQL Database
Programming
15
Relationships
Relationship from Right to Left (partial diagram)
EMPLOYEE
DEPARTMENT
responsible
for
Each DEPARTMENT may be responsible for one or more
EMPLOYEEs.
ITN 170 - MySQL Database
Programming
16
Relationships
In class exercises:
Exercise 1. Read the relationship between
STUDENT and COURSE.
STUDENT
enrolled
in
COURSE
taken
by
Write down the relationships.
ITN 170 - MySQL Database
Programming
17
Relationships
In class exercises:
Exercise 1. Read the relationship between
STUDENT and COURSE.
STUDENT
enrolled
in
COURSE
taken
by
Each STUDENT must be enrolled in one or more COURSEs.
Each COURSE may be taken by one or more STUDENTs.
ITN 170 - MySQL Database
Programming
18
Relationships
In class exercises:
Exercise 2. Read the relationship between
PAYCHECK and EMPLOYEE.
for
PAYCHECK
the
receiver
of
EMPLOYEE
Write down the relationships.
ITN 170 - MySQL Database
Programming
19
Relationships
In class exercises:
Exercise 2. Read the relationship between
PAYCHECK and EMPLOYEE.
for
PAYCHECK
the
receiver
of
EMPLOYEE
Each PAYCHECK must be for one and only one EMPLOYEE.
Each EMPLOYEE may be the receiver of one or more PAYCHECKs.
ITN 170 - MySQL Database
Programming
20
Relationships
There are three types of relationships:
Relationship Types
- Many to One Relationships
- Many to Many Relationships
- One to One Relationship
All relationships should represent the information
requirements and rules of the business.
ITN 170 - MySQL Database
Programming
21
Relationships
A Many to One Relationship (M to 1 or M:1) has a
degree of one or more in one direction and a degree
of one and only one in the other direction.
Quick Notes
- M:1 relationships are very common.
- M:1 relationships that are mandatory in both directions are
rare.
ITN 170 - MySQL Database
Programming
22
Relationships
Example:
CUSTOMER
visited
by
assigned
to visit
SALES
REPRESENTATIVE
Each CUSTOMER must be visited by one and only one SALES
REPRESENTATIVE.
Each SALES REPRESENTATIVE may be assigned to visit one or more
CUSTOMERs.
ITN 170 - MySQL Database
Programming
23
Relationships
A Many to Many Relationship (M to M or M:M) has a
degree of one or more in both directions.
Example 1:
There is a M:M relationship between STUDENT and COURSE.
STUDENT
enrolled
in
COURSE
taken
by
Each STUDENT must be enrolled in one or more COURSEs.
Each COURSE may be taken by one or more STUDENTs.
ITN 170 - MySQL Database
Programming
24
Relationships
Example 2:
There is a M:M relationship between EMPLOYEE and JOB.
assigned
to
EMLOYEE
JOB
Carried
out by
Each EMPLOYEE may be assigned to one or more JOBs.
Each JOB may be carried out by one or more EMPLOYEEs.
ITN 170 - MySQL Database
Programming
25
Relationships
- M:M relationships are very common.
- Many to Many relationships that are usually optional
in both directions (e.g. EMPLOYEE vs JOB), (this
implies that an instance of either entity can exist
without an association with the other).
- Many to Many Relationship may be optional in just
one direction (e.g. STDUDENT vs COURSE).
- Many to Many Relationships that are mandatory at
both ends are very rare because each is fully
dependent on the other for existence.
ITN 170 - MySQL Database
Programming
26
Relationships
A One to One Relationship (1 to 1 or 1:1) has a degree of
one and only in both directions.
Example:
There is a 1:1 relationship between MICROCOMUTER and
MOTHERBOARD.
MICROCOMPUTER
the host
for
MOTHERBOAD
incorporated
into
Each MICROCOMPUTER must be the host for one and only one
MOTHERBOARD.
Each MOTHERBOARD may be incorporated into one and only one
MICROCOMPUTER.
ITN 170 - MySQL Database
Programming
27
Relationships
Quick Notes
 1:1 relationships are rare (in the real world, you
usually do not see an example, but think about one)
 Furthermore, A 1:1 Relationship that is mandatory in
both directions is very rare (Think about an example)
HUSBAND vs WIFE
 Entities which seem to have a 1:1 relationship may
really be the same entity.
COUPLE
husband
HUSBAND vs WIFE
wife
ITN 170 - MySQL Database
Programming
28
Relationships
Another Example:
There is a 1:1 relationship between INVOICE and ORDER.
ORDER
INVOICE
INVOICE
ORDER
In this example, although the names order and invoice are different,
the data contained in them is probably almost identical.
ITN 170 - MySQL Database
Programming
29
Relationships
Answer:
ORDER
ITEM
issued for
number
type
originated
by
in
number
description
stored in
the
originator
of
the
repository
for
CUSTOMER
WAREHOUSE
first name
last name
id
address
ITN 170 - MySQL Database
Programming
30
Follow a series of five steps to analyze
and model relationships.
Determine the existence of a relationship.
Name each direction of the relationship.
Determine the optionality of each direction of
the relationship.
Determine the degree of each direction of the
relationship.
Read the relationship aloud to validate it.
ITN 170 - MySQL Database
Programming
31
DETERMINE A RELATIONSHIP’S EXISTENCE
Determine the existence of a
relationship.
Examine each pair of entities to
determine if a relationship exists.
Existence
Name
Optionality
Degree
Validate
 Ask About a Relationship’s Existence
 Does a significant relationship exist between ENTITY A
and ENTITY B?
ITN 170 - MySQL Database
Programming
32
DETERMINE A RELATIONSHIP’S EXISTENCE
Existence
Name
Optionality
Degree
Validate
Example 1
 Consider the entities DEPARTMENT and
EMPLOYEE.
 Is there a significant relationship between
DEPARTMENT and EMPLOYEE?
 Yes, there is a significant relationship between
DEPARTMENT and EMPLOYEE
ITN 170 - MySQL Database
Programming
33
DETERMINE A RELATIONSHIP’S EXISTENCE
Existence
Name
Optionality
Degree
Validate
Example 2
 Consider the entities DEPARTMENT and
ACTIVITY.
 Is there a significant relationship between
DEPARTMENT and ACTIVITY?
 No, there is NOT a significant relationship between
DEPARTMENT and ACTIVITY.
ITN 170 - MySQL Database
Programming
34
NAME THE RELATIONSHIP
It is a good working practice to name
every relationship in your Entity
Relationship Model. In some
methodologies, it is optional; and in
others, it is mandatory. However, since
every relationship represents a
business rule and the name reflects
that rule, it is sensible to try to name all
relationships.
ITN 170 - MySQL Database
Programming
Existence
Name
Optionality
Degree
Validate
35
NAME THE RELATIONSHIP
The name that you give to the
relationship is important since it
reflects your understanding of the
business rule that links the entities
together. Without a name, a
relationship may be misleading, and
incorrect assumptions can easily be
made.
ITN 170 - MySQL Database
Programming
Existence
Name
Optionality
Degree
Validate
36
NAME THE RELATIONSHIP
Name each direction of a relationship.
Existence
Name
Optionality
Degree
Validate
 Ask a Relationship’s Name
 How is an ENTITY A related to ENTITY B? An ENTITY A is
relationship name in ENTITY B.
 How is an ENTITY B related to an ENTITY A? An ENTITY B
is relationship name an ENTITY A.
ITN 170 - MySQL Database
Programming
37
NAME THE RELATIONSHIP
Existence
Name
Optionality
Degree
Validate
Example
 Consider the relationship between DEPARTMENT
and EMPLOYEE.
 How is a DEPARTMENT related to an EMPLOYEE?
Each DEPARTMENT is responsible for an EMPLOYEE.
 How is an EMPLOYEE related to a DEPARTMENT?
Each EMPLOYEE is assigned to a DEPARTMENT.
ITN 170 - MySQL Database
Programming
38
NAME THE RELATIONSHIP
Use a list of relationship name pairs to
assist in naming relationships.
 Useful Relationship Name Pairs
Existence
Name
Optionality
Degree
Validate
 based on
the basis for
 bought from
supplier of
 description of
for
 operated by
the operator for
 represented by
the representation of
 responsible for
the responsibility of
Oracle’s suggestion: Do not use related to or associated with
as relationship name
ITN 170 - MySQL Database
Programming
39
NAME THE RELATIONSHIP
Quick Note:
You must not use words such as
related to or associated with. The
presence of the relationship indicates
an association. The name must
convey the business rule causing the
association. Choose a name that is
meaningful within the business being
modeled.
ITN 170 - MySQL Database
Programming
Existence
Name
Optionality
Degree
Validate
40
NAME THE RELATIONSHIP
Use a list of relationship name pairs to
assist in naming relationships.
 Useful Relationship Name Pairs






based on
bought from
description of
operated by
represented by
responsible for
Existence
Name
Optionality
Degree
Validate
the basis for
supplier of
for
the operator for
the representation of
the responsibility of
ITN 170 - MySQL Database
Programming
41
DETERMINE THE RELATIONSHIP’S OPTIONALITY
Determine the optionality of each
direction of the relationship
Existence
Name
Optionality
Degree
Validate
 Ask About a Relationship’s Optionality
 Must ENTITY A be relationship name ENTITY B?
 Must ENTITY B be relationship name ENTITY A?
ITN 170 - MySQL Database
Programming
42
DETERMINE THE RELATIONSHIP’S OPTIONALITY
Example
 Consider the relationship between
DEPARTMENT and EMPLOYEE
Existence
Name
Optionality
Degree
Validate
 Must an EMPLOYEE be assigned to a DEPARTMENT?
Always? Is there any situation in which an EMPLOYEE
will not be assigned to a DEPARTMENT?
No, an EMPLOYEE must always be assigned to a
DEPARTMENT.
 Must a DEPARTMENT be responsible for an
EMPLOYEE?
No, a DEPARTMENT does not have to be responsible for
an EMPLOYEE.
ITN 170 - MySQL Database
Programming
43
DETERMINE THE RELATIONSHIP’S DEGREE
Determine the degree of the
relationship in both directions
 Ask about a Relationship’s Degree
Existence
Name
Optionality
Degree
Validate
 May ENTITY A be relationship name more than one
ENTITY B?
 May ENTITY B be relationship name more than one
ENTITY A?
ITN 170 - MySQL Database
Programming
44
DETERMINE THE RELATIONSHIP’S DEGREE
Example
 Consider the relationship between
DEPARTMENT and EMPLOYEE
Existence
Name
Optionality
Degree
Validate
 May an EMPLOYEE be assigned to more than one
DEPARTMENT?
No, an EMPLOYEE must be assigned to only one
DEPARTMENT.
 May a DEPARTMENT be responsible for more than one
EMPLOYEE?
Yes, a DEAPRTMENT may be responsible for one or
more EMPLOYEEs.
ITN 170 - MySQL Database
Programming
45
DETERMINE THE RELATIONSHIP’S DEGREE
 Add the relationship degrees to the E-R
Diagram.
EMPLOYEE
assigned
to
Existence
Name
Optionality
Degree
Validate
DEPARTMENT
responsible
for
ITN 170 - MySQL Database
Programming
46
VALIDATE THE RELATIONSHIP
Re-examine the E-R model and validate
the relationship.
 Read the Relationship Aloud
Relationship must be readable and make
business sense.
EMPLOYEE
assigned
to
Existence
Name
Optionality
Degree
Validate
DEPARTMENT
responsible
for
Each EMPLOYEE must be assigned to one and only one DEPARTMENT
Each DEPARTMENT may be responsible for one or more EMPLOYEEs.
ITN 170 - MySQL Database
Programming
47