Keys in DBMS

• KEYS in DBMS is an attribute or set of

attributes which helps you to identify a
row(tuple) in a relation(table). They allow you
to find the relation between two tables. Keys
help you uniquely identify a row in a table by
a combination of one or more columns in that
table. Key is also helpful for finding unique
record or row from the table. Database key is
also helpful for finding unique record or row
from the table.
 Why we need a Key?

• Here are some reasons for using sql key in the DBMS
system.
• Keys help you to identify any row of data in a table. In
a real-world application, a table could contain
thousands of records. Moreover, the records could be
duplicated. Keys ensure that you can uniquely identify
a table record despite these challenges.
• Allows you to establish a relationship between and
identify the relation between tables
• Help you to enforce identity and integrity in the
relationship.
 Types of Keys in Database
Management System
There are mainly seven different types of Keys in DBMS and each key has it’s different
functionality:
• Super Key - A super key is a group of single or multiple keys which identifies rows in a
table.
• Primary Key - is a column or group of columns in a table that uniquely identify every
row in that table.
• Candidate Key - is a set of attributes that uniquely identify tuples in a table. Candidate
Key is a super key with no repeated attributes.
• Alternate Key - is a column or group of columns in a table that uniquely identify every
row in that table.
• Foreign Key - is a column that creates a relationship between two tables. The purpose
of Foreign keys is to maintain data integrity and allow navigation between two
different instances of an entity.
• Compound Key - has two or more attributes that allow you to uniquely recognize a
specific record. It is possible that each column may not be unique by itself within the
database.
• Composite Key - An artificial key which aims to uniquely identify each record is called
a surrogate key. These kind of key are unique because they are created when you don't
have any natural primary key.
What is the Super key?
 What is a Primary Key?

• PRIMARY KEY is a column or group of columns in a table

that uniquely identify every row in that table. The
Primary Key can't be a duplicate meaning the same
value can't appear more than once in the table. A table
cannot have more than one primary key.
• Rules for defining Primary key:
Two rows can't have the same primary key value
It must for every row to have a primary key value.
The primary key field cannot be null.
The value in a primary key column can never be
modified or updated if any foreign key refers to that
primary key.
What is the Alternate key?
 What is a Candidate Key?

• CANDIDATE KEY is a set of attributes that uniquely identify

tuples in a table. Candidate Key is a super key with no
repeated attributes. The Primary key should be selected
from the candidate keys. Every table must have at least a
single candidate key. A table can have multiple candidate
keys but only a single primary key.
• Properties of Candidate key:
It must contain unique values
Candidate key may have multiple attributes
Must not contain null values
It should contain minimum fields to ensure uniqueness
Uniquely identify each record in a table
What is the Foreign key?
What is the Compound key?
 What is composite key
• COMPOSITE KEY is a combination of two or more
columns that uniquely identify rows in a table.
The combination of columns guarantees
uniqueness, though individually uniqueness is not
guaranteed. Hence, they are combined to
uniquely identify records in a table.
• The difference between compound and the
composite key is that any part of the compound
key can be a foreign key, but the composite key
may or maybe not a part of the foreign key.
 MCQ on Keys
• Which one of the following is a set of one or
more attributes taken collectively to uniquely
identify a record?
a) Candidate key
b) Sub key
c) Super key
d) Foreign key
• Answer: c
Explanation: Super key is the superset of all
the keys in a relation.
• Consider attributes ID, CITY and NAME. Which
one of this can be considered as a super key?
a) NAME
b) ID
c) CITY
d) CITY, ID
• Answer: b
Explanation: Here the id is the only attribute
which can be taken as a key. Other attributes
are not uniquely identified.
• The subset of a super key is a candidate key
under what condition?
a) No proper subset is a super key
b) All subsets are super keys
c) Subset is a super key
d) Each subset is a super key
• Answer: a
Explanation: The subset of a set cannot be the
same set. Candidate key is a set from a super
key which cannot be the whole of the super
set.
• A _____ is a property of the entire relation,
rather than of the individual tuples in which
each tuple is unique.
a) Rows
b) Key
c) Attribute
d) Fields
• Answer: b
Explanation: Key is the constraint which
specifies uniqueness.
• An attribute in a relation is a foreign key if the
_______ key from one relation is used as an
attribute in that relation.
a) Candidate
b) Primary
c) Super
d) Sub
• Answer: b
Explanation: The primary key has to be
referred in the other relation to form a foreign
key in that relation.
• The relation with the attribute which is the
primary key is referenced in another relation.
The relation which has the attribute as a
primary key is called
a) Referential relation
b) Referencing relation
c) Referenced relation
d) Referred relation
• B answer
• The ______ is the one in which the primary
key of one relation is used as a normal
attribute in another relation.
a) Referential relation
b) Referencing relation
c) Referenced relation
d) Referred relation
• C answer
• A _________ integrity constraint requires that
the values appearing in specified attributes of
any tuple in the referencing relation also
appear in specified attributes of at least one
tuple in the referenced relation.
a) Referential
b) Referencing
c) Specific
d) Primary
• Answer: a
Explanation: A relation, say r1, may include
among its attributes the primary key of
another relation, say r2. This attribute is called
a foreign key from r1, referencing r2. The
relation r1 is also called the referencing
relation of the foreign key dependency, and r2
is called the referenced relation of the foreign
key.
• Which is correct sentence regarding primary
key?
a)It is used to compare more than one record in
a table.
b)It is used to define records uniquely.
• Answer :b
• A --- key can be primary key?
Choose the correct term to complete the
sentence.
a) Foreign
b) Primary
c) Candidate
d) Composite
• Correct Answer - 3
Candidate