Super Key: set of one or more attributes, when taken collectively, can identify uniquely an entity in the entity set. There can be more than one super key of an Entity set.
Candidate Key: The minimal super key is a candidate key i.e those super keys of the entity set, who doesn’t have any subset which are also a super key are called candidate keys. There can be more than one candidate key of an Entity set.
Primary Key: A candidate key which used as the key by database administrator while implementing the database management system is called primary key of the entity set.
Example: Consider an Entity Set named "Student” which has following set of attributes:
1. Student ID
2. Roll Number
3. Name
4. Father’s Name
5. DOB
6. Address
Various entities of the above entity set will have the values for all the fields. But no two entities i.e. no two students will have same values for all the six attributes. So one of the super key is set containing following attributes
Super Key1: (Student ID, Roll Number, Name, Father’s Name, DOB, Address)
Super Key2: (Student ID, Roll Number, Name, Father’s Name, DOB) will not have same values for any two students in "Student” entity set. Similarly other super key of the above entity set are following:
Super Key3: (Student ID, Roll Number, Name, Father’s Name)
Super Key4: (Student ID, Roll Number, Name)
Super Key5: (Student ID, Roll Number)
Super Key6: (Student ID)
Also Roll number is unique for a student so
Super Key7: (Roll Number)
All of them are sufficient to identify a particular student entity in the Entity Set of all students.
The Super Key6 and Super Key7 is the candidate key of this entity set as no subset of any of them is a super key. They are minimal subsets which are super keys.
Candidate Key1: (Student ID)
Candidate Key2: (Roll Number)
Any one of the candidate keys can be used as primary key. So Primary Key: Either (Student ID) Or (Roll Number) The Primary Key of the many-to-many Relationship set is formed by including the primary keys of all participating Entity sets. The Primary Key of the many-to-one and one-to-many Relationship set is formed by including only the primary key of entity set from which many entities takes part in the association to one of other participating entity set. E.g. In case of "Borrowed By” relationship between entities "Book” and "User” which is a many-to-one relationship in the sense many books may be borrowed by one user but one book cannot be borrowed by many users. So for "Borrowed By” relationship the primary key will only contain the Primary key of Entity Set "Book”.
In case of One-to-One type the primary key of any one of the participating entity set is used as primary key of the relationship.
