User Tools

Site Tools


Entity-Relationship Model

The entity-relationship model (ER model) is a useful technique for the design of databases. When the ER model is finished, the tables and relationships can be created with SQL. Good database developer could understand a complex ER model very fast, so they can build the database in a short time. (1)

Elements (2)

The entity-relationship model consists of different elements: entities, attributes and relationships.


Entities are objects, that can be identify very easy. It is not important, if it is a thing or a creature.

For example: pilot, airplane, airport


These entities have different attributes which describe their characteristics. For example: name, birth date, address, email address etc. for the entity pilot

It is necessary, that every attribute have a matching value. The name can not be a numeric value, it must be an alphabetic value. The birth date can not be an alphabetic value etc. There are different types of attributes:

  • Simple attribute (i.e. phone number)
  • Composite attribute (i.e. first name and last name)
  • Derived attribute (i.e. age)
  • Single-value attribute (i.e. SocialSecurityNumber)
  • Multi-value attribute (i.e. email address)

Each entity must have a primary key. It is a key, that uniquely identify the entity.

For example: pilot_number or airplane_number


The connection between a entity and another entity is called a relationship.

For example: A pilot flies an airplane. An airplane lands at the airport.

In this context the relationship needs a cardinality. It defines the number of entities, which can be associated with the other entity.

There are different types of cardinalities:

  • One-to-one (i.e. one pilot flies one airplane.)
  • One-to-many (i.e. one pilot flies many airplanes.)
  • Many-to-one (i.e. many pilots fly one airplane.)
  • Many-to-many (i.e. many pilots fly many airplanes.)

Which cardinality is the best, depends on the requirements of the system.


A teacher has a number, first name, last name, birthday, phone number, room and an email address. He only teach one student. But every student could occupies different courses. The student is characterized by a number, name, age, address and an email address. The course has a number, name and a room.

In the first step the entities must be identified. In this case the entities are the teacher, student and the course.

In the next step the attributes of each entity must be added. Teacher_nr, first_name, last_name, birth_day, phone_number, room and email_address are the attributes of a teacher. Then it is important to chose a primary key. The primary key is the teacher_nr, because it is unique. So it can exactly identify each teacher. The other attributes could be add to the other two entities with the same method.

After that step, the relationships between the entities must be set. The teacher teaches the student and the student occupies the course.

The last step is to identify the cardinalities. For this example the cardinalities are good describe in the text.

  • Teacher teaches student 1:1
  • Student occupies course n:n

In the end the ER model for this example should look like the following picture:

(1) cf. 26-05-2016

(2) cf. 26-05-2016

concepts/entity-relationship-model.txt · Last modified: 2020/08/20 13:27 (external edit)