SQL Super Key

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.

This is a guide to SQL Super Key. Here we discuss the introduction to SQL Super Key along with the examples respectively. You may also have a look at the following articles to learn more –

1. SQL UNION ALL
2. SQL Window Functions
3. SQL DECODE()
4. SQL SELECT DISTINCT