Results 1 to 4 of 4

Thread: cycle

  1. #1
    Join Date
    Jan 2005
    Posts
    14

    Question cycle

    Hi all,

    1)I would to link the department with project table by inserting the dept_id foreign key in the project table. The relationships are in the cycle as you can see in the diagram. The reason that I want to do this so that I can query the number of project with respect to the department.If i dont link project with department table. It wont reflect the true number of projects. My question: IS it ok to link department with project? Please i need your views.


    2) If I were to query (list) the project according to their respective department. I have to link project, pro_assign, investigator, professor, dept_assign, department student.

    the sql will be:

    select e.dept_name, count(a.pro_id)
    from project a, pro_assign b , investigator c , student d , department e
    where a.pro_id= b.pro_id and b.inv_id = c.inv_id and c.inv_id = d.stu_id and d.dept_id = e.dept_id
    group by e.dept_name;

    I found out that i can only link all the tables in one way (like the sql query above) or vice versa through professor.

    If I to link the tables both way. for example:

    select e.dept_name, count(a.pro_id)
    from project a, pro_assign b , investigator c , student d , department e, professor f, dept_assign g
    where a.pro_id= b.pro_id and b.inv_id = c.inv_id and c.inv_id = d.stu_id and d.dept_id = e.dept_id and c.inv_id = f.prof_id and f.prof_id = g.prof_id and g.dept_id = e.dept_id

    the database will give empty set. No result.

    My Question: Any proper way to link the tables in sql queries?

    Please Advise. Thank you very much.
    Attached Thumbnails Attached Thumbnails diagram.JPG  

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    1) Yes, if a foreign key from Project to Department is meanigful and valid then it should be added.

    2) Presumably each Investigator is either a Professor or a Student? That explains why your "both ways" query returned no rows, since it will only return rows for Investigators who are both. If you wanted all Investigators you need an OR rather than an AND:
    Code:
    select e.dept_name, count(a.pro_id)
    from project a, pro_assign b , investigator c , student d , department e, professor f, dept_assign g
    where a.pro_id= b.pro_id
    and b.inv_id = c.inv_id
    and (  (c.inv_id = d.stu_id and d.dept_id = e.dept_id)
        or (c.inv_id = f.prof_id and f.prof_id = g.prof_id and g.dept_id = e.dept_id)
        )
    (As an aside, it helps if you apply some formatting to your SQL. It would also be better to use mnemonic table aliases like "d" or "dept" for "department", rather than just running through the alphabet!)

    Which query is the "proper" way to link the tables depends on what question you are trying to answer: they are all valid, but answer different questions:
    1) How many projects are there per department, associated with investigators who are students from that department?
    2) How many projects are there per department, associated with investigators who are both students and professors from that department? (A: 0 rows)
    3) How many projects are there per department, associated with investigators who are professors from that department?
    4) How many projects are there per department, associated with investigators who are either students or professors from that department?

    Write the SQL that answers the question you need answered!

  3. #3
    Join Date
    Jan 2005
    Posts
    14
    So, it's ok to link project with department table using dept_id FK. I think it will eliminate the problem of querying for How many projects are there per department, associated with investigators who are both students and professors from that department? (A: 0 rows) which does give any result.

    This is infact will shorten the sql query:
    select dept_id, count(pro_id)
    from project
    group by dept_id;

    Now i can query -> List the professors and graduate students connected with the department with the most projects.

    select c.inv_id, c.inv_name
    from project a, pro_assign b , investigator c
    where a.pro_id = b.pro_id and b.inv_id = c.inv_id and a.dept_id = (select dept_id
    from project
    group by dept_id
    having count(pro_id) > all (select count(pro_id) from project group by pro_id));

    i found that this query wont work if the project wrt to their deparment have the same number of projects.
    Last edited by roustabout; 01-21-05 at 12:18.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Yes. However, do bear in mind that this now answers a 5th different question, with a 5th different answer! i.e. "How many projects have been explicitly linked to each department?"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •