Lecture 3 :Database Analysis and Design (II)

1
Lecture 3 Database Analysis and Design (II)
ITN 170 MySQL Database Programming
2
Topics covering

• Identify and Model Entities
• Relationships

• Entity definition
• Attribute definition
• ERD and ERM

3
Entities Definition

• Entity
• An entity is something that can be identified in
  the users 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
  entitys 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.

4
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.

5
Example Identify the entities in the
following set of information
Im 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
instructors 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 ATT took
every course we offer! We track each students
name and phone number. Some of our students and
instructors do not give us their phone numbers.
6
Example Result
Im 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
instructors 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 ATT took
every course we offer! We track each students
name and phone number. Some of our students and
instructors do not give us their phone numbers.
7
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
8

• 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.

9
Relationships
A relationship is a two-directional, significant
association between two entities, or between an
entity and itself (e.g. employee manager).
Relationship Syntax




relationship name
Must be or may be
One or more or one and only one
each entity2
Each entity1
10
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.
11
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.

12
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

13
Relationships
Example
one
many
mandatory
optional
14
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.
assigned to
DEPARTMENT
EMPLOYEE
responsible for
Read this relationship first from left to right,
and then from right to left.
15
Relationships
Relationship from Left to Right (partial diagram)
assigned to
DEPARTMENT
EMPLOYEE
Each EMPLOYEE must be assigned to one and only
one DEPARTMENT
16
Relationships
Relationship from Right to Left (partial diagram)
DEPARTMENT
EMPLOYEE
responsible for
Each DEPARTMENT may be responsible for one or
more EMPLOYEEs.
17
Relationships
In class exercises Exercise 1. Read the
relationship between STUDENT and COURSE.
enrolled in
COURSE
STUDENT
taken by
Write down the relationships.
18
Relationships
In class exercises Exercise 1. Read the
relationship between STUDENT and COURSE.
enrolled in
COURSE
STUDENT
taken by
Each STUDENT must be enrolled in one or more
COURSEs. Each COURSE may be taken by one or more
STUDENTs.
19
Relationships
In class exercises Exercise 2. Read the
relationship between PAYCHECK and EMPLOYEE.
for
EMPLOYEE
PAYCHECK
the receiver of
Write down the relationships.
20
Relationships
In class exercises Exercise 2. Read the
relationship between PAYCHECK and EMPLOYEE.
for
EMPLOYEE
PAYCHECK
the receiver of
Each PAYCHECK must be for one and only one
EMPLOYEE. Each EMPLOYEE may be the receiver of
one or more PAYCHECKs.
21
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.
22
Relationships
A Many to One Relationship (M to 1 or M1) has a
degree of one or more in one direction and a
degree of one and only one in the other
direction. Quick Notes - M1 relationships
are very common. - M1 relationships that
are mandatory in both directions are rare.
23
Relationships
Example
visited by
SALES REPRESENTATIVE
CUSTOMER
assigned to visit
Each CUSTOMER must be visited by one and only one
SALES REPRESENTATIVE. Each SALES REPRESENTATIVE
may be assigned to visit one or more CUSTOMERs.
24
Relationships
A Many to Many Relationship (M to M or MM) has a
degree of one or more in both directions. Example
1 There is a MM relationship between STUDENT
and COURSE.
enrolled in
COURSE
STUDENT
taken by
Each STUDENT must be enrolled in one or more
COURSEs. Each COURSE may be taken by one or more
STUDENTs.
25
Relationships
Example 2 There is a MM 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.
26
Relationships
- MM 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.
27
Relationships
A One to One Relationship (1 to 1 or 11) has a
degree of one and only in both directions. Exampl
e There is a 11 relationship between
MICROCOMUTER and MOTHERBOARD.
the host for
MOTHERBOAD
MICROCOMPUTER
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.
28
Relationships
Quick Notes

• 11 relationships are rare (in the real world,
  you usually do not see an example, but think
  about one)
• Furthermore, A 11 Relationship that is mandatory
  in both directions is very rare (Think about an
  example)
• Entities which seem to have a 11 relationship
  may really be the same entity.

HUSBAND vs WIFE
HUSBAND vs WIFE
29
Relationships
Another Example There is a 11 relationship
between INVOICE and ORDER.
ORDER
INVOICE
In this example, although the names order and
invoice are different, the data contained in them
is probably almost identical.
30
Relationships
Answer
ORDER number type
ITEM number description
issued for
in
originated by
stored in
the originator of
the repository for
WAREHOUSE id address
CUSTOMER first name last name
31
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.

32
DETERMINE A RELATIONSHIPS 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 Relationships Existence
• Does a significant relationship exist between
  ENTITY A and ENTITY B?

33
DETERMINE A RELATIONSHIPS 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

34
DETERMINE A RELATIONSHIPS 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.

35
NAME THE RELATIONSHIP
Existence Name Optionality Degree Validate
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.
36
NAME THE RELATIONSHIP
Existence Name Optionality Degree Validate
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.
37
NAME THE RELATIONSHIP
Existence Name Optionality Degree Validate
Name each direction of a relationship.

• Ask a Relationships 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.

38
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.

39
NAME THE RELATIONSHIP
Use a list of relationship name pairs to assist
in naming relationships.
Existence Name Optionality Degree Validate

• Useful Relationship Name Pairs
• 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

Oracles suggestion Do not use related to or
associated with as relationship name
40
NAME THE RELATIONSHIP
Existence Name Optionality Degree Validate
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.
41
NAME THE RELATIONSHIP
Existence Name Optionality Degree Validate
Use a list of relationship name pairs to assist
in naming relationships.

• Useful Relationship Name Pairs
• 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

42
DETERMINE THE RELATIONSHIPS OPTIONALITY
Determine the optionality of each direction of
the relationship
Existence Name Optionality Degree Validate

• Ask About a Relationships Optionality
• Must ENTITY A be relationship name ENTITY B?
• Must ENTITY B be relationship name ENTITY A?

43
DETERMINE THE RELATIONSHIPS OPTIONALITY
Existence Name Optionality Degree Validate
Example

• Consider the relationship between DEPARTMENT and
  EMPLOYEE
• 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.

44
DETERMINE THE RELATIONSHIPS DEGREE
Determine the degree of the relationship in both
directions
Existence Name Optionality Degree Validate

• Ask about a Relationships Degree
• May ENTITY A be relationship name more than one
  ENTITY B?
• May ENTITY B be relationship name more than one
  ENTITY A?

45
DETERMINE THE RELATIONSHIPS DEGREE
Example
Existence Name Optionality Degree Validate

• Consider the relationship between DEPARTMENT and
  EMPLOYEE
• 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.

46
DETERMINE THE RELATIONSHIPS DEGREE
Existence Name Optionality Degree Validate

• Add the relationship degrees to the E-R Diagram.

assigned to
DEPARTMENT
EMPLOYEE
responsible for
47
VALIDATE THE RELATIONSHIP
Re-examine the E-R model and validate the
relationship.
Existence Name Optionality Degree Validate

• Read the Relationship Aloud
• Relationship must be readable and make business
  sense.

assigned to
DEPARTMENT
EMPLOYEE
responsible for
Each EMPLOYEE must be assigned to one and only
one DEPARTMENT Each DEPARTMENT may be responsible
for one or more EMPLOYEEs.