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)  編輯  收藏
          主站蜘蛛池模板: 阿拉善盟| 宣化县| 荃湾区| 莱阳市| 平潭县| 康平县| 江油市| 昔阳县| 如皋市| 义马市| 武隆县| 楚雄市| 蒙山县| 班玛县| 绵竹市| 宾阳县| 邵阳县| 白山市| 左权县| 施秉县| 福清市| 宁强县| 涡阳县| 青海省| 泗阳县| 从江县| 瓦房店市| 十堰市| 台北市| 锦州市| 台山市| 清涧县| 博野县| 和林格尔县| 宁强县| 高陵县| 宣威市| 南宁市| 邵东县| 界首市| 定南县|