Can anybody give an example of the following case:
Entity A has a 1:n AND a n:1 relation to Entity B. An example of this scenario that makes sense is greatly appreciated.
(the cardinalities dont really matter, what's important is that there are two different relations between two entites. Of course, if the two relations have the same cardinalities, one is redundant.)

Personally I think a case like this seems to be a design error.

This is not a homework-question. I currently develop a tool which translates one model (ERD) to another. During the transformation process entities are merged, which could lead to such a scenario as described above. Now, i have to find a way to solve this. I have thought of "relation-precedences". To be exact: m:n binds stronger than n:1, n:1 binds stronger than 1:n, 1:n binds stronger than 1:1. I'm not quite sure of this. What do you think?

But before i just remove relations that bind weaker than others, I need to know if there might be examples that _require_ 2 relations between 2 entities.

Any feedback is greatly appreciated.

mc_fly

A simple case is one where two (or more) employees are required to perform supervisory/disciplinary actions on other employees... One relationship between employees shows which employees form management groups, the other relationship shows which groups manage which employees.

-PatP

I think, in that case the model would look like this:

first relation:
employees >----- managementgroups
many employees belong to one management-group

second relation:
employees >------< managementgroups
many employees are managed by many management-groups

where the second relation is a violation to 3NF, hence a new entity is required. The second relation would become something like this:

employees ----< e/m >---- managementgroups


Another thought:
If there is only one management group, the second relation would look like this:
employees >---- managementgroup
many employees are managed by one managementgroup

this relation is redundant, because its the same as the first one.

okay, here's a great example

employee-project is many-to-many (an employee can be assigned to multiple projects, a project can have multiple employees assigned to it)

employee-project is one-to-many (an employee can be the project manager of multiple projects, but a project can have only one project manager)

in implementation, the first relationship always requires a third, intersection or junction table, holding two foreign keys, which themselves comprise a composite primary key

the second relationship is implemented by placing the foreign key of the project manager into the project entity

I think there is one issue though.
Since the m:n relation is resolved with an intersection entity, there are not two entities connected with two relations. but rather one relation between employee and project, one relation between project and intersection entity, one relation between intersection entity and employee.

I think there are no such examples, if no named relations are introduced. that way, two entities, employees and managementgroups, could be related like this:

employee >--- are managed --- managementgroup
employee >--- manages --- managementgroup

but this is very conceptual, just illustrating how the two entities can be related. Something like this would be redundant in a real world application.

Ideas?

I'm not sure I follow your reasoning. Are you saying that an entity model is necessarily invalid if it contains 2 relationships "A --(1:n)-- B" and "A --(n:1)-- B"? I don't see why. It may be possible to model it differently perhaps, but that doesn't make it wrong. How about:

PERSON --- owns/owned by ----< HOUSE

PERSON >-- lives in/occupied by --- HOUSE

These are two distinct relationships between PERSON and HOUSE. Assuming both are true independently (i.e. there is no joint ownership, etc.), then why should they not be true when considered together?

Note that a PERSON don't have to own the HOUSE he lives in, or live in the HOUSE he owns! If either of those held true then the model might be different.

You could generalize the model like this:

PERSON --< PERSON_HOUSE_RELATIONSHIP >-- HOUSE

with an attribute Relationship Type in the intersection that can be 'OWNS' or 'LIVES IN'. But now you have to find a new way to enforce the rules that a PERSON can only live in one HOUSE etc.

(BTW, please don't refer to relationships as "relations" - it is both wrong and confusing, since "relation" is in fact the proper term for a "table"!)

this is indeed an example that makes sense and is exactly what i was thinking of in my initial scenario.
damn, i'm in trouble now. this whole transformation thing is more complex than i have expected it to be.

thanks a lot for all the feedback. especially yours andrewst.

mc_fly