A modelling tool used in the design phase of the database development cycle. Objects/nodes are called entities, connected with attributes.
Entity Sets & Entities
An entity set is a blueprint to create an entity, i.e. every entity in an entity set has the same set of properties, known as attributes. For example, Dog, Cat are entities in the entity set Animal, since they have the same attributes (age, mass, etc.)
With respect to Object Oriented Programming, entities are like objects while entity sets are like classes
%%🖋 Edit in Excalidraw, and the dark exported image%%
Technicalities
Technically, in an ER diagram, we are actually referring to entity sets, not entities. However, most design conventions treat them as one and the same, when it comes to any designs. Hence, hereafter, any entity actually means entity set.
Key
Every entity set (and hence an entity in said set) must have a key, which is an underlined attribute
Attributes
| Attribute Type | Shape | Example | Visual |
|---|---|---|---|
| Standard | Ellipse | Age, Gender |  %%🖋 Edit in Excalidraw, and the dark exported image%% |
| Multi-valued | Outlined Ellipse | Phone Number |  %%🖋 Edit in Excalidraw, and the dark exported image%% |
| Composite | Ellipse connected to other ellipse | Address |  %%🖋 Edit in Excalidraw, and the dark exported image%% |
| Derived | Dotted Ellipse | Profit, (assuming revenue and expenses are known) |  %%🖋 Edit in Excalidraw, and the dark exported image%% |
Multi-valued Attribute
A multi-valued attribute is one that can have multiple values (must be finite) of the same attribute type.
For example, Phone Number can be a multi-valued attribute, to signify a home and a work phone number.
They are similar to using data structures in standard programming languages to store multiple variables.
%%🖋 Edit in Excalidraw, and the dark exported image%%
Composite Attribute
Essentially like a derived data type, can hold other attributes inside it.
A common example is address, which can store state, zip code, suburb and address lines.
%%🖋 Edit in Excalidraw, and the dark exported image%%
Derived Attributes
Attributes that can fully be constructed using other attributes. For example, given a birthDate, the attribute age can be derived by subtracting the current date from the birthDate (in years).
%%🖋 Edit in Excalidraw, and the dark exported image%%
Relationship
A relationship is an association with two or more entities. They can have attributes as well.
%%🖋 Edit in Excalidraw, and the dark exported image%%
Entity sets can be in a relationship with themselves and have different ‘roles’.
%%🖋 Edit in Excalidraw, and the dark exported image%%
Key Constraints
Key constraints define ‘mappings’ on entity relationships. Similar to how how functions can be one-to-one, many-to-many, one-to-many, key constraints define how many entities from each side of the relationship participate in it.
- One to one
- Many to many
- One to many
By default, a single line signifies a ‘many’ relationship, meaning a entity from the set can participate in multiple relationships.
%%🖋 Edit in Excalidraw, and the dark exported image%%
Keywords to look for:
- Key constraint: ‘Exactly one’
- Look to the entity to the left of the keyword to attach the key constraint to.
A professor must teach exactly one course
Entities are professor and course, relationship is ‘teaches’. Key constraint on professor
Participation Constraints
A constraint on whether every entity in a set must participate in the relationship. If every entity in the set does participate, it is a total participation. It is bold. Else, it is a partial participation
Keywords to look for:
- Total: ‘Each/Every’
- Partial: ‘But not every’, ‘Some’
Weak Entity
A weak entity is an entity that ‘depends’ on another entity. It can only uniquely be identified when considering the primary key of it’s dependent strong entity.
For weak entity to exist:
- The owner entity and weak entity must participate in a relationship where the weak entity has a one key constraint
- Weak entity must have total participation
The relationship is known as identifying, and is in bold.
%%🖋 Edit in Excalidraw, and the dark exported image%%
Weak entities have only a “partial key” (dashed underline) and they are identified uniquely only when considering the primary key of the owner entity