postgreSQL查询(在SQL中，关系代数和元组关系微积分)

Question

我正在做一个测试考试，我被困在一个特定的查询，在它的SQL代码，关系代数和元组关系演算。

查询语句如下:找出包含Branch中列出的每种类型分支的(city,state)对关系。

其中Branch为:

Branch_ID (Primary key)
Branch_City
Branch_State
Branch_Type

和City是:

City_Name (Primary key)
State_Name (Primary key)
Population

Branch_City和Branch_State分别是City_Name和State_Name的外键。

"规则"是聚合函数，如COUNT, MAX等不能使用。

查询必须被MySQL和PostgreSQL"理解"，但EXCEPT, INTERSECT等功能在PostgreSQL中可用，而在MySQL中不可以使用。

FROM子句中没有子查询

如前所述，如果能提供sQL、关系代数和元组关系演算的答案，将不胜感激。那些问题把我难住了。

提前感谢!

Answer 1

-- The query states: Find the (city,state) pairs which house a branch of every type which is listed in the Branch relation.
--                                               ((((                    ^^^^^ ^^^^    ))
-- This is equivalent to: Find cities for which "There does NOT EXIST a branchType that is NOT PRESENT in this City"
-- This leads to the double "NOT EXISTS (NOT EXISTS())" solution to relational devision.::
SELECT  * -- city,state
FROM city c
WHERE NOT EXISTS (
        -- find a branchtype that is not present in our city
        SELECT * FROM Branch b
        WHERE NOT EXISTS (
                -- same city for this  branchtype
                SELECT * FROM Branch nx
                WHERE nx.Branch_City = c.City_Name AND nx.Branch_State = c.State_Name
                AND nx.Branch_Type = b.Branch_Type
                )
        )
        ;

关系除法是这类操作的术语。

顺便说一句:city表的复合(城市，州)主键只是为了迷惑您。通常，您将使用数字(代理)city_id作为城市表的主键，并将其用作branches表中的外键。

Answer 2

这是SQL Server语法，因为我没有MySql或PostGresSQL，但它应该给你的想法:

with branches as (
  select * from ( values
    ('Perth',1),
    ('Toronto',1), ('Toronto',2), ('Toronto',3),
    ('Hamilton',2), ('Hamilton',3)
  ) branches(City,  Branch_Type)
)
  select distinct
    City
  from branches
except
  select distinct 
    b.City
  from branches t 
  cross join branches b 
  left join branches b2 on b2.Branch_Type = t.Branch_Type and b2.City = b.City
  where b2.Branch_Type is null

为了演示必要的集合操作，我将其缩减到最小值。

查询的上半部分返回所有三个城市;下半场只有汉密尔顿和珀斯复出;使整个查询只返回多伦多。

我已经30年没用过关系代数或关系微积分了，但是用这些方言表达上面的查询只是一个翻译练习。

MySQL:

with branches as (
  select * from ( values
    ('Perth',1),
    ('Toronto',1), ('Toronto',2), ('Toronto',3),
    ('Hamilton',2), ('Hamilton',3)
  ) branches(City,  Branch_Type)
)
select distinct
  City
from branches
where City not in (
  select distinct 
    b.City
  from branches t 
  cross join branches b 
  left join branches b2 on b2.Branch_Type = t.Branch_Type and b2.City = b.City
  where b2.Branch_Type is null
  )

因为子查询是在WHERE子句而不是FROM子句中，所以应该是合法的。它可以表示为左连接，但我认为这会将子查询移动到FROM子句中。