ch01.htm

21精通SQL

	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Becky Boudreaux</TD>
		<TD ALIGN="LEFT">Walk</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Bill Jackson</TD>
		<TD ALIGN="LEFT">Study</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Bill Jackson</TD>
		<TD ALIGN="LEFT">Interview for jobs</TD>
	</TR>
</TABLE>
</P>
<P>It would be improper to duplicate the employee's <TT>AGE</TT> and <TT>OCCUPATION</TT>
fields for each record. Over time, unnecessary duplication of data would waste a
great deal of hard disk space and increase access time for the RDBMS. However, if
<TT>NAME</TT> and <TT>DUTIES</TT> were stored in a separate table named <TT>RESPONSIBILITIES</TT>,
the user could join the <TT>RESPONSIBILITIES</TT> and <TT>EMPLOYEE</TT> tables on
the <TT>NAME</TT> field. Instructing the RDBMS to retrieve all fields from the <TT>RESPONSIBILITIES</TT>
and <TT>EMPLOYEE</TT> tables where the <TT>NAME</TT> field equals <TT>Becky Boudreaux</TT>
would return Table 1.3.
<H4><FONT COLOR="#000077"><B>Table 1.3. Return values from retrieval where </B>NAME<B>
equals </B>Becky Boudreaux<B>.</B></FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Name</B></TD>
		<TD ALIGN="LEFT"><B>Age</B></TD>
		<TD ALIGN="LEFT"><B>Occupation</B></TD>
		<TD ALIGN="LEFT"><B>Duties</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Becky Boudreaux</TD>
		<TD ALIGN="LEFT">25</TD>
		<TD ALIGN="LEFT">Model</TD>
		<TD ALIGN="LEFT">Smile</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Becky Boudreaux</TD>
		<TD ALIGN="LEFT">25</TD>
		<TD ALIGN="LEFT">Model</TD>
		<TD ALIGN="LEFT">Walk</TD>
	</TR>
</TABLE>
</P>
<P>More detailed examples of joins begin on Day 6, &quot;Joining Tables.&quot;
<H2><FONT COLOR="#000077"><B>Designing the Database Structure</B></FONT></H2>
<P>The most important decision for a database designer, after the hardware platform
and the RDBMS have been chosen, is the structure of the tables. Decisions made at
this stage of the design can affect performance and programming later during the
development process. The process of separating data into distinct, unique sets is
called <I>normalization</I>.
<H3><FONT COLOR="#000077"><B>Today's Database Landscape</B></FONT></H3>
<P>Computing technology has made a permanent change in the ways businesses work around
the world. Information that was at one time stored in warehouses full of filing cabinets
can now be accessed instantaneously at the click of a mouse button. Orders placed
by customers in foreign countries can now be instantly processed on the floor of
a manufacturing facility. Although 20 years ago much of this information had been
transported onto corporate mainframe databases, offices still operated in a batch-processing
environment. If a query needed to be performed, someone notified the management information
systems (MIS) department; the requested data was delivered as soon as possible (though
often not soon enough).</P>
<P>In addition to the development of the relational database model, two technologies
led to the rapid growth of what are now called client/server database systems. The
first important technology was the personal computer. Inexpensive, easy-to-use applications
such as Lotus 1-2-3 and Word Perfect enabled employees (and home computer users)
to create documents and manage data quickly and accurately. Users became accustomed
to continually upgrading systems because the rate of change was so rapid, even as
the price of the more advanced systems continued to fall.</P>
<P>The second important technology was the local area network (LAN) and its integration
into offices across the world. Although users were accustomed to terminal connections
to a corporate mainframe, now word processing files could be stored locally within
an office and accessed from any computer attached to the network. After the Apple
Macintosh introduced a friendly graphical user interface, computers were not only
inexpensive and powerful but also easy to use. In addition, they could be accessed
from remote sites, and large amounts of data could be off-loaded to departmental
data servers.</P>
<P>During this time of rapid change and advancement, a new type of system appeared.
Called <I>client/server development</I> because processing is split between client
computers and a database server, this new breed of application was a radical change
from mainframe-based application programming. Among the many advantages of this type
of architecture are

<UL>
	<LI>Reduced maintenance costs<BR>
	<BR>
	
	<LI>Reduced network load (processing occurs on database server or client computer)<BR>
	<BR>
	
	<LI>Multiple operating systems that can interoperate as long as they share a common
	network protocol<BR>
	<BR>
	
	<LI>Improved data integrity owing to centralized data location
</UL>

<P>In <I>Implementing Client/Server Computing,</I> Bernard H. Boar defines client/server
computing as follows:

<DL>
	<DD><I>Client/server computing</I> is a processing model in which a single application
	is partitioned between multiple processors (front-end and back-end) and the processors
	cooperate (transparent to the end user) to complete the processing as a single unified
	task. Implementing Client/Server Computing A client/server bond product ties the
	processors together to provide a single system image (illusion). Shareable resources
	are positioned as requestor clients that access authorized services. The architecture
	is endlessly recursive; in turn, servers can become clients and request services
	of other servers on the network, and so on and so on.
</DL>

<P>This type of application development requires an entirely new set of programming
skills. User interface programming is now written for graphical user interfaces,
whether it be MS Windows, IBM OS/2, Apple Macintosh, or the UNIX X-Window system.
Using SQL and a network connection, the application can interface to a database residing
on a remote server. The increased power of personal computer hardware enables critical
database information to be stored on a relatively inexpensive standalone server.
In addition, this server can be replaced later with little or no change to the client
applications.
<H2><FONT COLOR="#000077"><B>A Cross-Product Language</B></FONT></H2>
<P>You can apply the basic concepts introduced in this book in many environments--for
example, Microsoft Access running on a single-user Windows application or SQL Server
running with 100 user connections. One of SQL's greatest benefits is that it is truly
a cross-platform language and a cross-product language. Because it is also what programmers
refer to as a high-level or fourth-generation language (4GL), a large amount of work
can be donehigher-level language 4GL (fourth-generation) language fourth-generation
(4GL) language in fewer lines of code.
<H3><FONT COLOR="#000077"><B>Early Implementations</B></FONT></H3>
<P>Oracle Corporation released the first commercial RDBMS that used SQL. Although
the original versions were developed for VAX/VMS systems, Oracle was one of the first
vendors to release a DOS version of its RDBMS. (Oracle is now available on more than
70 platforms.) In the mid-1980s Sybase released its RDBMS, SQL Server. With client
libraries for database access, support for stored procedures (discussed on Day 14,
&quot;Dynamic Uses of SQL&quot;), and interoperability with various networks, SQL
Server became a successful product, particularly in client/server environments. One
of the strongest points for both of theseSQL Server powerful database systems is
their scalability across platforms. C language code (combined with SQL) written for
Oracle on a PC is virtually identical to its counterpart written for an Oracle database
running on a VAX system.
<H3><FONT COLOR="#000077"><B>SQL and Client/Server Application Development</B></FONT></H3>
<P>The common thread that runs throughout client/server application development is
the use client/server computing of SQL and relational databases. Also, using this
database technology in a single-user business application positions the application
for future growth.
<H2><FONT COLOR="#000077"><B>An Overview of SQL</B></FONT></H2>
<P>SQL is the de facto standard language used to manipulate and retrieve data from
these relational databases. SQL enables a programmer or database administrator to
do the following:

<UL>
	<LI>Modify a database's structure<BR>
	<BR>
	
	<LI>Change system security settings<BR>
	<BR>
	
	<LI>Add user permissions on databases or tables<BR>
	<BR>
	
	<LI>Query a database for information<BR>
	<BR>
	
	<LI>Update the contents of a database
</UL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE: </B></FONT>The term SQL can be confusing. The <I>S,</I>
	for Structured, and the <I>L,</I> for Language, are straightforward enough, but the
	<I>Q</I> is a little misleading. <I>Q</I>, of course, stands for &quot;Query,&quot;
	which--if taken literally--would restrict you to asking the database questions. But
	SQL does much more than ask questions. With SQL you can also create tables, add data,
	delete data, splice data together, trigger actions based on changes to the database,
	and store your queries within your program or database.</P>
	<P>Unfortunately, there is no good substitute for <I>Query</I>. Obviously, Structured
	Add Modify Delete Join Store Trigger and Query Language (SAMDJSTQL) is a bit cumbersome.
	In the interest of harmony, we will stay with SQL. However, you now know that its
	function is bigger than its name. 
<HR>


</BLOCKQUOTE>

<P>The most commonly used statement in SQL is the <TT>SELECT</TT> statement (see
Day 2, &quot;Introduction to the Query: The <TT>SELECT</TT> Statement&quot;), which
retrieves data from the database and returns the data to the user. The <TT>EMPLOYEE</TT>
table example illustrates a typical example of a <TT>SELECT</TT> statement situation.
In addition to the <TT>SELECT</TT> statement, SQL provides statements for creating
new databases, tables, fields, and indexes, as well as statements for inserting and
deleting records. ANSI SQL also recommends a core group of data manipulation functions.
As you will find out, many database systems also have tools for ensuring data integrity
and enforcing security (see Day 11, &quot;Controlling Transactions&quot;) that enable
programmers to stop the execution of a group of commands if a certain condition occurs.
<H2><FONT COLOR="#000077"><B>Popular SQL Implementations</B></FONT></H2>
<P>This section introduces some of the more popular implementations of SQL, each
of which has its own strengths and weaknesses. Where some implementations of SQL
have been developed for PC use and easy user interactivity, others have been developed
to accommodate very large databases (VLDB). This sections introduces selected key
features of some implementations.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>In addition to serving as an SQL
	reference, this book also contains many practical software development examples.
	SQL is useful only when it solves your real-world problems, which occur inside your
	code. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077"><B>Microsoft Access</B></FONT></H3>
<P>We use Microsoft Access, a PC-based DBMS, to illustrate some of the examples in
this text. Access is very easy to use. You can use GUI tools or manually enter your
SQL statements.
<H3><FONT COLOR="#000077"><B>Personal Oracle7</B></FONT></H3>
<P>We use Personal Oracle7, which represents the larger corporate database world,
to demonstrate command-line SQL and database management techniques. (These techniques
are important because the days of the standalone machine are drawing to an end, as
are the days when knowing one database or one operating system was enough.) In <I>command-line
R&#202;l,</I> simple stand+[cedilla]one SQL statements are entered into Oracle's