Editing Superkey

{{Short description|Set of attributes that uniquely identifies each tuple of a relation}}
{{about|the database design term|the keyboard button|Super key (keyboard button)}}

In the [[relational model|relational data model]] a '''superkey''' is any [[Set (mathematics)|set]] of attributes that uniquely identifies each [[tuple]] of a [[Relation (database)|relation]].<ref>{{cite web |url=https://www.dcs.warwick.ac.uk/~hugh/TTM/CJD-on-EFC's-First-Two-Papers.pdf |title=Codd's First Relational Papers: A Critical Analysis |last=Date |first=Christopher |date=2015 |website=warwick.ac.uk |access-date=2020-01-04 |quote=Note that the extract allows a “relation” to have any number of primary keys, and moreover that such keys are allowed to be “redundant” (better: reducible). In other words, what the paper calls a primary key is what later (and better) became known as a superkey, and what the paper calls a nonredundant (better: irreducible) primary key is what later became known as a candidate key or (better) just a "key".}}</ref><ref>
{{cite book|title=Introduction to Database Management Systems|publisher=Tata McGraw-Hill|page=77|isbn=9780070591196|year=2005|quote=no two tuples in any legal relation
}}</ref> 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 [[functional dependency|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]] (or minimal superkey) is a superkey that can't be reduced to a simpler superkey by removing an attribute.<ref>{{Cite journal|last=Saiedian|first=H.|date=1996-02-01|title=An Efficient Algorithm to Compute the Candidate Keys of a Relational Database Schema|url=https://academic.oup.com/comjnl/article-lookup/doi/10.1093/comjnl/39.2.124|journal=The Computer Journal|language=en|volume=39|issue=2|pages=124–132|doi=10.1093/comjnl/39.2.124|issn=0010-4620|url-access=subscription}}</ref>

For example, in an employee schema with attributes <code>employeeID</code>, <code>name</code>, <code>job</code>, and <code>departmentID</code>, if <code>employeeID</code> values are unique then <code>employeeID</code> combined with any or all of the other attributes can uniquely identify tuples in the table. Each combination, {<code>employeeID</code>}, {<code>employeeID</code>, <code>name</code>}, {<code>employeeID</code>, <code>name</code>, <code>job</code>}, and so on is a superkey. {<code>employeeID</code>} is a candidate key, since no subset of its attributes is also a superkey. {<code>employeeID</code>, <code>name</code>, <code>job</code>, <code>departmentID</code>} 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 (relational algebra)|projection]] of ''R'' over ''K'' has the same [[Cardinality (data modeling)|cardinality]] as ''R'' itself.

==Example==
{| class="wikitable"
|+ English Monarchs
|-
! Monarch Name
! Monarch Number
! Royal House
|-
| Edward
| II
| Plantagenet
|-
| Edward
| III
| Plantagenet
|-
| Richard
| III
| Plantagenet
|-
| Henry
| IV
| Lancaster
|}

First, list out all the sets of attributes:
:• {}
:• {Monarch Name} &nbsp;
:• {Monarch Number} &nbsp;
:• {Royal House}
:• {Monarch Name, Monarch Number}
:• {Monarch Name, Royal House}
:• {Monarch Number, Royal House}
:• {Monarch Name, Monarch Number, Royal House}

Second, eliminate all the sets which do not meet superkey's requirement.  For example, {Monarch Name, Royal House} 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:
* {Monarch Name, Monarch Number} — this is also the [[candidate key]]
* {Monarch Name, Monarch Number, Royal House} — this is also the trivial superkey.

In reality, superkeys cannot be determined simply by examining one set of tuples in a relation. A superkey defines a [[functional dependency]] constraint of a relation schema which must hold for all possible instance relations of that relation schema.

==See also==
* [[Alternate key]]
* [[Candidate key]]
* [[Compound key]]
* [[Foreign key]]
* [[Primary key]]

==References==
{{reflist}}

==Further reading==
* {{cite book|last=Silberschatz|first=Abraham|title=Database System Concepts (6th ed.)|year=2011|publisher=McGraw-Hill|isbn=978-0-07-352332-3|pages=45–46}}

==External links==
* [http://rdbms.opengrass.net/2_Database%20Design/2.1_TermsOfReference/2.1.2_Keys.html Relation Database terms of reference, Keys]: An overview of the different types of keys in an RDBMS

{{Databases}}

[[Category:Data modeling]]
[[Category:Database management systems]]