Re: turning attibute values into relations?

On 2014-12-11 17:40, ruben safir wrote:
> I know that usenet is bad for homework but this problem is driving me crazy
>
> I need some expertise outside my current ring of contacts.
>
> This question is
> (e) Retrieve the names of employees who work on every project
>
> Which I think is saying find the employees that work on ALL projects.
>

What you are looking for is relational division. Using the universal quantifier ∀ (not sure if this i shown correkt so I'll use Forall below)

Forall x:p(x) where x is project and p is "employee works on". This is not directly supported in SQL, but can be rewritten using Existential quantification:

Forall x:p(x) <=> NOT Exists x : NOT p(x)

I.e. employees for which there is no project such that the employee is not working on it. This is relatively simple to transform to SQL.

For SQL you could also use an approach with employees that work on the same number of projects as there are projects

/Lennart

> How do you turn a relationship into an atribute that can be quried on
>
> This is the relavent relations (tables)
> SQL> descr employee
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> FNAME NOT NULL VARCHAR2(20)
> MINIT CHAR(1)
> LNAME NOT NULL VARCHAR2(20)
> SSN NOT NULL NUMBER(9)
> BDATE NOT NULL DATE
> ADDRESS NOT NULL VARCHAR2(50)
> SEX NOT NULL CHAR(1)
> SALARY NOT NULL FLOAT(12)
> SUPERSSN NUMBER(9)
> DNO
> NUMBER(2)
>
>
> SQL> descr PROJECT
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> PNAME NOT NULL VARCHAR2(20)
> PNUMBER NOT NULL NUMBER(2)
> PLOCATION NOT NULL VARCHAR2(15)
> DNUM
> NUMBER(2)
> SQL> descr works_on
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> ESSN NOT NULL NUMBER(9)
> PNO NOT NULL NUMBER(2)
> HOURS FLOAT(5)
>
>
>
> So you want a query that includes off the essn's in workson that
> associated with all the possible pno and match pnumbers in projects.
>
> I tried this in SQL and in relational algebra and I can't see a path to
> a solution.
>
> Ruben
>
Received on Fri Dec 12 2014 - 12:50:05 CET