Which relationship is one in which a relationship exists between occurrences of the same entity set?

Prerequisite – ER Model
A relationship between two entities of a similar entity type is called a recursive relationship. Here the same entity type participates more than once in a relationship type with a different role for each instance. In other words, a relationship has always been between occurrences in two different entities. However, the same entity can participate in the relationship. This is termed a recursive relationship.

Inhaltsverzeichnis Show

Three models
Entity Relationship Model (ERM)
Relationships
Strong vs. Weak entities and relationships
Developing an ER Diagram
Conflicting Goals
The special case of the 1:1 recursive relationship
Is one in which a relationship can exist between occurrences of the same entity set?
Which relationship exists when two entities are associated?
What type of entity instances store attributes that are common to one or more entity subtypes?
What do call for a relationship in which the primary key of the related entity does not contain a primary key component of the parent entity?

Example –
Let us suppose that we have an employee table. A manager supervises a subordinate. Every employee can have a supervisor except the CEO and there can be at most one boss for each employee. One employee may be the boss of more than one employee. Let’s suppose that REPORTS_TO is a recursive relationship on the Employee entity type where each Employee plays two roles.

Supervisor
Subordinate

Supervisors and subordinates are called “Role Names”. Here the degree of the REPORTS_TO relationship is 1 i.e. a unary relationship.

The minimum cardinality of the Supervisor entity is ZERO since the lowest level employee may not be a manager for anyone.
The maximum cardinality of the Supervisor entity is N since an employee can manage many employees.
Similarly, the Subordinate entity has a minimum cardinality of ZERO to account for the case where CEO can never be a subordinate.
Its maximum cardinality is ONE since a subordinate employee can have at most one supervisor.

Note – Here none of the participants have total participation since both minimum cardinalities are Zero. Hence, the relationships are connected by a single line instead of a double line in the ER diagram.

To implement a recursive relationship, a foreign key of the employee’s manager number would be held in each employee record. A Sample table would look something like this:-

Emp_entity( Emp_no,Emp_Fname, Emp_Lname, Emp_DOB, Emp_NI_Number, Manager_no);

Manager no - (this is the employee no of the employee's manager)

This article is contributed by Siddhant Bajaj 2. If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to . See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

Admin

Slides are here .

Here’s a handy Crow’s Foot quick reference from Vivek M. Chawla. It doesn’t include attributes in the entities, but several authors and some company guidelines do include them when necessary to make a point.

Topics

Conceptual to Logical to Physical models
The main characteristics of entity relationship components
How relationships between entities are defined, refined, and incorporated into the database design process
How ERD components affect database design and implementation
That real-world database design often requires the reconciliation of conflicting goals

Three models

We begin the database design process with the conceptual model. Various styles of notation are used across the industry. We will use the crows-foot style.

In the conceptual model we will build and ERD diagram which only addresses entities and relationships and cardinality.

The second model is the logical model. In this model we populate the entity representation from the previous model with attributes and their generic types. We also identify the keys to be used.

The third model is the physical model. This is the only one of the three models that actually considers the specific database technology to be used. The first two models could be used with any database technology and, so, are written generically. The physical model specifies the precise data types of the attributes of the entities (relations).

There are tools available to aid these design steps. In particular, MySQLWorkbench can turn a completed ERD directly into the MySQL commands to create the entities and relations. It’s less helpful in the earlier stages.

Entity Relationship Model (ERM)

The basis of an entity relationship diagram (ERD) which depicts the:

Conceptual database as viewed by end user
Database’s main components
- Entities
- Attributes - Relationships
Entity refers to the entity set and not to a single entity occurrence
Chen (of Chen diagrams) described how to generally map between English sentence structures and ERD’s in this table from his ERD paper:

He also did the same analysis for Chinese characters.

Attributes

Characteristics of entities
- Required attribute: Must have a value, cannot be left empty
- Optional attribute: Does not require a value, can be left empty
Domain: Set of possible values for a given attribute
Identifiers or keys: One or more attributes that uniquely identify each entity instance
Composite identifier: Primary key composed of more than one 1 attribute
- (EmployeeNum, CountyCode)
Composite attribute: Attribute that can be subdivided to yield additional attributes
- e.g., Address
Simple attribute: Attribute that cannot be subdivided
- Most attributes are this way
Single-valued attribute: Attribute that has only a single value at a time
- Most attributes are this way
Multivalued attributes: Attributes that have many values
- Generally to be avoided unless necessary for performance or to match common app accesses.
- Slides 2 and 3 A Multivalued Attribute in an Entity (CAR_COLOR)
- These require creating:
  - Several new attributes, one for each component of the original multivalued attribute
  - A new entity composed of the original multivalued attribute’s components
Derived attribute: Attribute whose value is calculated from other attributes
- Derived using a formula or an algorithm
- Slide 4 - Depiction of a Derived Attribute
- Slide 5 - Advantages and Disadvantages of Storing Derived Attributes

Relationships

An association between entities, typically meaningful in both directions

Participants: Entities that participate in a relationship
Connectivity: Describes the relationship classification
Cardinality: (optional) Expresses the minimum and maximum number of entity occurrences associated with one occurrence of a related entity
- Slide 7 - Connectivity and Cardinality in an ERD
  Strong vs. Weak entities and relationships

Entities can either exist on their own or they can only exist when associated with some other entity type.

Strong entities
- A strong entity can be uniquely identified by its own attributes.
- Therefore, the entity’s existence does not depend on any other entity
- e.g., a Dormitory can be uniquely identified by its name and location.
- e.g., a US Bank is uniquely identified by its bank number.
Weak entities
- A weak entity cannot be uniquely identified by its own attributes.
- Thus, you must add attributes to the weak entity to uniquely identify it.
  - This means you must extend the weak entity’s primary key to include one or more attributes from the parent entity as a foreign key.
  - e.g., a Room in a Dormitory needs the Dormitory information as part of its identity.
  - e.g., an Account may be identified by an AccountNumber, but it is meaningless without being associated with a Bank.
- Alternatively, you could add a surrogate key to the weak entity.
  - These keys are not related to the entity’s real attributes, such as an AUTO INCREMENT , system date and time value, or some other generated value. Adding a surrogate key would turn it into a strong entity.

SLIDE 8

Relationship strength
- SLIDE 9
- A weak or non-identifying relationship exists between two entities when the primary key of one of the related entities does not contain a primary key component of the other related entities.
- A strong or identifying relationship is when the primary key of the related entity contains the primary key of the “parent”.
Entity strength
- Weak Entity
  SLIDE 10
  An entity is weak when two conditions are met:
  1. The entity is existence-dependent on another entity.
  2. The entity gets at least part of its primary key from that other entity.
  Database designers determine whether an entity is weak based on business rules
  - Dependent wouldn’t exist without an Employee
  - the primary key of Dependent is (EMP_NUM, DEP_NUM) which includes the primary key of Employee.
- Strong entity
  - An entity that is existence-independent.
Review the Crow’s Foot symbols

Slide 12
and two quick examples
SLIDE 13-14
Relationship Degree
- Indicates the number of entities or participants associated with a relationship
- Unary relationship: Association is maintained within a single entity
- Recursive relationship: Relationship exists between occurrences of the same entity set
- Binary relationship: Two entities are associated
- Ternary relationship: Three entities are associated
- Slide 15 - Three Types of Relationship Degree
- Slide 16 - An ER Representation of Recursive Relationships
Associative Entities
- Also known as composite or bridge entities
- Used ONLY in the ERD, they are NOT typically specified in the business rules.
- Used to represent an M:N relationship between two or more entities
- Is in a 1:M relationship with the parent entities
- Composed of the primary key attributes of each parent entity
- May also contain additional attributes that play no role in connective process
- Slide 16 - Converting the M:N Relationship into Two 1:M Relationships
- Slide 17 - A Composite Entity in an ERD

Developing an ER Diagram

Create a detailed narrative of the organization’s description of operations
Identify business rules based on the descriptions
Identify main entities and relationships from the business rules * Develop the initial ERD
Identify the attributes and primary keys that adequately describe entities
Revise and review ERD

In class example in Slides 18-26
Summary of example in Slide 27

Conflicting Goals

Only the simplest databases can achieve all of these guidelines and goals. You will often be faced with conflicting goals. Slide 28

High update occurrence requirements can conflict with designs including many related entities
common access patterns may drive changes to the design

The special case of the 1:1 recursive relationship

This kind of relationship occurs often and the first time you see it, it’s rather puzzling as to how to design and implement it. Slide 29

How would you model these:
- Patron can checkout 1+ or 0+ books?
- Library has 1+ books?
- A book may have been checked out by many patrons
  - (hint: a many to many relationship, so fix with a helper “Checkout Order”

Is one in which a relationship can exist between occurrences of the same entity set?

Unary relationship (recursive) A unary relationship, also called recursive, is one in which a relationship exists between occurrences of the same entity set. In this relationship, the primary and foreign keys are the same, but they represent two entities with different roles.

Which relationship exists when two entities are associated?

A binary relationship exists when two entities are associated. A ternary relationship exists when three entities are associated.

What type of entity instances store attributes that are common to one or more entity subtypes?

An entity supertype is a generic entity type that is related to one or more entity subtypes, where the entity supertype contains the common characteristics and the entity subtypes contain the unique characteristics of each entity subtype.

A weak or non-identifying relationship exists between two entities when the primary key of one of the related entities does not contain a primary key component of the other related entities.