Introduction to ER data model

A simple but comprehensive overview of the entity-relationship data model

ThisEntity Relationship Data Model, is also calledemergency room, Are variousData modelYou can use it to infer data.

in particularConceptual data modelBecause it is not linked to any specific implementation. This is the task of the logical data model.

ThisER data modelIt is so versatile and so advanced that it can be implemented through a variety of completely different databases.

That’s great, because you don’t need to think about implementation details, you only need to think aboutYour data and how it is organized. While doing this, you have to analyze the problem in a way you never thought of before.

I find that the ER diagram is very helpful for you to analyze scenarios involving data.

The ER model provides you with tools that use graphical representations to represent all the data you need to model, the relationship between different types of data, and the information related to it.

There are 2 items that make up the ER model:

  • Thisentity
  • Thisrelationship

Entities are data types that have common attributes, such as items or people.

A relationship is a relationship between entities.

Let me give an example, let's talk about books and their authors. We have2 entities:

  • book
  • Author

The specific book is an instance of the book entity.

Since we have 2 entities, we have2 relationshipBetween them. One is the relationship between a single book and the author entity. One is the relationship between a single author and the book entity. If we think about it, we have:

  • A book has an author
  • The author can write many different books

Physical visual symbol

Given this simple example, we can start to introduce visual symbols, which will help us create an ER data model of the scene.

Note: There are many ways to draw an ER diagram. I will use thatin my opinion, More intuitive and meaningful.

Entities are represented by rectangles, which contain some text to identify them:

Entities

Visual symbol of relationship

The relationship between entities is expressed in the most basic form. It uses a line connecting two relationships and a diamond with some text to identify the type of relationship:

Relation example

Please note that I did not create the two relationships "Book has Author" and "Author Writes Book". I established an "author" relationship between the book and the author.

Base

After establishing the relationship, we must now point out the numbers involved. Currently, we have many questions:

  • How many authors can a book have?
  • Can the author write more than one book?
  • Does an author need to write at least one book to be called an author?
  • Can a book be written by multiple authors?
  • Can a book at least have no author?

All of these are good questions, and in this case, I think the answer is very obvious. When the answer is not obvious, you can consider the question and add your own constraints.

There are many ways to visually indicate the cardinality on the chart. Some people like to change the shape of the line when linking to the entity.

I prefer numbers, which makes things clearer:

Cardinality example

The number above indicates this: a book can be created by 1 or more authors.nRepresents any number of elements.

An author can create 0 books (maybe one book is being written) to countless books.

The first one is calledZero-to-many relationship. The second one isOne-to-many relationship.

We can also:

  • One-to-one relationship
  • Many-to-many relationship
  • Zero-to-one relationship

Attributes

Each entity can have one or more attributes.

Suppose we will use the above relationship in a bookstore. Each author has a name, profile and website URL.

Each book has a title, publisher, year of publication and ISBN. If we want, the publisher can also be an entity. But we can also define it as an attribute of the book.

This is how we can represent the above information:

Attributes example

Identifier attribute

The entity must be identified by a unique key. The book entity can be uniquely identified by the ISBN attribute. Every book has an ISBN (considering that we do not represent a single copy of a book, but the "title" of the book).

We use them as a basis to identify primary key attributes:

Key

On the other hand, the author currently does not have a unique identifier. Two authors can use the same name.

Therefore, we must add unique key attributes. For example aidAttributes:

id attribute

The identifier attribute can span multiple attributes.

For example, the passport ID and the author’s country/region can uniquely identify the person and can replaceidThe attributes we added:

multiple attributes key

Which one to choose? This is the question of what makes more sense in your application. If we are modeling a bookstore, we cannot expect to have the country/region and passport ID of all book authors. Therefore, we will use randomidWe will select and contact each author.

Relationship attributes

The attribute is not unique to the entity. Relationships can also have attributes.

Consider that we need to model a library. In addition to books and author entities, we now introduceReader entity, A borrower. When they borrow, we will use its name and ID number:

relation without attribute

But we still miss the information. We need to know when the person borrowed the book and the date they returned it, so we can store all the history information about a particular book in our library. This information belongs neither to the book nor to the reader entity; it belongs to the following relationship:

relation attributes

Weak entity

We discussed the primary key above and how to help uniquely identify the entity.

The existence of some entities depends on other entities, so they are calledWeak entity.

Suppose we need to model an order for an online store.

For each order, we will store the order ID starting from 1 and increasing over time, the date and time the order was placed, and information about the customer, so we know who to pay and where to ship.

Then, we also need to know what they ordered. We create a weak entity "Order Goods", which represents each item in the shopping cart at checkout.

weak entity

This entity will store the price of the goods at checkout (so when we change the price of the goods sold, it will not affect the orders that have already been placed), the number of goods ordered, and the options selected. Suppose we sell T-shirts, so we need to store the color and size of the ordered T-shirts.

It is a weak entity, because there is no ordering entity without an order entity.

Recursive relationship

An entity can have a recursive relationship with itself. Suppose we have a human entity. We can model the parent-child relationship in the following ways:

recursive relation

A person can have 0 to n children, and a child has 2 parents (considering the simplest case).

ISA relationship

ISA stands for IS-A, which is a method of modeling and generalization in the ER model.

We use it to group similar entities into one category. For example, in the case of books and library examples, the person entity can be used to summarize authors and readers.

They all have names, so we extract names down to individual entities, and then manage the specificity of the corresponding entities as authors or readers:

isa relation

Non-dual relations

Not every relationship is strictly binary. Let us look at a lesson scenario.

At 10:00 today, I took a class on physics with a teacher in a classroom of the school.

Therefore, a class at a specific time of the day involves a subject, a teacher, a class and a classroom.

We can model it in the following ways:

non-binary relation


More database tutorials: