Database Concepts: Keys

When i was preparing the database concepts for the interview i was very much confused about the keys that is exists in the database.So i like to share the things about Candidate Key,Primary Key,Unique Key,Alternate Key,Super key,Foreign Key, Composite Key,Surrogate Key ...

I will be using the following table to explain about database keys -

EMPLOYEE [ EMPLOYEE_ID, EMPLOYEE_SSN_ID, EMPLOYEE_DEPT_ID, EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME, EMPLOYEE_ADDRESS ]

Candidate Key

A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys. In general, one of these candidate keys is selected as the table primary key.

Example - From the above table EMPLOYEE_ID, EMPLOYEE_SSN_ID, and EMPLOYEE_DEPT_ID can be considered as candidate keys

Primary Key

A primary key is a single column or combination of columns that uniquely defines a record. None of the columns that are part of the primary key can contain a null value. A table can have only one primary key.

Example - EMPLOYEE_ID or EMPLOYEE_SSN_ID can be considered as primary keys

Unique Key

A unique key or primary key [is a candidate key] to uniquely identify each row in a table. It be comprised of either a single column or multiple columns.

The major difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is enforced. Thus, the values in unique key columns may or may not be NULL.

Differences between Primary Key and Unique Key

Primary Keys -
1. It will not accept null values.
2. There will be only one primary key in a table.
3. Clustered index is created in Primary key.
4. Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist.

Unique Keys -
1. Null values are accepted.
2. More than one unique key will be there in a table.
3. Non-Clustered index is created in unique key.
4. Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values.

Alternate Key

A candidate key that is not the primary key is called an alternate key.

Example - If EMPLOYEE_ID is considered as primary keys then EMPLOYEE_SSN_ID is an alternate key.

Superkey

A superkey is a combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Candidate keys are a special subset of superkeys that do not have any extraneous information in them.

A primary key is therefore a minimum superkey.

Examples - Any combination of the following can be considered as a Super key

- EMPLOYEE_ID - Minimal Super Key

- EMPLOYEE_ID and EMPLOYEE_SSN_ID

- EMPLOYEE_ID, EMPLOYEE_SSN_ID and EMPLOYEE_DEPT_ID

- EMPLOYEE_ID, EMPLOYEE_SSN_ID, EMPLOYEE_DEPT_ID, EMPLOYEE_FIRST_NAME

- EMPLOYEE_SSN_ID, EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME

Foreign Key

The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.

Composite Key

A primary key that made up of more than one attribute is known as a composite key.

Example - [ EMPLOYEE_ID and EMPLOYEE_SSN_ID ] can together be treated as (one of) composite keys. Another combination can be [ EMPLOYEE_ID, EMPLOYEE_SSN_ID and EMPLOYEE_DEPT_ID ]

Surrogate Key

Surrogate keys are keys that have no business meaning and are solely used to identify a record in the table.

Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).

References :

http://en.wikipedia.org/wiki/Candidate_key

http://databases.about.com/od/specificproducts/a/keys.htm

Create a Career Tree from your LinkedIn Profile

This diagram illustrates the career path of the American President – the branches represent his education and the work experience. I think the size of the circles represent the relative time spent in a particular role.

If you would like to have something similar for your own career, simple head over to Newsweek.com and import your LinkedIn profile.

The more details you have in your LinkedIn profile, the ‘fuller’ your tree will be. You may also add details of your career to the tree manually using the “career tree form” available on the same page without changing your main LinkedIn profile.

About.Me- New Service dedicated to create your own profile

Few days back, a team of nine people designed a site for themselves. They found it tough pulling all social/online accounts and sites to a page. So they created about.me, a site to show off one in all online profile.

Later-on they decided to make it public. At present, public registrations are locked and its by invite only.



Credit


Features :
1.Create a personal dynamic splash page with a very easy to use administration.
2.Drag and Drop Administrator interface.
3.Custom Background, like Twitter.
4.Add multiple social networks, along with websites and RSS.
5.Get detailed statistics of your visitors like their about.me profile (if they have), their country, time of visit and whatever they do with your profile.
6.It also offers to measure your global reach counting up your Facebook friends and twitter followers.
7.I have got an invite there and thought to share with you an alpha sneak preview. It’s not yet publicly released , yet you can reserve a username down there.

I hope that you have enjoyed the post. If you are already registered with about.me, then please let me know your views or suggestions through the comments below if not registered and if you want to get in on the action, you can. About.me have kindly offered beta invites to the first 100 readers to reserve the username of their choice. Of course, we can’t guarantee the availability of the username of your choice, and if it is already taken, the folks at About.me will let you know. All you need to do is head over to About.me and reserve a URL (username). When you’re done, come back and type in the reserved username into this form.