3

i am confused in to keys in Database Managment System.as there are many partially questions...but this one is a good combined question.that's why i am posting here . Confusion Among SUPER KEY,KEY,PRIMARY KEY,ALTERNATE KEY,CANDIDATE KEY,MINIMAL KEY this is what i understand about it.

SUPER KEY : attribute or set of attribute which can identify a tupple uniquely .

Candidate key : set of all SUper keys..

Key : A single key from set of candidate keys which is minimal ( minimum attributes are there )

Primary key : A minimal key from the set of candidate keys..

Alternate key : set of all candidate keys except primary key

Foreign key. : i know it very well it's tough to mention all the things for it..

but i have confusion among superkey , key , candidate key , Primary key , Alternate key .. (add any key if remaining) all the definitions are okey?? for ER model definitions of these keys are different than definitions in Relational Model?

Can you please give your answer with example.?a single table having these all keys.!! Thanks...!!

Karan Patel
  • 89
  • 2
  • 2
  • 5

1 Answers1

3

A superkey is any set of attributes for which the values are guaranteed to be unique for all the permitted sets of tuples in a relation at all times.

A candidate key (= key or minimal superkey) is the smallest set of attributes in each superkey which is unique. Removing any one attribute from a candidate key would therefore make it non-unique (no longer a superkey).

A primary key is just any candidate key. There is no difference between a primary key and any other candidate key.

An alternate key is any candidate key that isn't designated as a primary key.

Foreign keys are not keys at all (the name is a bit unfortunate). A foreign key is just a set of attributes of a relation that are subject to a referential integrity constraint where the same set of attributes is also a candidate key in another relation.

An example? Suppose you have a Marriages relation, with husband and wife attributes (for simplicity I'm only considering monogamous, male-female marriage)

Marriages {Husband, Wife}

The Marriages relation has three superkeys: {Husband, Wife}, {Husband} and {Wife} (a person can only have one spouse at a time so both attributes are required to be unique).

{Husband} and {Wife} are candidate keys.

Now consider which one of the two, {Husband} or {Wife}, might be Primary Key and which Alternate? The answer is that it doesn't matter: the form, function and intended meaning of this relation is exactly the same whatever you choose to call a "primary" key. Primary key and Alternate Key are equivalent concepts and the relational model makes no distinction between them.

Entity Relationship modelling does tend to distinguish between primary keys and other keys and often gives one key per entity certain precedence over others, sometimes to the exclusion or detriment of other equally important keys. It's an aspect of ER modelling that can and does lead to errors in database design and data management generally.

nvogel
  • 24,981
  • 1
  • 44
  • 82