Keys In E-R Model
No two entities in an entity set are allowed to have exactly the same value for all attribute.
So, a key allows us to identify a set of attributes that suffice to distinguish entities from each other.
Example data in entity attribute customer-id of entity customer should be unique and should not be same for other entity set of same attribute customer-Id.
Keys in Entity Sets:There are many keys through which we can uniquely identify an entity and row.
1) Super Key:A super key is a set of one or more attributes that taken collectively, allow us to identify uniquely and entity in the entity set.
Customer ID attribute of the entity set customer sufficient to distinguish one customer entity from another. Thus customer ID is our super key. Combination of customer-name and customer-ID is super key for the entity set customer. But customer-name attribute is not super key as persons has same name.
2) Candidate Key:The Minimal super keys are called candidate key. If we consider k is a superkey then candidate key will be any superset of K.
3) Primary Key:It is a candidate key attribute / column that is more suited to maintain uniqueness in a table at the row/ level.
Example let's take entity employee with attributes
Employee_ID, Employee_Name, Date_Of_Birth, Date_Of_Joining SSN( Social Security Number), Department_Id, Manager_Id
In this Employee Entity, attribute Employee_ID and SSN individually can maintain uniqueness in a table. So they are eligible for candidate key. Attribute like Employee_Name, Date_Of_Birth combined can also make up a candidate key. But there is an error chance that two employees with same name can be born on same day.
In this Employee_ID and SSN (not combindally) only be used here at primary key because every value in this attribute are unique.
In this Employee_ID + Employee_Name is super key because super key is a superset of candidate key. If we add any attribute in candidate key then it becomes a super key, As employee name can be name of other employee so combining them gives unique value.