Distinguish among these keys Foreign, Candidate, Alternate, and Primary key. 1. A foreign key is an attribute (or set of attributes) that appears (usually) as a non key attribute in one relation and as a primary key attribute in another relation. I say usually because it is possible for a foreign key to also be the whole or part of a primary key: o A many-to-many relationship can only be implemented by introducing an intersection or link table which then becomes the child in two one-to-many relationships. The intersection table therefore has a foreign key for each of its parents, and its primary key is a composite of both foreign keys. o A one-to-one relationship requires that the child table has no more than one occurrence for each parent, which can only be enforced by letting the foreign key also, serves as the primary key. o 2. Candidate key: Is an attribute (or set of attributes) that uniquely identifies a row. A candidate key must possess the following properties: o Unique identification - For every row the value of the key must uniquely identify that row. o Non redundancy - No attribute in the key can be discarded without destroying the property of unique identification. Three absolute demands on the candidate key, if it is to be regarded as a possible primary key. There are three fundamental demands on the candidate keys that we should never deviate from, if it is to become the subject for a primary key: 1. The candidate key must be unique within its domain (the entity it represents, and beyond, if you also intend to access external entities, with is clearly illustrated in this article). 2. The candidate key cannot hold NULLs (NULL is not zero. Zero is a number. NULL is 'unknown value'). 3. The candidate key should never change. It must hold the same value for a given occurrence of an entity for the lifetime of that entity. 3. A primary key is the candidate key which is selected as the principal unique identifier. Every relation must contain a primary key. The primary key is usually the key selected to identify a row when the database is physically implemented. For example, a part number is selected instead of a part description. 4. An alternate key is similar to a primary key. It accepts null values; where as the primary key does not. The null values can be submitted to the attribute in a tuple. There can be a key apart from primary key in a table that can also be a key. This key may or may not be a unique key. For example, in an employee table, empno is a primary key, empname is a alternate key that may not be unique but still helps in identifying a row of the table. Candidate Key is a Key which identifies each row of a table uniquely. Generally a Candidate Key becomes a Primary Key of the table. If table has more than one Candidate Keys then one is called Primary key and rest are called Alternative Keys. For this relation table shown below. Serial num Name Department sex location 1 Joseph Computer male South Town Adam science 2 Tobbie Computer male Down Town Williams science 3 Paul Smith Computer female North side. science Generally a candidate key becomes the primary key,so in this case of having a given relation(table) will have more than one candidate key, when this happen one of such key is chosen as a primary key while the remaining are alternate keys. Primary key for this tuple relation is 1(serial num) while the others 2,3 are called alternate keys which identify each row record of the table. 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. One of these candidate keys is selected as the table primary key. Any attribute that is uniquely identify a row in a table is Candidate key for the table. We select one of the candidate key as Primary key.
Pages to are hidden for
"DATABASE SYSTEM"Please download to view full document