Archive

Posts Tagged ‘Super Key’

DB Basics – As Primary Key can identify a row uniquely, what are the scenarios you need a Super Key?

September 17, 2015 Leave a comment

 
In one of my [previous post] I discussed about various types of Keys in DBMS, like Candidate, Primary, Composite & Super Keys.

I got few questions like:
1. If there is already a Primary Key to identify a row uniquely, then in which scenario a Super Key can use used?
2. How to create a Super Key like a Primary Key in SQL Server or Oracle?
 

–> First of all what is a Primary Key (PK): It is the Candidate key attribute/column that is most suited to maintain uniqueness in a table at the tuple/row level.

–> And a Super Key is a super-set of Candidate key. If you add any other attribute/column to a Candidate Key then it become a Super Key. Or a minimal Super Key is called a Candidate Key.
 

–> Now consider the same example that I discussed in my [previous post]:

CREATE TABLE Employee (
    EmployeeID, 
    EmployeeName, 
    DOB, -- Date of birth
    DOJ, -- Date of joining
    SSN, -- Social Security Number
    DeptID, -- Department ID
    MgrID -- Manager ID
)

Now in this table there are 2 Candidate Keys i.e. EmployeeID & SSN.
– EmployeeID: should be unique with auto-increment column.
– SSN: should also be unique for every employee.

So, here you cannot make SSN as a Primary Key, because its a PII and secure data.
Hence, creating EmployeeID as Primary Key makes sense, this way SSN will be left as an Alternate Key.
 

–> Now, coming to the Super Key concept: As per the above definition, a minimal Super Key will be called as a Candidate or Primary Key. Thus every Primary Key and Alternate Key is also a Super Key. And if you combine any other column with the EmployeeID (PK) column, the combination will also be called as a Super Key, like:
– EmployeeID
– EmployeeID + DOB
– EmployeeID + DOJ
– EmployeeID + DeptID + MgrID
– EmployeeID + DOB + DOJ + DeptID + MgrID
– EmployeeID + [other column combinations]

For all the above and other possible combinations the row will still be identified uniquely, thus all these combinations will act like a Super Key.
 

And in SQL Server or Oracle there is no syntax as such to create a Super Key, this is just a concept in DBMS.

Hope this helps !!!


Categories: DB Concepts Tags: ,

DB Basics – What are Candidate, Primary, Composite & Super Keys and Difference between them?

September 15, 2014 4 comments

Let’s take an example of an Employee table:

CREATE TABLE Employee (
    EmployeeID, 
    EmployeeName, 
    DOB, -- Date of birth
    DOJ, -- Date of joining
    SSN, -- Social Security Number
    DeptID, -- Department ID
    MgrID -- Manager ID
)

1. Candidate Key: is the attribute/column or a set of attributes/columns in a relation/table that qualifies for uniqueness of each tuple/row. A relation/table can have one or more than one Candidate Keys. A Candidate key is also known as a minimal Super key.

Here in Employee table columns EmployeeID & SSN individually can maintain uniqueness in a table, thus are eligible for Candidate keys. The columns EmployeeName + DOB combined can also make up a Candidate Key, but there is a narrow chance that 2 Employees with same name can be born in same day.
 

2. Primary Key: is the Candidate key attribute/column that is most suited to maintain uniqueness in a table at the tuple/row level. More about PK.

Here in Employee table you can choose either EmployeeID or SSN column for a PK, EmployeeID is preferable choice because SSN is a secure (PII) value.
 

3. Alternate Key: are the other Candidate key attribute/columns that you didn’t choose as Primary key column.

Like if you choose EmployeeID as a PK then SSN would be the Alternate key.
 

4. Super Key: is a superset of Candidate key. If you add any other attribute/column to a Candidate Key then it become a Super Key.

Like EmployeeID + EmployeeName is a Super Key.
 

5. Composite Key: If a table do have a single column that qualifies for a Candidate key, then you have to select 2 or more columns to make a row unique.

Like if there is no EmployeeID or SSN columns in Employee table, then you can make EmployeeName + DOB as a Composite Primary Key. But still there can be a narrow chance of duplicate rows.
 

–> Check the video:

DBMS Keys Types