ch09.htm

Learn SQL in an easy way...

		<TD ALIGN="LEFT"><TT>BALANCE, NUMBER</TT></TD>
		<TD ALIGN="LEFT"><TT>CITY, CHAR(20)</TT></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"></TD>
		<TD ALIGN="LEFT"><TT>BANK, CHAR(30)</TT></TD>
		<TD ALIGN="LEFT"><TT>STATE, CHAR(2)</TT></TD>
	</TR>
</TABLE>
</P>
<P>Take a moment to examine these tables. Which fields do you think are the primary
keys? Which are the foreign keys?</P>
<P>The primary key in the <TT>BILLS</TT> table is the <TT>NAME</TT> field. This field
should not be duplicated because you have only one bill with this amount. (In reality,
you would probably have a check number or a date to make this record truly unique,
but assume for now that the <TT>NAME</TT> field works.) The <TT>ACCOUNT_ID</TT> field
in the <TT>BANK_ACCOUNTS</TT> table is the primary key for that table. The <TT>NAME</TT>
field is the primary key for the <TT>COMPANY</TT> table.</P>
<P>The foreign keys in this example are probably easy to spot. The <TT>ACCOUNT_ID</TT>
field in the <TT>BILLS</TT> table joins the <TT>BILLS</TT> table with the <TT>BANK_ACCOUNTS</TT>
table. The <TT>NAME</TT> field in the <TT>BILLS</TT> table joins the <TT>BILLS</TT>
table with the <TT>COMPANY</TT> table. If this were a full-fledged database design,
you would have many more tables and data breakdowns. For instance, the <TT>BANK</TT>
field in the <TT>BANK_ACCOUNTS</TT> table could point to a <TT>BANK</TT> table containing
bank information such as addresses and phone numbers. The <TT>COMPANY</TT> table
could be linked with another table (or database for that matter) containing information
about the company and its products.
<H4><FONT COLOR="#000077">Exercise 9.1</FONT></H4>
<P>Let's take a moment to examine an incorrect database design using the same information
contained in the <TT>BILLS</TT>, <TT>BANK_ACCOUNTS</TT>, and <TT>COMPANY </TT>tables.
A mistake many beginning users make is not breaking down their data into as many
logical groups as possible. For instance, one poorly designed <TT>BILLS</TT> table
might look like this: 
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Column Names</B></TD>
		<TD ALIGN="LEFT"><B>Comments</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><TT>NAME, CHAR(30)</TT></TD>
		<TD ALIGN="LEFT">Name of company that bill is owed to</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><TT>AMOUNT, NUMBER</TT></TD>
		<TD ALIGN="LEFT">Amount of bill in dollars</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><TT>ACCOUNT_ID, NUMBER</TT></TD>
		<TD ALIGN="LEFT">Bank account number of bill (linked to <TT>BANK_ACCOUNTS</TT> table)</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><TT>ADDRESS, CHAR(30)</TT></TD>
		<TD ALIGN="LEFT">Address of company that bill is owed to</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><TT>CITY, CHAR(15)</TT></TD>
		<TD ALIGN="LEFT">City of company that bill is owed to</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><TT>STATE, CHAR(2)</TT></TD>
		<TD ALIGN="LEFT">State of company that bill is owed to</TD>
	</TR>
</TABLE>
</P>
<P>The results may look correct, but take a moment to really look at the data here.
If over several months you wrote several bills to the company in the <TT>NAME</TT>
field, each time a new record was added for a bill, the company's <TT>ADDRESS</TT>,
<TT>CITY</TT>, and <TT>STATE</TT> information would be duplicated. Now multiply that
duplication over several hundred or thousand records and then multiply that figure
by 10, 20, or 30 tables. You can begin to see the importance of a properly normalized
database.</P>
<P>Before you actually fill these tables with data, you will need to know how to
create a table.
<H2><FONT COLOR="#000077">The CREATE TABLE Statement</FONT></H2>
<P>The process of creating a table is far more standardized than the <TT>CREATE DATABASE</TT>
statement. Here's the basic syntax for the <TT>CREATE TABLE</TT> statement:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">CREATE TABLE table_name
(     field1 datatype [ NOT NULL ],
      field2 datatype [ NOT NULL ],
      field3 datatype [ NOT NULL ]...)
</FONT></PRE>
<P>A simple example of a <TT>CREATE TABLE</TT> statement follows.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;  <B>CREATE TABLE BILLS (</B>
  2   <B>NAME CHAR(30),</B>
  3  <B> AMOUNT NUMBER,</B>
  4   <B>ACCOUNT_ID NUMBER);</B>

Table created.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This statement creates a table named <TT>BILLS</TT>. Within the <TT>BILLS</TT>
table are three fields: <TT>NAME</TT>, <TT>AMOUNT</TT>, and <TT>ACCOUNT_ID</TT>.
The <TT>NAME</TT> field has a data type of character and can store strings up to
30 characters long. The <TT>AMOUNT</TT> and <TT>ACCOUNT_ID</TT> fields can contain
number values only.</P>
<P>The following section examines components of the <TT>CREATE TABLE</TT> command.
<H3><FONT COLOR="#000077">The Table Name</FONT></H3>
<P>When creating a table using Personal Oracle7, several constraints apply when naming
the table. First, the table name can be no more than 30 characters long. Because
Oracle is case insensitive, you can use either uppercase or lowercase for the individual
characters. However, the first character of the name must be a letter between <TT>A</TT>
and <TT>Z</TT>. The remaining characters can be letters or the symbols <TT>_</TT>,
<TT>#</TT>, <TT>$</TT>, and <TT>@</TT>. Of course, the table name must be unique
within its schema. The name also cannot be one of the Oracle or SQL reserved words
(such as <TT>SELECT</TT>).


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You can have duplicate table names
	as long as the owner or schema is different. Table names in the same schema must
	be unique. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077">The Field Name</FONT></H3>
<P>The same constraints that apply to the table name also apply to the field name.
However, a field name can be duplicated within the database. The restriction is that
the field name must be unique within its table. For instance, assume that you have
two tables in your database: <TT>TABLE1</TT>and <TT>TABLE2</TT>. Both of these tables
could have fields called ID. You cannot, however, have two fields within <TT>TABLE1</TT>
called <TT>ID</TT>, even if they are of different data types.
<H3><FONT COLOR="#000077">The Field's Data Type</FONT></H3>
<P>If you have ever programmed in any language, you are familiar with the concept
of data types, or the type of data that is to be stored in a specific field. For
instance, a character data type constitutes a field that stores only character string
data. Table 9.2 shows the data types supported by Personal Oracle7.
<H4><FONT COLOR="#000077">Table 9.2. Data types supported by Personal Oracle7.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Data Type</B></TD>
		<TD ALIGN="LEFT"><B>Comments</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CHAR</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Alphanumeric data with a length between 1 and 255 characters. Spaces are padded to
			the right of the value to supplement the total allocated length of the column.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>DATE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Included as part of the date are century, year, month, day, hour, minute, and second.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>LONG</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Variable-length alphanumeric strings up to 2 gigabytes. (See the following note.)</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>LONG RAW</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Binary data up to 2 gigabytes. (See the following note.)</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>NUMBER</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Numeric <TT>0</TT>, positive or negative fixed or floating-point data.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>RAW</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Binary data up to 255 bytes.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>ROWID</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Hexadecimal string representing the unique address of a row in a table. (See the
			following note.)</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>VARCHAR2</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Alphanumeric data that is variable length; this field must be between 1 and 2,000
			characters long.</TD>
	</TR>
</TABLE>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The <TT>LONG</TT> data type is often
	called a <TT>MEMO</TT> data type in other database management systems. It is primarily
	used to store large amounts of text for retrieval at some later time.</P>
	<P>The <TT>LONG RAW</TT> data type is often called a binary large object (<TT>BLOB</TT>)
	in other database management systems. It is typically used to store graphics, sound,
	or video data. Although relational database management systems were not originally
	designed to serve this type of data, many multimedia systems today store their data
	in <TT>LONG RAW</TT>, or <TT>BLOB</TT>, fields.<BR>
	<BR>
	The <TT>ROWID</TT> field type is used to give each record within your table a unique,
	nonduplicating value. Many other database systems support this concept with a <TT>COUNTER</TT>
	field (Microsoft Access) or an <TT>IDENTITY</TT> field (SQL Server). 
<HR>
</P>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Check your implementation for supported
	data types as they may vary. 
<HR>


</BLOCKQUOTE>

<H4><FONT COLOR="#000077">The NULL Value</FONT></H4>
<P>SQL also enables you to identify what can be stored within a column. A <TT>NULL</TT>
value is almost an oxymoron, because having a field with a value of <TT>NULL</TT>
means that the field actually has no value stored in it.</P>
<P>When building a table, most database systems enable you to denote a column with
the <TT>NOT NULL</TT> keywords. <TT>NOT NULL</TT> means the column cannot contain
any <TT>NULL</TT> values for any records in the table. Conversely, <TT>NOT NULL</TT>
means that every record must have an actual value in this column. The following example
illustrates the use of the <TT>NOT NULL</TT> keywords.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B> CREATE TABLE BILLS (</B>
 2    <B>NAME CHAR(30) NOT NULL,</B>
 3    <B>AMOUNT NUMBER,</B>
 4    <B>ACCOUNT_ID NOT NULL);</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>In this table you want to save the name of the company you owe the money to, along
with the bill's amount. If the <TT>NAME</TT> field and/or the <TT>ACCOUNT_ID</TT>
were not stored, the record would be meaningless. You would end up with a record
with a bill, but you would have no idea whom you should pay.</P>
<P>The first statement in the next example inserts a valid record containing data
for a bill to be sent to Joe's Computer Service for $25.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO BILLS VALUES(&quot;Joe's Computer Service&quot;, 25, 1);</B>

1 row inserted.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO BILLS VALUES(&quot;&quot;, 25000, 1);</B>

1 row inserted.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice that the second record in the preceding example does not contain a <TT>NAME</TT>
value. (You might think that a missing payee is to your advantage because the bill
amount is $25,000, but we won't consider that.) If the table had been created with
a <TT>NOT NULL</TT> value for the <TT>NAME</TT> field, the second insert would have
raised an error.</P>
<P>A good rule of thumb is that the primary key field and all foreign key fields
should never contain <TT>NULL</TT> values.
<H4><FONT COLOR="#000077">Unique Fields</FONT></H4>
<P>One of your design goals should be to have one unique column within each table.
This column or field is a primary key field. Some database management systems allow
you to set a field as unique. Other database management systems, such as Oracle and
SQL Server, allow you to create a unique index on a field. (See Day 10.) This feature
keeps you from inserting duplicate key field values into the database.</P>
<P>You should notice several things when choosing a key field. As we mentioned, Oracle
provides a <TT>ROWID</TT> field that is incremented for each row that is added, which
makes this field by default always a unique key. <TT>ROWID</TT> fields make excellent
key fields for several reasons. First, it is much faster to join on an integer value
than on an 80-character string. Such joins result in smaller database sizes over
time if you store an integer value in every primary and foreign key as opposed to
a long <TT>CHAR</TT> value. Another advantage is that you can use <TT>ROWID</TT>
fields to see how a table is organized. Also, using <TT>CHAR</TT> values leaves you
open to a number of data entry problems. For instance, what would happen if one person
entered <TT>111 First Street</TT>, another entered <TT>111 1st Street</TT>, and yet