In the
relational data model
The relational model (RM) is an approach to managing data using a structure and language consistent with first-order predicate logic, first described in 1969 by English computer scientist Edgar F. Codd, where all data is represented in terms of tup ...
a superkey is a
set of attributes that uniquely identifies each
tuple
In mathematics, a tuple is a finite ordered list (sequence) of elements. An -tuple is a sequence (or ordered list) of elements, where is a non-negative integer. There is only one 0-tuple, referred to as ''the empty tuple''. An -tuple is defi ...
of a
relation. Because superkey values are unique, tuples with the same superkey value must also have the same non-key attribute values. That is, non-key attributes are ''
functionally dependent'' on the superkey.
The set of all attributes is always a superkey (the ''trivial superkey''). Tuples in a relation are by definition unique, with duplicates removed after each operation, so the set of all attributes is always uniquely valued for every tuple. A ''
candidate key A candidate key, or simply a key, of a relational database is a minimal superkey. In other words, it is any set of columns that have a unique combination of values in each row (which makes it a superkey), with the additional constraint that removin ...
'' (or ''minimal superkey'') is a superkey that can't be reduced to a simpler superkey by removing an attribute.
For example, in an employee schema with attributes employeeID, name, job, and departmentID, if employeeID values are unique then employeeID combined with any or all of the other attributes can uniquely identify tuples in the table. Each combination, , , , and so on is a superkey. is a candidate key--no subset of its attributes is also a superkey. is the trivial superkey.
If attribute set ''K'' is a superkey of relation ''R'', then at all times it is the case that the
projection of ''R'' over ''K'' has the same
cardinality
In mathematics, the cardinality of a set is a measure of the number of elements of the set. For example, the set A = \ contains 3 elements, and therefore A has a cardinality of 3. Beginning in the late 19th century, this concept was generalized ...
as ''R'' itself.
Example
First, list out all the sets of attributes:
:•
:•
:•
:•
:•
:•
:•
:•
Second, eliminate all the sets which do not meet superkey's requirement. For example, cannot be a superkey because for the same attribute values (Edward, Plantagenet), there are two distinct tuples:
* (Edward, II, Plantagenet)
* (Edward, III, Plantagenet)
Finally, after elimination, the remaining sets of attributes are the only possible superkeys in this example:
* (Candidate Key)
*
In reality, superkeys cannot be determined simply by examining one set of tuples in a relation. A superkey defines a
functional dependency
In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, a functional dependency is a constraint between two attributes in a relation.
Given a relation ' ...
constraint of a relation schema which must hold for all possible instance relations of that relation schema.
See also
*
Alternate key
In the relational model of databases, a primary key is a ''specific choice'' of a ''minimal'' set of attributes (columns) that uniquely specify a tuple ( row) in a relation (table). Informally, a primary key is "which attributes identify a record, ...
*
Compound key {{Unreferenced, date=October 2020
In database design, a composite key is a candidate key that consists of two or more attributes (table columns) that together uniquely identify an entity occurrence (table row). A compound key is a composite key fo ...
*
Foreign key A foreign key is a set of attributes in a table that refers to the primary key of another table. The foreign key links these two tables. Another way to put it: In the context of relational databases, a foreign key is a set of attributes subject to ...
*
Primary key
In the relational model of databases, a primary key is a ''specific choice'' of a ''minimal'' set of attributes (columns) that uniquely specify a tuple ( row) in a relation (table). Informally, a primary key is "which attributes identify a record, ...
*
Candidate key A candidate key, or simply a key, of a relational database is a minimal superkey. In other words, it is any set of columns that have a unique combination of values in each row (which makes it a superkey), with the additional constraint that removin ...
References
Further reading
*
External links
Relation Database terms of reference, Keys An overview of the different types of keys in an RDBMS
{{Databases
Data modeling
de:Superschlüssel
zh:超鍵