Entity and ER diagrams
Entities and entity sets
In a relational database,
An entity can be a tangible, physical thing (like every employee) or an abstract, conceptual thing (like a department). An entity is something that exists independently and is distinguishable from other entities.
An entity set is a collection of entities of the same type and nature. It’s a collection of things. Individuals of each type of data object are called entities.
Entity sets can intersect (for example, employees in the sales department belong to both the sales employee entity set and the employee entity set).
attribute
Attributes refer to the description of features that each entity in an entity set has. The field of an attribute refers to the range or set of values allowed for an attribute.
Connections and connection sets
Association refers to the interrelation between multiple entities. An association set is a collection of similar associations.
Graphical representation
Entities or sets of entities are represented by rectangles; Attributes of entities are represented by ellipses; The connections between entities are represented by diamonds;
E-r diagram
An E-R Diagram, also known as an Entity Relationship Diagram, provides a way to represent Entity types, attributes, and relationships and is used to describe conceptual models of the real world.
ERD for short.
ER diagram is divided into three core parts: entity, attribute and relation.
The following is an example of an ER- diagram
Entity-relationship Approach is a representation method of conceptual model, that is, e-R graph is used to describe the conceptual model of the real world.
The E-R method is also called the E-R model.
Relationships between entities
There are three types of relationships between entities:
-
One-to-one: One instance in entity A corresponds to one instance in entity B, denoted as 1:1. For example, a student has only one transcript.
-
One-to-many: one instance in A corresponds to multiple instances in B, denoted as 1:N. : A college has more than one student.
-
Many-to-many: multiple instances to multiple instances, denoted by M:N. For example, one teacher has more than one subject, and one subject has more than one teacher.
The type of the relationship is usually marked next to the entity-relationship. 1 in the figure above.
For strong entity and weak entity can also be distinguished by symbols, such as weak entity with bilateral rectangle, strong and weak contact with bilateral diamond. As follows:
Generally, there are two entities in 1:1 or 1:n. The former is a strong entity (which can be understood as an entity that exists independently and can be clearly expressed by attributes), and the latter is a weak entity (which can be understood as an entity that must exist depending on other entities and whose attributes cannot be clearly expressed by themselves).
Composite entities (also known as federated entities or bridging entities), commonly used to implement an M:N association between two or more entities, consist of the main code of each associated entity, represented by a diamond inside a rectangle.
Below is a typical complex entity, because only, for example, relatively coarse, users and commodities two entities is M: N relationship, order the entity contact between them, so the order the entity is a composite entity, if the user entity does not exist at the same time, there is no order entity exists, so the order for the user entity is weak entity, Similarly, if the commodity entity does not exist, there is no order entity. Therefore, for the commodity entity, the order is a weak entity, as shown in the figure below:
Another example is an ER diagram of teaching management:
Implementation in the database
The implementation of an entity in a database is a table (also known as an entity set, where each record is a concrete entity object).
The realization of one-to-one relationships between entities
-
Unique foreign key: Add a unique foreign key to either of the two tables.
-
Primary key association mode: AB two sets of data are associated if the primary keys are the same.
-
Intermediate relational table: Create a new table as an intermediate relational table, with two columns that record two tables, and the values of each column cannot be repeated (that is, both columns are set to unique foreign keys). Note: Setting the two columns as both primary and foreign keys does not work because the union of primary keys may result in one column being equal and the other not.
Many-to-one and one-to-many implementations between entities
-
Add a foreign key to the more side: For example, if a class has more than one student, add a column in the student table to record which class the student belongs to. The column is set as a foreign key and its value is the primary key of the class table.
-
Create an intermediate table: Create a new table with at least two columns for each table. Set the columns of the more party to “unique foreign key” and the columns of the less party to “foreign key”.
For example, if a class corresponds to multiple students, create at least two columns in the table to represent students and classes. The student column is set to be “foreign key + unique key” in case the student column with two rows of data has the same value (in case one student has two classes). The class column does not have to be set as a unique key, but as a foreign key, because different students can have the same class.
Many-to-many implementation between entities
There’s only one way to create a table of intermediate relationships.
For example, students and courses, a student can choose more than one course, a course can be chosen by more than one student. Create a new table, one column for students, one column for classes. Both columns are foreign keys. The value of one column is the unique identifier in the student table, and the value of the other column is the unique identifier in the course. The two columns need not be set as unique keys, because there can be duplicates in each column, but no two rows of data can be identical on both columns. So make both columns primary key + foreign key
View the software for managing the ERD
View entity Relationship diagrams in SSMS (SQL Server)
In SQL Server Management Studio(SSMS) object Explorer, under the database node, you can directly see the “Database Diagram” node.
Click the node + to expand, and the first time you expand, you’ll be prompted to create a support object.
Click OK to right-click “Database diagram” and create a new one.
After you add a table, you can see a diagram of the selected table. You can then add tables or create new tables
After the design is completed, it can be directly saved as an ER diagram for later viewing or modification. You can draw an ER diagram here while you’re designing your table.
To view the diagram, the database must specify the owner.
PgAdmin4 to view the entity relationship diagram (PostgreSQL)
PgAdmin 4, starting with V4.3, provides an ERD Tool that can be used to manage database diagrams. (ERD Tool is not available in earlier versions)
Edit tests are not very useful and are a great tool for generating diagrams.
Other tools
Most database system management software provides the function of viewing ER graph. Examples include Navicat, SQL Developer, and DBeaver, an open source management tool that supports almost any database
reference
- Database ER diagram basic concept collation
- Database entities, relationships (one-to-one implementation, one-to-many implementation, many-to-many implementation)