Keys are very important part of Relational database model. They are used to establish and identify relationships between tables and also to uniquely identify any record or row of data inside a table.
A Key can be a single attribute or a group of attributes, where the combination may act as a key.
In real world applications, number of tables required for storing the data is huge, and the different tables are related to each other as well.
Also, tables store a lot of data in them. Tables generally extends to thousands of records stored in them, unsorted and unorganised.
Now to fetch any particular record from such dataset, you will have to apply some conditions, but what if there is duplicate data present and every time you try to fetch some data by applying certain condition, you get the wrong data. How many trials before you get the right data?
To avoid all this, Keys are defined to easily identify any row of data in a table.
Let's try to understand about all the keys using a simple example.
Let's take a simple Student table, with fields
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 superset of Candidate key.
In the table defined above super key would include
Confused? The first one is pretty simple as
student_id is unique for every row of data, hence it can be used to identity each row uniquely.
(student_id, name), now name of two students can be same, but their
student_id can't be same hence this combination can also be a key.
Similarly, phone number for every student will be unique, hence again,
phone can also be a key.
So they all are super keys.
Candidate keys are defined as the minimal set of fields which can uniquely identify each record in a table. It is an attribute or a set of attributes that can act as a Primary Key for a table to uniquely identify each record in that table. There can be more than one candidate key.
In our example,
phone both are candidate keys for table Student.
Primary key is a candidate key that is most appropriate to become the main key for any table. It is a key that can uniquely identify each record in a table.
For the table Student we can make the
student_id column as the primary key.
Key that consists of two or more attributes that uniquely identify any record in a table is called Composite key. But the attributes which together form the Composite key are not a key independentely or individually.
In the above picture we have a Score table which stores the marks scored by a student in a particular subject.
In this table
subject_id together will form the primary key, hence it is a composite key.
The candidate key which are not selected as primary key are known as secondary keys or alternative keys.
Non-key attributes are the attributes or fields of a table, other than candidate key attributes/fields in a table.
Non-prime Attributes are attributes other than Primary Key attribute(s)..