×

DBMS Tutorial

DBMS Practice

Keys and Their Types in DBMS

DBMS Keys: In this tutorial, we will learn about the DBMS Keys and their types with the help of examples. By Shamikh Faraz Last updated : May 28, 2023

What are Keys in DBMS?

In the database management system, a key is an attribute or a set of attributes that is used for uniquely identification of the records i.e., to uniquely identify a tuple (or row) in a relation (or table).

Types of DBMS Keys

There are a total of 11 keys in the DBMS that you can use based on your requirements. DBMS keys are super key, candidate key, primary key, composite key, compound key, secondary or alternative key, non-key attribute, non-prime attribute, foreign key, simple key, and artificial key. Let's discuss them in detail.

1) Super keys

Super key is a set of one or more than one columns (attributes) which uniquely identifies each record in a table. Super key is a super set of candidate key.

DBMS Super keys

For example: Roll No. is unique in relation. This can be selected as a super key. Also we can select more than one column as a super key to uniquely identify a row, like roll no., First name.

2) Candidate keys

Candidate key is a set of one or more than one columns (attributes) which uniquely identifies each record in a table, but there must not be redundant values (repetition of cells) in selected attribute. Candidate key is a sub set of Super key.

DBMS Candidate keys

For example: Roll No. is unique in relation. This can be selected as a candidate key. Also we can select more than one column as a candidate key to uniquely identify a record. Unlike the super key in above example we can select only those attributes which don't have repeating cells like course code.

3) Primary keys

Primary key is used to uniquely identify a record in relation. The primary keys are compulsory in every table. The primary keys are having model stability, occurrence of minimum fields, being definitive and feature of accessibility.

DBMS primary keys

Only Roll No. is unique in the above table, so it is selected as primary key. Course code can also be selected as a primary key.

4) Composite keys

Composite Key has at-least two or more than two attributes which specially identifies the occurrence of an entity.

DBMS composite keys

In the above example the Roll No. and Course Code is combined to uniquely identify the record in relation.

5) Compound key

Like other keys Compound key is also used to uniquely recognize a record in relation. This can be an attribute or a set of attributes, but the attributes in relation cannot be use as independent keys. If we use them individually, we will not get any unique record.

6) Secondary or Alternative key

The key other than primary keys are called as secondary or alternative keys.

Example: If we consider Roll No. and Course code as primary key then First Name of Student and First Name of Student will be Secondary/alternate keys.

DBMS secondary keys

7) Non-key Attribute

The attributes excluding the candidate keys are called as non-key attributes.

Example: If we consider Roll No. and Course code as candidate key then First Name of Student and First Name of Student will be Non Key attribute.

DBMS Non Key attribute

8) Non-prime Attribute

Excluding primary attributes in a table are non-prime attributes.

DBMS Non Prime key attribute

Example: It is considered as only Roll No. is primary key, so all the remaining attributes will be non prime attributes, but if we considering course code also a primary key than it will not non prime attribute.

9) Foreign keys

Foreign key is a key of one table, which points to the primary key in second table. It has a relationship with primary key in another table.

DBMS foreign key

The "BusinessEntityID" attribute in the "Person" relation is the PRIMARY KEY. The "BusinessEntityID" attribute in the "PersonPhone" relation is a FOREIGN KEY.

10) Simple key

Simple key is a single cell to specially identify a record. The single cell cannot be divided into more cells. Primary key is a super set of simple key.

Example: In the below example student id is a single field because no other student will have same Id. Therefore, it is a simple key.

DBMS simple key

11) Artificial key

When primary key is very large and complex, then 'Artificial keys' are used. They are allowed when no property has the parameter of primary key. They are generally not used in professional organization.



Comments and Discussions!

Load comments ↻





Copyright © 2024 www.includehelp.com. All rights reserved.