Learn DBMS, Part - IV

KEY IN DBMS

The key is defined as the column or attribute of the database table. For example if a table has id,name and address as the column names then each one is known as the key for that table. We can also say that the table has 3 keys as id, name and address. The keys are also used to identify each record in the database table.The following are the various types of keys available in the DBMS system.


1. A simple key contains a single attribute.
2. A composite key is a key that contains more than one attribute.
3. A 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.

4. 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.
5. A superkey is any set of attributes that uniquely identifies a row. A superkey differs from a candidate key in that it does not require the non redundancy property.
6. 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 serve as the primary key.

7. A semantic or natural key is a key for which the possible values have an obvious meaning to the user or the data. For example, a semantic primary key for a COUNTRY entity might contain the value 'USA' for the occurrence describing the United States of America. The value 'USA' has meaning to the user.
8. A technical or surrogate or artificial key is a key for which the possible values have no obvious meaning to the user or the data. These are used instead of semantic keys for any of the following reasons:

o When the value in a semantic key is likely to be changed by the user, or can have duplicates. For example, on a PERSON table it is unwise to use PERSON_NAME as the key as it is possible to have more than one person with the same name, or the name may change such as through marriage.
o When none of the existing attributes can be used to guarantee uniqueness. In this case adding an attribute whose value is generated by the system, e.g. from a sequence of numbers, is the only way to provide a unique value. Typical examples would be ORDER_ID and INVOICE_ID. The value '12345' has no meaning to the user as it conveys nothing about the entity to which it relates.

NORMALIZATION


Data normalization is a set of rules and techniques concerned with:

• Identifying relationships among attributes.
• Combining attributes to form relations.
• Combining relations to form a database.

It follows a set of rules worked out by E FCodd in 1970. A normalized relational database provides several benefits:

• Elimination of redundant data storage.
• Close modeling of real world entities, processes, and their relationships.
• Structuring of data so that the model is flexible.

Because the principles of normalization were first written using the same terminology as was used to define the relational data model this led some people to think that normalization is difficult. Nothing could be more untrue. The principles of normalization are simple, common sense ideas that are easy to apply.


The only attribute values permitted by 1 NF are single atomic (or indivisible) values. If there is no value present for the column, then it should be denoted as null.

BCNF


A table is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and every determinant is a candidate key.

1. Anomalies can occur in relations in 3NF if there is a composite key in which part of that key has a determinant which is not itself a candidate key.
2. This can be expressed as R(A,B,C), C--->A where:

o The relation R contains attributes A, B and C.
o A and B form a candidate key.
o C is the determinant for A (A is functionally dependent on C).
o C is not part of any key.

3. Anomalies can also occur where a relation contains several candidate keys where:

o The keys contain more than one attribute (they are composite keys).
o An attribute is common to more than one key.