SQL Super Key

Examples of SQL Super Key

Given below are the examples of SQL Super Key:

Example #1

To further illustrate Super Keys in relational databases, let us take the 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 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 was executed successfully, and all six records have been inserted into 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 table above.

• {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 attributes mentioned above can be super keys, provided they uniquely determine a record. For example, consider {name, phone} key. The attribute’s 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 are not super keys, {name, phone}, key qualifies as a candidate key.

One good methodology to find super keys, which we found helpful, is listing all the possible sets of attributes and eliminating the ones that do not determine all the attributes in the set.

Let’s 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 the 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 that do not determine all the attributes in the table/relation.

• {student_name, class} Super Key, Candidate Key
• {student_name, class, subject} Super Key

All other sets, for example, {student_name, subject}, do 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.

We hope that this EDUCBA information on “SQL Super Key” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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