Identifying and Non Identifying Relationships –
Relationships among the entities may be classified as being either
- Identifying Relationships
- Non Identifying Relationships
Identifying Relationships –
Definition 1 :
In the previous post (Entity and its Types) we define about weak entity types. These are the entities, which does not have a key attribute. So, To identify it, what we do is to relate such type of entity with some other entity type.
The weak entity type relate to another entity type in combination with some of their attribute values.
We call this entity type the identifying or Owner Entity Type and the relationship type which a weak entity type creates with Owner Entity Type is identifying relationship of weak entity type.
If we consider the weak entity type as a child object and the owner entity type as a parent object, an Identifying relationship specifies that a child object cannot exist without the parent object and child object cannot be uniquely identified without the parent. If the parent entity is deleted, then the child entity must be deleted.
Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. This means that foreign key is a primary key too. So, put the primary key column from the parent into the primary key column of the child (To provide uniqueness, other columns can be included in the child entity if needed).
CREATE TABLE AuthoredBook ( author_id INT NOT NULL, book_id INT NOT NULL, PRIMARY KEY (author_id, book_id), FOREIGN KEY (author_id) REFERENCES Authors(author_id), FOREIGN KEY (book_id) REFERENCES Books(book_id) );
author_id is a foreign key, but it’s also one of the columns in the primary key. So there is an identifying relationship of the
AuthoredBook with the referenced table Authors.
Likewise it has an identifying relationship with
author_id is a foreign key, but it’s also one of the columns in the primary key. So there is an identifying relationship of this table with the referenced table Books.
Some more Examples-
1) A driving licence entity cannot exist unless it is related to person entity. 2) The account of any person cannot exist without that person. 3) Account (AccountID, AccountNum, AccountTypeID) PersonAccount (AccountID, PersonID, Balance) Person(PersonID, Name) The Account to PersonAccount relationship and the Person to PersonAccount relationship are identifying because the child row (PersonAccount) cannot exist without having been defined in the parent (Account or Person). In other words: there is no personaccount when the Person or account does not exists.
Non Identifying Relationships-
A non-identifying relationship is when the primary key attributes of the parent entity must not become primary key attributes of the child entity.So, put the primary key column of the parent into the table of the child, but not in the primary key.
A non-identifying relationship means that a child entity is related to parent entity but it can be identified independently of the parent entity. The child item should be kept even though the parent is deleted.
Types of Non Identifying Relationship –
Non Identifying Relationships can be further classified into –
- Mandatory Non Identifying Relationship- It exists when the foreign key value in the child table cannot be null or where a parent is required by setting the parent table cardinality.
- Optional (or) Non Mandatory Non Identifying Relationship – An optional (or) non-mandatory non-identifying relationship exists when the value in the child table can be null or where a parent is not required.
Example of Non-Identifying Relationship –
Account( AccountID, AccountNum, AccountTypeID ) AccountType( AccountTypeID, Code, Name, Description ) The relationship between the Account and AccountType is non-identifying because each AccountType can be identified without having to exist in the parent table.
Notation of Identifying Relationship –
In the below figure, both a weak entity types and its identifying relationship are distinguished by surrounding their boxes and diamonds with double lines as shown in the figure.
Partial key attributes(e.g. Name) is underlined with a dashed or dotted line.
|Entity and its Types||Data Models|
Incoming search terms:
- an example of Non-identifying relationship
- dbms identify relationship
- identifying relationships in dbms