All of the above sets are able to uniquely identify rows of the employee table.
Candidate key
Candidate keys are selected from the set of super keys, the only thing we take care while selecting candidate key is: It should not have any redundant attribute. That’s the reason they are also termed as minimal super key.Minimal suerkey is known as candidate key. Minimal super keys with no redundant attributes.
{Emp_SSN} {Emp_Number}
Only these two sets are candidate keys as all other sets are having redundant attributes that are not necessary for unique identification. Primary Key
A primary key is a special relational database table column (or
combination of columns) designated to uniquely identify all table records.
A primary key’s main features are:
It must contain a unique value for each row of data.
It cannot contain null values.
A primary key is either an existing table column or a column that is
specifically generated by the database according to a defined sequence.
Example:
Student Table
Stu_Id Stu_Name Stu_Age
101 Steve 23 102 John 24
103 Robert 28
104 Carl 22
In the above Student table, the Stu_Id column uniquely identifies each row of the table. Foreign keys
Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.
For example: In the below example the Stu_Id column in Course_enrollment table is a foreign key as it points to the primary key of the Student table.
Note: Practically, the foreign key has nothing to do with the primary key tag of another table, if it points to a unique column (not necessarily a primary key) of another table then too, it would be a foreign key. So, a correct definition of foreign key would be: Foreign keys are the columns of a table that points to the candidate key of another table. Composite key
A key that consists of more than one attribute to uniquely identify rows (also known as records & tuples) in a table is called composite key. It is also known as compound key.
Example: Table – Sales
cust_Id order_Id product_code product_count
C01 O001 P007 23
C02 O123 P007 19
C02 O123 P230 82
C01 O001 P890 42
Key in above table: {cust_id, order_id}
This is a composite key as it consists of more than one attribute.
Alternative or secondary keys
Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternative or secondary keys.
For example: Consider the below table
Emp_Id Emp_Number Emp_Name
E01 2264 Steve
E22 2278 Ajeet
E23 2288 Chaitanya
E45 2290 Robert
There are two candidate keys in above table:
{Emp_Id} {Emp_Number} Since we have selected Emp_Id as primary key, the remaining key Emp_Number would be called alternative or secondary key. Non-prime Attribute Excluding primary attributes in a table are non-prime attributes.
