ER Diagrams vs. ER Models vs. Relational Schemas

Introduction

It's crucial to choose the correct model for your data before starting a project. Over the years, developers coined terms to describe the various systems they use to perform this vital planning. This article will explain the differences between three different planning systems: ER models, ER diagrams, and relational schemas. We'll learn important terminology, compare and contrast these three concepts, also look at some examples and considerations for each.

ER Models and ER Diagrams

"ER model" is short for "entity-relationship model," a high-level data model. As the name suggests, a data model depicts how a database is structured at a logical and visual level. In layman's terms, an ER model defines the elements and their relationship in a specific database. It's like a "blueprint" to your database, providing a simple view of the data.

Key Insight: ER Models deal with single entities like a Student while ER Diagrams deal with sets of entities like a Table of Students

In comparison, we most often hear the term "ER diagram" when talking about databases. However, "ER model" and "ER diagram" are not mutually exclusive. The latter can be seen as a subset of the former. We use ER diagrams to convey our idea of an ER model.

Relational Schemas

A relational schema is also a data model representing a database structure logically. A relational schema uses tables to show a relationship between two or more entities.

Each table in a relational schema is referred to as a relation. Rows in the table are called tuples, whereas the table columns are attributes. Tuples can be seen as the instances of an entity. A table can have many instances. For example, a school can have several students enrolled simultaneously. If student is an entity, then each student's record will be a tuple of that table (or relation).

Similarly, a table can have multiple columns or attributes. Following the same example, each student will have attributes such as a name, student ID, or email address. Each attribute also has a domain that specifies or limits the value an attribute can take. For instance, if student ID is a number, we can restrict the domain of our database so that it does not accept alphabetic or special characters for this value. Like ER models, keys also play a significant role in relational schemas. They help in differentiating between multiple instances within a table.

There are mainly two types of keys:

1. Primary key: A primary key is an identification attribute of each instance within a table. For the same reason, it can not have null or duplicate values. For example, student_id can be a primary key since it's unique to every student. For more information on ensuring keys are unique, check out this article.
2. Foreign key: A foreign key links two tables in a relational model. It refers to a field in a table that is the primary key of another table. It can be a single attribute or a set of attributes.

The following diagram illustrates the concepts mentioned above in a relational schema via two tables in an educational setting:

COURSE

ER Models and Relational Schemas

A relational schema (or model) resembles an actual database model of the information stored in the database. The terminology is a critical difference between an ER model and a relational schema. For example, an ER model deals with entities and their relationship, whereas a relational schema talks about tuples and attributes.

Moreover, an ER model may be easier to understand than a relational schema because we map the cardinalities explicitly (one-to-one, many-to-one, etc.). In contrast, the relationship between two tables in a relational schema is more subtle. You have to figure out the foreign and primary keys and their overall connection to really understand a schema.

Constructing an ER Diagram

Let's construct an example ER diagram to lock in what we've learned so far. However, before we construct it, it is vital to know the basic terminology we'll use in this article:

Entity

An entity can be defined as any real-world object such as a student or an employee. It is usually represented as a rectangular box in an ER diagram.

Attribute

Attributes are an entity's characteristics, such as a student's roll number or an employee's ID. Attributes are represented by ellipses connected to the rectangular entity box. There are several types of attributes:

• Key attribute: The main attribute with which we can identify different instances of the same entity. For example, a student ID is unique to every student and thus can be thought of as a key attribute. To represent a key attribute in an ER diagram, we usually underline the attribute name inside the ellipse.
• Composite attribute: An attribute that consists of two or more sub-attributes is called a composite attribute. For instance, a student's name is an attribute that can be sub-divided into first name, middle name, and last name. A composite attribute is denoted by an ellipse further connected to other ellipses.
• Multivalued attribute: An attribute that can take more than one value is referred to as a multivalued attribute, as in the case of a student with multiple phone numbers. A multivalued attribute is represented with two concentric ellipses.
• Derived attribute: An attribute whose value can be derived from another attribute is called a derived attribute. For example, age can be found from a person's birthdate by subtracting it from the current date. Therefore, age is a derived attribute. A dashed ellipse denotes these attributes.

Relationship

A relationship is a connection between two entities, such as a student who goes to a school or an employee who works for a company. In the first case, "student" and "school" are two different entities linked by a relationship. Similarly, in the latter case, "employee" and "company" are two entities, and the relationship is "works for." There are mainly four kinds of relationships:

1. One-to-One (1:1): A "one-to-one" relationship is seen when one instance of entity 1 is related to only one instance of entity 2 and vice-versa. For example, one country can have only one capital, and each capital belongs to only one country.
2. One-to-Many (1:M): When one instance of entity 1 is related to more than one instance of entity 2, the relationship is referred to as "one-to-many." For example, an employee is supervised by only one manager at a time. However, a manager can manage many employees at the same time.
3. Many-to-One (M:1): If multiple instances of entity 1 are connected to only a single instance of entity 2, we have a "many-to-one relationship." For example, in a classroom, many students are taught by a single teacher simultaneously. Hence, the student-teacher relationship is many-to-one.
4. Many-to-Many (M:N): When multiple instances of entity 1 are linked to multiple instances of entity 2, we have a "many-to-many" relationship. Imagine a scenario where an employee is assigned more than one project. If you look at the relationship from the other end, a project can have many employees as well. Thus, it is a many-to-many relationship.

Now, let's put all these concepts together to construct an ER model, shown below. In this article, we will be using the example of a student-teacher relationship. You can find more examples here.

In the STUDENT table, student_id is the primary key. It is interesting to note that student_id is also present in the COURSE table as a foreign key. A student-to-course relationship is a many-to-many relationship as one student can enroll in many courses and each course can have multiple students. In order to show that relationship, we must have a foreign key. In the absence of the attribute student_id in the COURSE table, it would not make sense to store just the course name and the course ID as we will be losing the information of which student is enrolled in which course.

Conclusion

ER models and relational schemas are not substitutes for each other. Rather, they can be seen as complements. For instance, an ER model can be used as a "blueprint" in the initial phases of database development, whereas a relational schema can then be used to map the structure and constraints of a database. Relational schemas are often used by programmers because they resemble an actual database structure, but they are sometimes more difficult to comprehend than ER diagrams.