e.g. I create the entity EMP in a logical Subview. Two attributes are here essential for a self-reference: EMPNO and MGR. So each Employee has a reference to his boss. And this information is stored in MGR.
Now I create the entity EMP with the 8 columns from the SCOTT/TIGER schema. After that I define a 1:N-Relation between EMP and EMP. but it looks like I cannot define, which attributes are starting point of the relation and destination.
Who can help me here?
That kind of relationship is called recursive relationship. While defining such a relationship we have to consider
1 - The Cardinality
2 - The Modality
In the case of an EMP entity/table we have a cardinality from EMP to EMP of one-to-many such as EMP:EMP::1:M which means that an employee will have only one MGR but an MGR will have many employees.
The Modality from EMP to EMP will be optional/mandatory or partial/full which means that an employee may or may not have a MGR but a MGR must have EMP (at least theortically speacking)
The Logical Model
The Relational Model
you cannot select destination (as you said) attribute - they are automatically generated. However you can achieve what you want - it depends on your starting point:
1) start from logical model (analysis first)
- you create 1:n recursive relationship -
- change the name of created FK attribute to MGR or what ever you want - this is controlled by "FK Attribute name synchronization" setting at "General options>Model>Logical" page
2) start from relational model (reverse engineering of existing status)
- create self reference foreign key - select MGR as FK column;
- reverse engineer to logical model - you'll have FK attribute named MGR