SQL 到关系代数
我该如何为这个 SQL 查询编写关系代数？
Select patient.name,
patient.ward,
medicine.name,
prescription.quantity,
prescription.frequency
From patient, medicine, prescription
Where prescription.frequency = "3perday"
AND prescription.end-date="08-06-2010"
AND canceled = "Y"
关系...
处方
- 处方-参考
- 患者-参考
- 药物-参考
- 数量
- 频率
- 结束日期
- 取消（是/否））
药物
- 药物参考
- 名称
患者< /strong>
- 患者参考
- 姓名
- 病房
How do I go about writing the relational algebra for this SQL query?
Select patient.name,
patient.ward,
medicine.name,
prescription.quantity,
prescription.frequency
From patient, medicine, prescription
Where prescription.frequency = "3perday"
AND prescription.end-date="08-06-2010"
AND canceled = "Y"
Relations...
prescription
- prescription-ref
- patient-ref
- medicine-ref
- quantity
- frequency
- end-date
- cancelled (Y/N))
medicine
- medicine-ref
- name
patient
- Patient-ref
- name
- ward
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我只是指出你应该使用的运算符
投影 (π)
π(a1,...,an)：结果定义为当 R 中的所有元组都受到限制时获得的集合到集合 {a1,...,an}。
相同
例如，患者表上的 π(姓名) 与 SELECT 姓名 FROM 患者选择 (σ)
σ(条件) ：选择R 中条件成立的所有元组。
相同
例如，处方表上的 σ(频率 = "1perweek") 与 SELECT * FROM处方 WHERE 频率 = "1perweek" 叉积(X)
RXS ：结果是 R 和 S 之间的叉积。
例如，患者 X 处方将是 SELECT * FROM 患者,处方
您可以组合这些操作数来解决您的练习。如果您有任何问题，请尝试发布您的尝试。
注意：我没有包含自然连接，因为没有连接。对于这个练习来说，叉积应该足够了。
I will just point you out the operators you should use
Projection (π)
π(a1,...,an): The result is defined as the set that is obtained when all tuples in R are restricted to the set {a1,...,an}.
For example π(name) on your patient table would be the same as SELECT name FROM patient
Selection (σ)
σ(condition): Selects all those tuples in R for which condition holds.
For example σ(frequency = "1perweek") on your prescription table would be the same as SELECT * FROM prescription WHERE frequency = "1perweek"
Cross product(X)
R X S: The result is the cross product between R and S.
For example patient X prescription would be SELECT * FROM patient,prescription
You can combine these operands to solve your exercise. Try posting your attempt if you have any issues.
Note: I did not include the natural join as there are no joins. The cross product should be enough for this exercise.
一个例子如下所示。仅当您不小心遗漏了患者、药物和处方之间的连接时才会出现这种情况。如果没有，您将寻找叉积（在这种情况下这似乎是一个坏主意......），正如 Lombo 提到的。我给出了可能适合标记为“???”的表的示例连接。如果您可以包括表格的布局，那将会很有帮助。
我还假设取消来自处方，因为它没有前缀。
编辑：如果您需要标准 RA 形式的它，可以很容易地从图表中获得。
替代文本 http://img532.imageshack.us/img532/ 8589/diagram1b.jpg
An example would be something like the following. This is only if you accidentally left out the joins between patient, medicine, and prescription. If not, you will be looking for cross product (which seems like a bad idea in this case...) as mentioned by Lombo. I gave example joins that may fit your tables marked as "???". If you could include the layout of your tables that would be helpful.
I also assume that canceled comes from prescription since it is not prefixed.
Edit: If you need it in standard RA form, it's pretty easy to get from a diagram.
alt text http://img532.imageshack.us/img532/8589/diagram1b.jpg