Home » DBMS

Keys in Database Management System (DBMS)

In this article, we are going to learn about Database Management System keys Super key, Candidate key, Primary key, Composite key, Compound key, Secondary or Alternative key, Non- key attribute, Non- prime attribute, Foreign key, Simple key, Artificial key.
Submitted by Shamikh Faraz, on March 15, 2018

Database table consists of rows and columns, which are technically called 'record or tuple' and 'attributes or fields' respectively. A database table is generally called a 'relation'. The keys are used to uniquely identify a record (row) in the table. Which key should be used depends on requirement.

  • Table = Relation
  • Row = Record/Tuple
  • Column = Attribute/Field

Example:

Keys in DBMS 1

List of different type of keys in DBMS

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

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.

Test your skill: Aptitude Questions and Answers on DBMS Different types of Keys






Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.




Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates


© https://www.includehelp.com some rights reserved.