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:
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:
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:
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:
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.
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:
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
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:
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:
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 a
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 replace
idThe attributes we added:
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 random
idWe will select and contact each author.
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:
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:
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.
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.
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:
A person can have 0 to n children, and a child has 2 parents (considering the simplest case).
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:
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:
More database tutorials:
- MongoDB basic tutorial
- How is MongoDB different from SQL database
- Information system, data and information
- Data model
- Introduction to ER data model
- Relational model
- Relational algebra
- relational database
- What is a database? There is also a DBMS?
- Introduction to SQL
- How to install PostgreSQL on macOS
- Do you always need to provide a database for the application?
- How to install SQLite on macOS
- Introduction to PostgreSQL
- PostgreSQL user permissions
- How to list all users in PostgreSQL
- How to switch database using PostgreSQL
- How to list all databases using PostgreSQL
- How to use PostgreSQL to list the tables in the current database
- How to install MySQL on macOS
- Create user on MySQL
- PostgreSQL vs MySQL, comparison
- SQLite user permissions
- MySQL user permissions
- SQL, create table
- SQL, add data to the table
- SQL, how to use SELECT
- SQL, handle empty cells
- SQL, unique key and primary key
- SQL, how to update data
- SQL, how to update the table structure
- SQL, how to delete data and tables
- SQL connection
- SQL view
- How to insert multiple items in a MongoDB collection at once
- How to delete all items from MongoDB collection