Database/DBMS Interview Questions And Answers - Part X.

46) What Is A Key? What Are Different Types Of Keys In A Database?

Answer: A key is nothing but an attribute or group of attributes. Attribute is a particular property, which describes the entity. Attributes are also called columns or fields in DBMS.

Keys are used to establish and identify relation between tables. They also ensure that each record within a table can be uniquely identified by combination of one or more fields within a table.
Different types of keys in database are primary key, secondary key, alternative key, super key, candidate key, compound or concatenated or composite key, foreign key, etc.


47) What Is A Primary Key & Secondary Key?

Answer: An attribute that can be used to identify a record uniquely is considered to be a Primary Key. For example, In the student table roll_no is the primary key because it can be used to identify unique record or unique student.

An attribute that can be used to identify a group of records satisfying a given condition is said to be a Secondary Key. For Example, In the employee table, designation is a secondary key because more than one employee can have the same designation.


48) What Is A Candidate Key & Alternate Key?

Answer: Consider a Students table, registration_no usually allotted in the exams is also unique for each student. In that case for identifying a student uniquely either roll_no or register_no can be used. Hence one can say that there are two different candidates contesting for primary key post. Any one of them can be selected as primary key.

A candidate key is one that can identify each record of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.


If any one of the candidate key among the different candidate keys available is selected as primary key, then the remaining keys are called alternate key.


49) What Is A Super Key & Composite Key?

Answer: With primary key attribute if any other attribute is added then that combination is called Super Key. In other words, Primary Key/Candidate Key is the minimum possible Super Key. For example, in the student table roll_no + student_name is a Super Key.

A key formed by combining at least two or more columns is called composite key. If any Primary Key/Candidate Key is a combination of more than one key then it is called the Composite Key. For example, in table MARKS  roll_no + subject is a Composite Key.


50) What Is Foreign Key?

Answer: When a primary key of one table appears as an attribute in another table, than it is called the Foreign key in the second table.

EXAMPLE - FOR CLEAR UNDERSTANDING:

-- Super Key – An attribute or a combination of attribute that is used to identify the records uniquely is known as Super Key. A table can have many Super Keys. Consider an Employee table:

Example of Super Key:

• ID
• ID, Name
• ID, Address
• ID, Department_ID
• ID, Salary
• Name, Address
• Name, Address, Department_ID

So on as any combination which can identify the records uniquely will be a Super Key.


-- Candidate Key – It can be defined as minimal Super Key or irreducible Super Key. In other words an attribute or a combination of attribute that identifies the record uniquely but none of its proper subsets can identify the records uniquely.

Example of Candidate Key

• ID
• Name, Address

For above table we have only two Candidate Keys (i.e. Irreducible Super Key) used to identify the records from the table uniquely. "ID" Key can identify the record uniquely and similarly combination of Name and Address can identify the record uniquely, but neither Name nor Address can be used to identify the records uniquely as it might be possible that we have two employees with similar name or two employees from the same house.


-- Primary Key – A Candidate Key that is used by the database designer for unique identification of each row in a table is known as Primary Key. A Primary Key can consist of one or more attributes of a table.

Example of Primary Key - Database designer can use one of the Candidate Key as a Primary Key. In this case we have "ID" and "Name, Address" as Candidate Key, we will consider "ID" Key as a Primary Key as the other key is the combination of more than one attribute.


-- Composite Key – If we use multiple attributes to create a Primary Key then that Primary Key is called Composite Key (also called a Compound Key or Concatenated Key).

Example of Composite Key, if we have used "Name, Address" as a Primary Key then it will be our Composite Key.


-- Alternate Key – Alternate Key can be any of the Candidate Keys except for the Primary Key.

Example of Alternate Key is "Name, Address" as it is the only other Candidate Key which is not a Primary Key.


-- Secondary Key – The attributes that are not even the Super Key but can be still used for identification of records (not unique) are known as Secondary Key.

Example of Secondary Key can be Name, Address, Salary, Department_ID etc. as they can identify the records but they might not be unique.



ALSO CHECK THE BELOW FAQ's:

51) What Is Normalization. Explain It In Detail?
52) What Is The Need For Normalization In DBMS?
53) What Are The Goals Of Normalization In DBMS?
54) State The Benefits Of Normalization In DBMS?
55) What Is Functional Dependency?


56) What Is Fully Functional Dependency?
57) What Is Partial Dependency?
58) What Is Transitive Dependency?
59) What Is A Trivial Functional Dependency?
60) What Is Multivalued Dependency?

... Return To DBMS FAQ's Main Page.

... Return To HR Interview Questions With Answers Main Page.