Introduction to SQL Super Key
The following article provides an outline for SQL Super Key. Super Key in relational databases is an attribute or a set of attributes that uniquely identifies a row or a record in the database table. By attribute, we mean a field or column in the table. There can be more than one super key in the database and it can also be NULL. Super key can be considered similar to super set in relational algebra.
Example:
Consider a relation that has A, B, C, D, E, F as attributes.
R = {A, B, C, D, E, F}
Now, consider the following functional dependencies.
Functional Dependencies | Super Key |
A → B, C | No |
AB → C, D, E, F | Yes |
CD → A, B, F | No |
D → E, F | No |
CF → ABDE | Yes |
Out if the given functional dependencies, AB, CF are super keys because they can determine all the attributes in the relation.
A relation with n attributes can have a maximum of 2^n super keys. Newbies to relational databases often get confused between candidate key and super key. Candidate key is a subset of super key. It is the minimal key that determines all other attributes in the relation.
For illustration, consider a relation R = {A, B, C, D} and functional dependencies as A→C and B → D. Here, A and B alone cannot determine all the attributes in the relation but when we club A and B together, we are able to determine all the attributes. Hence, AB is a super key. It is minimal also because A and B alone are not super keys. Ergo, AB qualifies for candidate key too. Now you must be wondering what’s the difference between a candidate key and a primary key then. Both the keys uniquely identify a record but a primary key cannot have NULL fields but a candidate key can.
Examples of SQL Super Key
Given below are the examples of SQL Super Key:
Example #1
In order to further illustrate Super Keys in relational databases, let us take help of a dummy table called “student_record”.
Here is the CREATE TABLE statement for the “student_record” table.
Code:
CREATE TABLE student_record (
id INT NOT NULL,
name CHARACTER VARYING (50) NOT NULL,
gender CHARACTER VARYING (50) NOT NULL,
phone CHARACTER VARYING (50) NOT NULL,
state CHARACTER VARYING (50) NOT NULL,
class INT
);
The table has been successfully created. The next task is to insert a few records in it to work with. You may use the given INSERT statement for this purpose.
Code:
INSERT INTO student_record(
id, name, gender, phone, state, class)
VALUES (1,'Rohit','M','989898989','MH',1),
(2,'Indira','F','942505001','MP',2),
(3,'Rohit','M','888888989','KA',1),
(4,'Priya','F','989898989','MH',1),
(5,'Steve','M','789007890','KA',2),
(6,'Priya','F','544554451','MP',2);
The command executed successfully and all the 6 records have been inserted in the table. Let’s have a look at the table using a SELECT statement.
Code:
SELECT *
FROM student_record;
Output:
Now let’s try to see and find super keys based on the above mentioned table.
- {id}
- {id, name}
- {id,gender}
- {id,phone}
- {id,state}
- {id,class}
- {name,phone}
- {name,state}
- {gender,phone}
- {id,name,gender}
- {id,name,phone}
- {id, name,state}
- {id,name,class}
- {id,gender,phone}
- {id,gender,state}
- {id,gender,class}
- {id,state,class}
- {name,phone,gender}
- {name,gender,state}
- .
- .
- .
- {id,name,gender,phone.state,class}
All of the above mentioned and more attribute sets can be super keys provided that they uniquely determine a record. For example consider, {name, phone} key. The attributes name and phone number individually cannot determine all other attributes in the relation. But when they are clubbed together, they uniquely identify a record in the student_record table. Since, name and phone number in themselves are not super keys, {name, phone} key qualifies to be a candidate key as well in this case.
One good methodology to find super keys, which we personally found useful is listing all the possible sets of attributes and then eliminating the ones which do not determine all the attributes in the set.
Lets see one more example to illustrate it more.
Example #2
In this example we will illustrate super keys with the help of the “student_list” table.
Code:
CREATE TABLE student_list(
student_name VARCHAR(50),
class VARCHAR(50),
Subject VARCHAR(50)
);
INSERT INTO student_list(
student_name, class, subject)
VALUES ('Rohan','V','Maths'),
('Rohan','VI','Maths'),
('Steve','VI','Maths'),
('Peter','II','Science');
The data in student_list table looks something as follows :
Code:
SELECT * FROM student_list;
Output:
Step 1: List all possible sets of attributes.
- {}
- {student_name}
- {class}
- {subject}
- {student_name, class}
- {student_name,subject}
- {class, subject}
- {student_name, class, subject}
Step 2: Eliminate sets which do not determine all the attributes in the table/relation.
- {student_name, class} Super Key, Candidate Key
- {student_name, class, subject} Super Key
Here, all other sets, for example, {student_name,subject} does not uniquely determine a row. Hence, they are not super keys.
Conclusion
In this article we saw about super keys. They are a set of one or more attributes that uniquely determines a record in the relational database table.
Recommended Articles
We hope that this EDUCBA information on “SQL Super Key” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
6 Online Courses | 7 Hands-on Projects | 37+ Hours | Verifiable Certificate of Completion
4.5
View Course
Related Courses