 |
Ben Forta
Senior Product Evangelist for ColdFusion
Author of The Macromedia ColdFusion 5 Web Application Construction
Kit
|
|
|
|
|
 |
|
Chapter 3: Building the Databases
Chapter 6: Introduction to SQL
Ben Forta's book, ColdFusion
5 Web Application Construction Kit, is written for anyone
who wants to create cutting-edge web-based applications, whether
you are a Webmaster or web page designer, or even an experienced
database administrator who wants to take advantage of the web
to publish or collect data. This book teaches you how to create
real-world applications that solve real-world problems. Along
the way, you acquire all the skills you need to design, implement,
test, and roll out world-class applications. Below are excerpts
from Chapter 3: Building the Databases
and Chapter 6: Introduction to SQL (Structured
Query Language), respectively. You can also download
the entire chapter for all the details.
Chapter 3: Building the Databases
Chapter 3 begins with an overview of database fundamentals.
To guide you through the discussion as you read the book, Ben
sets up a scenario in which you're charged with creating and maintaining
a list of all movies produced by your movie-making employer, Orange
Whip Studios. Ben explains why the best way to keep track of such
information is with a database. After a defining what databases
are and describing how they are used, he discusses the importance
of data types before launching into creating the Orange Whip Studios
database in Microsoft Access. It turns out that the database you're
creating is called a "relational database," in which you store
data in separate tables and then link them (in various "relationships"),
rather than putting all the data into one table.
Understanding the Various Types of Database Applications
"All the information described to this point applies equally
to all databases. The basic fundamentals of databases, tables,
keys, and indexes are supported by all database applications.
At some point, however, databases start to differ. They can differ
in price, performance, features, security, scalability, and more.
"One decision you should make very early in the process is whether
to use a shared-file–based database, such as Microsoft Access,
or a client/server database application, such as Microsoft SQL
Server and Oracle. Each has advantages and disadvantages, and
the key to determining which will work best for you is understanding
the difference between shared-file–based applications and client/server
systems.
Shared-File–Based Databases
"Databases such as Microsoft Access and Visual FoxPro and Borland
dBASE are shared-file–based databases. They store their data
in data files that are shared by multiple users. These data files
usually are stored on network drives so they are easily accessible
to all users who need them, as shown in Figure 3.11.
|
 |
"When you access data from a Microsoft Access table, for example,
that data file is opened on your computer. Any data you read is
read by Microsoft Access running on your computer. Likewise, any
data changes are made locally by the copy of Access running on your
computer.
"Considering this point is important when youÍre evaluating shared-file–based
database applications. The fact that every running copy of Microsoft
Access has the data files open locally has serious implications:
|
|
•
|
Shared data files are susceptible
to data corruption„Each user accessing the tables has the
data files open locally. If the user fails to terminate the
application correctly or the computer hangs, those files donÍt
close gracefully. Abruptly closing data files like this can
corrupt the file or cause garbage data to be written to it. |
|
•
|
Shared data files create a great
deal of unnecessary network traffic„If you perform a search
for specific expenses, the search takes place on your own
computer. The database application running on your computer
has to determine which rows it wants and which it does not.
The application has to know of all the records„including those
it will discard for this particular query„for this determination
to occur. Those discarded records have to travel to your computer
over a network connection. Because the data is discarded anyway,
unnecessary network traffic is created. |
|
•
|
Shared data files are insecure„Because
users have to open the actual data files with which they intend
to work, they must have full access to those files. This also
means that users can delete, either intentionally or accidentally,
the entire data file with all its tables." |
|
Client/Server–Based Databases
"Databases such as Microsoft SQL Server and Oracle are client/server–based
databases. Client/server applications are split into two distinct
parts. The server portion is a piece of software that is responsible
for all data access and manipulation. This software runs on a computer
called the database server. In the case of Microsoft SQL Server,
it is a computer running Windows NT or 2000 and the SQL Server software.
"Only the server software interacts with the data files. All
requests for data, data additions and deletions, and data updates
are funneled through the server software. These requests or changes
come from computers running client software. The client is the
piece of software with which the user interacts. If you request
a list of movies sorted by rating, for example, the client software
submits that request over the network to the server software.
The server software processes the request; filters, discards,
and sorts data as necessary; and sends the results back to your
client software. This process is illustrated in Figure 3.12.
|
| Figure 3.12: Client/server databases enable clients to perform
database operations that are processed by the server software. |
 |
"All this action occurs transparently to you, the user. The fact
that data is stored elsewhere or that a database server is even
performing all this processing for you is hidden. You never need
to access the data files directly. In fact, most networks are set
up so that users have no access to the data, or even the drives
on which it is stored.
"Client/server–based database servers overcome the limitations
of shared-file–based database applications in the following ways:
|
|
•
|
Client/server–based data files
are less susceptible to data corruption caused by incorrect
application termination„If a user fails to exit a program
gracefully, or if her computer locks up, the data files do
not get damaged. That is because the files are never actually
open on that userÍs computer. |
|
•
|
Client/server–based database
servers use less network bandwidth„Because all data filtering
occurs on the server side, all unnecessary data is discarded
before the results are sent back to the client software. Only
the necessary data is transmitted over the network. |
|
•
|
End users in a client/server
database environment need never have access to the actual
physical data files„This lack of access helps ensure that
the files are not deleted or tampered with." |
|
This discussion, of course, begs the question of how to decide
which database product to use, shared-file–based databases or client/server–based
databases.
Which Database Product to Use
"Now that you have learned the various types of database systems
you can use, how do you determine which is right for your application?
"Unfortunately, this question has no simple answer. You really
need to review your application needs, the investment you are
willing to make in the system, and which systems you already have
in place.
"To get started, try to answer as many of the following questions
as possible:
Do you have an existing database system in place? If yes, is
it current technology that is still supported by the vendor? Do
you need to link to data in this system, or are you embarking
on a new project that can stand on its own feet?
|
|
•
|
Do you have any database expertise
or experience? If yes, with which database systems are you
familiar? |
|
•
|
Do you have database programmers
or administrators in-house? If yes, with which systems are
they familiar? |
|
•
|
How many users do you anticipate
will use the system concurrently? |
|
•
|
How many records do you anticipate
your tables will contain? |
|
•
|
How important is database uptime?
What is the cost associated with your database being down
for any amount of time? |
|
•
|
Do you have existing hardware
that can be used for a database server? |
|
"These questions are not easy to answer, but the effort is well
worth your time. The more planning you do up front, the better chance
you have of making the right decision. Getting the job done right
the first time will save you time, money, and aggravation later.
"Of course, there is no way you can anticipate all future needs.
At some point you might, in fact, need to switch databases. If
you ever do have to migrate from one database to another, contact
the database vendor to determine which migration tools are available.
As long as you select known and established solutions from reputable
vendors, you should be safe."
The rest of Chapter 3 walks you through the tables used in
the Orange Whip Studios application, the database you use throughout
the book.
Chapter 6: Introduction to SQL
Chapter 6 starts out with a general overview of Structured
Query Language (SQL), including its history. To keep readers confident
that they can actually understand SQL, author Ben Forta says that
SQL is really easy to learn.
"In fact, you need to learn only four statements to be able to
perform almost all the data manipulation you will need on a regular
basis."
Those statements are SELECT, INSERT, UPDATE, and DELETE.
"ColdFusion provides you with all the tools you need to add based
interaction to your databases. ColdFusion itself, though, has
no built-in database. Instead, it communicates with whatever database
you select, passing updates and requests and returning query results.
Introducing ODBC
"The communication between ColdFusion and the database usually
takes place via a database interface called Open Database Connectivity,
or ODBC. ODBC is a standard Application Programming Interface
(API) for accessing information from different database systems
and different storage formats.
Working with Database System Differences
"The purpose of ODBC is to enable you to access a diverse selection
of databases and data formats without having to learn the features
and peculiarities of each. ODBC provides a layer of abstraction,
accomplished using database drivers, between your client application
and the underlying database. The database drivers create a database-independent
environment, as illustrated in Figure 6.1. This way, you can write
one program and have it work with almost any major database system.
|
|
|
"Of course, differences exist between database systems. Microsoft
SQL Server and Oracle, for example, both require you to log in
to the database server before you can manipulate any data. Based
on your login, you are granted or denied access to specific tables
or other objects. Microsoft Access, on the other hand, has no
concept of login-based security. If you have access to the data
file (the MDB file), you have full access to all data in it.
"You will find other differences, too. To access Microsoft SQL
Server, your client application must know the address of the server.
It might be an IP address or an NT server name. To use Microsoft
Access data files, however, you just need to know the drive and
path to the data file."
"ODBC itself is not a language; the language used by ODBC is
SQL. Part of the magic of the ODBC database driver is that it
understands SQL and converts it to whatever is appropriate for
any specific database. This way, you can use SQL commands to work
with xBASE-based databases (such as Microsoft FoxPro and Borland
dBASE) and ISAM databases (such as Btrieve), even though they
have entirely different native languages.
"And herein lies the power of ODBC. The combination of database
independence and a common standard language grants ODBC clients
a tremendous level of freedom„freedom to use any database they
want, freedom to use different databases for different tasks seamlessly
and simultaneously, and freedom to concentrate on application
development without having to learn database-specific languages
and APIs.
"Applications that use ODBC do not interact with the underlying
databases directly. Rather, they communicate with ODBC data sources.
A data source is kind of a virtual database„it is a saved profile
that includes the ODBC driver to be used as well as necessary
configuration options and settings. Data sources typically are
defined using operating system features (such as the Control Panel
in Windows), but the ColdFusion Administrator (as seen in Chapter
4, "Accessing the ColdFusion Administrator") provides
a Web-based interface to creating and managing data sources that
is ideally suited for Web-based development."
Understanding ODBC and ColdFusion
"ColdFusion is an ODBC client. ODBC enables you to use ColdFusion
with whichever database you choose. If youÍre using Microsoft
Access, ColdFusion uses the Access driver; if youÍre using Oracle,
the Oracle ODBC driver is used instead. You can even use ODBC
to read and write plain-text files. As long as you have the correct
ODBC driver, ColdFusion will support that data store.
Because ColdFusion is an ODBC client, the database language used
by ColdFusion is SQL. To truly exploit the power of ColdFusion,
you must have a thorough understanding of SQL. Fortunately, by
the end of this chapter, you will know enough basic SQL to start
generating world-class ColdFusion applications."
Once you have determined your data source, all you need is
a client application with which to access the data. That client
is ColdFusion. To start learning real SQL, Ben advises you to
use a standalone query tool, such as Microsoft Query or George
PouloseÍs Query Tool. He describes each query utility a bit before
showing how to create a simple query.
Creating Queries
"With all the preliminaries taken care of, you can roll up your
sleeves and start writing SQL. The SQL statement you will use
most is the SELECT statement. You use SELECT, as its name implies,
to select data from a table.
"Most SELECT statements require at least the following two parameters:
|
•
|
What data you want to select,
known as the select list. If you specify more than one item,
you must separate each with a comma. |
|
•
|
The table (or tables) from which
to select the data, specified with the FROM keyword. |
|
The first SQL SELECT you will create is a query for a list of movies
in the Films table. Type the code in Listing 6.1 as seen in Figure
6.9, and then execute the statement.
Listing 6.1 Simple SELECT Statement
SELECT MovieTitle FROM Films
|
 |
"ThatÍs it! YouÍve written your first SQL statement. The results
will be shown as seen in Figure 6.10 (assuming you are using MS
Query)." |
 |
Download Chapters 3 and 6, ColdFusion 5 Web Application Construction
Kit below:
|
| -------------------------------------------------------------------------------- |
|
About the author
Ben Forta is the Macromedia Senior Product Evangelist for ColdFusion.
Ben has two decades of experience in the computer industry, and
is a much sought after speaker and lecturer. Ben is the author
of the popular ColdFusion Web Application Construction Kit, and
its sequel Advanced ColdFusion Application Development, as well
as books on SQL, JSP (JavaServer Pages), WAP, and other technologies.
Macromedia Press
Expand your product knowledge with hands-on tutorials and comprehensive
guides. Whether you are beginner, seasoned professional,
designer or developer, Macromedia Press has the book for you.
Search through titles today at: www.macromedia.com/go/books
|
|
|
