Keys and Integrity Rules in RDBMS
What is a Key and what are the different type of Keys ?
Key is an attribute or a group of attribute which work as a unique identifier to identify the all the tuples uniquely.
Based on the uniqueness there are three kind of keys:-
1.Candidate Key
2.Super Key
3.Primary Key
Candidate Key:-When more than one or group of attributes serves as a unique identifier , they are each called as candidate key. Candidate key have two following properties:-
1. Uniqueness :-In the relation there are no two tuples with the same value for k(candidate key).
Suppose we have candidate key having attribute Name and Class like (Name , Class) then two tuples cant have the same value for the Name and class . for eg if one tuple have (Ishant , CSE) as value of its attribute then the other tuple cant have both the same value of Name and class .
of course other tuple may have value like this (Ishant , ECE ) and (Rahul, CSE) but not (Ishant, CSE).
2. Irreducibility:- It states that No proper subset of K has the uniqueness property.
if (Name,class) is an candidate key then Name and class cant be unique individually. That is-
if(Name, Class)->unique
|
this will be a candidate key if and only if Name is not unique and class is not unique .
Please note that since null value is not considered as unique hence no component of candidate key is allowed to be null. Also note that there can be any number of candidate key in a table .
Super Key :-A super Key is defined as an attribute or a set of attributes that uniquely identifies a tuple within a relation. A super key has the uniqueness property but not necessarily the irreducibility property . for eg:-
We have a relation Student(Roll_no,Name,Class) in which Roll_no is unique.then the set of attributes (Roll_no,Name,Class) is an super key . But if we remove attribute Name and Class from the super key Roll_no will still uniquely identify each tuple hence super does not have irreducibility property .
Please note that candidate key is the special case of super key.
C.K(candidate key)=S.K(super key)-P.K(primary key)
Primary Key :It is the candidate key which has been selected to identify tuples uniquely within the relation .
Primary key are selected by the DBA itself. Please note that primary key can't contain NULL values because we cannot uniquely identify multiple NULL values.
Properties of Primary key:
1. Primary key must not be NULL .
2. Primary Key must be composed of minimal attributes which uniquely identifies tuples .
Other Keys:-
Alternate Key:- Those candidate key which has not been selected as the primary key are known as alternate key.
Composite Key: A primary key made up of more than one attribute is known as composite key.
Foreign Key : The column in the child table that reference a primary key of the Parent table is called as a foreign key. Also note that the foreign key are not unique and they may contain Null values.
Why do we need Foreign Key ?
Foreign Key are needed to link together two or more table which has some sort of relationship with each other. for eg:-
Consider the following Parent table: Std_add(Roll_no, Name,Addr, Place )
Now consider the child table : Std_marks(Roll_no, Subject, Marks, Percentage)
Roll_no in the parent table(Std_addr) is the primary key whereas the roll_no column of Std_marks is foreign key.
There are two type of integrity rule as follows :-
1. Entity Integrity Rule.
2. Referential Integrity Rule.
Entity Integrity Rule:- It says that the value of the attribute of primary key cannot contain Null values. If we allow null values in primary key then we will not able to uniquely identify each tuples on the basis of primary key .
Referential Integrity Rule :- If a foreign key value exist in a relation ,either the foreign key value must match a primary key value of the parent table or the foreign key must be null.
Now see the following example.We have the following two table as follows:-
Std_add(Roll_no, Name) is the master table with Roll_no as the primary key.
Std_marks(Roll_no, Subject, Marks) is the child table with Roll_No as foreign key
In this example we are violating the Referential Integrity rule .
Can we delete row Roll_No=3 from the (Std_add) master table ?
No. Certainly not. Because Roll_no=2 has dependent row in the child table .But please note that we can delete the row of Roll_no = 1 form the master table because there are no dependent rows in the child table (Std_marks).
The following figure will make you clear everything :
ON DELETE CASCADING RULE:-
The referential integrity rule which allows the user to delete the rows from the master and at the same time it will delete the dependent rows form in all the child table is actually know as on delete cascading.
This covers the concept of different keys in DBMS and Integrity rules . If you have any question or you want to add anything , then please comment below.
Key is an attribute or a group of attribute which work as a unique identifier to identify the all the tuples uniquely.
Based on the uniqueness there are three kind of keys:-
1.Candidate Key
2.Super Key
3.Primary Key
Candidate Key:-When more than one or group of attributes serves as a unique identifier , they are each called as candidate key. Candidate key have two following properties:-
1. Uniqueness :-In the relation there are no two tuples with the same value for k(candidate key).
Suppose we have candidate key having attribute Name and Class like (Name , Class) then two tuples cant have the same value for the Name and class . for eg if one tuple have (Ishant , CSE) as value of its attribute then the other tuple cant have both the same value of Name and class .
of course other tuple may have value like this (Ishant , ECE ) and (Rahul, CSE) but not (Ishant, CSE).
2. Irreducibility:- It states that No proper subset of K has the uniqueness property.
if (Name,class) is an candidate key then Name and class cant be unique individually. That is-
if(Name, Class)->unique
|
this will be a candidate key if and only if Name is not unique and class is not unique .
Please note that since null value is not considered as unique hence no component of candidate key is allowed to be null. Also note that there can be any number of candidate key in a table .
Super Key :-A super Key is defined as an attribute or a set of attributes that uniquely identifies a tuple within a relation. A super key has the uniqueness property but not necessarily the irreducibility property . for eg:-
We have a relation Student(Roll_no,Name,Class) in which Roll_no is unique.then the set of attributes (Roll_no,Name,Class) is an super key . But if we remove attribute Name and Class from the super key Roll_no will still uniquely identify each tuple hence super does not have irreducibility property .
Please note that candidate key is the special case of super key.
C.K(candidate key)=S.K(super key)-P.K(primary key)
Primary Key :It is the candidate key which has been selected to identify tuples uniquely within the relation .
Primary key are selected by the DBA itself. Please note that primary key can't contain NULL values because we cannot uniquely identify multiple NULL values.
Properties of Primary key:
1. Primary key must not be NULL .
2. Primary Key must be composed of minimal attributes which uniquely identifies tuples .
Other Keys:-
Alternate Key:- Those candidate key which has not been selected as the primary key are known as alternate key.
Composite Key: A primary key made up of more than one attribute is known as composite key.
Foreign Key : The column in the child table that reference a primary key of the Parent table is called as a foreign key. Also note that the foreign key are not unique and they may contain Null values.
Why do we need Foreign Key ?
Foreign Key are needed to link together two or more table which has some sort of relationship with each other. for eg:-
Consider the following Parent table: Std_add(Roll_no, Name,Addr, Place )
Now consider the child table : Std_marks(Roll_no, Subject, Marks, Percentage)
Roll_no in the parent table(Std_addr) is the primary key whereas the roll_no column of Std_marks is foreign key.
Integrity Rule
There are two type of integrity rule as follows :-
1. Entity Integrity Rule.
2. Referential Integrity Rule.
Entity Integrity Rule:- It says that the value of the attribute of primary key cannot contain Null values. If we allow null values in primary key then we will not able to uniquely identify each tuples on the basis of primary key .
Null represent a values for an attribute that is currently unknown or is not applicable for this tuple. Null is not same as a zero numerical values or a text string filled spaces as zeros and spaces are value but Null represent the absence of a value. Always remember one Null is never equal to ,greater than or smaller to other Null values.
Referential Integrity Rule :- If a foreign key value exist in a relation ,either the foreign key value must match a primary key value of the parent table or the foreign key must be null.
Now see the following example.We have the following two table as follows:-
Std_add(Roll_no, Name) is the master table with Roll_no as the primary key.
Std_marks(Roll_no, Subject, Marks) is the child table with Roll_No as foreign key
In this example we are violating the Referential Integrity rule .
Can we delete row Roll_No=3 from the (Std_add) master table ?
No. Certainly not. Because Roll_no=2 has dependent row in the child table .But please note that we can delete the row of Roll_no = 1 form the master table because there are no dependent rows in the child table (Std_marks).
The following figure will make you clear everything :
ON DELETE CASCADING RULE:-
The referential integrity rule which allows the user to delete the rows from the master and at the same time it will delete the dependent rows form in all the child table is actually know as on delete cascading.
This covers the concept of different keys in DBMS and Integrity rules . If you have any question or you want to add anything , then please comment below.
Comments
Post a Comment