The Entity Relationship Model

Some Definitions top of page

As discussed earlier, the first step in the database design process is to identify the organisations data requirements.  Once the data requirements have been identified, the next step is to model the data as a conceptual schema for the database.  The Entity Relationship Model (ERM) was first described by Chen in 1976, and is widely accepted as the standard for conceptual data modelling in the relational database environment.  The ERM is a detailed, logical representation of the entities, associations and data elements for an organisation.  Because conceptual models are only concerned with what is represented in the database, the ERM does not include components to represent how the model is implemented.

The Entity Relationship Diagram (ERD) is a graphical representation of an ERM and is currently one of the most popular and widely used database design tools.

Components of the ER Model top of page

The three main components of the ER Model are entities, attributes and relationships.

In ERM terms, an entity is a "thing" within the organisation, that we want to keep information about, such as a customer, employee or course.  In other words, an entity in an ERM actually refers to a table, and rows within the table are referred to as entity occurrences.

Entities are represented by rectangles containing the name of the entity.  Entity names must be singular and in capital letters.

Each entity has attributes which are the properties of each entity.  Attributes will be implemented as columns in the tables.  Each attribute has a domain which specifies the set of possible values an attribute can have.  For instance, the range of values for a telephone extension may be specified as a set of integer numbers between 4000 and 4999.  An attributes domain is not displayed in ER diagrams, but is recorded in the data dictionary.

Attributes are represented by ovals with lines connecting them to the entity.

Attributes can be of various types.  A composite attribute can be subdivided into smaller parts.  For example, an attribute Name can be subdivided into First Name and Last Name.  Attributes that cannot be subdivided are called simple attributes.   First Name and Last Name are now simple attributes.

Most attributes have only a single value and as such are called single valued attributes.   For example, a Teacher can have only one Last Name or a Subject can have only one Subject Code.  Multivalued attributes can have more than one value.  For example, a Student could have more than one Certificate or a Department may have several Extensions.

There are a number of diagramming conventions used to represent multivalued attributes.  Some conventions implement the multivalued attribute as an oval with a double line around it.  Others use a double bar to connect the attribute to the entity.  Use either method in your own diagramming.

A key attribute is an attribute that has a unique value for each entity occurrence.  In other words, a key attribute is used to identify each row uniquely.  For example, a Subject Code will uniquely identify each subject as no two subjects can have the same Subject Code.

Key attributes are represented by underlining its name.

A relationship is the association between entities or entity occurrences.  For example, a Teacher teaches Subjects or a Subject has Offerings.  You will look at relationships in more detail in the next section [ER Model Relationships].

Relationships are represented by diamonds with straight lines connecting the participating entities.

Diagrams for ER Components top of page

ER Diagram Components