将ER图转换为关系模型 [英] Converting an ER diagram to relational model

问题描述

我知道如何将实体集，关系等转换为关系模型，但我不知道，当给出整个图表时，我们应该做什么？我们如何转换？我们为每个关系和每个实体集创建一个单独的表吗？例如，如果我们给出以下ER图：

I know how to convert an entity set, relationship, etc. into the relational model but what i wonder is that what should we do when an entire diagram is given? How do we convert it? Do we create a separate table for each relationship, and for each entity set? For example, if we are given the following ER diagram:

我的解决方案如下：

 //this part includes the purchaser relationship and policies entity set

CREATE TABLE  Policies (
  policyid  INTEGER,
  cost  REAL,
  ssn  CHAR(11)  NOT NULL,
  PRIMARY KEY (policyid).
  FOREIGN KEY (ssn) REFERENCES Employees,
  ON DELETE CASCADE)


 //this part includes the dependents weak entity set and beneficiary relationship

 CREATE TABLE Dependents (
  pname  CHAR(20),
  age  INTEGER,
  policyid  INTEGER,
  PRIMARY KEY (pname, policyid).
  FOREIGN KEY (policyid) REFERENCES Policies,
  ON DELETE CASCADE)


 //This part includes Employees entity set

 CREATE TABLE Employees(
   ssn Char(11),
   name char (20),
   lot INTEGER,
   PRIMARY KEY (ssn) )

我的问题是：

1)Is my conversion true?
2)What are the steps for converting a complete diagram into relational model. 
Here are the steps that i follow, is it true?
    -I first look whether there are any weak entities or key constraints. If there
     are one of them, then i create a single table for this entity set and the related        
     relationship. (Dependents with beneficiary, and policies with purchaser in my case)
    -I create a separate table for the entity sets, which do not have any participation  
     or key constraints. (Employees in my case)
    -If there are relationships with no constraints, I create separate table for them.
    -So, in conclusion, every relationship and entity set in the diagram are included 
     in a table.

如果我的步骤不是真的或有一些我缺少，转换？另外，如果只有一个关系的参与约束，但没有关键约束，我们该怎么办？我们再次为相关的实体集和关系创建一个表吗？

If my steps are not true or there is something i am missing, please can you write the steps for conversion? Also, what do we do if there is only participation constraint for a relationship, but no key constraint? Do we again create a single table for the related entity set and relationship?

我感谢任何帮助，我是新的数据库，并试图学习这种转换。

I appreciate any help, i am new to databases and trying to learn this conversion.

谢谢

推荐答案

Hi @bigO我认为您的转换是真实的，您所遵循的步骤是正确的。然而，从实现的角度来看，可能有改进的余地。您实现的是比物理模型更多的逻辑模型。

Hi @bigO I think it is safe to say that your conversion is true and the steps that you have followed are correct. However from an implementation point of view, there may be room for improvement. What you have implemented is more of a logical model than a physical model

通常的做法是向物理表添加代理实例标识符，这是一个通用要求大多数持久化引擎，以及@Pieter Geerkens指出，可以帮助提高数据库效率。实例ID（例如EmployeeId（INT））的值将由插入时的数据库自动生成。这也将有助于@Pieter Geerkens指出与SSN的问题。将ID添加为所有表的第一列，我遵循 tablename Id的约定。

It is common practice to add a Surrogate Instance Identifier to a physical table, this is a general requirement for most persistence engines, and as pointed out by @Pieter Geerkens, aids database efficiency. The value of the instance id for example EmployeeId (INT) would be automatically generated by the database on insert. This would also help with the issue that @Pieter Geerkens has pointed out with the SSN. Add the Id as the first column of all your tables, I follow a convention of tablenameId. Make your current primary keys into secondary keys ( the natural key).

添加ID后，必须实现DependentPolicy交叉表

Adding the Ids then makes it necessary to implement a DependentPolicy intersection table

DependentPolicyId, (PK)
PolicyId,
DependentId

你可能需要考虑一下什么是从属表的自然键。

You may then need to consider as to what is natural key of the Dependent table.

我注意到你的年龄属性，您应该考虑这是在策略创建时的年龄还是依赖的实际年龄，我应该使用出生日期。

I notice that you have age as an attribute, you should consider whether this the age at the time the policy is created or the actual age of the dependent, I which case you should be using date of birth.

您可以考虑的其他装饰是创建和修改日期。

Other ornamentations you could consider are creation and modified dates.

我也普遍倾向于对表使用单数，即雇员不是雇员。

I also generally favor using the singular for a table ie Employee not Employees.

欢迎来到数据建模和设计的世界。

Welcome to the world of data modeling and design.

这篇关于将ER图转换为关系模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！