Normal Forms Based on Primary Keys

normalization of data is a process of analyzing the given relation schemas based on their fds and primary keys to achieve the desirable properties of
(1) minimizing redundancy and
(2) minimizing the insertion, deletion, and updating anomalies.

it can be considered as a “filtering” or “purification” process to make the design have successively better quality.
we assume that a set of functional dependencies is given for each relation, and that each relation has a designated primary key.
each relation is then evaluated for adequacy and decomposed further as needed to achieve higher normal forms, using the normalization theory.
we focus on the first three normal forms for relation schemas and the intuition behind them, and discuss how they were developed historically.
more general definitions of these normal forms, which take into account all candidate keys of a relation rather than just the primary key.


normalization of relations
the normalization process, as first proposed by codd (1972a), takes a relation schema through a series of tests to certify whether it satisfies a certain normal form.
the process, which proceeds in a top-down fashion by evaluating each relation against the criteria for normal forms and decomposing relations as necessary, can thus be considered as relational design by analysis.
initially, codd proposed three normal forms, which he called first, second, and third normal form.
a stronger definition of 3nf—called boyce-codd normal form (bcnf)—was proposed later by boyce and codd. all these normal forms are based on a single analytical tool: the functional dependencies among the attributes of a relation.

the normalization procedure provides database designers with:
a formal framework for analyzing relation schemas based on their keys and on the functional dependencies among their attributes.
a series of normal form tests that can be carried out on individual relation schemas so that the relational database can be normalized to any desired degree


definition.
the normal form of a relation refers to the highest normal form condition that it meets, and hence indicates the degree to which it has been normalized.
normal forms, when considered in isolation from other factors, do not guarantee a good database design. it is generally not sufficient to check separately that each relation schema in the database is in a given normal form.
rather, the process of normalization through decomposition must also confirm the existence of additional properties that the relational schemas, taken together, should possess. these would include two properties:
the nonadditive join or lossless join property, which guarantees that the spurious tuple generation problem does not occur with respect to the relation schemas created after decomposition.
the dependency preservation property, which ensures that each functional dependency is represented in some individual relation resulting after decomposition.

the nonadditive join property is extremely critical and must be achieved at any cost.

practical use of normal forms
most practical design projects acquire existing designs of databases from previous designs, designs in legacy models, or from existing files.
normalization is carried out in practice so that the resulting designs are of high quality and meet the desirable properties stated previously.
although several higher normal forms have been defined, database design as practiced in industry today pays particular attention to normalization only up to 3nf, bcnf, or at most 4nf.
another point worth noting is that the database designers need not normalize to the highest possible normal form. relations may be left in a lower normalization status, such as 2nf, for performance reason.
denormalization is the process of storing the join of higher normal form relations as a base relation, which is in a lower normal form.


definitions of keys and attributes


participating in keys


definition: a superkey of a relation schema r = {a1, a2, ... , an} is a set of attributes s ??r with the property that no two tuples t1 and t2 in any legal relation state r of r will have t1[s] = t2[s].
a key k is a superkey with the additional property that removal of any attribute from k will cause k not to be a superkey anymore. the difference between a key and a superkey is that a key has to be minimal that is, if we have a key
k = {a1, a2, ..., ak} of r, then k – {ai} is not a key of r for any
ai, 1 ? i ?k
{ssn} is a key for employee, whereas {ssn}, {ssn, ename},
{ssn, ename, bdate}, and any set of attributes that includes ssn are all superkeys.
if a relation schema has more than one key, each is called a candidate key.
one of the candidate keys is arbitrarily designated to be the primary key, and the others are called secondary keys.

in a practical relational database, each relation schema must have a primary key. if no candidate key is known for a relation, the entire relation can be treated as a default superkey. in the table employee, {ssn} is the only candidate key for employee, so it is also the primary key.


definition:
an attribute of relation