Difference between identifying and non-identifying relationships

In this article, we will discuss the overview of an entity and identifying and non-identifying relationships and finally will focus on differences between both of them. Let’s discuss it one by one.

Overview :

• Entity types that do not have their own key attributes are known as the weak entity type. Unlike this, there are regular entity types that have their key attributes, also known as the strong entity types.
• Entity belongs to a weak entity type are identified when they are related to other entity which has their own key attributes are also known as strong entity type or identifying or owner entity type.
• One or more key attribute of a strong entity type is associated with any number of attributes of weak entity type for identifying it.

Identifying relationship :
The relationship that relates the weak entity type to an owner entity type is known as identifying relationship. The weak entity type always has total participation(existence dependency) in a relationship because the weak entity type can not be identified without an owner identity. This doesn’t mean that every existence dependency(total participation) results in a weak entity type.

Identifying relationship

Diagram Explanation –

• Here Family member(entity) is a weak entity. It does not have any primary key. Given an owner id(i.e. primary key Cust_id) and one or more attributes of a weak entity(i.e. name of a family member), we can uniquely identify a tuple in weak entity relation.
• Name is not the primary key in the weak entity, but it is a key attribute in the weak entity. In ER diagram weak entity partial key is represented using a dotted underline.
• An owner entity should always be a strong entity and the relationship between the owner and the weak entity is always one-to-many and total participation from a weak entity
• The double diamond box in ER diagram represents a relation between a weak entity and its owner strong entity, this relationship is called an identifying relationship. As there is a one-to-many relationship and total participation towards many sides. So here two relations are required one for weak entity and one for weak entity and weak relationship attributes.
• A weak entity type may have more than one identifying entity type and an identifying relationship type of degree higher than two.
• The identifying entity type is also sometimes called the parent entity type or the dominant entity type. The weak entity type is also sometimes called the child entity type.

Example-1 :
Let’s focus on movies, directors, and movies_directors relations.

Requirement analysis : 
A director can direct many(M) movies and a movie can be directed by many(N) directors. Therefore, this is M: N relationship, therefore the relationship is required to store this information. One Relation is used for storing Movies information and one relation is used for storing the director’s information and one relation(movies_directors) in which movie_id is acted as the foreign key of movies relation and director_id is acts as the foreign key of directors relation, therefore, they help to reference movies table and director table for providing the result of the query which is specified by the user. The relation between movies and movies_directors is one too many(a movie can be directed by many directors) and the relationship between directors and movies_directors is also one too many(as a director can direct multiple movies). So the relation between movies and movie_directors is identifying relationship because every tuple in the child(i.e. movie_directors) depends on the entry present in movies relation. In the same manner directors and movies_directors are also related to identifying relationships. If we delete any entry from movie Relation then the corresponding entry from movie_directors are also needed to be deleted.

Symbols for binary relationships 

Non-Identifying relationship :
In a non-identifying relationship, the prime attribute/attributes (i.e. attribute of primary key) of a parent is not used as prime attribute/attributes in the child table, but it can be attributed (i.e. non-prime) of the child table.  Here the dotted line between the country(parent) table and the city(child table) represents the non-identifying relationship between country and city. The child entity can be uniquely identified without a parent entity.

Country table

City table

Example 1: 

• If you have an author relation and a book relation. In some cases, we may require storing data about a book, but we don’t know who the author is. The book’s identity/existence is not dependent on having the author’s information.
• In the above example taken in the non-identifying relationship, the country entity, Code attribute is used as the primary key for uniquely identifying country relation. Here the primary key(i.e. Code) of the country(i.e. parent table) is present in the city(i.e. child) table but not as a part of the primary key or primary key.
• Two relations city(child) and country(parent) is associated with the non-identifying relationship. The relation city has ID as a primary key. But in country entity Code attribute is used as the primary key for uniquely identifying country relation. Here the primary key(i.e. Code) of parent country is present in city(i.e. child) relation but not as a primary key or prime attribute).

Parts of non-identifying relation :
A non-identifying relation is further divided into two parts as follows.

1. Mandatory non-identifying relationship
2. Optional non-identifying relationship

Let’s discuss it.

1. Mandatory non-identifying relationship –
   A “mandatory” non-identifying relationship exists when an attribute the parent relation, present in the child relation can not contain the NULL value. 
   
2. Optional non-identifying relationship – An “optional” non-identifying relationship is present between two relations when the attribute of the parent relation(prime attribute or primary key) which is also in child relation contains NULL values.

Difference between Identifying relationship and Non-identifying relationship :

S.No.	Identifying relationship	Non-identifying relationship
1.	In identifying relationship, the primary key of parent(Identifying entity type) is added in child relations with some attribute/attributes of the child for uniquely identifying each tuple in child relation i.e. a tuple in the child table depends on the row in the parent table.	In a non-identifying relationship, the prime attribute/data tributes (i.e. attribute of primary key) of a parent is not used as prime attribute/attributes in the child table, but it can be attributed (i.e. non-prime) of the child table.
2.	For example – The above example was taken in identifying relationships. In that example, every tuple in family member relation(which includes attributes like name, age of family member) can not be uniquely determined by relation Family_member.  So the customer primary key is combined with one or more attributes(i.e. name) to uniquely identify a tuple in relation to a Family member.  So the relationship between Customer (i.e. Parent table) and Family_member(child table) is identifying( i.e. a row in Family_member table/relation depends upon row in Customer table).  Here the combination of C_Id(i.e. from the parent table and name in Family member(child) table) together helps to identify each tuple in Family_member.	For example – In the above example taken in the non-identifying relationship.        The country entity, Code attribute is used as the primary key for uniquely identifying country relations.  Here the primary key(i.e. Code) of the country which is the parent table is present in the city(i.e. child) table but not as a part of the primary key or primary key. Each tuple in relation to the city can be uniquely identified without referring to country relation. The relationship between city and country is defined as ‘different cities can belong to the same country’ So this is many to one non-identifying relationship because a relation city can be uniquely identified with ID(i.e. its primary key).