In a Database, the tuples or attributes need to be uniquely identified. That is, the values of tuples should be such that they must satisfy the non-redundancy condition. In other words, if all the values are the same for two or more attributes, this may create a problem.
Superkey
In a relation, the super key is the set of attributes that are used to uniquely identify tuples in the relation. For example, the ID attribute of the relation instructor is sufficient to distinguish one instructor tuple from 29 others. Thus, ID is a superkey. The name attribute of the instructor, on the other hand, is not a superkey, because several instructors might have the same name. Formally, let R denote the set of attributes in the schema of relation r. If we say that a subset K of R is a superkey for r, we are restricting consideration to instances of relations r in which no two distinct tuples have the same values on all attributes in K. That is, if t1 and t2 are in r and t1 = t2, then t1.K = t2.K.
Candidate key
A candidate key is a specific type of field in a relational database that can identify each unique record independently of any other data. Experts describe a candidate key as having “no redundant attributes” and being a “minimal representation of a tuple” in a relational database table.
It is possible that several distinct sets of attributes could serve as a candidate key. Suppose that a combination of name and dept name is sufficient to distinguish among members of the instructor relation. Then, both {ID} and {name, dept name} are candidate keys. Although the attributes ID and name together can distinguish instructor tuples, their combination, {ID, name}, does not form a candidate key, since the attribute ID alone is a candidate key.
Primary Key
The minimal set of attributes that can uniquely identify rows in a table is called primary key attributes.
Primary keys must be chosen with care. The following things that need to be taken care of to make a key primary key are as follows –
Minimal – Primary key must contain the least possible number of attributes that are required to uniquely identify tuples in a relation.
Accessibility – Primary key must be easily accessible to the users who want to use it, and they must be able to insert or delete it easily.
Non-Null – Primary key attributes must not contain NULL values
Time–Variance – The primary key must not change with the occurrence of time variance.
This can be easily done if we select some unique combination of other attributes as the primary key. The primary key should be chosen such that its attribute values are never, or very rarely, changed. For example, the current address of a worker in relation of factory management cannot be chosen as the primary key because it is likely to change over time. Whereas, social security number or aadhaar card number can be chosen as the primary key because it is guaranteed that it will not change.
Foreign Key
As the name suggests, it is used to maintain reference with some other table or foreign table. If some attribute is the primary key in one table, we can make it a foreign key in the other table. In this way, a connection is set between the two tables via the primary key.
Definition – Foreign key is a field or a collection of fields that refers to a primary key in some other table.
In these two relations, the id in the second table is made the primary key and the id in the second table is made the foreign key to insert the linkage between the two tables.
You may also like to read: Data Structures Tutorials
Add also me