Various Keys in Database Management System (DBMS): For the clarity in DBMS, keys are preferred and they are important part of the arrangement of a table. Keys make sure to uniquely identify a table’s each part or record of a field or combination of fields. A database is made up of tables, which (tables) are made up of records, which (records) further made up of fields. Let us take an example to illustrate what are keys in database management system. This article is about different keys in database management system (DBMS).

Example:

Keys In Database Management
Keys In Database Management

In the above data item, each column is a field and each row is a record.

Various Keys in Database Management System (DBMS)

Types of Keys in Database Management System: Each key which has the parameter of uniqueness is as follows:

  1. Super key
  2. Candidate key
  3. Primary key
  4. Composite key
  5. Secondary or Alternative key
  6. Non- key attribute
  7. Non- prime attribute
  8. Foreign key
  9. Simple key
  10. Compound key
  11. Artificial key

The detailed explanation of all the mentioned keys is as follows:

  1. Super key

          Super Key is a set of properties within a table; it specially identifies each record in a table. Candidate key is a unique case of super key.

  • For example: Roll No. of a student is unique in relation. The set of properties like roll no., name, class, age, sex, is a super key for the relation student.

Also See: Advantages Of Database Management System

  1. Candidate keys

          Candidate keys are the set of fields; primary key can be selected from these fields. A set of properties or attributes acts as a primary key for a table. Every table must have at least one candidate key or several candidate keys. It is a super key’s subset.

  • Example:

Candidate Key

  • The above fields of a candidate key uniquely identify a student.
  • It has the properties like – Being unique and Parameter of irreducibility.

  1. Primary key

          The candidate key which is very suitable to be the main key of table is a primary key.

  • The primary keys are compulsory in every table.
  • The properties of a primary key are:
    • Model stability
    • Occurrence of minimum fields
    • Defining value for every record i.e. being definitive
    • Feature of accessibility
  • Example

Primary KeyAlso See: Database Architecture in DBMS with Diagram

  1. Composite key

          Composite Key has two or more properties which specially identifies the occurrence of an entity.

  • Example:

Candidate Key

  • In the above example the customer identity and order identity has to combine to uniquely identify the customer details.

  1. Secondary or Alternative key

          The rejected candidate keys as primary keys are called as secondary or alternative keys.

  • Example:

Alternative Key

  1. Non-key Attribute

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

  • Example:

Non Key Attribute

  1. Non-prime Attribute

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

  • Example:

 

Non prime attributes
Non prime attributes

  1. Foreign key

          Generally foreign key is a primary key from one table, which has a relationship with another table.

  • Example:

Primary Key and Foreign Key Relationship

  1. Simple key

          Simple keys have a single field to specially recognize a record. The single field cannot be divided into more fields. Primary key is also a 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.

 Simple Key

  1. Compound key

          Compound key has many fields to uniquely recognize a record. Compound key is different from composite key because any part of this key can be foreign key but in composite key its part may or may not be a foreign key.

  1. Surrogate/Artificial key

          Surrogate key is artificially generated key and its main purpose it to be the primary key of table. Artificial keys do not have meaning to the table.There are few properties of surrogate or artificial keys.

They are unique because these just created when you don’t have any natural primary key.

They are integer values.

One cannot find the meaning of surrogate keys in the table.

End users cannot surrogate key.

Surrogate keys are allowed when

  • No property has the parameter of primary key.
  • The primary key is huge and complex.

Example: Table which has the details of the student has primary key but it is large and complex. The addition of row id column to it is the DBA’s decision, where the primary key is row id.

12. Natural /Domain/ Business Key

It is a key that is naturally declared as the Primary key. Natural keys are sometimes called as business or domain keys because these key are based on the real world observation. So it is a key whose attributes or values exists in the real world. These attributes have logical relationship with the table.

For Example: Social Security Number (SSN) is a natural key that can be declared as the primary key

Also See: Examples of Database Management System

So all these are the Various Keys in Database Management System (DBMS). If you have any problem in these database keys then please comment below.

Various Keys in Database Management System (DBMS)https://whatisdbms.com/wp-content/uploads/2016/06/photo-database.jpghttps://whatisdbms.com/wp-content/uploads/2016/06/photo-database-150x150.jpgSumit ThakurWhat Is DBMSVarious Keys in Database Management System (DBMS): For the clarity in DBMS, keys are preferred and they are important part of the arrangement of a table. Keys make sure to uniquely identify a table’s each part or record of a field or combination of fields. A database is made...Let's Define DBMS