ER Modeling

by K. Yue

1. Introduction to DB Modeling

What you can do to understand the problem and model it?

  1. Start by asking a lot of questions.
  2. Collect as much documentation as possible.
  3. Study and use the existing system: find out what is currently being used. Use them with the users.
  4. Participate in the work process.
  5. Model, write, and document!
  6. Use your own words and avoid the 'copy and paste' trap. (What is the 'copy and paste' culture?)

Some possible questions to ask for modeling.

  1. What are the purposes of the application?
  2. What problems will the application solve? What value will it provide?
  3. What are the boundary and context of the applications?
  4. Who are the users?
  5. Who are the domain experts?
  6. What are the available documentation?
  7. How does the existing system work?
  8. What are the workflow processes of the current system?

Prepare your own list of questions.

Two important options for database modeling:

2. ER Modeling and ER Diagrams

ER Diagrams

Example:

Using ER Assistant, we may have:

ER1_1

Note:

Student is an entity type that you can use to instantiate Student objects. Examples: we may have the following instances.

S1: Sadegh Davari
S2: Brad Pitts
S3: Stephen Curry

Each student entity represents a student.

Class is an entity type that you can use to instantiate Class entity. Examples: we may have the following instances.

C1: CSCI 4333
C2: CSCI 3388
C3: CINF 3311

Each course entity represents a course.

The relationship Enroll represents association between a student and a class. The meaning: a student may enroll in a class. For example, we may have:

S1 enrolls in C1
S1 enrolls in C3
S2 enrolls in C1
S3 enrolls in C2
S3 enrolls in C3

The relationship is actually not between two entity types. The relationship is between two entities of some specific types.

In fact, a relationship may be between two entities of the same type.

Example:

Toyu: A drastically simplified university.

ER Diagram:

toyu_relationship.jpg

The diagram is prepared by MySQL Workbench.

PVFC in Microsoft Visio:

pvfc

Example:

Consider (created by ER Assistant):

ER1_2

The meaning of the relationship "is a prerequisite": one class entity can be a prerequisite of another class entity. For example, we may have:

C1 is a prerequisite of C2
C3 is a prerequisite of C2

er1_3

Example:

Using ER Assistant, we may have:

ER1_1

The relationship type Enroll allows a student entity to has an Enroll association with a class entity.

Here are five relationship instances of Enroll:

r1:S1 enrolls in C1
r2:S1 enrolls in C3
r3:S2 enrolls in C1
r4:S3 enrolls in C2
r5:S3 enrolls in C3

Together, they form a relationship set.

Example:

ER_1_1b

ER_1_1c

ER2_b

workfor_1

Workfor_2

workfor_3

workFor_4

manage_1

Example:

Discuss the ER model for students in an university and its relationship with degree program (e.g. B.Sc. in CIS, BA in Math, MS in CS, etc).

You may start with:

ProgDeg_1

Total participation:

R_Part1

Partial participation:

ER_Part_2

Notes:

Example:

Relationship_1

Note that:

  Entity-Relationship Diagram Relationship Diagram
Rectangle Entity Relation
Link Relationship between entities; can be n-ary. Foreign key relationship between relations; always binary.