Re: Relational Algebra :Division.

From: Stefan Oedenkoven <stefan-oedenkoven_at_gmx.de>
Date: Tue, 29 Jun 2004 20:07:01 +0200
Message-ID: <2kdpdrF15bvcU1_at_uni-berlin.de>

Hi Ben,

you've got already an example.
So I present you the division in RA and SQL

---

Idea:
Q = S / R

1. A = All combinations of dividend_subject and divisor (e.g. relation with all possible pilot - airplane combinations)
2. B = A MINUS divisor (note that we have here already the complement of our desired result - e.g. assume we have PilotX who can use all three airplanes. If we subtract this three tupels from our cartesian product (A), he is not longer listed in relation B.
3. Q = A - B (just the complement of B)
4. project on subject (e.g. pilotname) für duplicate elimination

so we get this (sorry not very readable) Q = proj[Name] (A - (A - B))
or exact:
Q = proj[Name] ( (proj[Name](S) x proj[airplane]R) - (R - (proj[Name](S) x proj[airplane]R) ))

Tupel calculus (to explain why the SQL-Thing works ;-):

---

{[S.Name] | ForAll(R.Airplane) elementOf R: [r.Airplane,s.Name] elementOf R }
ForAll (x) <=> Not Exist Not (x)
{[S.Name] | NOT EXISTS(R.Airplane) elementOf R: [r.Airplane,s.Name] NOT elementOf R }

and SQL:

SELECT S.Name
FROM S
WHERE NOT EXIST (SELECT Airplane

                FROM R
                WHERE  R.Airplane, S.Name NOT IN
                        (SELECT Airplane, Name
                         FROM R))

That means: Select the Pilot for whom we cannot find a airplane in R for which the combination [Pilot, Airplane] in not in R

regards,
Stefan

"ben brugman" <ben_at_niethier.nl> schrieb im Newsbeitrag news:40dfe3cf$0$302$4d4ebb8e_at_read.news.nl.uu.net...
> I am searching for a simple example of the
> Division as in relational algebra.
> (Operations : Union, intersection, difference, cartesian product,
> selection, projections, join and DIVISION).
>
> The divisor should have more than one attribute and more than
> one tuple. The quotient should also be multiple tuples.
> And how to present (express) this division in SQL.
>
> Thanks for your attention.
>
> ben brugman
>
>
Received on Tue Jun 29 2004 - 20:07:01 CEST