Keys




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.

Topics You May Be Interested In
Database-system Applications Relational Operations
A Sample University Eer Schema, Design Choices, And Formal Definitions Initial Conceptual Design Of The Company Database
Database Architecture Relationship Types, Relationship Sets, Roles, And Structural Constraints
Database Schema Relationship Between Information Security Versus Information Privacy
Schema Diagrams Access Control, User Accounts, And Database Audits


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.


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.

Topics You May Be Interested In
View Of Data The Natural Join
Purpose Of Database Systems Weak Entity Types
History Of Database Systems Sensitive Data And Types Of Disclosures
Introduction To Database Security Issues Control Measures
Introduction To Database Security Issues Aggregate Functions


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.


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.

Topics You May Be Interested In
Using High-level Conceptual Data Models For Database Design Introduction To Database Security Issues
Introduction To Transaction Processing Overview Of Object Database Concepts
Database Security Additional Basic Operations-string Operations
Database Design Refining The Er Design For The Company Database
Introduction To Database Security Issues Null Values


Frequently Asked Questions

+
Ans: The database schema is the logical design of the database. view more..
+
Ans: A relational database consists of a collection of tables, each of which is assigned a unique name. view more..
+
Ans: Information processing drives the growth of computers, as it has from the earliest days of commercial computers. In fact, automation of data processing tasks predates computers. view more..
+
Ans: A super-key is a set of one or more attributes that, taken collectively, allow us to identify uniquely a tuple in the relation. view more..
+
Ans: DBMS typically includes a database security and authorization subsystem that is responsible for ensuring the security of portions of a database against unauthorized access view more..
+
Ans: The typical method of enforcing discretionary access control in a database system is based on the granting and revoking of privileges. Let us consider privileges in the context of a relational DBMS. view more..
+
Ans: This chapter discusses techniques for securing databases against a variety of threats. It also presents schemes of providing access privileges to authorized users. view more..
+
Ans: This chapter discusses techniques for securing databases against a variety of threats. It also presents schemes of providing access privileges to authorized users. view more..
+
Ans: Object databases is the power they give the designer to specify both the structure of complex objects and the operations that can be applied to these objects view more..
+
Ans: XML (Extensible Markup Language)—has emerged as the standard for structuring and exchanging data over the Web. XML can be used to provide information about the structure and meaning of the data in the Web pages rather than just specifying how the Web pages are formatted for display on the screen view more..
+
Ans: A database schema, along with primary key and foreign key dependencies, can be depicted by schema diagrams. view more..
+
Ans: A query language is a language in which a user requests information from the database. view more..
+
Ans: All procedural relational query languages provide a set of operations that can be applied to either a single relation or a pair of relations. view more..
+
Ans: An object database is a database management system in which information is represented in the form of objects as used in object-oriented programming. Object databases are different from relational databases which are table-oriented. Object-relational databases are a hybrid of both approaches. view more..
+
Ans: IBM developed the original version of SQL, originally called Sequel, as part of the System R project in the early 1970s. view more..
+
Ans: The set of relations in a database must be specified to the system by means of a data-definition language (DDL). view more..
+
Ans: The basic structure of an SQL query consists of three clauses: select, from, and where. view more..
+
Ans: This chapter discusses techniques for securing databases against a variety of threats. It also presents schemes of providing access privileges to authorized users. view more..




Rating - 3/5
497 views

Advertisements