What are Keys in DBSM?–Types of Keys

A key is an attribute or set of attributes that uniquely identifies a tuple in a relation. The keys in a table help you access or sequence stored data quickly and smoothly. They are also used to create relationships between different tables.

Super Key

When we talk about keys in the context of databases, a super key is an attribute, or a combination of attributes, that can be used to uniquely identify a row within a given table. In other words, if you have a table with several rows of data, a super key would be something that allows you to identify one specific row out of all the others. Super keys are the most general type of key.

For example, a relation STUDENT consists of different attributes like Registration No, Name, FatherName, Class, and Address. The only attribute that can uniquely identify a tuple in the relation is Registration No. The Name attribute can’t be used to identify a tuple because more than one student might have the same name.

The same goes for FatherName, Class, and Address. That means registration is the super key for the relation. Any combination of attributes with the super key is also a super key. It means any attribute or set of attributes combined with the super key RegistrationNo will also become a super key. A combination of two attributes (RegistrationNo, Name] is also a super key. This combination can also be used to identify a tuple in the relation. Similarly, (RegistrationNa Class) or [RegistrationNo, Name, Class) are also super keys.

Candidate Key

A candidate key is a super key that contains no extra attribute. It consists of minimum possible attributes. A super key like (Registration No, Name contains an extra field Name. It can be used in identifying a tuple uniquely in the relation.

But it does not consist of the minimum possible attribute as only Registration No can be used to identify a tuple in the relation. It means that [RegistrationNo, Name) is a super key but it is not a candidate key because it contains an extra field. On the other hand, Registration Net super key as well as a candidate key.

Primary Key

A primary key is a candidate key that is selected to identify tuples uniquely in a relation. A relation may contain many candidate keys. A primary key is selected by the designer when only one candidate key is available. This key is important because it means that if there is only one tuple in the relation, it cannot be selected as the primary key.

Some most important points about a primary key are:

• A relation have only one primary key.
•  Each value in the primary key attribute must be unique.
• A primary key cannot contain null values, because null values cannot be used to uniquely identify a row.

Suppose a relation Student contains different attributes such as Reg No. Name and Class. Attribute RegNo uniquely identifies the student in the table. It can be used as the primary key for the table The attribute Name cannot uniquely identify each row because two students can have the same name. I cannot be used as a primary key.

Alternate Key

There are some keys that, although they may not be selected as the primary key, are still important. These are called alternate keys. A student’s information in a school’s database can be divided into different categories, such as by their registration number, roll number, name, and class.

Two attributes that can be used to identify each student are their RegNo and RollNo. The RollNo attribute is referred to as the alternate key if RegNo is selected as the primary key.

Composite Key

A composite key consists of more than one attribute.

For example, the following relation uses two fields RollNo and subject to identify each tuple.

Foreign Key

 A foreign key is an attribute or set of attributes in one relation that correspond to the primary key in another relation. The relation in which foreign keys are created is known as the dependent table or child table. The relation to which the foreign key refers is known as the parent table.

When two relations are established, a key connects them. This is called a foreign key. A relation may have many foreign keys.

The RollNo attribute in the parent relation is used as the primary key. The RollNo attribute in the child relation is used as the foreign key. This refers to the RollNo attribute in the parent relation.