Module 5 - One to One and Recursive Relationships

One-to-One and Recursive Relationships

• Instructor: Nikhil Srinivasan

• Course: Management Information Systems at Terry College, University of Georgia

Organization Chart

• Displays the structure of an organization, listing key roles such as Managing Director and various department heads.

• Underlying data model implied in organization structures.

Modeling a 1:1 Relationship

• Definition: A 1:1 relationship signals a direct link between two entities where one corresponds to exactly one of the other.

• Relationship descriptor should be labeled, though obvious relationships may not need labels.

Labeling a Relationship

• Access through: Workbench Preferences > Diagram.

• Options include:

  • Diagram appearance (object colors, column types, etc.).

  • Control over captions and visibility.

Editing Relationships

• Example attributes include:

  • dept: Department-related fields (e.g., department name, employee number).

  • emp: Employee-related fields (e.g., salary, employee name).

  • Various columns can be added with captions for clarity.

Recursive Relationships

• Definition: A recursive relationship is an association where an entity relates to itself (i.e., an employee's boss is also an employee).

• Example attributes include department characteristics and employee details.

Mapping a 1:1 Relationship

• Considerations:

  • Placement of foreign keys among related entities.

  • Mandatory relationships (e.g., every department must have a boss).

  • Opt for the entity with the stronger mandate for a foreign key relationship.

Mapping Recursive Relationships

• Follow general rules but add complexity by including an additional column to track foreign keys.

• Use distinct names for foreign keys when mapping.

Results of Mapping 1:1 Relationship

• Sample tables:

  • Dept:

    • Management, 5, 2001, 1

    • Marketing, 1, 2002, 2

  • Emp:

    • Alice, 75000, Management, 1

    • Ned, 45000, Marketing, 2

Creating the Tables

• Table structure:

  • Dept:

    • deptname: VARCHAR(15)

    • deptfloor: SMALLINT NOT NULL

    • deptphone: SMALLINT NOT NULL

  • Emp:

    • empno: SMALLINT

    • empfname: VARCHAR(10)

Inserting Rows

• Insert employee records with appropriate values ensuring the integrity of relationships.

• Example insert: INSERT INTO emp (empno, empfname, empsalary, deptname, bossno).

Exercises

• Design a data model for Olympic events, identifying particular relationships such as team connections and captain roles.

Querying a 1:1 Relationship

• Example SQL query to list department bosses and their salaries:

  • SELECT empfname, deptname, empsalary FROM emp WHERE empno IN (SELECT empno FROM dept);

Joining Tables

• Explore salaries of employees and their bosses through self-joins, generating results for comparative salary analysis.

Recursive Query Examples

• Finding boss salaries: SELECT wrk.empfname, wrk.empsalary, boss.empfname, boss.empsalary WHERE wrk.empfname = 'Nancy';

  • Results include Nancy's salary and her boss's salary.

• Identifying over-earnings: SELECT wrk.empfname WHERE wrk.empsalary > boss.empsalary;

Modeling 1:1 Recursive Relationships

• Example: English monarchy succession with uniquely identifying attributes like monarch type, name, and reign dates.

Creating Recursive Relationship Tables

• Schema example:

  • Table for monarchs including attributes for present and preceding monarchs to enforce self-referential constraints.

Mapping Multiple Types of Recursive Relationships

• 1:1 and 1:m relationships both introduce foreign key constraints, but uniqueness differs between the two configurations.

Inserting Monarch Data

• Contributions of historical monarchs can be tracked by inserting entries that include details on reigns and predecessors.

Additional Exercises

• Expand modeling efforts to cover additional scenarios, such as Olympic cities and rounds in tournaments, ensuring logical structuring of relationships.