Transcript The Entity-Relationship Model II

Relational Database
Modeling II
“We don’t live in a world of reality, we live in a world of
perceptions.” J. Gerald Simmons
Class Outline
 Using
the ER methodology and notation discussed
last day, create a completed entity-relationship
model for:

a simple library database

a simple university faculty database
 What
are weak entities?
 What
is a generalized hierarchy?
 What
are the strengths and weaknesses of the
Entity-Relationship model?
Steps in Entity-Relationship Modeling
1. Identify entities
2. Identify relationships
3. Determine relationship type
4. Determine level of participation
5. Assign an identifier for each entity
6. Draw completed E-R diagram
7. Deduce a set of preliminary skeleton tables along with a
proposed primary key for each table (using rules provided)
8. Develop a list of all attributes of interest (not already listed
and systematically assign each to a table in such a way to
achieve a 3NF design (i.e., no repeating groups, no partial
dependencies, and no transitive dependencies)
E-R Method Example: Library Database
 Step
1. Identify entity types
AUTHOR
 Step
BOOK
PUBLISHER
2. Identify relationships
AUTHOR
writes
BOOK
PUBLISHER
publishe
s
BOOK
Library Database (cont’d)
 Step

3. Determine relationship type. Ask:
Each book is written by how many authors? Each author
writes how many books?
Each book may be authored by zero (anonymous), one, or more than
one author and each author may write zero, one, or more than one
book. The relationship type is many-to-many or:
AUTHOR
M
writes
N
BOOK
For PUBLISHER-publishes-BOOK, each publisher publishes zero,
one, or more books and each book is published by exactly one
publisher. The relationship type is one-to-many where BOOKS is on
the many side and PUBLISHER is one the one side.
PUBLISHER
1
publishe
s
M
BOOK
Library Database (cont’d)
 Step 4. Determine level of participation
 Since each book does not have to be authored (anonymous) and
since each author does not have to write a book (may make CD)
the level of participation is optional for both sides of the
relationship of AUTHOR-writes-BOOK combination
M
N
writes
AUTHOR

BOOK
(0, N)
(0, N)
For the PUBLISHER-publishes-BOOK combination, the level of
participation for PUBLISHER is optional (publishers do not
necessarily have to publish a book, perhaps newsletters) and the
level of participation for the BOOK side is mandatory (each book
must have a publisher)
1
N
publishe
PUBLISHER
(0, N)
s
BOOK
(1,1)
Library Database (cont’d)
 Step

5. Assign an identifier for each entity
AuthorID, ISBN, PublisherID
 Step
6. Draw completed E-R diagram
AUTHOR
M
AuthorID, ... (0,N)
writes
BOOK
N
(0,N)
N
ISBN, ...
(1,1)
publishes
1
PUBLISHER
PublisherID, ...
(0,N)
Library Database (cont’d)
 Step
6. Draw completed E-R diagram - resolve
M:N relationships
AUTHOR
1
(0,N)
AuthorID, ...
M
writes
M
(1,1)
(1,1)
AuthorID,ISBN, ...
1
BOOK
(0,N)
ISBN, ...
M (1,1)
publishes
1
PUBLISHER
PublisherID, ...
(0,N)
E-R Modeling: University Example
 A database
is to be set up to record information about
faculty, the courses they teach, and the students who take
courses. Some courses are taught by teams of faculty
members.

Step 1. Identify entity types
FACULTY

COURSE
STUDENT
Step 2. Identify relationships
FACULTY
teaches
COURSE
STUDENT
takes
COURSE
University Example (cont’d)
 Step 3. Determine relationship type. Ask:
– Each faculty member teaches how many courses?
– Each course is taught by how many faculty?
– Each student takes how many courses?
– Each course is taken by how many students?

Use occurrences diagram to visualize relationship
between entities
F1
C1
S1
F2
C2
S2
F3
C3
S3
F4
C4
S4
F5
C5
S5
F6
C6
S6
C1
C2
C3
C4
C5
C6
University Example (cont’d)
 Step 3. Determine Relationship type (cont’d)
 For FACULTY-teaches-COURSE we are told each faculty member
teaches zero, one, or two courses. We are told some courses are
taught by zero, one, two, or three faculty. This is a many-to-many
relationship.
M
N
FACULTY
COURSE
teaches

For STUDENT-takes-COURSE each student enrols in one to six
courses and each course is taken by zero or up to 30 students. This
too is a many-to-many relationship.
STUDENT
M
takes
N
COURSE
University Example (cont’d)
 Step

4. Determine level of participation
FACULTY-teaches-COURSE - level of participation is optional,
since sometimes Faculty do not have to teach (e.g., sabbatical);
similarly, a course may not have anyone interested in teaching it
M
N
FACULTY
COURSE
teaches
(0,2)

(0,3)
STUDENT-takes-COURSE - level of participation is mandatory
since students must take at least one course; a course, however, may
or may not have students taking it
M
N
STUDENT
COURSE
takes
(1,6)
(0,30)
University Example (cont’d)
 Step

5. Assign an identifier for each entity
FacultyID, CourseID, StudentID
 Step
6. Draw completed E-R diagram
N
M
COURSE
CourseID, ...
(0,3)
M
taught by
(0,2)
N
FACULTY
FacultyID, ...
taken by
(0,30)
STUDENT
(1,6)
StudentID, ...
University Example (cont’d)




You are now told that in addition to the relationships given, each
student is assigned a faculty advisor who gives direction in choosing
courses.
Use occurrences diagram to visualize relationship between entities
We are told each student is advised by exactly one faculty advisor.
We can assume that each faculty member advises zero, one, or more
students. This means the additional relationship is of type one-tomany or 1:M.
The STUDENT is on the many side of the relationship and must be
advised therefore, faculty is mandatory to student; FACULTY on the
one side of the relationship may or may not have a student, therefore
student is optional to faculty.
1
FACULTY
(0,N)
advises
M
STUDENT
(1,1)
University Example (cont’d)
 Step
6. Draw completed E-R diagram
1 M
StudentID, ...
STUDENT
M
(1,1)
takes
(1,6) (1,1)
M 1
COURSE
(1,1) (0,30)
CourseID, ...
1 (0,3)
M (1,1)
taught
by
M (1,1)
1 (0,2)
(0,N)
1
FACULTY
FacultyID, ...
Weak Entities
 an
entity that has a dependency on the existence of another
entity (mandatory participation) and
 has a primary key that is partially or totally derived from
the parent entity of the relationship
 depict weak entity and relationship with rounded corners
COURSE
contains
CourseID, ...
EMPLOYEE
EmployeeID, ...
SECTION
CourseID, SectionID, ...
has a
DEPENDENT
EmployeeID, DependentID, ...
Generalization Hierarchy


A subtype entity is an entity that contains a set of optional attributes
of the supertype entity and inherits its attributes and its
relationships from the supertype entity
If the supertype entity is related to exclusive (can belong to only one
subtype) subtype entities, indicate with 1; if subtypes are
overlapping (can belong to more than one), use m
supertype
CLIENT
1
INDIVIDUAL
CORPORATE
CONTRACT
subtype
PRODUCTS
m
SERVICES
The same identifier (e.g., ClientID) is used for the supertype as
well as subtype.
Evaluation of the E-R Model




Using data models to conceptualize the design of a database saves
time and money because a completed E-R diagram is the actual
blueprint of the database. Its composition must reflect an
organization's operations accurately if the database is to meet that
organization's data requirements.
The completed E-R diagram also lets the designer communicate more
precisely with those who commissioned the database design. It’s
easier to correct design flaws at the data modeling stage.
Do not confuse entities and relationships with actual tables. The
transformation or decomposition of E-R models will be discussed
within the next few weeks.
E-R modeling is an iterative process. Even when complete, ER
models generally do not provide a complete picture (e.g., business
rules cannot always be shown), therefore, much additional
documentation is necessary.