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.
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.
The "rules" are that aggregate functions, such as COUNT,MAX etc may not be used.
The query must be "understood" by MySQL and PostgreSQL however functions like EXCEPT, INTERSECT available in PostgreSQL but not in MySQL can be used.
No subqueries in the FROM clause
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.
Thanks in advance!
-- 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.