Re: Combinatorial problem

On 11/29/05 6:44 PM, "Srinivas Iyyer" <srini_iyyer_bio(at)yahoo(dot)com> wrote:

> Dear Group,
> I have a piece of data that is more relational.
>
> The following is an illustrative example:
>
>
> Drug Target
> m1 T1
> m2 T1
> m3 T2
> m2 T3
> m2 T2
> m4 T4
> m1 T4
> m5 T1
> m5 T5
> m6 T3
> m6 T4
> m6 T5
> m6 T6
> .. ..
> .. ..
> m3200 T9000
>
>
> In this example m1 drug targets are T1 and T4.
>
> m2 drug targets are T1,T2,T3.
>
> Likewise, I have over 3000 drugs and over 9000
> targets.
>
>
> The question that I asked is:
>
> For a given target what are the unique Drugs (such as
> m1,m4,m6)
> For a group of related drugs (m1,m2 and m3) what are
> the targets.
>
>
> I am novice programmer in python and R-statistical
> language. I attempted in both these and it is proving
> very very difficult.
>
> I chose a database approach.
>
> I created 3 tables:
>
> A drug table 'Drug'
> A target table 'Target'
> A combined table with mapping information 'Comb'
>
> (in comb table I have the mapping information as
> illustrated in the above example)
>
>
> Drug:
> --------------------
> drug_id | drug_name
> --------------------
> 1 | m1
> 2 | m2
> 3 | m3
>
>
> Here drug_id is primary key and made as serial.
> drug_name is varchar(10)
> Target:
>
> --------------------
> target_id | target_name
> --------------------
> 1 | T1
> 2 | T2
> 3 | T3
>
> target_id is 'Serial' primary key
> Target_name is varchar(20)
>
>
> Comb:
>
> --------------------
> drug_id | target_ID
> --------------------
> 1 | 1
> 2 | 1
> 1 | 2
> 1 | 4
> 1 | 432
>
>
>
> Now my question is how can I ask SQL the question:
>
> For T3 what are the Drugs ?

select drug_name
from drug, target, comb
where drug.drug_id=comb.drug_id and
comb.target_id=target.target_id and
target_name='T3';

> For T4 what are the drugs?
>
> For a group of drugs m1,m3,m5 what are the targets?

select target_name
from drug, target, comb
where drug.drug_id=comb.drug_id and
comb.target_id=target.target_id and
drug_name in ('m1','m3','m5');

Three points: In postgresql, capital letters in column names and table
names are folded to lower case if you don't use "" around them ALL THE TIME.
Therefore, I tend to not use any capitals. Second, there are a number of
good SQL tutorials online that will be quite helpful for learning how to
squeeze information from your database. Lastly, if you are an R user, be
sure to check out RdbiPgSQL, available via the BioConductor site, for
interfacing R with Postgres.

Sean