3.2: Entities

The entity relationship (ER) data model has existed for over 35 years. It is well suited to data modelling for use with databases because it is fairly abstract and is easy to discuss and explain. ER models are readily translated to relations. ER models, also called an ER schema, are represented by ER diagrams.

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

Here is an example of how these two concepts might be combined in an ER data model: Prof. Ba (entity) teaches (relationship) the Database Systems course (entity).

For the rest of this chapter, 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. Important points to note include:

• 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.
• A department controls a number of projects, each of which has a unique name, a unique number and a budget.
• Each employee has a name, identification number, address, salary and birthdate. 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.
• We want to keep track of the dependents for each employee. Each dependent has a name, birthdate and relationship with the employee.

Entity, Entity Set and Entity Type

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
  • The Spouse table, in the COMPANY database, is a weak entity because its primary key is dependent on the Employee table. Without a corresponding employee record, the spouse record would not exist.

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

• Kernels are strong entities.
• A table without a foreign key or a table that contains a foreign key that can contain nulls is a strong entity

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. For example, in Figure \(\PageIndex{1}\), the entity type is EMPLOYEE.

Figure \(\PageIndex{1}\): ERD with entity type EMPLOYEE.

Existence dependency

An entity’s existence is dependent on the existence of the related entity. It is existence-dependent if it has a mandatory foreign key (i.e., a foreign key attribute that cannot be null). For example, in the COMPANY database, a Spouse entity is existence -dependent on the Employee entity.

Kinds 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.
• The foreign key identifies each associated table.
• There are three options for the primary key:
  1. Use a composite of foreign keys of associated tables if unique
  2. Use a composite of foreign keys and a qualifying column
  3. Create a new simple primary key

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.
• The foreign key is used to further identify the characterized table.
• Options for primary key are as follows:
  1. Use a composite of foreign key plus a qualifying column
  2. Create a new simple primary key. In the COMPANY database, these might include:
     • Employee (EID, Name, Address, Age, Salary) – EID is the simple primary key.
     • EmployeePhone (EID, Phone) – EID is part of a composite primary key. Here, EID is also a foreign key.