英文:
UML ERD Database modelling Query
问题
I have the following tables in my database.
- (Member) is assigned to 1.m facility
- (Facility) has assigned 1.m member
- (Facility) enrolled to 1.m program
- (Program) enrolls 1.m facility
I want to query all member facilities that are not enrolled in a program.
Should there be a direct link between (nacmemberfacility) and (nacFacilityProgram)?
Can two intersecting entities be linked?
What is code for this query?
英文:
I have the following tables in my database.
- (Member) is assigned to 1.m facility
- (Facility) has assigned 1.m member
- (Facility) enrolled to 1.m program
- (Program) enrolls 1.m facility
I want to query all member facilities that are not enrolled in a program.
Should there be a direct link between (nacmemberfacility) and (nacFacilityProgram)?
Can two intersecting entities be linked?
What is code for this query?
答案1
得分: -1
Member - Facility 是根据您的叙述一个多对多的关系。在您的图表中，您将其分解为两个一对多的关系 Member - MemberFacility - Facility（在这方面图表是错误的）。请注意，在这两种情况下，“多”的一侧都在错误的一侧。
对于 Facility - Program 也是一样，您将其转化为了 Facility - FacilityProgram - Program。
如果您想找出所有未参加 Program 的 MemberFacility，则无需直接关系连接 MemberFacility 和 Program，因为您可以从现有的关系中推断出：
select * from MemberFacility
where facilityId not in (select facilityId from MemberProgram);
只有在设施的个别成员是否参加设施所参加的计划时，您才需要直接关系。
英文:
Member - Facility is a many-to-many relationship according to your narrative. In your diagram you have decomposed it into two one-to-many relationships Member - MemberFacility - Facility (the diagram is wrong in this regard). Note that the "many" side is on the wrong side in both cases.
Same for Facility - Program which you transformed into Facility - FacilityProgram - Program
If you want to find out all the MemberFacility that are not enrolled in a Program, you do not need a direct relationship between MemberFacility and Program since you may deduce it from the existing relationships:
select * from MemberFacility
where facilityId not in (select facilityId from MemberProgram);
You would need the direct relationship only if individual members of a facility would or would not participate to the program a facility is enrolled in.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论