What is the keys?
A keys in DBMS is a field, or combination
of fields, in a database table to uniquely identify a row in the table. Keys
are used to retrieving and sorting rows in the table based on certain
requirements.
Why are keys used?
Keys are defined to speed up access to data and, in many cases, to
create links between different tables and to enforce certain constraints
to ensure the integrity of data.
Types of Keys:
1. Super Key
2. Primary Key
3. Candidate Key
4. Composite Key
5. Alternate Key
6. Foreign Key
7. Compound Key
8. Surrogate Key
Super Keys
Super Key is defined as a set of attributes within a table that can
uniquely identify each record within a table. Super Key is a super-set of the
Candidate key. It may have additional attributes that are not needed for unique
identification.
For the above example: Super Key= (Sap ID, student_id,first_name) Here,
Sap ID alone could be the key to uniquely identify each row, yet additional
fields like student_id, first_name are included.
Primary Keys
A column or group of columns in a table that helps us to uniquely
identifies every row in that table is called a primary key For the above example: Primary Key= (Sap ID) or,
(student_id, first_name, last_name) or any other extra field added with each.
Candidate Keys
A super key with no redundant attribute is known as
a candidate key.
For the above example: Candidate Key= (Sap ID) or,
(student_id) or (first_name, last_name). Here First name alone can’t be the key
as it appears/could appear in more than 1 record.
Composite Keys
A key that consists of more than one attribute to
uniquely identify rows (also known as records & tuples) in a table is
called composite key
For the above example: Primary Key= (first_name,
last_name). Since this has 2, i.e. more than 1 field, therefore, it’s a valid
composite key.
Alternate keys
All the keys which are not primary key are called
an alternate key. It is a candidate key which is currently not the primary key.
For the above example: If (Sap ID) is the Primary
key then Alternate Key= (student_id) or (first_name, last_name) or even (phone)
if they are known to be unique.
Foreign Keys
Foreign keys are the columns of a table that points
to the primary key of another table. They act as a cross-reference between
tables
For the above example: If (Sap ID) is the Primary
key then it could be a foreign key to some other table (Eg: class_details table
that stores Sap ID and related class details for eg standard, division, branch,
etc.)
Compound Key
There may be chances that 2 columns/fields are not
unique by themselves, but when combining both of them it could be used as a
key. Such keys are called compound keys.
For the above example: first_name and last_name
aren’t unique by themselves but when combining them they can be used as a key.
Compound key: (first_name, last_name)
Surrogate Key
A surrogate key is an artificial key that aims to
uniquely identify each record in the absence of or too complicated primary key
in a table. These kinds of keys are unique, usually an integer, but they do not
lend any meaning to the data in the table.
For the above example: Consider Sap ID can be the
same for two students from the different academic years then, we could add the timestamp of admission or just any random additional number as the surrogate
key.
If you are interested to learn Digital marketing so kindly go to the World Stream SEO official website.
Post a Comment