Tuesday, May 24, 2016
Normalization is an analytical process that yields good database design and table structure with minimal data redundancy, and therefore, less anomaly, predicated on the concepts of attribute determination and dependency. The process works through stages of lower to higher normal forms, as well as Boyce-Codd Normal Form (BCNF). (Sometimes, denormalization or less normalization is best, for some examples, when one needs to better reflect an organization's real operations or when end-user demand requires faster querying because more resources may be required or more relational join operations must be performed with higher forms.) However, the first through third normal forms should almost always be implemented, along with the BCNF, when analyzing database design. A table in the 1NF has labeled dependencies, identifiable primary keys and attributes and lacks repeating groups, where one piece of data represents a set of data, leaving nulls instead of an appropriate data value. To be in the 2NF, a table must, in addition to the above 1NF requirements, be without partial dependencies, where one attribute is dependent on part of a composite primary key determinant. To be 3NF compliant, in addition to the above, a table must lack transitive dependences, where a nonprime attribute is determinant of a nonprime dependent. In both cases, the determinant is copied and made a primary key in its own table with the dependent, which is extracted from the table of origin. Lastly, for discussion here, is the BCNF, which may only be violated when the table contains more than one candidate key, when a non-key attribute determines a prime key attribute. It can be resolved by reassigning the primary keys. A table is in BCNF when all determinants are candidate keys.