Data Modelling Using Entity Relationship Model

ER model stands for an Entity Relationship Model. It is a high-level data model which is used to define the data elements and relationship for a specified system.

It develops a conceptual design for the database. In ER modeling, the database structure is portrayed as a diagram called an entity-relationship diagram.

ER modelling is based on two concepts:

Entities, defined as tables that hold specific information (data)
Relationships, defined as the associations or interactions between entities

Data Modeling Using The Entity Relationship Model

In this article, we will use a sample database called the COMPANY database to illustrate the concepts of the ER model. This database contains information about employees, departments and projects.

Key points to note include:

  1. There are several departments in the company. Each department has a unique identification, a name, location of the office and a particular employee who manages the department.
  2. A department controls a number of projects, each of which has a unique name, a unique number and a budget.
  3. Each employee has a name, identification number, address, salary and birth date. An employee is assigned to one department but can join in several projects. We need to record the start date of the employee in each project. We also need to know the direct supervisor of each employee.
  4. We want to keep track of the dependents for each employee. Each dependent has a name, birthdate and relationship with the employee.

What is Entity, Entity Set and Entity Type ?

An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as rectangles.

Consider an organization as an example- manager, product, employee, department etc. can be taken as an entity.

Entity er model

An entity is an object in the real world with an independent existence that can be differentiated from other objects. An entity might be;

  • An object with physical existence (e.g., a lecturer, a student, a car)
  • An object with conceptual existence (e.g., a course, a job, a position)

Entities can be classified based on their strength. An entity is considered weak if its tables are existence dependent.

  • That is, it cannot exist without a relationship with another entity
  • Its primary key is derived from the primary key of the parent entity

An entity is considered strong if it can exist apart from all of its related entities.

Another term to know is entity type which defines a collection of similar entities.

An entity set is a collection of entities of an entity type at a particular point of time. In an entity relationship diagram (ERD), an entity type is represented by a name in a box.

Types of Entities

You should also be familiar with different kinds of entities including independent entities, dependent entities and characteristic entities. These are described below.

Independent entities

Independent entities, also referred to as kernels, are the backbone of the database. They are what other tables are based on. Kernels have the following characteristics:

  • They are the building blocks of a database.
  • The primary key may be simple or composite.
  • The primary key is not a foreign key.
  • They do not depend on another entity for their existence.

If we refer back to our COMPANY database, examples of an independent entity include the Customer table, Employee table or Product table.

Dependent entities

Dependent entities, also referred to as derived entities, depend on other tables for their meaning. These entities have the following characteristics:

  • Dependent entities are used to connect two kernels together.
  • They are said to be existence dependent on two or more tables.
  • Many to many relationships become associative tables with at least two foreign keys.
  • They may contain other attributes

Characteristic entities

Characteristic entities provide more information about another table. These entities have the following characteristics:

  • They represent multivalued attributes.
  • They describe other entities.
  • They typically have a one to many relationship.

Attributes

Each entity is described by a set of attributes (e.g., Employee = (Name, Address, Birthdate (Age), Salary).

Each attribute has a name, and is associated with an entity and a domain of legal values. However, the information about attribute domain is not presented on the ERD.

In the entity relationship diagram, each attribute is represented by an oval with a name inside.

Attributes learnerscoach

Types of Attributes

There are a few types of attributes you need to be familiar with. Some of these are to be left as is, but some need to be adjusted to facilitate representation in the relational model.

Simple attributes
Simple attributes are those drawn from the atomic value domains; they are also called single-valued attributes. In the COMPANY database, an example of this would be: Name = {John} ; Age = {23}

Composite attributes
Composite attributes are those that consist of a hierarchy of attributes. Using our database example as shown in the figure below, Address may consist of Number, Street and Suburb. So this would be written as → Address = {59 + ‘Meek Street’ + ‘Kingsford’}

composite attribute

Multivalued attributes
Multivalued attributes are attributes that have a set of values for each entity. An example of a multivalued attribute from the COMPANY database, as seen in the below Figure, are the degrees of an employee: BSc, MIT, PhD.

Multivalued Attributes

Derived attributes
Derived attributes are attributes that contain values calculated from other attributes. An example of this can be seen in Figure below. Age can be derived from the attribute Birthdate. In this situation, Birthdate is called a stored attribute, which is physically saved to the database.

Derived Attribute

Keys

An important constraint on an entity is the key. The key is an attribute or a group of attributes whose values can be used to uniquely identify an individual entity in an entity set.

Types of Keys

There are several types of keys. These are described below.

Candidate key

A candidate key is a simple or composite key that is unique and minimal. It is unique because no two rows in a table may have the same value at any time. It is minimal because every column is necessary in order to attain uniqueness.

Composite key
A composite key is composed of two or more attributes, but it must be minimal.

Primary key
The primary key is a candidate key that is selected by the database designer to be used as an identifying mechanism for the whole entity set. It must uniquely identify tuples in a table and not be null. The primary key is indicated in the ER model by underlining the attribute.

Secondary key
A secondary key is an attribute used strictly for retrieval purposes (can be composite), for example: Phone and Last Name.

Alternate key
Alternate keys are all candidate keys not chosen as the primary key.

Foreign key
A foreign key (FK) is an attribute in a table that references the primary key in another table OR it can be null. Both foreign and primary keys must be of the same data type.

Relationships

Relationships are the glue that holds the tables together. They are used to connect related information between tables.

Relationship strength is based on how the primary key of a related entity is defined. A weak, or non-identifying, relationship exists if the primary key of the related entity does not contain a primary key component of the parent entity.

Types of Relationships

Below are descriptions of the various types of relationships.

One to many (1:M) relationship
A one to many (1:M) relationship should be the norm in any relational database design and is found in all relational database environments. For example, Scientist can invent many inventions, but the invention is done by the only specific scientist.

one to many relationships

One-to-One Relationship
When only one instance of an entity is associated with the relationship, then it is known as one to one relationship. For example, A female can marry to one male, and a male can marry to one female.

one to one relationship

Many-to-one relationship
When more than one instance of the entity on the left, and only one instance of an entity on the right associates with the relationship then it is known as a many-to-one relationship. For example, Student enrolls for only one course, but a course can have many students.

many to one relationship

Many-to-many relationship
When more than one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then it is known as a many-to-many relationship. For example, Employee can assign by many projects and project can have many employees.

many to many relationship

Leave a Comment