Database — Modeling : Entity Relationship Diagram (ERD) (Part 5)

A common approach to sketch the entities and their relationships.

Omar Elgabry
Sep 14, 2016 · 5 min read

Let’s take an example of a company database

ERD of a company database — Images were taken from Fundamentals of Database Systems by Ramez Elmasri.

Wish you already came along the last part Database — Database Design: Conceptual Design (Part 4)

Entity Relationship Diagram

An entity relationship model, also called an entity-relationship (ER) diagram, is a graphical representation of entities (which will become your tables) and their relationships to each other.

Database modeling is the process of creating a data model.

Entity

A simple rectangular block represents a table.

Relationship

It’s sketched using the diamond shape.

Relationship

You can sketch the type of the relationship, whether one-to-many using “1-M” , or many-to-many using “M-N”, or one-to-one using “1–1”.

The lines connecting an entity with a relationship whether single or double line refer to another constrain (we talked about earlier) called “Existence Dependency Constraint” (also called “Participation Constraint”).

So, for example, if an employee must work for a department, this sketched by double lines, and called “total or mandatory participation”. And if an employee may or may not manage a department, this is sketched by a single line and called “partial or optional participation”.

A relationship itself can have attributes. In which table we will include these attributes will be discussed in Database Mapping in the next tutorial.

— Recursive Relationship

It’s a relationship with the the same entity. For example, an employee may act as a supervisor for one or more employees, while an employee may be supervised by an employee (it’s one-to-many relationship).

Recursive Relationship

Weak Entity

A weak entity is simply an entity where it’s existence depends on another entity. You can’t logically have dependent(son, daughter, ..etc.) with the absence of the employee table. It’s sketched same as a normal entity but wit double lines.

Weak Entity

— Partial Key

A weak entity has what’s called a “partial key”. It’s one or more attributes that uniquely identify a weak entity for a given owner entity. In our example, the dependent name is unique for every employee.

It’s sketched the same as a normal attribute, but, with dashed underline.

It’s called partial because it can’t be a primary key on it’s own, it needs another column, which is the foreign key of the owner entity. This will be discussed in Database Mapping in the next tutorial.

— Weak Entity Relationship

Also whenever there is a relationship between a weak entity and another entity, it’s sketched same as the one above, but with double lines.

Weak Entity Relationship

Attribute

An attribute or a column (simple) represents a piece of data in the table, like an address, salary, and date.

Attribute

Primary Key

It’s sketched the same as a normal attribute, but, with underline.

Primary Key

Composite Attribute

A value that composed of some other values, for example, you may have name that’s composed of ( first name, middle name, and last name).

Composite Attribute

We don’t define one column for name (if it’s composite), instead we split it up into simple separate columns; first name, last name. One reason is because you may do something different with the last name(or first name), and you want to get to it by itself. This will be discussed in Database Mapping in the next tutorial.

Multi-valued Attribute

Multiple values for a single column, for example the locations of a department may have more than one value at the same time.

Multi-Valued Attribute

Multi-valued attributes will be extracted in another table. This will be discussed later in Database Mapping.

Derived Attribute

When you have a column where it’s value could be calculated from another column. Thus, there is no need to insert it’s value every time. For example, we can know the number of employees work on a specific department by counting the number of rows.

Derived Attribute

This will be done automatically by the DBMS, and it will lead to having a consistent values rather than accidentally inserting a wrong value.

Symbols Summary

Here is a summary for all the symbols in the ERD.

Wrapping Up

Using the entity relationship diagram to sketch your database is a common way to visualize your tables, columns, and their relationship.

Now, It’s time to translate the entities, relationships, multi-valued attributes, into actual tables. Next is the “Logical Design (Data Model Mapping)”.

OmarElgabry's Blog

This is my freedom area.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store