DBMS
Lecture 4: Keys in Relational Model
Keys
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.
Why we need 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 student_id, name, phone and age.
Super Key
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 student_id, (student_id,
name), phone etc.
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.
Next comes, (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 Key
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, student_id and phone both are candidate keys for table Student.
A candiate key can never be NULL or empty. And its value should be unique.
There can be more than one candidate keys for a table.
A candidate key can be a combination of more than one columns(attributes).
Primary Key
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.
Composite 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 student_id and subject_id together will form the primary key, hence it is
a composite key.
Secondary or Alternative key
The candidate key which are not selected as primary key are known as secondary keys
or alternative keys.
Non-key Attributes
Non-key attributes are the attributes or fields of a table, other than candidate
key attributes/fields in a table.
Non-prime Attributes
Non-prime Attributes are attributes other than Primary Key attribute(s)..
