Hi,
I have 2 entities named by:"Department" and "Professor" which are related in this manner:
Professor(ProfID,....,DeptID)-----------Department(DeptID,....,ManagerID)

which managerID is a professors id in this system.OK?so I have a FK from Professor to Deptartment and another from Department to Professor.
So I want to insert records into this two tables but any one could not be first because its FK is there!
What should I do? Is there any problem with this design?
-any solution is appreciated

Use a deferred foreign key constraint on one (or both) of the tables (if your DBMS supports them). A deferred constraint is not validated until the end of the transaction (i.e. COMMIT statement):

insert into Professor(ProfID,....,DeptID) values (123, ..., 400);
insert into Department(DeptID,....,ManagerID) values (400, ..., 123);
commit;

Thanx Andrew for your quick reply,
now consider I have many records in both tables and want to truncate both tables in Oracle so should I disable FKs? How can I do this in Oracle?
and if some DBMS unlike Oracle does not support deferable FKs what should I do?
-thanks again

Some of the tricks that you might consider:
- make department.manager_id nullable; you'll then insert a department record with manager_id set to null, insert a professor record, then update the department record to set manager_id to whatever;
- if any professor can only be a manager of one department at a time you can eliminate department.manager_id altogether and add an attribute to the professor record indicating that the professor is a manager of that department;
- create a relationship table so that there are no foreign keys between department and professor, only between the relationship table and the other two.

There's one more:

Professor(ProfID,...)

ProfessorDepartment(ProfID(FK),DeptID(FK),ManagerY N)

Department(DeptID,...)

I agree with certus's model.

Whenever you have such a problem, it points to an area where the design can be improved. In plain English, the design is wrong.

I do not have these problems because my design takes care of them and I do not have to resort to arcane code and in-depth knowledge of various DBMS engines to solve self-created problems.

Ravi

But what if the manager doesn't have to be in the same department?

There is nothing instrinsically "wrong" with the original table design - it is just different.

Ok, Let's see. Here is what I understand to be the requirements,

1. Need to keep professor info.
2. Need to keep dept info.
3. Need to associate a prof with a dept. (Reasonable to assume that a professor will not move from one dept to another.)
4. Need to have managers for dept.

Then the model as defined by certus with some simple modification works.

1. Professor (prof_id, name, ..., dept_id)
2. Dept (dept_id, name, location, ...)
3. Dept_Manager(dept_id, prof_id, start_dt, end_dt).

This generally avoids all the problems that we saw and whose solution depended upon the DBMS we are using.

I am generally uncomfortable with circular relationships.

Ravi

Hi,
Let me explain the model more precisely:There are two relationships in fact:
1. "Membership" between prof and dept that is N..1 and I designed this by putting the DeptID in Prof table.
2."Management" again between prof and dept which is 1..1 and departments participation is mandatory here.So I designed this one by putting the ManagerID (profID) in Dept table. Note that one dept has just ONE manager.

So the initial design is not wrong (You can refer to accepted references such as Date and Elmaseri and...).IMO it should be a nice alternative than designing this model as a M..N relationship by using a relation_table.

-thanks again