2019-11-29

Introduction to SQL - Entity Relationships

We're so close to finalizing the Introduction to SQL articles, but before that, we have another critical topic to learn: Entity Relationships.

So far, we have learned that Entity is a synonym for Tables. In database administration, only those things about which data will be captured or stored are considered entities. So if you aren't going to capture data about something, there's no point in creating an entity in a database.

Once you decide that it is worth it to create an entity or table, you can end up with multiple tables, as we saw here. And then, you will need to join those tables to have a big picture of your data, your queries, and your business logic.

When to draw ER Diagrams?

So, when do we draw ERDs? While ER models are primarily developed for designing relational databases in terms of concept visualization and physical database design, there are still other situations when ER diagrams can help. Here are some typical use cases.

* Database design - Depending on the scale of change, it can be risky to alter a database structure directly in a DBMS. To avoid ruining the data in a production database, it is essential to plan out the changes carefully. ERD is a tool that helps. By drawing ER diagrams to visualize database design ideas, you have a chance to identify the mistakes and design flaws and to make corrections before executing the changes in the database.
* Database debugging - To debug database issues can be challenging, mainly when the database contains many tables, requiring writing complex SQL to get the information you need. By visualizing a database schema with an ERD, you have a complete picture of the entire database schema. You can quickly locate entities, view their attributes, and identify their relationships with others. These allow you to analyze an existing database and reveal database problems easier.
* Database creation and patching - Visual Paradigm, an ERD tool, supports a database generation tool that can automate the database creation and patching process using ER diagrams. So, with this ER Diagram tool, your ER design is no longer just a static diagram but a mirror that truly reflects the physical database structure.
* Aid in requirements gathering - Determine the needs of an information system by drawing a conceptual ERD that depicts the high-level business objects of the system. Such an initial model can also be evolved into a physical database model that aids the creation of a relational database or aids in creating process maps and data flow modes.

We learned about Inner Join, Left and Right Join in the previous join tables blog post. But we need another piece on our cake: Entity Relationships.

Entity Relationships

An Entity Relationship is a high-level conceptual data model diagram. Entity Relationship helps to systematically analyze data requirements to produce a well-designed database. Furthermore, entity Relationships represent real-world entities and the relationships between them. Therefore, creating an Entity Relationship is considered a best practice before implementing your database.

Let's re-use the Database Design used in this blog post.

As you can see we have a Customer who can have many Orders. So, as you can imagine, each Order belongs to a Customer. At the same time, the Subscription (to a given magazine, e.g., Time Magazine) has many Orders, and each Order belongs to a Subscription. The technical name for this in Database management is Cardinality.

Cardinality

Defines the numerical attributes of the relationship between two entities or entity sets.

Different types of cardinal relationships are:

* One-to-One Relationships
* One-to-Many Relationships
* May to One Relationships
* Many-to-Many Relationships

1.One-to-one:

One Entity set X can be associated with at most one entity set Y and vice versa.

Example: One student can register for numerous courses. However, all those courses have a single line back to that one student.

2.One-to-many:

One Entity from entity set X can be associated with multiple entities of entity set Y. Still, an entity from entity set Y can be related to at least one Entity.

For example, one class consists of multiple students.

3. Many to One

More than one Entity from entity set X can be associated with at most one Entity of entity set Y. However, an entity from entity set Y may or may not be associated with more than one Entity from entity set X.

For example, many students belong to the same class.

4. Many to Many:

One Entity from X can be associated with more than one Entity from Y and vice versa.

For example, students are associated with multiple faculty members, and faculty members can be related to various students.

How to draw an ER diagram?

If you find it challenging to get started with drawing an ER diagram, don't worry. In this section, we will give you some ERD tips. Then, try to follow the steps below to understand how to draw an ER diagram effectively.

* Make sure you are clear about the purpose of drawing the ERD. Are you trying to present an overall system architecture that involves the definition of business objects? Or are you developing an ER model ready for database creation? You must be clear about the purpose to create an ER diagram at the right level of detail (Read the section Conceptual, Logical and Physical Data Models for more information)
* Make sure you are clear about the scope of the model. Knowing the modeling scope prevents you from including redundant entities and relationships in your design.
* Draw the principal entities involved in the scope.
* Define the properties of entities by adding columns.
* Review the ERD carefully and check if the entities and columns are enough to store the system's data. If not, consider adding additional entities and columns. Usually, you can identify some transactional, operational, and event entities in this step.
* Consider the relationships between all entities and relate them with proper Cardinality (e.g., A one-to-many between entity Customer and Order). Don't worry if there are orphan entities. Although it's not common, it's legit.
* Apply the technique of database normalization to restructure the entities in a way that can reduce data redundancy and improve data integrity. For example, the Manufacturer's details might initially be stored under the Product entity. During the normalization process, you may find that the detail keeps repeating records over records. You can split it as a separate entity, Manufacturer, and with a foreign key that links between Product and Manufacturer.

This blog post aims to give you an introduction to Entity-Relationship and how to diagram it. With all of the cumulative knowledge about SQL, we'll finish this introduction series with database structures. So, I'll see you in the next blog post

I hope you learned a lot.

Thanks for reading
Daniel Morales