I know that,
"A candidate key is a minimal subset of a superkey"
That means there cannot be any other super keys inside a Candidate key.
What i don't understand is that,
Where can we use this special property of a candidate key in a database design. Special property means:- "there cannot be superkeys inside a candidate key."
example explanation is highly appreciated.
Note : This question defers from the most answered question deference between keys, Finding/Identifying Candidate keys
I think the minimal nature of candidate keys are useful for unique constraints, in primary keys as well as user-defined unique constraints. These allow us to ensure that functional dependencies are uniquely represented in a database, which is important for data consistency.
If we used a non-minimal superkey as a primary key, we could record multiple rows with the same values for a subset of the primary key, varying only in the complement of the subset. If the subset is a determinant of a functional dependency, we could have inconsistent data.
For example, let's consider a simplified vehicle registration table. Every vehicle has a unique registration number and a unique engine number, so both attributes are candidate keys. Any superset of these is a superkey, e.g. the combination of the two.
I indicated the primary key in blue. As you can see, each row's primary key value is unique, but the table allowed the same vehicle_registration and engine_number to be recorded more than once, with different associated attributes. Is the vehicle with registration "abc123" an XC60 or an XC90? We don't know, our data is inconsistent.
A better design would handle each candidate key as a separate unique constraint (regardless of which is chosen as primary key). This would prevent the same vehicle registration or engine number from being recorded twice.
DBMSs enforce functional dependencies using uniqueness constraints. A uniqueness constraint on a candidate key means that every dependency on every superkey that includes the candidate key is guaranteed to be satisfied. From a data integrity perspective, therefore, it's important identify the right candidate keys so that you enforce the right superkey dependencies. For example, a uniqueness constraint on a table's attributes {A,B} would enforce superkeys {A,B,C}, {A,B,D}, {A,B,C,D} but not {A,C,D}. Identifying the correct candidate keys relieves the database designer from the need to enforce every superkey separately.
A second reason why it makes sense to identify candidate keys is to ensure data can be used and interpreted accurately by users. Users and consumers of data need to understand facts recorded in a database and relate them to real objects or concepts outside the database. Candidate keys are the identifying attributes that make it possible to perform that mapping from database to reality. If a non-minimal superkey is used to perform such a mapping then there may be a greater possibility of ambiguity and error.
For example, suppose the key of employees in a company database is {EmpNum}. If the user of the database incorrectly thinks the key is {EmpNum, DeptCode} then she might erroneously believe that the following information refers to two different employees, instead of one.
+-------+---------+
|EmpNum |DeptCode |
+-------+---------+
|14972 |SALES |
+-------+---------+
+-------+---------+
|EmpNum |DeptCode |
+-------+---------+
|14972 |HR |
+-------+---------+
In reality, perhaps the single employee 14972 has moved from one department to another. Or maybe this employee truly is assigned to more than one DeptCode simultaneously. Either way, those interpretations depend on the user understanding that only one person is identified by the key EmpNum=14972.
Successful database design requires the designer to identify keys, verify their fitness for purpose and ensure that database users are familiar with what the keys are - at least for important entities that the users need to understand and work with.
