Tags:concept database erd ermodelling Status:🟩

ER Modeling

Summary

ER (Entity-Relationship) modeling is a visual representation of a database’s structure, using entities (objects or concepts), relationships (associations between entities), and attributes (properties of entities). The purpose of ER models is to simplify the design of complex databases by illustrating how data elements are connected.

Details

The Entity Relationship Model is one of the most popular models for conceptual data modelling. It consists of three building blocks:

Entity types.
Attribute types.
Relationship types.

EER is an extension to the basic ER model, which introduces three new semantic data modeling concepts:

Specialization/Generalization.
Categorization.
Aggregation.

Neither ER or EER is standardized, which means that there are no specific rules on how to visualization should look. However most have agreed on a specific way to do visualize it.

Entity Types

Entity types defines a collection of entities that have similar characteristics. It’s a definable thing or concept like shop, product, employee etc.

An entity is a particular instance of an entity type.

When building a conceptual data model like an ER model we focus on entity types and not specific entities.

In ER model, entity types are visualized using a rectangle:

Attribute Types

Attribute types are properties of entity types. An attribute type defines a collection of similar attributes like name, id, age etc. An attribute is an instance of an attribute type. There are different types of attribute types.

Key: When an attribute type is underlined it means it’s a primary key. ER diagrams cannot show secondary keys, and must be noted somewhere else.

Composite: A composite attribute type is just a grouping of multiple attribute types if there isn’t enough space. The composite attribute isn’t directly in the table, but just represents some other attributes that are related. Example instead of showing the attributes street, house number, city etc. you could instead just have a composite attribute type named address. All other attribute types are called single attribute types.

Multi-Valued: A multi-valued attribute type can hold multiple values. In database design, multi-valued attributes are often handled by creating a separate entity or table to store the multiple values. All other attribute types are single-valued.

Derived Attributes: A derived attribute type is an attribute type whose value is calculated or derived from other attributes rather than being stored directly. It can be made by either creating an attribute and maintaining it with a trigger or creating a view that computes it. If an entity type had the attribute types Date of Birth and we could get the current date, then a derived attribute type age could be calculated.

In ER model, attribute types are depicted using an ellipse:

Relationships

Relationships represents an association between two or more entities. A relationship type defines a set of relationships among instances of one or more entity types. It shows the relationship type between two entity types.

Degree refers to the number of entities involved in a relationship. A binary relationship involves two entities, a ternary relationship involves three, and so on. Role describes the function that an entity plays in a relationship, clarifying how it participates, especially when the same entity is involved multiple times. Like when an employee can take the role ‘supervisor’ and then another employee can be supervised.

Relationship attribute types

A relation can have attribute types as well. Lets say the tables employees and project have a relationship works_on. This relationship can then have an attribute type hours.

In ER model, relationship types are depicted using a rhombus: Here it shows relationship ‘works_on’ between the entity types ‘employee’ and ‘project’.

Cardinalities

Cardinalities in ER diagrams define the numerical relationships between entities, indicating how many instances of one entity can or must relate to instances of another. They describe the minimum and maximum number of entity occurrences involved in the relationship. Relationships always have cardinalities.

More about cardinalities: ER Modeling Relationship Cardinalities

Example:

A student is enrolled in 1 or more courses. So at least 1 course.
A student can be assigned to maximum 1 master thesis.
An employee can manage 0 or many projects. A project must be managed by exactly 1 employee.

Ternary relationships

Ternary relationships in ER diagrams involve three entities in a single relationship, capturing interactions between all three that can’t be modeled with binary relationships. Each entity has its own cardinality constraints. The problem is that is becomes less readable.

To read it you take 2 of the entities and read the relation.

An Instructor can offer many Courses during a Semester.
A Course offered in a given Semester must have at least one Instructor involved.
An Instructor can offer a given Course for many Semesters.

Basic relationship table in SQL DDL

CREATE TABLE WorkIn
	ProfID INT,
	DeptID CHAR(4),
	Since DATE NOT NULL,
	Status CHAR(10) NOT NULL,
	PRIMARY KEY (ProfID, DeptID),
	FOREIGN KEY (ProfID) REFERENCES Professor (ID),
	FOREIGN KEY (DeptID) REFERENCES Department (ID)
 
-- Or inline referencing
	ProfID INT REFERENCES Professor (ID),
	DeptID CHAR(4) REFERENCES Department (ID)

Weak Entity Types

A strong entity is an entity that has a key attribute type. A weak entity type is an entity type that does not have a key attribute type of its own. Entities belonging to a weak entity type are defined by being related to specific entities from the owner entity type, which is an entity type from which they borrow an attribute type. The burrows attribute type is combined with some of the weak entity’s own attribute types (partial keys) into a key attribute type.

In ER Model, weak entity types are represented using a double-lined rectangle. The Room Number (RNR) cant stand alone, since there could be other hotels with the same RNR. RNR does not suffice as a key attribute type. Hence it has to borrow the Hotel Room Number to come up with a key attribute type.

An Owner Entity type is an entity type from which an attribute type is borrowed by a weak entity type.

Extended Entity-Relationship Model (EER)

This is an extension of the normal ER model. Includes all the modeling concepts of the ER model (entity types, attribute types, relationship types), as well as three new additional semantic data modeling concepts:

specialization/generalization.
categorization.
aggregation.

Specialization / Generalization

Generalization abstracts shared features into a single entity, while Specialization divides an entity into sub-entities with unique attributes in an EER diagrams.

Partial: Not all parent entity instances need to be in a sub-entity
- Is visualized by a ‘p’ on the line.
Total: Every parent entity instance must be in a sub-entity.
- Is visualized by a ‘t’ on the line.
Overlap: An instance can be in more than one sub-entity.
- Is visualized by an ‘o’ in the circle.
Disjoint: An instance can only be in one sub-entity.
- Is visualized by a ‘d’ in the circle.

The arcs are there to visualize that the entities ‘inherit’ from the parent entity.

A person can be specialized into a professor or student. A professor or student can be generalized to a person.

Partial: An artist can exist in the database without being specialized. Total: A person has to be either a professor or a student. Overlap: An artist can be both a singer and actor. Disjoint: A person only be either a professor or student.

Categorization

Categorization in EER diagrams allows a subclass to be linked to multiple superclasses, each representing different entity types. The subclass includes entities that are a subset of the combined superclasses, sharing certain attributes or relationships with them. This helps organize entities that fit into several categories but don’t fully belong to just one.

Total categorization: All entities of the superclasses belong to the subclass. Partial categorization: Not all entities of the superclasses belong to the subclass.

It is represented using ‘u’ in the EER model. Notice the direction of the arc in the union. It refers to the flow of inheritance.

Every person or company doesn’t have to be account holder. This is partial categorization.

Relationship Aggregation

Sometimes we need a relationship to a relationship. Aggregations allows us to “convert” relationship types to entity types. It can be complicated to draw, so instead is visualized like this:

Requirements for ER Diagram

Nouns represent entities.
- Descriptive elements of entities are called attributes.
Verbs represent relationships between entities.
- Descriptive elements of relationships are also called attributes.
- Pay attention to words that suggest participation constraints (e.g., “must” or “can”).
- If no constraints are mentioned, assume the cardinality is 0..N (optional, many).
Example:
- Professors have an SSN, a name, an age, a rank, and a research specialty.
- Projects have a project number, a sponsor name (e.g., NSF), a starting date, …
- Each project is managed by one professor (1..1 on professor, 0..N on projects).
- Professors may work on many projects (0..* on both sides).
- Each project must be reviewed by some professors (1..N on professor, 0..N on projects).

Limitations of ER Diagram

ER diagrams do not capture all design details
- Example: Multiple candidate keys
- Must note missing details somewhere!
Some aspects do not map well to SQL DDL
- Example: 1..M cardinalities
- Triggers can be used to handle some problems
- Normalization provides a mechanism for fixing some problems
- Some must simply be noted and addressed in code or ignored!

INDBS Noter

Explorer