Is this natural join operation used correctly? (Relational Algebra)

Question

I have the following task given from the professor:R-E Modell

5. Assume the companies may be located in several cities. Find all companies located in every city in which “Small Bank Corporation” is located.

Now the professor's solution is the following:

s ← Π city (σ company_name=’Small Bank Corporation’ (company))
temp1 ← Π comp_id, company_name (company)
temp2 ← Π comp_id, company_name ((temp1 × s) − company)
result ← Π company_name (temp1 − temp2)

I for myself found a completely different solutions with a natural join operation which seems much simpler:

What I tried to do was using the natural joint operation which whe defined as following that a relation r and s are joined on their common attributes. So I tried to get all city names by using a projection on a selection of all companies with the company_name "Small Bank Cooperation". After that I joined the table with the city names with the company table, so that I get all company entrys which have the city names in it.

company ⋈ Π city (σ company_name=”Small Bank Cooperation” (company)))

My question now is if my solution is also valid, since it seems a little bit to trivial?

Answer 1

Yours isn't the same.

My answer here says how to query relationally. It uses a version of the relational algebra where headings are sets of attribute names. My answer here summarizes it:

Every query expression has an associated (characteristic) predicate--statement template parameterized by attributes. The tuples that make the predicate into a true proposition--statement--are in the relation.

We are given the predicates for expressions that are relation names.

Let query expression E have predicate e. Then:

• R ⨝ S has predicate r and s
• R ∪ S has predicate r or s
• R - S has predicate r and not s
• σ p (R) has predicate r and p
• π A (R) has predicate exists non-A attributes of R [r]

When we want the tuples satisfying a certain predicate we find a way to express that predicate in terms of relation operator transformations of given relation predicates. The corresponding query returns/calculates the tuples.

Your solution

company ⋈ Π city (σ company_name=”Small Bank Corporation” (company)))

is rows where

    company company_id named company_name is in city
AND FOR SOME company_id & company_name [
            company company_id named company_name is in city
        AND company_name=”Small Bank Corporation”]

ie

    company company_id named company_name is in city
AND FOR SOME company_id [
        company company_id named ”Small Bank Corporation” is in city]

ie

    company company_id named company_name is in city
AND some company named ”Small Bank Corporation” is in city

You are returning rows that have more columns than just company_name. But your companies are not the requested companies.

Projecting your rows on company_name gives rows where

    some company named company_name is in some city
AND some company named ”Small Bank Corporation” is in that city

After that I joined the table with the city names with the company table, so that I get all company entrys which have the city names in it.

That isn't clear about what you get. However the companies in your rows are those in at least one of the SBC cities. The request was for those in all of the SBC cities:

companies located in every city in which “Small Bank Corporation” is located

The links I gave tell you how to compose queries but also convert between query result specifications & relational algebra expressions returning a result.

When you see a query for rows matching "every" or "all" of some other rows you can expect that that part of your query involves relational-division or some related idiom. The exact algebra depends on what is intended by the--frequently poorly/ambiguously expressed--requirements. Eg whether "companies located in every city in which" is supposed to be no companies (division) or all companies (related idiom) when there are no such cities. (The normal mathematical interpretation of your assignment is the latter.) Eg whether they want companies in exactly all such cities or at least all such cities.

(It helps to avoid "all" & "every" after "find" & "return", where it is redundant anyway.)

Database Relational Algebra: How to find actors who have played in ALL movies produced by “Universal Studios”?
How to understand u=r÷s, the division operator, in relational algebra?
How to find all pizzerias that serve every pizza eaten by people over 30?