📄 ch12.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=iso-8859-1">
<META NAME="Author" Content="Steph Mineart">
<TITLE>Ch 12 -- Understanding Relational Databases</TITLE>
</HEAD>
<BODY BACKGROUND="bg1.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/bg1.gif" BGCOLOR="#FFFFFF">
<P ALIGN="CENTER"><IMG SRC="sams.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/sams.gif" WIDTH="75" HEIGHT="24" ALIGN="BOTTOM"
BORDER="0"><BR>
<BR>
<A HREF="index-3.htm" tppabs="http://pbs.mcp.com/ebooks/0672310228/index.htm"><IMG SRC="toc.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/toc.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM"
ALT="TOC" BORDER="0" NAME="toc4"></A><A HREF="ch11.htm" tppabs="http://pbs.mcp.com/ebooks/0672310228/ch11.htm"><IMG SRC="back-1.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/back.gif"
WIDTH="40" HEIGHT="40" ALIGN="BOTTOM" ALT="BACK" BORDER="0" NAME="toc1"></A><A HREF="ch13.htm" tppabs="http://pbs.mcp.com/ebooks/0672310228/ch13.htm"><IMG
SRC="forward.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/forward.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM"
ALT="FORWARD" BORDER="0"
NAME="toc2"></A></P>
<H2 ALIGN="CENTER"><FONT COLOR="#000077">Charlie Calvert's C++ Builder Unleashed</FONT></H2>
<P>
<H2 ALIGN="CENTER"><A NAME="Heading1"></A>- <FONT COLOR="#000077">12 -</FONT></H2>
<H2 ALIGN="CENTER"><A
NAME="Heading2"></A><FONT COLOR="#000077">Understanding Relational
Databases</FONT></H2>
<P>In order to make sure everyone is following the discussion in the next few chapters,
I'm going to spend a few pages giving a quick-and-dirty introduction to
relational
databases. This discussion will also include a brief overview of the Database Desktop.</P>
<P>My purpose here is to give a relatively concise explanation of what it means to
use a relational, as opposed to a flat-file, database. Naturally,
this will be a
very broad overview of a complex and highly detailed subject. I am not attempting
an academic analysis of this field of study, but instead want to provide a practical
guide for everyday use.</P>
<P>In this chapter, I will be working
with Paradox tables and InterBase tables. Each
database has its own unique set of rules. There is no definitive example of a relational
database, any more than there is a definitive operating system or a definitive compiler.
All databases have things
in common, just as all compilers and all operating systems
have things in common. As much as possible, I try to stress these common traits throughout
this chapter. However, the specific implementation that I am referencing here is
for Paradox and
InterBase databases, and not everything I say will apply to Oracle
or dBASE tables.</P>
<P>In particular, this chapter is about the following:
<UL>
<LI>Indices
<P>
<LI>Primary keys
<P>
<LI>Foreign keys
<P>
<LI>Referential integrity
</UL>
<P>If you already understand these subjects, you probably won't have much use for
this chapter. If you need to review these subjects, or need to be introduced to them,
you should read this chapter.
<H3><A NAME="Heading3"></A><FONT
COLOR="#000077">Getting Started with Relational
Databases</FONT></H3>
<P>There are many different kinds of possible databases, but in today's world, there
are only two kinds that have any significant market share for the PC:
<DL>
<DD><B>1.</B>
Flat-file databases<BR>
<BR>
<B>2.</B> Relational databases
</DL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Emerging in recent years has a been
a new system called object-oriented databases. These databases
represent an interesting
form of technology, but I will omit discussion of them here because they have a small
user base at this time. <BR>
<BR>
The subject of object-oriented databases will come up again briefly in the chapters
on OOP called
"Inheritance," "Encapsulation," and "Polymorphism."
In those chapters you will see that OOP has some powerful features that it can bring
to the database world.
<HR>
</BLOCKQUOTE>
<P>Flat-file databases consist of a
single file. The classic example would be an
address book that contains a single table with six fields in it: Name, Address, City,
State, Zip, and Phone. If that is your entire database, what you have is a flat-file
database. In a flat-file database,
the words table and database are synonymous.</P>
<P>In general, relational databases consist of a series of tables related to each
other by one or more fields in each table. In Chapter 9, "Using <TT>TTable</TT>
and <TT>TDataSet</TT>," and
Chapter 10, "SQL and the <TT>TQuery</TT> Object,"
you saw how to use the <TT>TTable</TT> and <TT>TQuery</TT> objects to relate the
<TT>Customer</TT> and <TT>Orders</TT> tables together in a one-to-many relationship.
As you recall, the two
tables were joined on the <TT>CustNo</TT> field. The relationship
established between these two tables on the <TT>CustNo</TT> field is very much at
the heart of all relational databases.</P>
<P>The Address program shown in Chapter 13, "Flat-File,
Real-World Databases,"
is an example of a flat-file database. In Chapter 14, "Sessions and Relational
Real-World Databases," you will see a second program, called KDAdd, which is
a relational database.</P>
<P>Here are three key
differences between relational and flat-file databases:
<DL>
<DD><B>1.</B> A flat-file database, like the address book example outlined previously,
consists of one single table. That's the whole database. There is nothing more to
say about it.
Each table stands alone, isolated in its own little solipsistic world.<BR>
<BR>
<B>2. </B>Relational databases always contain multiple tables. For instance, the
<TT>Customer</TT> and <TT>Orders</TT> tables are both part of the <TT>BCDEMOS</TT>
database. As you will see, there are many other tables in this database, but for
now just concentrate on the <TT>Customer</TT> and <TT>Orders</TT> tables.<BR>
<BR>
<B>3. </B>Tables in relational databases are tied together in special fields. These
fields are called primary and foreign keys. They are usually indexes, and they usually
consist of a simple integer value. For instance, the <TT>Customer</TT> and <TT>Orders</TT>
tables are related to one another by the <TT>CustNo</TT> field. The
<TT>CustNo</TT>
field is a primary key in the <TT>Customer</TT> table, and a foreign key in the <TT>Orders</TT>
table. There are also indexes on both fields.
</DL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Indices
are about searching and
sorting. Keys, on the other hand, are about relating tables, and particularly about
something called referential integrity. <BR>
<BR>
In practice, these concepts get mixed together in some pretty ugly ways, but the
underlying theory relies on the kind of distinctions I am drawing in this note. For
instance, keys are usually indexed, and so people often talk about keys and indexes
as if they were the same thing. However, they are distinct concepts. <BR>
<BR>
One way to start to draw the distinction is to understand that keys are part of the
theory of relational databases, while indexes are part of the implementation of relational
databases. More on this as the chapter evolves.
<HR>
</BLOCKQUOTE>
<P>Clearly relational databases are radically different from flat-file databases.
Relational databases typically consist of multiple tables, at least some of which
are related together by one or more fields. Flat-file databases, on the other hand,
consist of only one single table, which is not related to any other table.
<H4><A NAME="Heading6"></A><FONT COLOR="#000077">Advantages of the Relational Database
Model</FONT></H4>
<P>What advantages do relational databases have over flat-file
databases? Well, there
are many strengths to this system; here are a few of the highlights:
<UL>
<LI>Relational databases enforce something called referential integrity. These constraints
help you enter data in a logical, systematic, and error-free
manner.
<P>
<LI>Relational databases save disk space. For instance, the <TT>Customer</TT> table
holds information about customers, including their address, phone, and contact information.
The <TT>Orders</TT> table holds information about orders,
including their date, cost,
and payment method. If you were forced to keep all this information in a single table,
each order would also have to list the customer information, which would mean that
some customers' addresses would be repeated dozens
of times in the database. In a
big database, that kind of duplication can easily burn up megabytes of disk space.
It's better to use a relational database because each customer's address would be
entered only once. You could also have two flat-file
databases, one holding the customer
information and the other holding the orders information. The problem with this second
scenario is that flat-file databases provide no means of relating the two tables
so that you can easily see which orders
belong to which customer.
<P>
<LI>Relational databases enable you to create one-to-many relationships. For instance,
you can have one name that is related to multiple addresses. There is no simple way
to capture that kind of relationship in a
flat-file database. In the KDAdd program,
you will see that it is possible to easily relate multiple addresses, phone numbers,
and so on with each name. The flexible structure of relational databases enables
programmers to adopt to these kinds of
real-world situations. For many entries in
a database, you will want to keep track of two addresses: one for a person's home,
and the other for his or her work. If someone you know has a summer home or an apartment
in the city, you need to add yet
more addresses to the listing. There is no convenient
way to do that in flat-file databases. Relational databases handle this kind of problem
with ease. In the last paragraph I emphasized that this kind of feature saves space;
in this paragraph I'm
emphasizing that it allows for a more logical, flexible, and
easy-to-use arrangement of your data.
</UL>
<P>To summarize, a relational database offers these possibilities:
<UL>
<LI>You can view the <TT>Customer</TT> table alone, or you can view
the <TT>Orders</TT>
table alone.
<P>
<LI>You can place the two tables in a one-to-many relationship, so that you can see
them side-by-side, but only see the orders relating to the currently highlighted
customer.
<P>
<LI>You can perform a join
between the two tables, so that you see them as one combined
table, much like the combined table you would be forced to use if you wanted to "join"
the <TT>Customer</TT> and <TT>Orders</TT> tables in a single flat-file database.
However,
you can decide which fields from both tables will be part of the join, leaving
out any you don't want to view. The joined table is also temporary, and does not
take up unnecessary disk space. In short, relational databases can use joins to provide
some of the benefits of flat-file databases, whereas flat-file databases cannot emulate
the virtues of relational databases.
</UL>
<P>As you can see, the three concepts that stand out when talking about relational
databases are referential
integrity, flexibility, and conservation of disk space.
In this case, the word "flexibility" covers a wide range of broad features
that can only be fully appreciated over time.</P>
<P>The one disadvantage that relational databases have when
compared to flat-file
databases is that they are more complicated to use. This is not just a minor sticking
point. Neophytes are often completely baffled by relational databases. They don't
have a clue as to what to do with them. Even if you have a
relative degree of expertise,
anyone can still become overwhelmed by a relational database that consists of three
dozen tables related to one another in some hundred different ways. (And yes, complexity
on that scale is not uncommon in corporate
America!) As you will see later in the
book, almost the only way to work with big systems of that type is through case tools.
<H4><A NAME="Heading7"></A><FONT COLOR="#000077">Simple Set Logic: The Basis of Relational
Databases</FONT></H4>
<P>The basis
for relational databases is a very simple form of mathematics. Each
table represents a simple set that can be related to other tables through very fundamental
mathematics. Because computers are so good at math, and particularly at integer math,
they
find relational databases easy to manipulate.</P>
<P>One common feature of relational databases is that most records will have a unique
number associated with them, and these numbers will be used as the keys that relate
one table to another. This
enables you to group tables together using simple mathematical
relationships. In particular, you can group them using simple integer-based set arithmetic.</P>
<P>For instance, in the <TT>Customers</TT> table from <TT>BCDEMOS</TT>, there is
a unique
<TT>CustNo</TT> field in each record. Furthermore, the <TT>Orders</TT> table
has a unique <TT>OrderNo</TT> field associated with it. The <TT>Orders</TT> table
also has a <TT>CustNo</TT> field that will relate it to the <TT>Customer</TT> table.
The
terminology of relational databases expresses these ideas by saying that the
<TT>Customer</TT> table has a primary key called <TT>CustNo</TT>, and the <TT>Orders</TT>
table has a primary key called <TT>OrderNo</TT> and a foreign key called
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -