Skip to main content

ER Models

ER Models provide a detailed and logical representation of the data of an organisation or business areas

Expressed in terms of Entities of business environment, Relationships among these entities. and the Attributes of both entities and relationships.

Entity​

A person, place, object, event or concept in the organisation or business area to be modelled.

Notation

Represented by a rectangular box.

Weak Entity​

  • Does not have any key attribute of its own.
  • Can only be identified by relating it to an owner entity.
    • Entity instances belonging to a weak entity are identified by being related to specific entity instances from the owner entity, in combination with some attribute values of the weak entity.
    • A weak entity always has a total participation constraint with its owner entity.
Notation

Weak entity is represented by a double-line box.
Identifying relationship is represented by a double-line diamond.
Partial key is underlined with a dashed line.

Partial Key​

A weak entity normally has a Partial Key, which is an attribute or a set of attributes that can uniquely identify weak entitiesrelated to the same owner.

Example: For each Book(Owner entity), there cannot be the same BookCopyNo(Partial Key) in BookCopy(Weak entity)

The Primary Key of the owner entity is used as part of the weak entity.

Example: The primary key of BookCopy becomes ISBN, CopyNo

Weak entities can have more than one owner entities.

Regular Entity​

Any entity that is not a weak entity is a regular entity.

Attribute​

A property or characteristic of an entity that is of interest to the organisation or business area concerned.

Notation

Represented by an oval.

An attribute can be classified by the component(s) it has:

An attribute can be classified by the value it holds:

Simple Attribute​

Composed of a single component with an independent existence.

Simple attributes cannot be further divided and are sometimes called atomic attributes.

  • Gender, salary, colour

Composite Attribute​

Composed of multiple components, each with an independent existence.

  • Address attribute: BlkNo, HouseNo, StreetName, PostalCode

Single-Valued Attribute​

Holds a single value for a single entity.

  • Member entity: MemberID(Primary Key), Name, Gender

Multi-Valued Attribute​

Holds multiple values for a single entity.

  • Salesperson entity: ContactNo(Mobile1, Mobile2, Office), Qualifications(GCEOLevels, Diploma, Degree)
Notation

Represented by a double oval.

Derived Attribute​

Represents a value that is derivable from the value of a related attrbute or set of attributes, not necessarily in the same entity.

  • Age can be derived from DateOfBirth
  • NumberOfEmployees working for a department can be derived by counting the number of employees that work for that department.
Notation

Represented by a dotted oval

Candidate Key Attribute​

Every entity type must have an attribute or a set of attributes that uniquely identifies each entity instance.

  • This key or uniqueness constraint prohibits any two instances from having the same value for the key attribute simultaneously.
  • Some entity types have more than one key attribute.

Examples:

  • Each Staff entity has a unique StaffID
  • Each Branch entity has a unique BranchNo
  • Each BookCopy entity is uniquely by its ISBN and BookCopy together.
Criteria for selecting primary key
  • Choose a candidate key that will not change its value over the life of each instance of the entity type.
  • Choose a candidate key such that for each instance of the entity type, the attribute is guaranteed to have a valid value and not be NULL.
  • Avoid use of so-called intelligent keys, whose structure indicates classifications, locations and so on.
  • Consider substituting a single-attribute surrogate key for a large composite key.

Surrogate Key​

A new attribute that is specifically introduced into an entity to serve as a key to uniquely identify each instance.

Guidelines for selecting a surrogate key:

  • If we cannot select a natural identifier that can gurantee uniqueness of each instance.
  • If the primary key is a large composite key that consists of a number of attributes.

Example: LoanNo

Relationship​

A relationship is a set of associations between two or more participating entities.

  • Publisher(entity) publishes(relationship) many Books(entity)
  • Branch(entity) registers(relationship) many Members(entity)
  • Each Member(Entity) makes(relationship) many Loans(entity)
Notation

Represented by a diamond-shaped box and is connected to the entities that are related through this relationship.

Degree of a Relationship​

Refers to the number of entities that participate in that relaipnship.

Unary Relationship​

Represents the relationship between instances of ONE entity.

  • Also known as recursive relationship.
Important

ROLE NAME is used to signify the role or function that a participating entity plays in each relationship. It is essential for distinguishing the meaning of each participation.

Binary Relationship​

Represents the relationship between instances of TWO entities.

  • The most common type of relationship encountered in data modelling.

Ternary Relationship​

Represents the simultaneous relationship among instances of THREE entities.

Constraints on Relationship​

Relationships usually have certain constraints that limit possible combinations of entities that may participate in relationship instances.

2 main types of relationship constraints:

  • Cardinality Ratio
  • Participation Constraint

Cardinality Ratio​

Refers to the number of instances of entity B that can be associated with each instance of entity A.

  • Determines the number of possible relationships for each participating entity.
  • Most common cardinality ratio for binary relationships are one-to-one (1:1), one-to-many (1:M), and many-to-many (M:M).
One-to-One​
One-to-Many​
Many-to-Many​

Participation Constraint​

Specifies whether the existence of an entity depends upon it being related to another entity through the relationship.

2 types of participation constraint

  • Partial Participation
  • Total Participation
Partial Participation​

Specifies that some of the instances of an entity are related to instances of another entity through this relationship, but not necessarily all.

  • Also known as Optional Participation.
  • Student Ryan does not take any subject.
  • Subject History is not taken by any student.
Notation

A single line represents Partial/Optional Participation.

Total Participation​

Specifies that every instance of an entity must be associated with an instance of the related entity.

  • Also known as Mandatory Participation.
Notation

A double line drawn next to the entity represents Total/Mandatory Participation of the entity.