關係代數查詢 - 如何解決問題？

我有以下架構：

employee(sin, emp_name, emp_address, salary, supervisor_sin, dept#)

works_on(emp_sin, project_number, hours)

問題是：

Assume that no employee works on more than 2 projects. For those employees working on two projects, give the employee name and the two project numbers. Each employee name should appear once only

到目前為止我的解決方案：

R1 = works_on [theta-join]emp_sin=d.emp_sin [Rename]d(works_on)- 以“emp_sin | project_number | d.project_number | hours | d.hours”的形式獲取元組？

Result = [Projection]emp_name,project_number,d.project_number(employee [theta-join]sin=emp_sin R1)- 以“emp_name | project_number | d.project_number”形式獲取所有名稱和 2 個項目編號？

這是正確的想法嗎？我錯過了什麼嗎？

我認為你在正確的軌道上，但我沒有看到將結果限制在實際從事兩個項目的員工的規定，重命名符號讓我感到困惑。您可能希望在自聯接上利用 theta-join 來完成此操作。

首先，我將重命名工作表中的相關屬性以準備自聯接：

ρ sin/emp_sin,project_number1/project_number works_on

然後為簡單起見僅投影這些屬性：

π sin,project_number1 ( ρ sin/emp_sin,project_number1/project_number works_on )

出於視覺化目的，此處等效的 SQL 將類似於以下內容：

SELECT  emp_sin 
           AS sin,
       project_number
           AS project_number1
FROM    works_on;

然後我會通過創建另一個關係來準備自我加入，重命名project_number不同的：

π sin,project_number2 ( ρ sin/emp_sin,project_number2/project_number works_on )

此時，可以應用 theta-join，利用假設中定義的最大 2 carnality。project_numbers這個 theta-join通過以下方式與兩者建立關係emp_sin：

( π sin,project_number1 ( ρ sin/emp_sin,project_number1/project_number works_on ) ) ⋈ sin, project_number1 < project_number2 ( π sin,project_number2 ( ρ sin/emp_sin,project_number2/project_number works_on ) )

實際上，這類似於 SQL，如下所示：

SELECT  *
FROM (  SELECT  emp_sin 
                   AS sin,
               project_number
                   AS project_number1
       FROM    works_on ) AS w1
INNER JOIN (
       SELECT  emp_sin 
                   AS sin,
               project_number
                   AS project_number2
       FROM    works_on ) AS w2 
   ON  w1.sin = w2.sin
   AND w1.project_number1 < w2.project_number2;

此處需求中的小漏洞讓我們進行自然連接和最終投影以完成（因為我已經將emp_sin屬性重命名為sin）：

π emp_name, project_number1, project_number2 ( 員工 ⋈ ( π sin,project_number1 ( ρ sin/emp_sin,project_number1/project_number works_on ) ) ⋈ sin, project_number1 < project_number2 ( π sin,project_number2 ( ρ sin/emp_sin,project_number2/project_number works_on ) )

同樣，作為視覺證明，這大致相當於以下 SQL，在此 SQL Fiddle中顯示：

SELECT  e.emp_name, w1.project_number1, w2.project_number2
FROM    dbo.employee e
INNER JOIN (
       SELECT  emp_sin 
                   AS sin,
               project_number
                   AS project_number1
       FROM    dbo.works_on ) AS w1
   ON  e.sin = w1.sin
INNER JOIN (
       SELECT  emp_sin 
                   AS sin,
               project_number
                   AS project_number2
       FROM    dbo.works_on ) AS w2 
   ON  w1.sin = w2.sin
   AND w1.project_number1 < w2.project_number2;

引用自：https://dba.stackexchange.com/questions/77964