database ER图和数据库模式有什么区别？

What is different between ER Diagram and Database Schema?

提问by sam

What is the difference between ER Diagrams and Database Schema? MySQL Workbench has facility to draw ER diagrams, but the symbols for ER diagrams different in other drawing tools than MySQL Workbench method.

ER 图和数据库模式有什么区别？MySQL Workbench 具有绘制 ER 图的功能，但在其他绘图工具中，ER 图的符号与 MySQL Workbench 方法不同。

回答by ChristHope

A database schemais usually a relational model/diagram. it shows the link between tables: primary keys and foreign keys.

数据库模式通常是一个关系模型/图。它显示了表之间的链接：主键和外键。

In database diagram the relation between an apple and a apple tree would be: A foreign key "ID__TRE" which cannot be null in the table "APPLE" is linked to a primary key "ID_TRE" in the table "TREE".

在数据库图中，苹果和苹果树之间的关系是：表“APPLE”中不能为空的外键“ID__TRE”链接到表“TREE”中的主键“ID_TRE”。

An entity relationship diagram. Shows links between the entities and the kind of relation between them. We are not talking about tables or keys there! Usually the entity relationship diagram follows Merise model. Database manager and developer as myself usually build an entity relationship model before conceiving the relational model/diagram.
The set of symbol in Merise are:(0-1, 0-n, 1-1, 1-n). The first number 0 or 1 describes whether the other part of the association is required for an object to exist. If it is zero, it means it can exists without being associated. If it is One it means that the object only exist in relation with an other object (e.g an apple need a tree to exist --> 1, a tree needn't apple to exists -->0) The second character tell us how many objects are accepted in the other part of the association. If it is 1, then only one object can exists in the relation, if it is n, a infinite number of object can be linked (e.g.: an apple can have one tree --> 1, a tree can have multiples apples --> n)

实体关系图。显示实体之间的链接以及它们之间的关系类型。我们不是在谈论那里的桌子或钥匙！通常实体关系图遵循 Merise 模型。像我这样的数据库管理员和开发人员通常在构思关系模型/图之前构建实体关系模型。
Merise 中的符号集是：(0-1, 0-n, 1-1, 1-n)。第一个数字 0 或 1 描述关联的其他部分是否需要对象存在。如果为零，则表示它可以存在而无需关联。如果是 One，则表示该对象仅与其他对象相关（例如，一个苹果需要一棵树才能存在 --> 1，一棵树不需要苹果才能存在 --> 0）第二个字符告诉我们如何许多对象在关联的另一部分被接受。如果是1，那么关系中只能存在一个对象，如果是n，则可以链接无限多个对象（例如：一个苹果可以有一棵树 --> 1，一棵树可以有多个苹果 -- > n)

With Entity relationship the relationship will be described as : An apple has to belong to at least one tree to exists and can belong to only one tree(1-1). A tree needn't an apple to exist but it can have an infinite number of apples (0-n).

对于实体关系，这种关系将被描述为：一个苹果必须属于至少一棵树才能存在，并且只能属于一棵树（1-1）。一棵树不需要一个苹果就可以存在，但它可以有无数个苹果（0-n）。

In fact both description mean the same but one is database oriented while the other is modelling oriented. Some modelling software such as DB-MAIN convert automatically an ER diagram to the relational diagram.

事实上，这两种描述的含义相同，但一种是面向数据库的，另一种是面向建模的。一些建模软件如 DB-MAIN 会自动将 ER 图转换为关系图。

回答by Mark Vogt

ENTITY RELATIONSHIP DIAGRAMS (ERDs) are just that: DIAGRAMS which describe the RELATIONSHIPS between ENTITIES. Now let's look closer...

实体关系图（ERD）就是：描述实体之间关系的图。现在让我们仔细看看...

• ERDs are often created by Business Analysts (NOT DBAs);
• ERDs are often described in LAYMAN's terms (NOT techno-speak of DBAs or other);
• ERDs are meant to summarize & clarify understanding for End Users and Business SMEs (again, NOT the DBAs or Developers)
• ERDs work best when each entity is described in the SINGULAR, and the lines connecting various entities to other entities in the ERD use verbs (of action or possession, or existence) to describe each relation;
• ERDs can (and do) include lines which denote n:n relationships, but this is not a requirement.
• Examples of entities in an ERD for a blog: Member, Post, Comment, Category
• Examples of relationships described in an ERD:
  • Member "posts" 1 to n Posts; (note we AREN'T describe WHAT a post looks like)
  • Post "relevant-to" 1 to n Categories etc.

• ERD 通常由业务分析师（而非 DBA）创建；
• ERD 通常用 LAYMAN 的术语来描述（不是 DBA 或其他技术术语）；
• ERD 旨在总结和阐明最终用户和中小企业的理解（同样，不是 DBA 或开发人员）
• 当每个实体都用 SINGULAR 描述时，ERD 效果最好，并且在 ERD 中将各种实体连接到其他实体的线使用动词（动作或拥有或存在）来描述每个关系；
• ERD 可以（并且确实）包括表示 n:n 关系的行，但这不是必需的。
• 博客 ERD 中的实体示例：成员、帖子、评论、类别
• ERD 中描述的关系示例：
  • 会员“帖子”1 到n 个帖子；（请注意，我们没有描述帖子的样子）
  • 发布“相关”1到n个类别等。

DATA SCHEMAS bear some resemblance to ERDs, but they should NOT be considered either equivalent or interchangeable. If you make an ERD which can be used as a data schema... be open to the possibility you DIDN'T make an ERD ! ;-)

DATA SCHEMAS 与 ERD 有一些相似之处，但不应将它们视为等效或可互换的。如果您制作了一个可以用作数据模式的 ERD ......对您没有制作 ERD 的可能性持开放态度！;-)

DATA schemas are diagrams used to describe to a DBA how data will be stored in a database (relational or non-relational).

数据模式是用于向 DBA 描述数据将如何存储在数据库中（关系或非关系）的图表。

• Data Schemas almost invariable describe the structure & characteristics of TABLES;
• Tables are "containers" (cardboard boxes);
• As such tables in a data schema are BEST named in the PLURAL
• Examples of the TABLES in a SCHEMA for the same blog: MEMBERS, POSTS, CATEGORIES, COMMENTS(relational database) or POSTS(keyed by Member-Date and all other columns in 1 table (non-relational database like for a "big data" project);

• a data schema would then describe the data contained in each table:

  MEMBER 
  FirstName (char:25)
  LastName (char:25)
  

  etc.

• the lines between tables in a data schema would NOT try to represent any 'relation' other than a "KEY" between 2 fields which could be used to "join" the tables, and some additional characteristics of those lines to denote n:n relationships.

• 数据模式几乎不变地描述了表的结构和特征；
• 桌子是“容器”（纸板箱）；
• 由于数据模式中的此类表在 PLURAL 中最好命名
• 同一博客的 SCHEMA 中的 TABLES 示例： MEMBERS, POSTS, CATEGORIES, COMMENTS（关系数据库）或 POSTS（由 Member-Date 和 1 个表中的所有其他列键控（非关系数据库，如“大数据”项目）；

• 然后，数据模式将描述每个表中包含的数据：

  MEMBER 
  FirstName (char:25)
  LastName (char:25)
  

  等等。

• 数据模式中表之间的线不会尝试表示除可用于“连接”表的 2 个字段之间的“KEY”之外的任何“关系”，以及这些线的一些附加特征来表示 n:n 关系.

BOTH diagrams serve quite DIFFERENT purposes: ERD: to make mere mortal end-users (and business owners) UNDERSTAND the model of a given business solution; and DATA SCHEMA: a "blueprint" used by DBAs to BUILD databases, and by DEVELOPERS to CONSUME the data in that database.

两个图都有不同的用途： ERD：让普通的最终用户（和企业主）理解给定业务解决方案的模型；和 DATA SCHEMA：DBA 使用的“蓝图”来构建数据库，并由开发人员使用该数据库中的数据。

回答by Conor

A database schemais a description of the actual construction of the database. It is an all-encompassing term that refers to the collective of tables, columns, triggers, relationships, key constraints, functions and procedures. It can refer to a document that describes all of this (such as an XML Schema) or as an abstraction of database makeup itself ("It would be difficult to change the schema of the database at this point"). It does not refer to rows inserted into the schema, or data itself. You would insertdata into an existing schema.

一个数据库模式是对数据库的实际施工的描述。它是一个包罗万象的术语，指的是表、列、触发器、关系、关键约束、函数和过程的集合。它可以引用描述所有这些的文档（例如 XML 模式）或作为数据库构成本身的抽象（“此时很难更改数据库的模式”）。它不是指插入到模式中的行，也不是数据本身。您可以将数据插入到现有架构中。

An Entity Relationship Diagramis a visualization of the relationships between tables in a database. At the very least, it includes table names visualized as squares connected by lines that represent primary and foreign key constraints. It often includes the column names and symbols that include information about what kind of relationship exists between the columns (one-to-one, one-to-many, many-to-many).

一个实体关系图是数据库中的表之间的关系的可视化。至少，它包括表名，这些表名可视化为由代表主键和外键约束的线连接的正方形。它通常包括列名称和符号，其中包含有关列之间存在何种关系（一对一、一对多、多对多）的信息。