Keys: As there are number of keys can be defined, but some commonly and mainly used keys are explained as below:
1. Primary Key
A key is a single attribute or combination of two or more,
attributes of an entity that is used to identify one or more instances
of the set. The attribute Roll # uniquely identifies an instance of the
entity set STUDENT. It tells about student Amrita having address 101,
Kashmir Avenue and phone no. 112746 and have paid fees 1500 on basis of
Roll No. 15. The 15 is unique value and it gives unique identification
of students So here Roll No is unique attribute and such a unique entity
identifies called Primary Key. Primary key cannot be duplicate.
From the definition of candidate key, it should be clear that each
relation must have at least one candidate key even if it is the
combination of all the attributes in the relation since all tuples in a
relation are distinct. Some relations may have more t one candidate
keys.
As discussed earlier, the primary key of a relation is an
arbitrarily but permanently selected candidate key. The primary key is
important since it is the sole identifier for the tuples in a relation.
Any tuple in a database may be identified by specifying relation name,
primary key and its value. Also for a tuple to exist in a relation, it
must be identifiable and therefore it must have a primary key. The
relational data model therefore imposes the following two integrity
constraints:
(a) No component of a primary key value can be null;
(b) Attempts to change the value of a primary key must be carefully controlled.
The first constraint is necessary because if we want to store
information about some entity, then we must be able to identify it,
otherwise difficulties are likely to arise. For example, if a relation
CLASS (STUNO, LECTURER, CNO)
has (STUNO, LECTURER) as the primary key then allowing tuples like
3123 NULL CP302
NULL SMITH CP302
is going to lead to ambiguity since the two tuples above may or may
not be identical and the integrity of the database may be compromised.
Unfortunately most commercial database systems do not support the
concept of primary key and it would be possible to have a database state
when integrity of the database is violated.
The second constraint above deals with changing of primary key
values. Since the primary key is the tuple identifier, changing it needs
very careful controls. Codd has suggested three possible approaches:
Method 1
Only a select group of users be authorised to change primary key values.
Method 2
Updates on primary key values be banned. If it was necessary to
change a primary key, the tuple would first be deleted and then a new
tuple with new primary key value but same other values would be
inserted. Of course, this does require that the old values of attributes
be remembered and be reinserted in the database.
Method 3
A different command for updating primary keys be made available.
Making a distinction in altering the primary key and another attribute
of a relation would remind users that care needs to be taken in updating
primary keys.
2. Secondary Key
The ke1 which is not giving the unique identification and have
duplicate infonna6o is called secondary key, e g in a STUDENT entity if
Roll Number is the primary key, then Name of the student, address of the
student, Phone number of the student and the fees paid by the student
all are secondary keys. A secondary key is an attribute or combination
of attributes that not be primary key and have duplicate data. In
otherworlds secondary key is used after the identification of the
primary key. Also we can identify the data from the combination of the
secondary keys.
3. Super Key
If we add additional attributes to a primary key, the resulting
combination would still uniquely identify an instance of the entity set
Such keys are called super keys A primary key is therefore a minimum
super key For example, if DOB (date of birth field or attribute) is the
primary key, then by adding some additional information about the day of
the month key in the DOB field, this field or attribute becomes more
powerful and useful Such type of key is called super key Super key are
less used in a small database file. Now these days it has less
importance, but due to its feature, this key gives the complete
description of the database.
4. Candidate Key
There may be two or more attributes or combination of attributes
that uniquely identify an instance of an entity set These attributes or
combination of attributes are called candidate keys. Candidate key also
gives unique identification. Candidate key comes with primary key. A
candidate is a combination of two or more attributes e.g. if Roll No.
and student name are two different attributes then we combine these two
attribute and form a single attribute Roll No. & Name, then this
combination is the candidate key and it is unique and gives unique
identification about a particular roll no. and about particular name.
5. Alternative Key
A candidate key which is not the primary key is called alternative
key, e.g. if Roll No. and Name combination is the candidate key, then if
Roll No, is the primary key, other key in the candidate key is Name.
Name attribute work as the alternative key.
6 Foreign Key
Suppose there are some relations as: SP (S#, P#, QTY), relation S
(S#, S Name, status, city) and relation P (P#, PName, Color, Weight,
City). We know entity SP is defined as the relationship of the relation S
and the relation P. These two relations has sand P# as the Primary Keys
in relation S and P respectively, but in the relation SP we can take
either # as the primary key or P# as the primary key. Suppose if we take
P# as the primary key, then other primary key S# which is actually the
primary key, but do not work as primary key in the relation SF is called
the Foreign Key. If S# is the primary key then P# is the Foreign Key.
Similarly in the relation ASSIGNMENT, attribute Emp #, Prod #, Job # are
given and if S# and P# are the primary keys, then the Job # key is the
Foreign Keys.
Comments
Post a Comment