Sun River
          Topics about Java SE, Servlet/JSP, JDBC, MultiThread, UML, Design Pattern, CSS, JavaScript, Maven, JBoss, Tomcat, ...
          posts - 78,comments - 0,trackbacks - 0
           

          2NF and 3NF

          The 2NF and 3NF are very similar--the 2NF deals with composite primary keys and the 3NF deals with single primary keys. In general, if you do an ER diagram, convert many-to-many relationships to entities, and then convert all entities to tables, then your tables will already be in 3NF form.

          Second normal form is violated when a non-key field is a fact about a subset of a key. It is only relevant when the key is composite, i.e., consists of several fields. Consider the following inventory record:

          ---------------------------------------------------
          | PART | WAREHOUSE | QUANTITY | WAREHOUSE-ADDRESS |
          ====================-------------------------------

          The key here consists of the PART and WAREHOUSE fields together, but WAREHOUSE-ADDRESS is a fact about the WAREHOUSE alone. The basic problems with this design are:

          • The warehouse address is repeated in every record that refers to a part stored in that warehouse.
          • If the address of the warehouse changes, every record referring to a part stored in that warehouse must be updated. Because of the redundancy, the data might become inconsistent, with different records showing different addresses for the same warehouse.
          • If at some point in time there are no parts stored in the warehouse, there may be no record in which to keep the warehouse's address.

          To satisfy second normal form, the record shown above should be decomposed into (replaced by) the two records:

          ------------------------------- --------------------------------- 
          | PART | WAREHOUSE | QUANTITY | | WAREHOUSE | WAREHOUSE-ADDRESS |
          ====================----------- =============--------------------
           

          The 3NF differs from the 2NF in that all non-key attributes in 3NF are required to be directly dependent on the primary key of the relation. The 3NF therefore insists that all facts in the relation are about the key (or the thing that the key identifies), the whole key and nothing but the key.

          Third normal form is violated when a non-key field is a fact about another non-key field, as in

          ------------------------------------
          | EMPLOYEE | DEPARTMENT | LOCATION |
          ============------------------------

          The EMPLOYEE field is the key. If each department is located in one place, then the LOCATION field is a fact about the DEPARTMENT -- in addition to being a fact about the EMPLOYEE. The problems with this design are the same as those caused by violations of second normal form:

          • The department's location is repeated in the record of every employee assigned to that department.
          • If the location of the department changes, every such record must be updated.
          • Because of the redundancy, the data might become inconsistent, with different records showing different locations for the same department.
          • If a department has no employees, there may be no record in which to keep the department's location.

          To satisfy third normal form, the record shown above should be decomposed into the two records:

          ------------------------- -------------------------
          | EMPLOYEE | DEPARTMENT | | DEPARTMENT | LOCATION |
          ============-------------  ==============-----------
           
          To summarize, a record is in second and third normal forms if every field is either part of the key or provides a (single-valued) fact about exactly the whole key and nothing else.

           

          posted on 2007-07-20 00:51 Sun River 閱讀(298) 評(píng)論(0)  編輯  收藏
          主站蜘蛛池模板: 搜索| 桐梓县| 绥化市| 阿尔山市| 万全县| 雷州市| 南雄市| 革吉县| 松江区| 石家庄市| 三门峡市| 高唐县| 蒙城县| 沁源县| 博乐市| 梁河县| 华蓥市| 邵阳市| 铁力市| 台东市| 双桥区| 邹城市| 准格尔旗| 华亭县| 桓台县| 甘谷县| 太仓市| 万盛区| 盈江县| 额尔古纳市| 阿拉善右旗| 清镇市| 西宁市| 绥化市| 瑞金市| 长葛市| 沂源县| 松溪县| 辽源市| 桦川县| 伊通|