什么是使用关联实体的正确方法? [英] What is the right way to use Associative Entity?
问题描述
这是描述:
为Poke-Hospital绘制一个实体关系图，该图提供了 神奇宝贝的医疗服务.
Draw an Entity-Relationship diagram for Poke-Hospital which provides medical service to pokemon.
每个宠物小精灵都会与其中一位护士乔伊斯约会.在 除了记录每个口袋妖怪的名称，类型和训练师之外， 系统需要跟踪多种疾病 被诊断为神奇宝贝.预约期间，护士将 总是开药.需要记录日期，时间和 药物的剂量.宠物小精灵可能需要服用多个 一次服药.每种药物均带有其名称，品牌和 购买成本.药量没有限制 由任何护士开处方.
Each pokemon has an appointment with one of the nurse Joys. In addition to recording the name, type and trainer of each pokemon, the system needs to keep track of the multiple types of sickness being diagnosed for the pokemon. During an appointment, the nurse will always prescribe medicine. It is required to record the date, time and dosage of the medicine. A pokemon may need to take more than one medicine at a time. Each medicine is stored with its name, brand and cost of purchase. There is no restriction on the amount of medicine to be prescribed by any nurse.
在约会中，宠物小精灵可能需要接受以下程序 作为手术和/或诊断.每个过程需要不同的类型 房间和设备清单.日期，时间和实际房间 该过程需要记录.
Within an appointment, a pokemon may need to undergo procedures such as a surgery and/or diagnosis. Each procedure requires different type of rooms and a list of equipment. The date, time and the actual room of the procedure need to be recorded.
一个程序可以由不止一个护士执行.护士是 根据她所拥有的培训技能参与了程序 完全的.并非所有的护士都有执行手术的资格.
A procedure may be performed by more than one nurse. A nurse is involved in the procedure based on the training skills that she has completed. Not all nurses are qualified to perform procedures.
每个护士的姓名，传呼机号码以及办公室号码最多 众所周知.您的图表应显示实体，关系及其 属性以及任何关系的基数.标记最好 强调每个实体的主键.
Name, pager number as well as office number for each nurse most be known. Your diagram should show the entities, relationships and their attributes, and the cardinality of any relationships. Mark the best primary key for each entity by underlining it.
这是我的解决方案:
这是我的问题:
-
我应该使用具有约会"作为关联实体吗?
Should I use Have Appointment as associative entity?
我应该删除2个关系进行并指定并连接2个 实体程序和预约医学直接拥有 任命关联实体? ERD会仍然正确吗?
Should I remove 2 relationships Undergo and Prescribe and connect 2 entities Procedure and Appointment Medicine directly to Have Appointment associative entity? Will the ERD still right then?
如果错了，那和问题2一样，我打开"Have" 将关联实体任命为恋人?
If it's wrong, what about the same as question 2 and I turn the Have Appointment associative entity into a relationship?
对于使用关联实体和关系之间的区别，我感到非常困惑(例如在这篇帖子中，教与教":
I feel really confused about the difference between using associative entity with a relationship (like in this post Enrollment with Teach and Teacher: When to use Associative entities?) and using ternary relationship (connect Teacher directly to Enrollment relationship instead of changing Enrollment to an associative entity and have the Teach relationship).
推荐答案
- 我应该使用预约"作为关联实体吗?
不，我认为它应该是常规实体集.您给了它自己的身份-ID主键-我同意，但是应该与元素类型的更改相对应.关联实体集(AES)首先是关系，这意味着它们由它们所关联的(实体的)键来标识.
No, I believe it should be a regular entity set. You gave it its own identity - the ID primary key - which I agree with, but that should've corresponded with a change in element type. Associative entity sets (AES) are relationships first, which means they're identified by the (keys of the) entity sets that they relate.
这是一个广泛困惑的主题，因为实体关系模型中的AES与网络数据模型中的AES不同.从本质上讲，后者是开发人员所熟悉的，因为它本质上是基于记录和指针的模型，但是由于它仅支持有向二进制关系，因此任何更复杂的关系(多对多关系以及三元关系和更高关系)都需要表示为AES.在此模型中，AES通常由代理ID标识，因为通常也不支持复合密钥.
This is a topic that's widely confused, since AES in the entity-relationship model are different than in the network data model. The latter is intuitively more familiar to developers, since it's essentially a model based on records and pointers, but since it only supports directed binary relationships, anything more complicated - many-to-many relationships as well as ternary and higher relationships - need to be represented as AES. In this model, AES are identified by a surrogate ID, since composite keys generally aren't supported either.
实体关系模型支持n元关系和组合键，因此几乎不需要AES.无法用常规实体集和n元关系表示的一种情况是，当一个关系需要成为进一步关系的主体时.
The entity-relationship model supports n-ary relationships and composite keys, and so doesn't need AES nearly as frequently. One situation that can't be represented by regular entity sets and n-ary relationships is when a relationship needs to be the subject of a further relationship.
例如，让我们看一下Procedure和Nurse之间的关系，以代表参与该过程的护士.
For example, let's look at the relationship between Procedure and Nurse to represent the nurses involved in a procedure.
我更喜欢基数指标的交叉约定-护士可以执行0个或更多程序，而一个程序需要1个或更多护士.无论如何，这里的Perform关系由组合主键(ProcedureID, NurseID)标识.
I prefer the look-across convention for cardinality indicators - a nurse can perform 0 or more procedures, while a procedure requires 1 or more nurses. Anyway, the relationship Perform here is identified by the composite primary key (ProcedureID, NurseID).
现在，如果我们想跟踪每位护士在执行过程中使用的设备，我们可能会认为简单的三元关系会解决问题:
Now, if we wanted to track the equipment used by each nurse in the performance of the procedure, we might think a simple ternary relationship would do the trick:
，但该关系将由(ProcedureID, NurseID, EquipmentID)识别，从而使我们无法在不使用任何设备的情况下记录辅助手术的护士.我们需要两个独立的关系:
but that relationship would be identified by (ProcedureID, NurseID, EquipmentID), preventing us from recording nurses that assisted in the procedure without using any equipment. What we need is two separate relationships:
(ProcedureID, NurseID)
((ProcedureID, NurseID), EquipmentID)
从第二个到第一个有FK约束，以防止护士不协助操作程序处理设备.
with an FK constraint from the second to the first to prevent nurses not assisting in the procedure from handling the equipment.
回到Have Appointment-这不是口袋妖怪和护士之间的关系(口袋妖怪可以多次见到同一位护士)，这是一个涉及口袋妖怪，护士，程序和药物的事件.最好将其作为具有其他四个关系的常规实体集来处理.至于身份，我想像一下，口袋妖怪或护士一次只能预约一次，因此我们可以选择(PokemonID, DateTime)或(NurseID, DateTime)作为自然键.但是，实际上，我们通常通过代理ID来识别事件，因为事件跨越的间隔是大多数DBMS不能有效地作为主键的时间.
Back to Have Appointment - it's not a relationship between pokemon and nurses (a pokemon can see the same nurse multiple times), it's an event that involves pokemon, nurses, procedures and medicine. It's best handled as a regular entity set with relationships to the other four. As for identity, I imagine a pokemon or nurse can only have one appointment at a time, so we could choose (PokemonID, DateTime) or (NurseID, DateTime) as a natural key. However, in practice we usually identify events by a surrogate ID since events span an interval which most DBMSs can't handle effectively as a primary key.
- 我应该删除2个进行中的处方并开处方并将2个实体与程序和约会医学"直接连接到具有约会关联实体"吗? ERD会现在还正确吗?
不，我认为在将AES转换为常规实体集之后，应该在Pokemon和Have Appointment之间以及在Nurse和Have Appointment之间添加关系.
No, I think you should add relationships between Pokemon and Have Appointment, and between Nurse and Have Appointment, after converting the AES to a regular entity set.
- 如果错了，与问题2一样，我将具有约会"关联实体转变为关系吗?
上面回答了.
这篇关于什么是使用关联实体的正确方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
