📄 lib0051.html
字号:
<html>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<head>
<title>Creating Database Schema Definitions</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="LiB0050.html"><img src="images/previous.gif" width="62" height="15" border="0" align="absmiddle" alt="Previous Section"></a>
<a href="LiB0052.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="238"></a><a name="ch07lev1sec4"></a>Creating Database Schema Definitions</h2><p class="first-para">Typically, database administrators use the data model to create relational database schemas for the rest of the team to use. And most database administrators use modeling tools to do the dirty work. Unfortunately, few open source tools for creating schemas are available. Although the process is a bit more involved than what I illustrate in this section, with the help of a qualified database administrator, you can create schemas using the following central algorithm:</p>
<ol class="orderedlist">
<li class="first-listitem">
<p class="first-para">
<i class="emphasis">Directly translate each entity into a table.</i> All attributes of an entity become columns in the table. Explicitly define the primary key in each table.</p>
</li>
<li class="listitem">
<p class="first-para">
<i class="emphasis">Assign a foreign key in the child entity of each one-to-many relationship. </i>Remember, a foreign key is the primary key of another entity that exists so that you can match data in one entity to another. For example, <span class="fixed">CUSTOMER_ID</span> will appear as a foreign key in the <span class="fixed">ACCOUNT</span> table so that you have a way to associate an account with a specific customer using a SQL join.</p>
<a name="239"></a><a name="IDX-94"></a>
</li>
<li class="listitem">
<p class="first-para">
<i class="emphasis">Rewrite each many-to-many relationship by adding an associative table</i> <i class="emphasis">and two one-to-many relationships.</i> An associative table has a primary key that is made up of two foreign keys. For example, look back at <a href="LiB0048.html#224" target="_parent" class="chapterjump">figure 7.2</a> to see the many-to-many relationship between <span class="fixed">CUSTOMER</span> and <span class="fixed">PRODUCT</span>. This will get implemented by creating a new table (called <span class="fixed">CUSTOMER_LINE_ITEM</span>, for example) that relates customers to products.</p>
</li>
</ol>
<p class="para">As an illustration, <a class="internaljump" href="#ch07list01">listing 7.1</a> translates the ProjectTrak data model from <a href="LiB0050.html#237" target="_parent" class="chapterjump">figure 7.6</a> into Oracle DDL.</p>
<div class="example">
<span class="example-title"><span class="example-titlelabel">Listing 7.1: </span>ProjectTrak DDL for Figure 7.6</span><a name="240"></a><a name="ch07list01"></a>
<div class="formalbody">
<table class="BlueLine" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td bgcolor="000080" class="bluecell"><font size="2" face="Arial" color="010100"><b><img src="_.gif" width="1" height="2" alt="Start example" border="0"></b></font></td>
</tr>
</table>
<pre class="literallayout">
create table Project (Project_ID number primary key);
create table Project_Task
( Task_ID number primary key,
Task_Name varchar(50) not null,
Estimate_In_Hrs number,
Completion_Pct number,
Project_ID number not null,
Resource_ID number
)
create table Resource (Resource_ID number primary key);
ALTER TABLE Project_Task
ADD CONSTRAINT Project_FK
FOREIGN KEY (Project_ID)
REFERENCES Project(Project_ID);
ALTER TABLE Project_Task
ADD CONSTRAINT Resource_FK
FOREIGN KEY (Resource_ID)
REFERENCES Resource(Resource_ID);
</pre>
<table class="BlueLine" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td bgcolor="000080" class="bluecell"><font size="2" face="Arial" color="010100"><b><img src="_.gif" width="1" height="2" alt="End example" border="0"></b></font></td>
</tr>
</table>
<table class="BlankSpace" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td height="16"></td>
</tr>
</table>
</div>
</div>
<div class="section">
<h3 class="sect3-title">
<a name="241"></a><a name="ch07lev2sec1"></a>Common Mistakes</h3>
<p class="first-para">
<b class="bold">Denormalizing the database out of habit.</b> Denormalizing a database means replicating information to avoid look-ups and enhance performance. Consequently, denormalization can introduce maintenance problems if the two copies get out of synch.</p>
<p class="para">In the early days of relational databases, denormalization for performance was a must. However, the technology has advanced to the point where forced <a name="242"></a><a name="IDX-95"></a>denormalizations are rare. Today, denormalizations are done more out of (bad) habit than for performance reasons.</p>
<p class="last-para">
<b class="bold">Dropping database integrity constraints for programmatic convenience. </b>Some developers like to shut off the foreig key relationships between tables. Not using database integrity constraints initially saves the programmer time because it permits invalid inserts, updates, and deletes. But I've found you lose more time than you save because you end up having to fight bugs created by flawed inserts, updates, and deletes. The sooner you catch a bug, the cheaper and easier it is to fix.</p>
</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="LiB0050.html"><img src="images/previous.gif" width="62" height="15" border="0" align="absmiddle" alt="Previous Section"></a>
<a href="LiB0052.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 + -