📄 lib0049.html
字号:
<html>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<head>
<title>Design Practices and Normal Form</title>
<link rel="STYLESHEET" type="text/css" href="images/xpolecat.css">
<link rel="STYLESHEET" type="text/css" href="images/ie.content.css">
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;"><a href="toc.html"><img src="images/teamlib.gif" width="62" height="15" border="0" align="absmiddle" alt="Team LiB"></a></div></td>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href="LiB0048.html"><img src="images/previous.gif" width="62" height="15" border="0" align="absmiddle" alt="Previous Section"></a>
<a href="LiB0050.html"><img src="images/next.gif" width="41" height="15" border="0" align="absmiddle" alt="Next Section"></a>
</div></td></tr></table>
<br>
<div class="chapter">
<a name="ch07"></a>
<div class="section">
<h2 class="first-section-title"><a name="227"></a><a name="ch07lev1sec2"></a>Design Practices and Normal Form</h2><p class="first-para">
<b class="bold">Normal form</b> is a set of rules that guide you in identifying entities and relationships. In fact, there are many different degrees of normal form, but in practice, third normal form is the one most frequently used. For that reason, I limit the discussion here to third normal form; if you are interested in other normal forms, I recommend <a href="LiB0053.html#250" target="_parent" class="chapterjump">Date (2003)</a>.</p>
<p class="para">To qualify for third normal form, entities must satisfy three conditions:</p>
<ol class="orderedlist">
<li class="first-listitem">
<p class="first-para">All repeating attribute groups should be removed and placed in a separate entity.</p>
</li>
<li class="listitem">
<p class="first-para">All nonkey attributes should be dependent only on the primary key.</p>
</li>
<li class="listitem">
<p class="first-para">All nonkey attributes should be dependent on every attribute in the primary key.</p>
</li>
</ol>
<p class="para">Suppose the <span class="fixed">CUSTOMER</span> entity has the attributes <span class="fixed">ADDRESS_LINE_1</span>, <span class="fixed">ADDRESS_LINE_2</span>, <span class="fixed">ADDRESS_LINE_3</span>, and <span class="fixed">ADDRESS_LINE_4</span>. Technically, such an entity isn't third normal form because it's a repeating group and violates condition 1. <a class="internaljump" href="#ch07fig4a">Figure 7.4a</a> illustrates the example of this bad practice, and <a class="internaljump" href="#ch07fig4b">Figure 7.4b</a> illustrates a possible correction.</p>
<div class="figure">
<a name="228"></a><a name="ch07fig4a"></a><span class="figuremediaobject"><img src="images/fig105_01.jpg" height="227" width="193" alt="" border="0"></span>
<br style="line-height: 1">
<span class="figure-title"><span class="figure-titlelabel">Figure 7.4a: </span>Violation of the First Condition of Third Normal Form</span>
</div>
<div class="figure">
<a name="229"></a><a name="ch07fig4b"></a><span class="figuremediaobject"><a href="images/fig106%5F01%5F0%2Ejpg" NAME="IMG_22" target="_parent"><img src="images/fig106_01.jpg" height="85" width="350" alt="Click To expand" border="0"></a></span>
<br style="line-height: 1">
<span class="figure-title"><span class="figure-titlelabel">Figure 7.4b: </span>Violation Corrected</span>
</div>
<a name="230"></a><a name="IDX-90"></a>
<p class="para">Suppose the <span class="fixed">ACCOUNT</span> entity contains the attribute <span class="fixed">ACCOUNT_BALANCE</span>. This isn't third normal form because it violates condition 2. <span class="fixed">ACCOUNT_BALANCE</span> is fully dependent on outstanding orders, the line items on them, and the payments that have been made—items in other entities. Another problem with <span class="fixed">ACCOUNT_BALANCE</span> is that it isn't atomic. <span class="fixed">ACCOUNT_BALANCE</span> is computed based on previous orders and customer payments.</p>
<p class="para">Suppose the <span class="fixed">CUSTOMER_ORDER</span> entity (which has a primary key that combines <span class="fixed">ACCOUNT_NUMBER</span> and <span class="fixed">ORDER_NUMBER</span>) has the attributes <span class="fixed">ACCOUNT_NAME</span> and <span class="fixed">ADDRESS_INFORMATION</span>. This technically isn't third normal form because these attributes relate to the account but not the specific order, which violates condition 3. <a class="internaljump" href="#ch07fig5a">Figure 7.5a</a> illustrates the order violating third normal form, and <a class="internaljump" href="#ch07fig5b">Figure 7.5b</a> illustrates a corrected version.</p>
<div class="figure">
<a name="231"></a><a name="ch07fig5a"></a><span class="figuremediaobject"><img src="images/fig106_02.jpg" height="276" width="323" alt="" border="0"></span>
<br style="line-height: 1">
<span class="figure-title"><span class="figure-titlelabel">Figure 7.5a: </span>Violation of the Third Condition of Third Normal Form</span>
</div>
<div class="figure">
<a name="232"></a><a name="ch07fig5b"></a><span class="figuremediaobject"><a href="images/fig107%5F01%5F0%2Ejpg" NAME="IMG_24" target="_parent"><img src="images/fig107_01.jpg" height="126" width="350" alt="Click To expand" border="0"></a></span>
<br style="line-height: 1">
<span class="figure-title"><span class="figure-titlelabel">Figure 7.5b: </span>Violation Corrected</span>
</div>
</div>
</div><br>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;"><a href="toc.html"><img src="images/teamlib.gif" width="62" height="15" border="0" align="absmiddle" alt="Team LiB"></a></div></td>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href="LiB0048.html"><img src="images/previous.gif" width="62" height="15" border="0" align="absmiddle" alt="Previous Section"></a>
<a href="LiB0050.html"><img src="images/next.gif" width="41" height="15" border="0" align="absmiddle" alt="Next Section"></a>
</div></td></tr></table>
</body></html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -