2 Replies Latest reply: Sep 14, 2009 10:02 AM by Philip Stoyanov-Oracle RSS

    Data Modeler: How to create a self reference in ER?

    119596
      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?
      Gerd
        • 1. Re: Data Modeler: How to create a self reference in ER?
          710217
          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
          http://i32.tinypic.com/290y5ix.jpg

          The Relational Model
          http://i30.tinypic.com/2aaiyb9.jpg
          • 2. Re: Data Modeler: How to create a self reference in ER?
            Philip Stoyanov-Oracle
            Gerd,

            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

            Philip