Differences between key, superkey, minimal superke

Question

I'm new to MySQL, and I'm really confused about the different terms that I've encountered. I tried googling the answer but the results are really confusing and when I try and understand it just seems like they are the same thing.

What exactly are the differences among key, superkey, minimal superkey, candidate key and primary key?

Answer 1

Superkey - An attribute or set of attributes that uniquely defines a tuple within a relation. However, a superkey may contain additional attributes that are not necessary for unique identification.

Candidate key - A superkey such that no proper subset is a superkey within the relation. So, basically has two properties: Each candidate key uniquely identifies tuple in the relation ; & no proper subset of the composite key has the uniqueness property.

Composite key - When a candidate key consists of more than one attribute.

Primary key - The candidate key chosen to identify tuples uniquely within the relation.

Alternate key - Candidate key that is not a primary key.

Foreign key - An attribute or set of attributes within a relation that matches the candidate key of some relation.

Answer 2

SUPER KEY:

Attribute or set of attributes used to uniquely identify tuples in the database.

CANDIDATE KEY:

1. Minimal super key is the candidate key
2. Can be one or many
3. Potential primary keys
4. not null
5. attribute or set of attributes to uniquely identify records in DB

PRIMARY KEY:

1. one of the candidate key which is used to identify records in DB uniquely

2. not null

Answer 3

Primary key is a subset of super key. Which is uniquely define and other field are depend on it. In a table their can be just one primary key and rest sub set are candidate key or alternate keys.

Answer 4

Super Key : Super key is a set of one or more attributes whose values identify tuple in the relation uniquely.

Candidate Key : Candidate key can be defined as a minimal subset of super key. In some cases , candidate key can not alone since there is alone one attribute is the minimal subset. Example,

Employee(id, ssn, name, addrress)

Here Candidate key is (id, ssn) because we can easily identify the tuple using either id or ssn . Althrough, minimal subset of super key is either id or ssn. but both of them can be considered as candidate key.

Primary Key : Primary key is a one of the candidate key.

Example : Student(Id, Name, Dept, Result)

Here

Super Key : {Id, Id+Name, Id+Name+Dept} because super key is set of attributes .

Candidate Key : Id because Id alone is the minimal subset of super key.

Primary Key : Id because Id is one of the candidate key

Answer 5

I have always found it difficult to remember all the keys; so I keep the below notes handy, hope they help someone! Let me know if it can be improved.

• Key: An attribute or combination of attributes that uniquely identify an entity/record in a relational table.

• PK: A single key that is unique and not-null. It is one of the candidate keys.

• Foreign Key: FK is a key in one table (child) that uniquely identifies a row of another table (parent). A FK is not-unique in the child table. It is a candidate key in the parent table. Referential integrity is maintained as the value in FK is present as a value in PK in parent table else it is NULL.

• Unique Key: A unique key that may or may not be NULL

• Natural key: PK in OLTP. It may be a PK in OLAP.

• Surrogate Key: It is the Surrogate PK in OLAP acting as the substitute of the PK in OLTP. Artificial key generated internally in OLAP.

• Composite Key: PK made up of multiple attributes

• SuperKey: A key that can be uniquely used to identify a database record, that may contain extra attributes that are not necessary to uniquely identify records.

• Candidate Key: A candidate key can be uniquely used to identify a database record without any extraneous data. They are Not Null and unique. It is a minimal super-key.

• Alternate Key: A candidate key that is not the primary key is called an alternate key.

• Candidate Key/s with Extraneous data: Consider that can be used to identify a record in the Employee table but candidate key alone is sufficient for this task. So becomes the extraneous data.

Note that the PK, Foreign Key, Unique Key, Natural key, Surrogate Key, Composite Key are defined as Database objects; where the Natural key is a PK in the OLTP and could be a PK in the target OLAP. For the rest of the keys, it's up to the DB designer/architect to decide whether unique/not-null/referential integrity constraints need to enforced or not.

Below I have tried to use set theory to simplify the representation of the membership of the keys w.r.t. each other.

key = { All of the below keys   }
PK  = { PK  }
Foreign Key = { Key with Not Null constraint    }
Unique Key  = { {Candidate Key/s}, {attributes containing NULL} }
Natural key = { PK  }
Surrogate Key   = { PK  }
Composite Key   = { PK  }
Super Key   = { {Candidate Key/s}, {Candidate Key/s with Extraneous data}   }
Candidate Key   = { PK, {Alternate Key/s}   }
Alternate Key   = { {Candidate Keys} - PK   }
Candidate Key/s with Extraneous data    = {     }

I have summarized it below:

Notes: an-overview-of-the-database-keys-primary-key-composite-key-surrogate-key-et-al

Answer 6

Candidate Key: The candidate key can be defined as minimal set of attribute which can uniquely identify a tuple is known as candidate key. For Example, STUD_NO in below STUDENT relation.

• The value of Candidate Key is unique and non-null for every tuple.
• There can be more than one candidate key in a relation. For Example, STUD_NO as well as STUD_PHONE both are candidate keys for relation STUDENT.
• The candidate key can be simple (having only one attribute) or composite as well. For Example, {STUD_NO, COURSE_NO} is a composite
  candidate key for relation STUDENT_COURSE.

Super Key: The set of attributes which can uniquely identify a tuple is known as Super Key. For Example, STUD_NO, (STUD_NO, STUD_NAME) etc. Adding zero or more attributes to candidate key generates super key. A candidate key is a super key but vice versa is not true. Primary Key: There can be more than one candidate key in a relation out of which one can be chosen as primary key. For Example, STUD_NO as well as STUD_PHONE both are candidate keys for relation STUDENT but STUD_NO can be chosen as primary key (only one out of many candidate keys).

Alternate Key: The candidate key other than primary key is called as alternate key. For Example, STUD_NO as well as STUD_PHONE both are candidate keys for relation STUDENT but STUD_PHONE will be alternate key (only one out of many candidate keys).

Foreign Key: If an attribute can only take the values which are present as values of some other attribute, it will be foreign key to the attribute to which it refers. The relation which is being referenced is called referenced relation and corresponding attribute is called referenced attribute and the relation which refers to referenced relation is called referencing relation and corresponding attribute is called referencing attribute. Referenced attribute of referencing attribute should be primary key. For Example, STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in STUDENT relation.