我的关系代数正确吗？

发布于 2024-07-12 08:34:54 字数 818 浏览 6 评论 0原文

我有一个数据库作业，我必须为两个问题创建一些关系代数。我对其中的大部分感觉还不错，但当我尝试从连接到另一个表的表中投影属性时，我只是感到困惑。

例如，这是正确的吗？

Q1) 列出没有拨打电话的事件的详细信息，以便接待员知道我试图将其

RESULT <-- PROJECT<STUDENT.FirstName, STUDENT.LastName, STAFF.FirstName,
STAFF.INCIDENT.LastName, INCIDENT.DateTimeReported, 
INCIDENT.NatureOfIllness(SELECTINCIDENT.DecisionMade = 
  ''(Staff RIGHT JOIN<STAFF.StaffID = INCIDENT.StaffID>
(INCIDENT LEFT JOIN<INCIDENT.StudentID = STUDENT.StudentID>(STUDENT))))

解释为关系代数的 SQL 是：

SELECT 
  s.FirstName, s.LastName, st.FirstName, st.LastName
  , i.DateTimeReported, i.NatureOfIllness
FROM Student s 
RIGHT JOIN Incident i ON s.StudentID = i.StudentID  
LEFT JOIN Staff st ON st.StaffID = i.StaffID
WHERE i.DecisionMade = ''

任何建议点将不胜感激。

原文

I have a database assignment which I have to create some relational algebra for two problems. I feel fairly all right with the majority of it, but I just get confused when trying to project attributes out of a table which is joined to another table.

for example is this correct?

Q1) List the details of incidences with no calls made, so that the receptionist knows
which incidents still need to be called in.

RESULT <-- PROJECT<STUDENT.FirstName, STUDENT.LastName, STAFF.FirstName,
STAFF.INCIDENT.LastName, INCIDENT.DateTimeReported, 
INCIDENT.NatureOfIllness(SELECTINCIDENT.DecisionMade = 
  ''(Staff RIGHT JOIN<STAFF.StaffID = INCIDENT.StaffID>
(INCIDENT LEFT JOIN<INCIDENT.StudentID = STUDENT.StudentID>(STUDENT))))

The SQL which I am trying to interpret into relational algebra is:

SELECT 
  s.FirstName, s.LastName, st.FirstName, st.LastName
  , i.DateTimeReported, i.NatureOfIllness
FROM Student s 
RIGHT JOIN Incident i ON s.StudentID = i.StudentID  
LEFT JOIN Staff st ON st.StaffID = i.StaffID
WHERE i.DecisionMade = ''

Any points of advice would be much appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

抱着落日 2024-07-19 08:34:54

如果您全部使用 LEFT JOIN 编写，通常（当然也有一些例外）更容易阅读和理解 sql：

SELECT s.FirstName, s.LastName, st.FirstName, st.LastName, i.DateTimeReported, i.NatureOfIllness
FROM Incident i
LEFT JOIN Student s ON s.StudentID = i.StudentID
LEFT JOIN Staff st ON st.StaffID = i.StaffID
WHERE i.DecisionMade = ''

It's usually (some exceptions apply, of course) easier to read and understand the sql if you write it all with LEFT JOINs:

SELECT s.FirstName, s.LastName, st.FirstName, st.LastName, i.DateTimeReported, i.NatureOfIllness
FROM Incident i
LEFT JOIN Student s ON s.StudentID = i.StudentID
LEFT JOIN Staff st ON st.StaffID = i.StaffID
WHERE i.DecisionMade = ''

回复收藏 0 原文

随梦而飞# 2024-07-19 08:34:54

您的版本似乎是正确的，除了一些拼写错误，例如 STAFF.INCIDENT.LastName。这是我的版本：

RESULT <--- 
   PROJECT <STUDENT.FirstName, STUDENT.LastName, 
                     STAFF.FirstName, STAFF.LastName,
                     INCIDENT.DateTimeReported, INCIDENT.NatureOfIllness>
      (SELECT <INCIDENT.DecisionMade = ''> 
         ((STUDENT RIGHT JOIN <STUDENT.StudentID = INCIDENT.StudentID> INCIDENT)
             LEFT JOIN <INCIDENT.StaffID = STAFF.StaffID> STAFF)

Your version seems correct, except for some typos like STAFF.INCIDENT.LastName. Here's my version:

RESULT <--- 
   PROJECT <STUDENT.FirstName, STUDENT.LastName, 
                     STAFF.FirstName, STAFF.LastName,
                     INCIDENT.DateTimeReported, INCIDENT.NatureOfIllness>
      (SELECT <INCIDENT.DecisionMade = ''> 
         ((STUDENT RIGHT JOIN <STUDENT.StudentID = INCIDENT.StudentID> INCIDENT)
             LEFT JOIN <INCIDENT.StaffID = STAFF.StaffID> STAFF)

回复收藏 0 原文

~没有更多了~