Entity–relationship model (ER model)

ER models describe interrelated things of interest in a specific domain of knowledge.

This is actually the most fundamental knowledge you should know for designing a Database.

Methodology to Design ER Model

1. Recognize entity sets
2. Recognize relationship sets and participating entity sets
3. Recognize attributes of entity and relationship sets
4. Define relationship types and existence dependencies
5. Define general cardinality constraints, keys and discriminators

For each step, update the ER diagram and maintain a log of assumptions motivating the choices, and of restrictions imposed by the choices.

See page 46-49 of module 7 to see a walkthrough example, that has been the most helpful.

Notes

Entity

ER Modeling: Entities

Entity: A distinguishable thing. Entity Set: A set of entities of the same variety.

Entities are represented by a rectangle shape

Examples of entity sets:

1. students currently at University of Waterloo
2. flights offered by Air Canada
3. burglaries in Ontario during 1994

Attribute

ER Modeling: Attributes

Attribute: Captures a concrete fact about an entity.

Domain: A set of possible values for an attribute.

Examples of attributes for entities that are students:

1. student number
2. student name
3. major

Attributes are represented by an oval shape (with an edge to the entity)

Relationship

ER Modeling: Relationships

Relationship: Captures the existence of an association between two or more entities.

Relationship Set: A set of relationships of the same variety.

Relationships are represented graphically by a diamond shape

Examples of relationship sets:

1. students registered in courses
2. bank branches, customers and their accounts
3. passengers booked on flights
4. parents and their children

Dealing with Multiple Entity Roles and Role Names

Role: The purpose served by a particular entity in a relationship. Role Name: An identifier indicative of this purpose.

Examples of role names:

• A match takes place between a home team and a visitor.

Primary Key

Primary Key

Primary Key: A selection of attributes for an entity set for which facts serve as the means of reference to its entities.

Primary keys are represented graphically via underlining

Examples:

1. departments are identified by their department number
2. employees are identified by their first name, middle initial and last name

Existence Dependencies

Sometimes the existence of an entity depends on the existence of another entity. If $x$ is existence dependent on $y$, then

1. $y$ is a dominant entity, and
2. $x$ is a subordinate entity.

Dependencies are represented graphically via double boundaries on an entity set

Identifying Subordinate Entities

Subordinate Entities

Weak Entity Set: An entity set containing subordinate entities.

Discriminator: A selection of attributes for a weak entity set for which facts serve as the means of distinguishing subordinate entities for any given dominant entity.

The discriminators are represented graphically via dashed underlining of attributes

Example:

• Each transaction for a given account has a unique transaction number.

Confusion

I’ll be honest, this stuff is really confusing. So when it’s a many-to-1 relationship, like ask yourself does this thing depend on the existence on another thing? Or does it exist on its own. If former, then it’s a subordinate entity.

Think about placing orders. Does an order “depend” on the existence of items and customers? Technically yes, but the definition for a weak entity set is that it must be in a (N:1) relationship. In this case, it probably depends on the customer. But maybe there can be multiple customers for the same order?

• probs not lol, so the order would a subordinate entity of the customer

In the assignment case, I also make the model part of the identifying relationship, since you can only have one type of item per order. See CS348 A4 Written.

General Cardinality Constraints

General Cardinality Constraint

A general cardinality constraint determines lower and upper bounds on the number of relationships of a given relationship set in which a component entity must participate.

General Cardinality Constraints are annotated graphically via component edge labelling.

Example:

• Students must take between 3 and 5 courses; courses must have between 6 and 100 students taking them.

Tip

The way I think about this is that if you look at the relationship table, $(x,y)$ means that you expect at least $x$ entries for the given id of this entity, and $y$ is the maximum of entries for the give id of this entity.

Binary Relationships

The CS348 pdf from 2019 is super clutch, see p.42. Types of Binary Relationships:

• many-to-many (N:N): An entity in one entity set can be related to any number of entities in the other, and vice-versa

• many-to-one (N:1): Each entity in one entity set can be related to at most one entity in the other entity set, but not vice-versa.

• one-to-many (1:N): Inverse of many-to-one.

• one-to-one (1:1): Each entity in one entity set can be related to at most one entity in the other, and the same holds for the converse.

Binary relationships are annotated graphically via arrowheads.

Examples:

1. Employees work in at most one department.
2. Employees manage at most one department, and departments are managed by at most one employee.

I don't really understand the arrows still...?

Emre explained this: The arrowhead indicates the multiplicity of the entity’s participation in the relationship and is syntactic sugar for (0, 1). See the added diagrams above.

In the example below,

• the arrowhead on the TaughtBy relationship in the diagram indicates that a Section entity may participate in the TaughtBy relationship at most once.
  • In English, that means a section is taught by one professor or no professors.

Extended Entity-Relationship Model (EER)

These are more complex things that we can include in ER diagrams.

Aggregation

Aggregation

Aggregation: A relationship set can be aggregated to enable its relationships to be higher-level entities that can in turn participate in other relationships.

Example:

• Accounts are assigned to a given student enrolment

Represented by surrounding with a rectangle

Specialization

Specialization

Specialization: An integrity constraint asserting that the entities of one entity set are also entities of another entity set.

Example:

• Graduate students are students who have a supervisor and a number of degrees.

Generalization

Generalization

Generalization: An integrity constraint asserting that entities of one entity set are also entities of at least one of two or more other entity sets.

Example:

• A vehicle is also either a car or a truck.

Specialization vs. generalization?

They seem like the same thing to me. What is the difference? In the example provided, I guess you can think of it like Inheritance right. In specialization, we have that class Graduate: public Student, whereas in generalization, there’s no idea of inheritance?

The difference in the direction of the process. In specialization, you’re going from a general class to more specific classes. In generalization, you’re going from several specific classes to a more general class. UMM the arrow’s direction is the same though…

Use the “overlaps” keyword if the entity can be both.

Look at the slides below, which help you understand the logic:

• So generalization is more strict

Disjointness

Disjointness: Two entity sets participating in a generalization are assumed to be disjoint by default. This can be overridden by a graphical annotation on a generalization.

Done using the “OVERLAPS” instead of “COVERS” keyword.

Making Design Decisions

This is SUPER important!!!

• Do you choose to make it an attribute, or an entire entity set?
• Do you choose to make it an entity set, or a relationship set?

See slides below.

Translation into SQL Code

This is also super important.

Representing Entity Sets

Representing Weak Entity Sets

Weak entity set E maps to a new table E. Columns of table E should include:

1. attributes of the weak entity set,
2. attributes of the identifying relationship set, and
3. primary key attributes of entity set for dominating entities.

⇒ (primary key of weak entity set) → (primary key of table E)

Representing Relationship Sets

A relationship set may map to either new table columns for existing tables of entity sets or to a new table.

• If the relationship set is an identifying relationship set for a weak entity set then no action needed.

• If we can deduce the general cardinality constraint (1,1) for a component entity set E then add following columns to table E:

  1. attributes of the relationship set, and
  2. primary key attributes of remaining component entity sets.

• Otherwise, relationship set R translates to a new table R

• Columns of table R should include:

  1. attributes of the relationship set, and
  2. primary key attributes of each component entity set.

• Primary key of table R determined as follows:

• If we can deduce the general cardinality constraint (0,1) for a component entity set E, then take the primary key attributes for E.

• Otherwise, choose primary key attributes of each component entity.

Representing Aggregation

Always map a relationship set R that is aggregated to a new table R. ⇒ (tabular representation of aggregation of R) = (tabular representation for relationship set R)

• To represent a relationship set involving the aggregation of R, treat the aggregation like an entity set whose primary key is the primary key of the table for R.

Example

This is how you would translate this ER Diagram into code: