Entity Relationship Diagram (ERD)

Because I didn't know the relevant knowledge in detail, there was an error when drawing the ER diagram this week. Here is an introduction to the ER diagram.

What is ERD?

ERD, namely entity Relationship diagram, entity relationship diagram. Use a defined set of symbols such as rectangles, diamonds, ellipses, and connecting lines to describe the interconnection of entities, relationships, and their properties.

Components of an ER Diagram

An ER diagram consists of entities, relationships, and attributes.

And different notation has different symbols.
Here are two commonly used techniques: Chen notation style and Crow's Foot style

Chen notation style

Peter Chen (aka Peter Pin-Shan Chen) developed ER modeling for database design in the 1970s. So named after him

Crow's Foot style

Crow's Foot means crow's foot, like a bird's claw. Because some of its symbols look a lot like bird claws.

entity

The most important component of an ER diagram is its entity. Since an ERD represents the structure of a database, entities are the tables that make up the database.

Chen notation style notation: In this notation, entities are represented by rectangles. Entities are written inside a rectangular box

This is the notation introduced in the database book I learned this semester. As follows: represented by a rectangular box.

Crow's Foot Notation : In tabular form, use the entity as the title of the symbol.

Attributes

Chen notation style : In this notation, attributes are represented by circles. They originate from entities.

Crow's Foot Notation: The properties are listed below the table with the entity name as the title.

relation

The second component is the relationships between entities, which show the associations between different entities.

The three main basic relationships between two entity types are one- to-one, one-to-many , and many-to -many .

One-to-one example (1 : 1): A student is associated with an email address.

One-to-many example (1 : n): (or many-to-one): a class with multiple students.

Many-to-many example (m : n): A student is associated with multiple teachers, and teachers are associated with multiple students.

For example, a student might enroll in a course. The two entities are students and courses, and the relationship described is the registration behavior.

Chen notation style notation: In this notation, the relationship is represented by a diamond with descriptive text inside

Crow's Foot Notation: In this notation, the relationship is represented by a line. At both ends of the line there are symbols describing the cardinality of the relationship.

In this notation, the following symbols represent different relational connections.

One to one . For example, a student is associated with an email address.

The PK (primary key) here represents the primary key in the database and is the only primary attribute column of a table.

Two vertical lines are used on the left, indicating that the entity must exist, and zero one is used on the right, with a minimum of 0 and a maximum of 1.

One to many : (1 : n) (or many to one). For example, a class with multiple students

Here FK (FOREIGN KEY), represents a foreign key. Foreign keys are not necessarily primary keys, but must be unique indexes.

A foreign key represents a related connection between two relationships. A table with a foreign key of another relation as its primary key is called a primary table, and a table with a foreign key is called a secondary table of the primary table.

The role of foreign keys: to ensure data integrity and consistency through foreign keys

When the user performs operations such as insert, update, delete, etc., the database will first pass the foreign key detection (do relationship check) before allowing the operation. E.g:

insert: The primary key that does not exist in the main table, and the corresponding foreign key cannot be inserted into the sub-table.
delete: The master table can be deleted only when the records from the slave table do not exist. Delete the slave table, the master table remains unchanged

It can be seen from this that the disadvantage of foreign keys: low processing performance for massive data

This is why it is recommended to use nosql for massive data. Nosql does not need to handle a large number of relationships and is faster when executing related database commands.

manyToMany(m : n): For example, a student is associated with multiple teachers, and teachers are associated with multiple students.

Using manyToMany will actually generate an intermediate table, as shown below the horizontal line, to which the two entities have a oneToMany relationship. However, for simplicity, we will generally use the line shown above, which by default has an intermediate table.

Draw ER diagram

https://draw.io/ This URL provides online drawing operations.

From the Entity Relationships item, you can choose symbols. Both Chen notation style and Crow's Foot Notation are available. Fully visual operation, convenient and fast.

https://plantuml.com/zh/ie-diagram provides online tools to quickly write UML diagrams

In contrast, you need to understand a certain syntax to develop quickly