2

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

  1. 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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
hikari_temp
  • 25
  • 4
  • You don't use “Small Bank Corporation'. There is no column 'name'. But your general approach is not right. [Re relational querying.](https://stackoverflow.com/a/24425914/3404097) PS Nested RA calls form a programming language. So give as much of a [mre] as you can, even if you are not actually running code. But also--Google 'run relational algebra online'. PS This is a case of 'relational division'. It has faqs & a tag. – philipxy Oct 20 '20 at 09:27
  • You don't actually explain how "all company entrys which have the city names in it" for the city names you join with--namely just 'SBC'--is the requested set of companies. (It isn't.) – philipxy Oct 20 '20 at 10:17
  • Why isnt it the requested set of companies? I get a table with all the cities where Small Bank Cooperation is stationed, don't I? After that I take those city names and look for companies which have the city attributes in common (natural join). – hikari_temp Oct 20 '20 at 10:29
  • 1
    Your comment & post are unclear. Please don't ask us to disprove you when you don't clearly justify. (Your wrong justification has led to wrong code.) Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. (A subexpression returns (all) the SBC cities. But you don't finally return companies in all of those, ie in every one of those; you return companies in at least one of those.) – philipxy Oct 20 '20 at 10:55
  • What I just said is, your assignment is companies in all the SBC cities ie every SBC city, but you return companies in at least one of the SBC cities. I suggested that you work through an example, run an example, give the membership condition for every subexpression's table, read about division & read how to build a query (also give table definitions in text & format your code as code not wrapped prose), but you haven't; but I hope you do. (Again: Your last step about what the join returns is unjustifed). (I am editing an answer re what your query does.) – philipxy Oct 20 '20 at 11:24

1 Answers1

3

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 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?

philipxy
  • 14,867
  • 6
  • 39
  • 83