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.

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:
(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!

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.

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