Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Explain the concept of key attributes with an example in DBMS?
Key is a data item which is used to identify a record or a value used to identify a record in a database is called a key. It helps uniquely to identify an entity from an entity set.Key allows us to identify a set of attributes that make it sufficient to distinguish entities from each other.
Example
Account number, employee number, customer number are used as key fields because they specifically identify a record stored in a database.
Different types of keys
The different types of keys in the database management system (DBMS) are as follows −
Primary key
Super key
Candidate key
Alternate key
Artificial key
Foreign key
Primary, super, candidate, alternate and artificial keys show the property of uniqueness whereas the foreign key shows referential integrity.
Let’s consider a table −
| StdNo | Name | Branch | Branch |
|---|---|---|---|
| 01 | Hari | Cse | A |
| 02 | Pinky | Ece | B |
| 03 | Bob | Civil | A |
Candidate key
It is an attribute whose values uniquely identify each record of a table. A table can have more than one candidate key.
Candidate key satisfy some conditions which are as follows −
Different records cannot be identical.
Subset of the candidate key cannot be a key.
In the above student table, candidate keys are as follows −
stdNo and branch, because they uniquely identify each record.
But {stdNo, name} is not a candidate key because stdNo alone is candidate key
Primary Key
From many candidate keys, the database designer selects one candidate key for his database called as primary key. The primary key uniquely identifies each record in a table and must never be the same for two records.
Example
In the above table stdNo is the primary key.
Primary key has some properties as mentioned below:
Uniqueness (two different records cannot be identical).
NOT NULL (null value is not allowed).
Value in a primary key column can never be modified or updated, if any foreign key refers to that primary key.
Primary key is underlined in a table to clearly identify.
For Example − stdNo
A single column primary key is called a simple key and a Multi-column primary key is called composite primary key.
Example
stdNo is a simple primary key.
{orderNo, productNo} is a composite primary key.
Alternate Key
Candidate keys which are not selected for primary keys are called alternate keys. That is, except for the primary key the remaining candidate keys are alternate keys.
For Example −
In the above student table stdNo is the primary key so the rest candidate key i.e branch is an alternate key.
Super key
It is the superset of the candidate key. The combine attributes where the first attribute always have uniqueness and second attribute may or may not have uniqueness.
Example
In the above table candidate keys are {stdNo},{branch}
Super keys are as follows −
{stdNo, name}
{stdNo, address}
{branch, section}
{stdNo} etc.
Super keys are formed by adding extra attributes to candidate keys.
Artificial key
It is used in the form of indexes. If the size of the database is large and no attribute can be specified as a key field then the index or artificial key is applied on the given database.
Foreign key
In a relation, the column whose data values correspond to the values of a key column in another relation is called a foreign key. In a relational database, the foreign key of a relation may be the primary key of another relation.
3K+ Views
