|Level |Rule | |First normal form (1NF) |An entity type is in 1NF when it contains no repeating groups of data. | |Second normal form (2NF) |An entity type is in 2NF when it is in 1NF and when all of its non-key attributes are fully dependent on its | | |primary key. | |Third normal form (3NF) |An entity type is in 3NF when it is in 2NF and when all of its attributes are directly dependent on the primary | | |key. |
Rules of Data Normalization
|1NF |Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary | | |key. | |2NF |Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table. | |3NF |Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a| | |separate table. | |BCNF |Boyce-Codd Normal Form - If there are non-trivial dependencies between candidate key attributes, separate them out | | |into distinct tables. | |4NF |Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not | | |directly related. | |5NF |Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify | | |separating logically related many-to-many relationships. | |ONF |Optimal Normal Form - a model limited to only simple (elemental) facts, as expressed in Object Role Model notation. | |DKNF |Domain-Key Normal Form - a model free from all modification anomalies. |
|Important Note! | |All normal forms are additive, in that if a model is in 3rd normal form, it is by definition also in 2nd and 1st. |
1. Eliminate Repeating Groups
In the original member list, each member name is followed by any databases that the member has experience with. Some might know many, and others might not know any. To answer the question, "Who knows DB2?" we need to perform an awkward scan of the list looking for references to DB2. This is inefficient and an extremely untidy way to store information. Moving the known databases into a seperate table helps a lot. Separating the repeating groups of databases from the member information results in first normal form. The MemberID in the database table matches the primary key in the member table, providing a foreign key for relating the two tables with a join operation. Now we can answer the question by looking in the database table for "DB2" and getting the list of members. [pic]
2. Eliminate Redundant Data
In the Database Table, the primary key is made up of the MemberID and the DatabaseID. This makes sense for other attributes like "Where Learned" and "Skill...