More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
What's a natural key?in Sybase
Let me think back to my database class... okay, I can't think that far so I'll paraphrase... essentially, a natural key is a key for a given table that uniquely identifies the row. It's natural in the sense that it follows the business or real world need.
For example, assume that social security numbers are unique (I believe it is strived to be unique but it's not always the case), then if you had the following employee table:
employee:
ssn char(09)
f_name char(20)
l_name char(20)
title char(03)
Then a natural key would be ssn. If the combination of _name and l_name were unique at this company, then another natural key would be f_name, l_name. As a matter of fact, you can have many natural keys in a given table but in practice what one does is build a surrogate (or artificial) key.
The surrogate key is guaranteed to be unique because (wait, get back, here it goes again) it's typically a monotonically increasing value. Okay, my mathematician wife would be proud of me... really all it means is that the key is increasing linearly: i+1
The reason one uses a surrogate key is because your joins will be faster.
If we extended our employee table to have a surrogate key:
employee:
id identity
ssn char(09)
f_name char(20)
l_name char(20)
title char(03)
Then instead of doing the following:
where a.f_name = b.f_name
and a.l_name = a.l_name
we'd do this:
where a.id = b.id
We can build indexes on these keys and since Sybase's atomic storage unit is 2K, we can stash more values per 2K page with smaller indexes thus giving us better performance (imagine the key being 40 bytes versus being say 4 bytes... how many 40 byte values can you stash in a 2K page versus a 4 byte value? -- and how much wood could a wood chuck chuck, if a wood chuck could chuck wood?)
(Continued on next question...)
