Entity-relationship model
A database visualisation model that describes interrelated things of interest in a specific domain of knowledge. Provides a detailed and logical representation of the data in a database.
# Terminologies
| Term | Definition |
|---|---|
| Simple/Atomic attribute | An attribute that cannot be further divided into smaller attributes |
| Composite attribute | An attribute that is made up of multiple components, each with an independent existence |
| Single-valued attribute | An attribute that holds only a single value for a single entity (e.g., name, gender, ID) |
| Multi-valued attribute | An attribute that holds multiple values for a single entity (e.g., phone numbers, qualifications) |
| Derived attribute | An attribute that represents a value derivable from the value of related attributes |
# Symbols
| Symbol | Representation |
|---|---|
| Rectangle | Entity (singular) |
| Concentric rectangle | Weak entity (singular) |
| Oval | Simple/Composite single-valued attribute; underlined attributes represent primary keys |
| Concentric oval | Single/Composite multi-valued attribute |
| Dotted oval | Derived attribute |
| Diamond | Relationship (verb) |
| Line | Representation |
|---|---|
| Single line | Partial/Optional participation |
| Double line | Total/Mandatory participation |
| Line with horseshoe | Subtype declaration |
# Relationships
# Degrees of relationships
Relationships can have different degrees to show in an ER model. Common degrees include the:
- unary (one) degree;
- binary (two) degree; and
- ternary (three) degree.
# Unary relationships
Represents the relationship between instances of a single entity. Also known as a recursive relationship.
- It is important to assign a role name that signifies the function a participating entity plays in each relationship; this allows you to distinguish the meaning of each participation
# Binary relationships
Represents the relationship between instances of two entities. Is the most common type of relationship encountered in data modelling.
# Ternary relationships
Represents the relationship among instances of three entities.
# Relationship constraints
Relationships can be constrained such that only certain cases are valid in a database. This helps to plan out a database in greater detail.
# Cardinality ratio
Refers to the number of instances of an entity that can be associated with an instance of another entity. The three most commonly-used cardinality ratios used in databases include:
- one-to-one (1:1) relationship;
- example: one staff member supervising one branch
- one-to-many (1:M) relationship; and
- example: one book publisher publishing many books
- many-to-many (M:N) relationship.
- the ‘N’ is used to differentiate the letters, but still means many. Can be ignored (don’t need to change)
- example: many students taking many subjects
# Participation
Specifies whether the existence of an entity depends upon it being another entity through the relationship. Shown in the ER diagram as different strokes of lines connecting entities and relationships.
Generally has two participation constraints:
- partial/optional participation, where instances of an entity are related to instances of another entity through the relationship, but not necessarily all; and
- total/mandatory participation, where instances of an entity must be associated with an instance of the related entity.
# Types
There are two types of entities that can be drawn in an ER model. These are akin to base and derived classes in object-oriented programming:
- Supertype — generic entity that can be divided into subtypes;
- Subtype — a subset of a supertype that shares common attributes (inheritance) or relationships distinct from other subsets.
- The subtype should not have a primary key, but rather inherit a primary key from its supertype.
The process of defiing a set of subtypes of an entity is known as specialisation.
The reverse process of abstraction, which involves:
- supressing differences among entities;
- identifying common features; and
- generalising the entities into a supertype;
is known as generalisation.
# Constraints
Similar to relationship constraints, there are constraints when it comes to specialisation and abstraction.
# Disjointment
An entity must only have one subtype or can have many subtypes according to the context of the situation. This is referred to as the disjoint constraint, in which an additional symbol (a circle with a character below) is added to denote the case.
When a specialisation consists of only one subtype, there is no need to show the disjoint constraint.
dis used to represent that an entity is disjoint, meaning that the entity can only be one subtype; andois used to represent that an entity overlaps, meaning that the entity can be more than one substype.
# Participation
Specifies whether the existence of an entity depends upon it being another entity through the relationship. Shown in the ER diagram as different strokes of lines connecting entities and relationships.
Generally has two participation constraints:
- partial/optional participation, where instances of an entity are related to a subtype, but not necessarily; and
- total/mandatory participation, where instances of an entity must be associated with a subtype.
# Rough outline of steps
- Identify the main entities in the user’s view of the enterprise.
- Identify the important relationships existing between the identified entities.
- Associate attributes with an appropriate entity or relationship.
- Identify the candidate keys for each entity before settling on a primary key.
- Identify supertypes and subtypes where appropriate.
- Draw the ER model for the database.
- Review the ER model.
- Reiterate where required.