ER Modeling

by K. Yue

1. Introduction to DB Modeling

• Why DB modeling?
  1. Many DB developers just directly figure out the relational schema without formal data modeling.
  2. The complexity of a problem needs to be thoroughly understood and modeled before designing a solution.
• What are involved in DB modeling?
  • Understand, capture, and refine the application requirement until it is clear and detailed enough for design and implementation.
  • Communications are crucial. "Do not build your DB application. Build the DB application for the users."
  • Complexity management (try to compare problem solving not involving computers). Tools and techniques:
    1. modeling language: Entity-Relationship Diagram (ERD) in this course.
    2. best practice and theory: How to model.
    3. process: When to do what in the modeling process.

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 generic 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 generic questions.

Two important options for database modeling:

1. Entity Relationship (ER) Modeling and Extended ER (EER) Modeling: more relational database specific.
2. UML modeling: more general purpose.

2. ER Modeling and ER Diagrams

• Some advantages of ER diagrams:
  1. Easy to understand
  2. Simple
  3. More specific to relational database modeling
  4. Good collection of theory and best practices
  5. Good vendor and tool support
  6. Good DBMS support
  7. Popular
• Some Disadvantages of ER diagrams:
  1. Limited expressiveness
  2. Not concise
  3. Can be ambiguous
  4. No standards: many versions that can be confusing
  5. Mostly for relational database only.
• There are many ER diagram tools, with different relative merits.
• For our course, use draw.io Desktop version: er_in_drawio.pdf.
• Additional material: ER-Assistant is a simple ER tool: ("EA Assistant v2.1") from http://highered.mheducation.com/sites/0072942207/student_view0/e_r_assistant.html).
• In ER modeling, requirements are captured, such as business rules, and modeled using ER constructs.

ER Diagrams

• Entities are 'objects that exists and that can be distinguished from other objects.'
  • Entities can be anything: concepts with information to store.
  • Entities usually have properties that are represented by attributes.
• Note the difference between:
  1. Entity type: the type of an entity. E.g. the entity type Student. Compare to a class in OO modeling.
  2. Entity set: a set of entities of the same type. E.g. a set of entities containing students in the class CSCI 4333.1, Fall 2015.
  3. An entity: an instance of one entity. E.g. a student S1.
• Compare these concepts with Java's classes and objects.
• In a ER diagram, the rectangle may represent an entity type that you can create an entity instance to be stored in an entity set.
• Relationships are associations between entities.

Additional Material: Traditional ER notation (Chen's Notation)

• Symbols used in traditional ER Diagrams:
  1. Entity: rectangle
  2. Relationship: diamond
  3. Attribute: oval
• Other symbols used in traditional ER diagrams:
1. Double rectangle: weak entities (existence depending on parent entities)
2. Double diamond: relationship joining a weak entity to its parent entity.
3. Double oval: multi-valued attributes.
4. Dashed oval: derived attributes.
5. Cardinality: 1 for one, N for many.

Crow-Foot Notations

• Entity has a compartment for storing attributes.
• Binary association does not need to use a diamond.
• Cardinaly: crow-foot. 0, |, ∈ for zero, one and many.

Example:

Description: A student has a unique student id and can enroll in any number of classes.

For example, Jane Smith, with an student of 3100910, enrolls in the classes 'CSCI 4333.1' and 'CSCI 3352.1'.

The ER diagram can be used to create entity and relationship instances.

• Note the difference between:
  • Entity type: the type of an entity. E.g. the entity type Student. Compare to a class in OO modeling.
  • Entity set: a set of entities of the same type. E.g. a set of entities containing students in the class CSCI 4333.1, Fall 2015.
  • An entity: an instance of one entity. E.g. a student S1.
• Compare these concepts with Java's classes and objects.
• In a ER diagram, the rectangle may represent an entity type that you can create an entity instance to be stored in an entity set.

Entity type: Student(StudentId, LName, FName)
Entity (instance): (3100910, 'Smith', 'Jane')
Entity set: {(3100910, 'Smith', 'Jane'), (2001001, 'Johnson', 'John'), (1201021, 'Bajaj', 'Rashid')}

Example:

A simple ER diagram:

Note:

• We will demonstrate how to draw the ER diagram in draw.io and/or ER Assistant in class.
• It does not use the traditional ER notations,.
• Both ER-Assistant and Hoffer use the Crow-Foot notation.

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 (drawn using MySQL Workbench)

Example:

Consider:

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

• An attribute presents a property of an entity.
• A multi-valued attribute may have many values.
  • The name of the attribute is usually in plural.
  • Example: Hobbies, Names, Honors, Medals, etc.
  • Example: Names: a DB problem may need to store all names (or alias) of a person. 'Bun', 'Bun Yue', 'K.B. Yue', etc.
• A derived attribute has a value that can be computed (derived) from other attributes (that may be in another relations).
• In the traditional ER model:
  • ordinary attribute: oval.
  • multi-valued attribute: double oval
  • derived attribute: dashed oval
• Draw.Io or ER Assistant does not directly support notations for multi-valued attribute or derived attribute.
  • Multi-valued attributes: consider promoting the attribute to an entity.
  • Derived attributes: document them in the comment section.

• Relationships are connections, associations, or interactions between entity instances.
• Note the difference between:
  1. relationship type: what entities and their types participate in the relationship.
  2. relationship set: a set of relationships of a specific type
  3. relationship instance: a relationship between two entity instances.

Example:

Consider again:

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.

• There are different degrees of relationship
  • binary – links two entities: we only use this here.
  • ternary – links three entities sets
  • N-ary – links n entities
• Relationship cardinality (or multiplicity): number of entity instances to which another entity can map under the relationship.
• For binary relationship:
  1. one to one
  2. one to many
  3. many to many
• Furthermore, carindality can be optional or mandatory.
  • One can be (0..1) or (1..1)
  • Many can be (0..*) or (q..*)

Example:

• Zero or many employees work for one department.
• Assumptions:
  1. A department may have no employee.
  2. An employee must work with one department.

• Zero or many employees work for one and only one department.
• Assumptions:
  1. A department may have no employee.
  2. An employee must work with exactly one department. if an employee does not work for any department, it cannot be added to the database.
• What are the assumptions made below?

• What about this?

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:

• What should the cardinality be?
• Relationship may have entity role: how the entity participate in the relationship.
• A relatiionship may have attirubtes. In this case, a relationship may have an associative entity to store the attributes.

Example:

• Kinds of participation:
  • Total participation: 0 not allowed (1..1 or 1..*)
  • Partial participation: 0 allowed (0..1 or 0..*)

Total participation:

Partial participation:

Additional Materials: advanced relevant concepts

• Existence dependence: same as total participation, but may be more forceful. Example:
  • If an employee must work for a department, this is total participation.
  • It can also be interpreted as "existence dependent": an employee cannot exist (in the database) without a department.
  • Semantically, in the real world, employee can exist independent of a department. It may just not exist in the database.
• If Y is existence dependent of X, and Y does not have its own candidate keys, then Y is a weak entity and X is a strong entity.
• Weak entity may have a partial key, a discriminator, that unique identifies a weak entity among those related to the same strong entity.
• In some notations, use double rectangle for weak entity, with double diamond for relationship connecting it to its strong entity (e.g. Ricardo).
  • Recommendation: don't use it. This is because an entity can be a weak entity in one relationship and a strong entity in another relationship.

Modeling is about finding the true meanings, assumptions, and requirements and captured them in the selected modeling language.

• There is no 'mechanical' formula.

Example: Chen's suggested 'rule of thumb':

• Person -> Entity type
• Bun -> Entity
• Student -> Entity type
• Jean (a student): Entity
• Teaches (as in Bun teaches Jean): Relationship type
• Black-eyed: attribute type?
• 'Black' eye: attribute for entity.

However, there are so many exceptions.

Notes:

• Do not confuse an ER diagram with a relationship diagram (usually provided by DBMS).
• A relationship diagram shows the relations in a database and their relationships through foreign keys.

Example: In MS Access

Note that: