Candidate Key
Q.1: Can a candidate key be a super key?
Answer:
All candidate keys are super key but not all super keys are candidate keys. A reference table to understand this :
Student_Id |
Enrollment_No |
name |
|
branch |
---|---|---|---|---|
001 |
CS201001 |
Adwitiya Mourya |
adwitiya@example.com |
CS |
002 |
EC201002 |
Juan Phillipes |
juan@example.com |
EC |
003 |
IT201003 |
Ethan Hunt |
hunt@example.com |
IT |
004 |
CS201004 |
Jake Samuel |
jake@example.com |
CS |
In the above table, we have 3 candidate keys: Student_Id, Enrollment_No, email.
Now, to prove that every candidate key is a super key, as the definition of super key goes – “All set of attributes that can uniquely identify a record”. That means, super key can be made of either single attribute or combination of attributes. So if the minimal set is 1-attribute, then its still an attribute that come under the category – “All attributes that can uniquely identify a record”. Hence, that 1 single attribute will be treated as a candidate key and super kye both.So, in above relation following attributes : {Student_Id, Enrollment_No, email} are not only candidate keys but super keys as well.
Q.2: Since all candidate keys are super keys then how do we determine the number of super keys that are not candidate keys ?
Answer:
1. Identify candidate keys
2. Find total super keys.
3. Super keys that are not candidate keys = Total no of super keys – No of candidate keys
Q.3: What criteria is used to select primary key from candidate keys ?
Answer:
Primary key is chosen based on the candidate key, whose value will never be altered, NULL , Unique, and requires minimal storage space to enhance performance. For example, email is a candidate key in above reference table but it shouldn’t be selected as a primary key because it consumes more space and its possible because of application logic that it can contain NULL value.
Q.4: Can a candidate key include NULL Values ?
Answer:
Yes, a candidate key can contain a NULL Value. But that candidate key can never be chosen as the primary key. So, it must be kept in mind while defining a schema.
Q.5: How can we enforce candidate key column in RDBMS ?
Answer:
We can utilise the constraint – “UNIQUE Constraint” to ensure that all the values entered in a column are unique.
Candidate Key in DBMS
Candidate keys play an essential role in Database Management Systems (DBMS) by ensuring data integrity and efficient retrieval. A candidate key refers to a set of attributes that can uniquely identify each record in a table. In this article, we will explore the concept of candidate keys, their significance in DBMS, and their crucial role in optimizing databases.