Super Key, Candidate Key and Primary Key

[Tommy.net]

I read about these in book(writer: korth) but i am not getting the concept.I have read it so many times that i have already learnt the terminologies/definitions but the inner concept present in their definitions are not at all clear to me. Please someone explain these to me with very very simple examples.

Please help

[stlaural]

Lets take this table as an example :

Emp_ID     SSN     Location
1          123     England
2          234     United-States
3          345     Canada
4          456     Italy

The Prymary Key is Emp_ID, its a columns that can uniquely identify any row of the table, it is chosen by the DB designer and is usualy a minimal Super Key. We say minimal Super Key because its a Key that necessitate the smallest number of attributes/columns to uniquely identify rows.

Candidate Keys would be SSN and Location as they can both uniquely identify any row of the table using the smallest number of attribules/columns. they both, like Emp_ID, only require one column to do so.

Super Keys would be the three already mentioned : Emp_ID, SSN, Location and the following columns combinations {Emp_ID; SSN} , {Emp_ID; Location}, {SSN; Location} And {Emp_ID;SSN;Location} as they can all uniquely identify rows of the table but not necessarily using the smallest number of columns.

So basically, Super Keys are ALL the different way to uniquely identify your table rows. Candidate Keys are all the minimal Super Keys (Super keys using the smallest number of columns) and the Primary Key is usually one of the Candidate Keys as the best practice would always be to use a minimal Super Key.

Honestly, I've never had to think about that much once the primary key is identified.

hope this helps you a little.

[Tommy.net]

ok now i get the concept of primary and super keys.........please explain the candidate key once more

[techgnome]

by itself, EmpID uniquely identifies a row. By itself, SSN uniquely identifies a unique row. Together EmpID and SSN uniquely identify a row .... each one of those is an example of a CandidateKey... in short it is the combination of the minimal number of cols to uniquely identify rows...

-tg

[Tommy.net]

in #2,the table shown contains of 7 super keys:

Emp_ID, SSN, Location, {Emp_ID; SSN}, {Emp_ID; Location}, {SSN; Location} And {Emp_ID;SSN;Location}

i.e; if n is the number of columns,then there are 2^n - 1 number of super keys,Correct?

[techgnome]

No... it depends... in this case, yes, because all of the data is unique... but that's not always the case.

Consider if your data looks like this:

Emp_ID     SSN     Location
1          123     England
2          234     United-States
3          345     Canada
4          456     Italy
5          789     England
6          987     United-States
7          654     Canada
8          321     Italy
9          212     England
10         232     United-States
11         343     Canada
12         454     Italy

Location is no longer a valid key... it isn't unique...
Your list of super keys now looks like this:
Emp_ID, SSN, and {Emp_ID; SSN}

Greatly reduced the list now didn't it?

-tg

[Tommy.net]

Thanks.........Now the concept is very much clear

My next problem is the Normalization. I read it a lot of times. The 1NF says that the values in every attributes must be atomic,and if they are in a non-atomic form then break them into multiple tuples to make them atomic even if some redundant data comes into play while breaking them into.

But unfortunatly i cant get the concept of the 2NF and the 3NF. Please somebody help me to understand them clearly.

Thank You

[Nightwalker83]

Thanks.........Now the concept is very much clear

My next problem is the Normalization. I read it a lot of times. The 1NF says that the values in every attributes must be atomic,and if they are in a non-atomic form then break them into multiple tuples to make them atomic even if some redundant data comes into play while breaking them into.

But unfortunatly i cant get the concept of the 2NF and the 3NF. Please somebody help me to understand them clearly.

Thank You

Your not doing the same course as I am are you? I have to do both of those for my database design class. I too am struggling with normalization, I was given a powerpoint on the subject but it still confuses me. Although, the version we do is a simplified version of what the universities do we only go up to 3 normal form while at uni they have to learn up to 5 or 6.

[Tommy.net]

I am having an interview this weekend and its really very important for me to understand the 2NF and 3NF. Its a must question;hence i have already learnt their definitions by heart from the books but unfortunately the concept is not at all clear. Please somebody step forward and explain the 2NF and the 3NF.

[techgnome]

The point of normalization is to breakout easily repeated data into their own tables so that instead of the data being in the main table your have an FKey. Take states for instance... Rather than storing the text right in the main table, you may normalize it by having a states table and then in the main table have a StateID ...

And that's how it goes... you keep breaking things out in to more and more tables. If it's taken far enough you end up with a 5NF database... but it usually comes at a price - queries can take longer since it has to join more tables together to get to the data. So you have to balance the perfomance with the normalization. 3NF seems to be the tipping point, and so is what's typically strived for. I don't think I've ever used a 4NF or 5NF database outside of the normalization class I took many years ago. I've worked with 0NF and 2NF and 3NF....that's usually it.

Clearly I've simplified it, but each round of normalization runs a similar course.

-tg

[leinad31]

1.1 A Layman's Approach to Normalization

http://www.oracle-dox.net/Digital.Pr...1/DDU0013.html