ERD Modeling with Barker's Notation

ERD Modeling Introduction

• This document serves as a guide to Entity Relationship Modeling using Barker's notation.
• The document is intended for database designers, educators, and learners.
• The intention is to portray a method and approach of explaining topics while showcasing an understanding of such topics.

Modeling

• A data model is a graphical representation of the solution to a problem as adopted by a particular community.
• Different communities trying to resolve the same problem can produce different yet valid solutions.
• An Entity Relationship Diagram is a logical diagram representing the database structure using the relational model.
• Barker's notation is adopted.

ERD Rules

• Capture all required information.
• Information appears only once.
• Model no information that is derivable from other information already modelled.
• Information is in a predictable, logical place.

ERD Concepts

• ERDs use three concepts: Entities, Attributes and Relationships

Entities

• An entity is an object that a community feels is important to include.
• Other communities trying to solve the same problem might depreciate the entity to an attribute or omit it.
• An entity would transmit to a table in a database.
• An entity instance is a record within a table.
• For example, in a CD database, entities such as CD are expected.

Attributes

– An attribute is a property of an entity describing instance characteristics.

• Attributes can be:
• Unique Identifier (UID): uniquely identifies an entity instance and is implemented as a Primary Key.
• Mandatory Attribute: must have a value (cannot be null).
• Optional Attribute: value can be null.

Relationships

• A relationship links two or more entity instances together.
• A relationship is implemented as a Foreign Key and therefore the FK attribute is not listed as an attribute in the target entity.

Drawing Entities

• An entity is represented by a rounded corner rectangle.
• The entity name is placed inside the rectangle at the top.
• Entity names should be in upper case and in singular form.

Drawing Attributes

• Attributes should be written in a way that is understandable and meaningful.
• The first letter of each word in an attribute should be in upper case with the rest in lower case.
• A symbol represents the type of attribute.

Drawing Relationships

• A relationship exists between a maximum of two entities.
• A relationship can exist on the same entity.
• A relationship has two perspectives that must be labelled.
• When drawing a relationship:
• Determine the entities affected.
• Determine the optionality.
• Determine the degree.
• Label the perspectives.

Optionality of Relationships

• Mandatory Relationship: Each instance from an entity must be related to another instance; represented by a straight line.
• Optional Relationship: Each instance from an entity may be related to another instance; represented by a dashed line.

Relationship Perspectives

• A relationship is always made up of two perspectives using the following notation:
• First Perspective / A Perspective: Each A must label a one or more Bs
• Second Perspective / B Perspective: Each B may label b exactly one A.

Labelling Relationships

• A label is applied for each perspective, and should be a verb in lower case form.
• The label is positioned according to the diagram shown, and should be relevant to the scenario.

Degree of a Relationship

• A relationship can be one of three types:
• One-to-One Relationship (1:1): Each entity instance is related to just one entity instance.
• One-to-Many Relationship (1:M): Each entity instance is related to multiple entity instances.
• Many-to-Many Relationship (M:M): This type of relationship is never required and needs to be resolved.

Steps in Resolving Many-to-Many Relationships

• Create a third/intermediary/transactional entity (AB).
• Add two one-to-many relationships from the original entities to the new entity (A to AB, B to AB).
• The optionality from the AB perspective should always be mandatory.
• The optionality of the remaining perspectives is derived from the original many-to-many relationship.
• UID Bars are added to the AB perspectives, indicating that the foreign keys are also primary keys.

UID Bar concept

• Foreign key is also part of a primary key, generally a composite key.
• To solve this issue, add the UID Bar to the ORDER ITEM perspectives.
• Each individual UID Bar represents the fact that the foreign key represented by the relationship is also a primary key of the entity to which the perspective belongs.

Recursive Relationships

• A relationship acts upon the same entity
• This type of relationship is called a recursive relationship and is generally implemented by having a foreign key referring to the primary key of the same entity.

Redundant Relationships

• The information represented by a relationship can be derived from other information already represented in an ERD.
• This relationship is called a redundant relationship and needs to be removed.

UID Attributes

• There are four types of Unique Identifier attributes which are implemented as primary keys:
• Single UID Attribute: An entity is made up of only one UID attribute which is not a foreign key.
• Multiple UID Attribute: A primary key is made up of multiple attributes (composite primary key) all of which are non-foreign keys.
• Composed UID Attribute: An entity has a primary key which is also a foreign key. These are marked with a UID Bar.
• Composed Cascade UID Attribute: An entity uses its foreign keys as primary keys from an entity with composed UID attributes.

Entity Subtypes

• Different entities are grouped together under one common entity called a super type entity whilst the nested entities would be the subtype entities.

Entity Subtypes Rules

• Exhaustive Rule: Every entity instance of the super type must be an instance of one of the subtypes.
• Mutually Exclusive Rule: Every entity instance of the super type can be an instance of only one entity subtype and not the other.

Drawing subtypes guidelines

• Each subtype is a specialisation of a super type and therefore must be enclosed within an entity.
• The common attributes and relationships for all subtypes must be listed in the super type only but are inherited in every subtype.
• A subtype can and would generally have attributes and relationships of its own.
• There can never be just one subtype; another subtype should be created to cater for the rest.
• If none of the subtypes have any unique attributes or relationships then it is not recommended to make use of subtypes but rather create a separate entity containing a list of the various types required.

Implementing a subtype

• There are various methods of implementing a subtype but the preferred method is as follows:
• Create a table for the super type with all common attributes and relationships.
• Create a separate table for each subtype.
• In the subtypes include the specific attributes and relationships.
• Create a foreign key in the super type for each subtype.
• Impose a constraint that only one foreign key may not be null.
• Impose a constraint that at least one foreign key must not be null.

Exclusive Relationship Arc

• An entity is either related to one entity or to another but not both. Whenever a selection needs to be done between relationships, an exclusive relationship arc is created.

Rules of the exclusive relationship arc

• A relationship arc may be applied to only one entity, called the target entity.
• The relationship arc must be applied to a minimum number of two relationships.
• The target entity will contain the foreign keys of the relationships affected by the arc.
• The optionality of the relationships affected by the arc must be the same from the perspective of the target entity.
• The optionality of the relationships affected by the arc may be different from the perspectives of the source entities.
• The relationships affect by the arc may be have a different cardinality (one-to-one, one-to-many).

Drawing an exclusive relationship arc

• To design an exclusive relationship arc the following steps need to be taken:
• Determine the owner of the relationship arc.
• Draw an arc across the relationships to be affect and around the entity that owns the arc.
• Circle the crossed relationships that are to be affected.

Implementing an exclusive relationship arc

• To implement an exclusive relationship arc the following steps need to be done:
• Create a foreign key for each relationship affected by the arc. The foreign keys must be placed in the entity to which the arc belongs to.
• Implement a constraint that allows only one foreign key value not to be null.

Non-Transferability of Relationships

• In certain situations once a relationship is set it can never change. It is called the non-transferability of a relationship.

Drawing Non-Transferability

• When drawing the non-transferability of a relationship the following rules need to be respected:
• The diamond symbol should be on the perspective where the foreign key will be implemented.
• The non-transferability symbol should be displayed only once per relationship provided that any many-to-many relationships have been resolved.

Implementation of Non-Transferability

• When implementing the non-transferability of a relationship a constraint is applied to the foreign key which does not allow the values to be updated.