Keys



Rating - 3/5
521 views

We must have a way to specify how tuples within a given relation are distinguished.
This is expressed in terms of their attributes. That is, the values of the
attribute values of a tuple must be such that they can uniquely identify the tuple.
In other words, no two tuples in a relation are allowed to have exactly the same
value for all attributes.


A superkey is a set of one or more attributes that, taken collectively, allow us
to identify uniquely a tuple in the relation. For example, the ID attribute of the
relation instructor is sufficient to distinguish one instructor tuple from another.
Thus, ID is a superkey. The name attribute of instructor, on the other hand, is not
a superkey, because several instructors might have the same name.


Formally, let R denote the set of attributes in the schema of relation r. If we
say that a subset K of R is a superkey for r, we are restricting consideration to
instances of relations r in which no two distinct tuples have the same values on
all attributes in K. That is, if t1 and t2 are in r and t1 not = t2 . Then t1.K = t2.K.


A superkey may contain extraneous attributes. For example, the combination
of ID and name is a superkey for the relation instructor. If K is a superkey, then so
is any superset of K. We are often interested in superkeys for which no proper
subset is a superkey. Such minimal superkeys are called candidate keys.


It is possible that several distinct sets of attributes could serve as a candidate
key. Suppose that a combination of name and dept_name is sufficient to distinguish
among members of the instructor relation. Then, both {ID} and {name, dept_name}
are candidate keys. Although the attributes ID and name together can distinguish
instructor tuples, their combination, {ID, name}, does not form a candidate key,
since the attribute ID alone is a candidate key.

These Topics Are Also In Your Syllabus
1 Introduction to Transaction Processing link
2 Database Security link
You May Find Something Very Interesting Here. link
3 Database Languages link
4 Database Design link
5 Types Of Systems link


We shall use the term primary key to denote a candidate key that is chosen
by the database designer as the principal means of identifying tuples within a
relation. A key (whether primary, candidate, or super) is a property of the entire
relation, rather than of the individual tuples. Any two individual tuples in the
relation are prohibited from having the same value on the key attributes at the
same time. The designation of a key represents a constraint in the real-world
enterprise being modeled.


Primary keys must be chosen with care. As we noted, the name of a person is
obviously not sufficient, because there may be many people with the same name.
In the United States, the social-security number attribute of a person would be
a candidate key. Since non-U.S. residents usually do not have social-security
numbers, international enterprises must generate their own unique identifiers.
An alternative is to use some unique combination of other attributes as a key.


The primary key should be chosen such that its attribute values are never,
or very rarely, changed. For instance, the address field of a person should not be
part of the primary key, since it is likely to change. Social-security numbers, on
the other hand, are guaranteed never to change. Unique identifiers generated by
enterprises generally do not change, except if two enterprises merge; in such a case
the same identifier may have been issued by both enterprises, and a reallocation
of identifiers may be required to make sure they are unique.


It is customary to list the primary key attributes of a relation schema before
the other attributes; for example, the dept_name attribute of department is listed
first, since it is the primary key. Primary key attributes are also underlined.


A relation, say r1, may include among its attributes the primary key of another
relation, say r2. This attribute is called a foreign key from r1, referencing r2.
The relation r1 is also called the referencing relation of the foreign key dependency,
and r2 is called the referenced relation of the foreign key. For example, the
attribute dept_name in instructor is a foreign key from instructor, referencing department,
since dept_name is the primary key of department. In any database instance,
given any tuple, say ta, from the instructor relation, there must be some tuple, say
tb, in the department relation such that the value of the dept_name attribute of ta is
the same as the value of the primary key, dept_name, of tb.

These Topics Are Also In Your Syllabus
1 Additional Basic Operations: The Rename Operation link
2 Additional Basic Operations: String Operations link
You May Find Something Very Interesting Here. link
3 A Sample Database Application link
4 Entity Types, Entity Sets, Attributes, and Keys-1 link
5 Types Of Systems link


Now consider the section and teaches relations. It would be reasonable to
require that if a section exists for a course, it must be taught by at least one
instructor; however, it could possibly be taught by more than one instructor.
To enforce this constraint, we would require that if a particular (course_id, sec_id,
semester, year) combination appears in section, then the same combination must
appear in teaches. However, this set of values does not form a primary key for
teaches, since more than one instructor may teach one such section. As a result,
we cannot declare a foreign key constraint from section to teaches (although we
can define a foreign key constraint in the other direction, from teaches to section).


The constraint from section to teaches is an example of a referential integrity
constraint; a referential integrity constraint requires that the values appearing in
specified attributes of any tuple in the referencing relation also appear in specified
attributes of at least one tuple in the referenced relation.


Rating - 3/5
514 views

Advertisements
Rating - 3/5