SQL查询(在SQL、关系代数和元组关系演算中)

Im doing a test exam where I've gotten stuck on one particular query, in both its SQL code, relational algebra and tuple relational calculus.

我正在做一个测试，在这个测试中，我遇到了一个特定的查询，它包含SQL代码、关系代数和元组关系演算。

The query states: Find the (city,state) pairs which house a branch of every type which is listed in the Branch relation.

查询状态:查找包含分支关系中列出的所有类型的分支的(城市、州)对。

Where Branch is:

分支:

Branch_ID (Primary key)
Branch_City
Branch_State
Branch_Type

and City is:

和城市是:

City_Name (Primary key)
State_Name (Primary key)
Population

And Branch_City and Branch_State is a foreign key to City_Name and State_Name respectively.

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

The "rules" are that aggregate functions, such as COUNT,MAX etc may not be used.

“规则”是聚合函数，如COUNT、MAX等可能不被使用。

The query must be "understood" by MySQL and PostgreSQL however functions like EXCEPT, INTERSECT available in PostgreSQL but not in MySQL can be used.

查询必须由MySQL和PostgreSQL“理解”，但是可以使用诸如EXCEPT、INTERSECT等函数，这些函数可以在PostgreSQL中使用，但不能在MySQL中使用。

No subqueries in the FROM clause

FROM子句中没有子查询

As said, it would be greatly appreciated if answers could be provided for sQL, relational algebra and tuple relational calculus. Those questions has stalled me.

如前所述，如果可以为sQL、关系代数和元组关系演算提供答案，将非常感谢。这些问题把我难住了。

Thanks in advance!

提前谢谢!

2 个解决方案

#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
                )
        )
        ;

Relational division is the term for this type of operation.

关系分割是这种类型的操作的术语。

BTW: the composite (city,state) primary key for the city table is only there to confuse you. Normally, you would use a numerical (surrogate) city_id as a primary key for the city table, and also use that as a foreign key in the branches table.

顺便说一句:城市表的合成(城市、州)主键只会让你感到困惑。通常，您将使用数字(代理)city_id作为城市表的主键，并将其用作分支表中的外键。

#2

This is SQL Server syntax, because I do not have MySql or PostGresSQL, but is should give you the idea:

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

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

I have trimmed it down to the bare minimum to demonstrate the necessary set operations.

我已经把它削减到最低限度，以显示必要的集合操作。

The top half of the query returns all three cities; the second half returns only Hamilton and Perth; so that the whole query returns only Toronto.

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

I haven't used either Relational Algebra or Relational Calculus in 30 years, but exxpressing the above query in those dialects is simply a translation exercise.

我已经有30年没有使用关系代数或关系演算了，但是exxonpress在这些方言中使用上述查询仅仅是一个翻译练习。

Update - for MySQL:

更新——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
  )

Since the sub-query is in the WHERE clause instead of the FROM clause this shold be legitimate. It could be expressed as a left join, but I think that moves the sub query into the FROM clause.

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

#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
                )
        )
        ;