DBMS KEY

KEY :- key is collection or a group of one or more than one attributes by which we can differentiate an entity with other entity or a relationship is differentiate by other relationship. We find every entity (record/row/tuple) of an entity set (table) in unique manner by key. ER model uses following keys -

1. Primary key :- primary key is group of that attribute or attributes of any entity, by which we can find all the entities in unique manner which are present in entity set. Primary key is determinate by DBA on the basis of organizational behavior which will very appropriating for that organisation. Primary key obeys following two conditions - 

a. Primary key must store unique value.

b. Primary key shouldn't be NOT NULL.

Example :- A. Enrollment_no. Is primary key for a student. 

B. Aadhar card is primary key for Indian citizens. 

C. Account no. Is primary key for bank customer.

2. Candidate key :- for finding entities (records) of any entity set (table) in unique manner. There is need of group of attributes or a special attribute then that is called candidate key.

  In other words, candidate key keeps all the properties of primary key but it is not primary key. We can also find entities in unique manner by it. It is responsibility of DBA that he/she select best & suitable candidate key as primary key from candidate keys.

Example:- for a student entity set, application_no, transaction ID, roll no, Enrollment_no etc. Are the candidate keys. From that Enrollment_no is selected as primary key by DBA.

3. Alternate key :- we know that, a primary key is selected from candidate keys by DBA. In absence of primary key any key from candidate key takes place as primary key then it is called alternate key.

  Example :- at the time of opening of email account,  if user not know/forget email ID or password then we can reset our email account's password by the use of alternate email ID, mobile no., find friends and hint questions in forgot password option.

4. Super key :- " a super set of all keys of an entity set is called super key."

According to super key, it stores group of one or more than one attributes, by which we can find thr values of entities of entity set in unique way. Primary key, candidate key and alternate key select from group of super key.

5. Composite key :- composite key is a primary key,  which are created by two or more than two attributes and it also capable for finding any entity of entity set in unique form.

  In entity set, composite key is used when then any attribute doesn't keep all the properties of primary key in entity set.

6. Foreign key:- foreign key is used to express relationship between any two entity set (table). It means within foreign key, primary key of a table is defined in form of attribute (foreign key) in other table. Foreign key stores values (references) of primary key but foreign key doesn't apply rules of primary key that means value of foreign key may be NULL or duplicate. Foreign key plays an important role in running complex queries because this queries work on data of two or more than two tables.

Example :- primary key customers ID of customer table are used as foreign key in orders table.