The SQL language and relational database systems based on it are one of the most
important foundation technologies in the computer industry today. Over the last decade,
the popularity of SQL has exploded, and it stands today as the standard computer
database language. Literally hundreds of database products now support SQL, running
on computer systems from mainframes to personal computers and even handheld
devices. An official international SQL standard has been adopted and expanded twice.
Virtually every major enterprise software product relies on SQL for its data management,
and SQL is at the core of the database products from Microsoft and Oracle, two of the
largest software companies in the world. From its obscure beginnings as an IBM
research project, SQL has leaped to prominence as both an important computer
technology and a powerful market force.
What, exactly, is SQL? Why is it important? What can it do, and how does it work? If SQL
is really a standard, why are there so many different versions and dialects? How do
popular SQL products like SQL Server, Oracle, Informix, Sybase, and DB2 compare?
How does SQL relate to Microsoft standards, such as ODBC and COM? How does JDBC link
SQL to the world of Java and object technology? Does SQL really scale from mainframes
to handheld devices? Has it really delivered the performance needed for high-volume
transaction processing? How will SQL impact the way you use computers, and how can
you get the most out of this important data management tool?
The SQL Language
SQL is a tool for organizing, managing, and retrieving data stored by a computer
database. The name "SQL" is an abbreviation for Structured Query Language. For
historical reasons, SQL is usually pronounced "sequel," but the alternate pronunciation
"S.Q.L." is also used. As the name implies, SQL is a computer language that you use to
interact with a database. In fact, SQL works with one specific type of database, called a
relational database.
Figure 1-1 shows how SQL works. The computer system in the figure has a database
that stores important information. If the computer system is in a business, the database
might store inventory, production, sales, or payroll data. On a personal computer, the
database might store data about the checks you have written, lists of people and their
phone numbers, or data extracted from a larger computer system. The computer program
that controls the database is called a database management system, or DBMS.
When you need to retrieve data from a database, you use the SQL language to make the
request. The DBMS processes the SQL request, retrieves the requested data, and
returns it to you. This process of requesting data from a database and receiving back the
results is called a database query—hence the name Structured Query Language.
The name Structured Query Language is actually somewhat of a misnomer. First of all,
SQL is far more than a query tool, although that was its original purpose and retrieving
data is still one of its most important functions. SQL is used to control all of the functions
that a DBMS provides for its users, including:
• Data definition. SQL lets a user define the structure and organization of the stored
data and relationships among the stored data items.
• Data retrieval. SQL allows a user or an application program to retrieve stored data
from the database and use it.
• Data manipulation. SQL allows a user or an application program to update the
database by adding new data, removing old data, and modifying previously stored
data.
• Access control. SQL can be used to restrict a user's ability to retrieve, add, and modify
data, protecting stored data against unauthorized access.
• Data sharing. SQL is used to coordinate data sharing by concurrent users, ensuring
that they do not interfere with one another.
• Data integrity. SQL defines integrity constraints in the database, protecting it from
corruption due to inconsistent updates or system failures.
SQL is thus a comprehensive language for controlling and interacting with a database
management system.
Second, SQL is not really a complete computer language like COBOL, C, C++, or Java.
SQL contains no IF statement for testing conditions, and no GOTO, DO, or FOR
statements for program flow control. Instead, SQL is a database sublanguage, consisting
of about forty statements specialized for database management tasks. These SQL
statements can be embedded into another language, such as COBOL or C, to extend
that language for use in database access. Alternatively, they can be explicitly sent to a
database management system for processing, via a call level interface from a language
such as C, C++, or Java.
Finally, SQL is not a particularly structured language, especially when compared to highly
structured languages such as C, Pascal, or Java. Instead, SQL statements resemble
English sentences, complete with "noise words" that don't add to the meaning of the
statement but make it read more naturally. There are quite a few inconsistencies in the
SQL language, and there are also some special rules to prevent you from constructing
SQL statements that look perfectly legal, but don't make sense.
Despite the inaccuracy of its name, SQL has emerged as the standard language for using
relational databases. SQL is both a powerful language and one that is relatively easy to
learn. The quick tour of SQL in the next chapter will give you a good overview of the
language and its capabilities.
The Role of SQL
SQL is not itself a database management system, nor is it a stand-alone product. You
cannot go into a computer store and "buy SQL." Instead, SQL is an integral part of a
database management system, a language and a tool for communicating with the DBMS.
Figure 1-2 shows some of the components of a typical DBMS, and how SQL acts as the
"glue" that links them together.
The database engine is the heart of the DBMS, responsible for actually structuring,
storing, and retrieving the data in the database. It accepts SQL requests from other
DBMS components, such as a forms facility, report writer, or interactive query facility,
from user-written application programs, and even from other computer systems. As the
figure shows, SQL plays many different roles:
•
SQL is an interactive query language. Users type SQL commands into an interactive
SQL program to retrieve data and display it on the screen, providing a convenient,
easy-to-use tool for ad hoc database queries.
•
SQL is a database programming language. Programmers embed SQL commands into
their application programs to access the data in a database. Both user-written
programs and database utility programs (such as report writers and data entry tools)
use this technique for database access.
•
SQL is a database administration language. The database administrator responsible
for managing a minicomputer or mainframe database uses SQL to define the database
structure and control access to the stored data.
•
SQL is a client/server language. Personal computer programs use SQL to
communicate over a network with database servers that store shared data. This
client/server architecture has become very popular for enterprise-class applications.
•
SQL is an Internet data access language. Internet web servers that interact with
corporate data and Internet applications servers all use SQL as a standard language
for accessing corporate databases.
•
SQL is a distributed database language. Distributed database management systems
use SQL to help distribute data across many connected computer systems. The
DBMS software on each system uses SQL to communicate with the other systems,
sending requests for data access.
•
SQL is a database gateway language. In a computer network with a mix of different
DBMS products, SQL is often used in a gateway that allows one brand of DBMS to
communicate with another brand.
SQL has thus emerged as a useful, powerful tool for linking people, computer programs,
and computer systems to the data stored in a relational database.
SQL Features and Benefits
SQL is both an easy-to-understand language and a comprehensive tool for managing
data. Here are some of the major features of SQL and the market forces that have made
it successful:
• Vendor independence
• Portability across computer systems
• SQL standards
• IBM endorsement (DB2)
• Microsoft commitment (ODBC and ADO)
• Relational foundation
• High-level, English-like structure
• Interactive, ad hoc queries
• Programmatic database access
• Multiple views of data
• Complete database language
• Dynamic data definition
• Client/server architecture
• Extensibility and object technology
• Internet database access
• Java integration (JDBC)
personal computers, minicomputers, and mainframes. They are described in the sections
that follow.
Vendor Independence
SQL is offered by all of the leading DBMS vendors, and no new database product over
the last decade has been highly successful without SQL support. A SQL-based database
and the programs that use it can be moved from one DBMS to another vendor's DBMS
with minimal conversion effort and little retraining of personnel. PC database tools, such
as query tools, report writers, and application generators, work with many different
brands of SQL databases. The vendor independence thus provided by SQL was one of
the most important reasons for its early popularity and remains an important feature
today.
Portability Across Computer Systems
SQL-based database products run on computer systems ranging from mainframes and
midrange systems to personal computers, workstations, and even handheld devices.
They operate on stand-alone computer systems, in departmental local area networks,
and in enterprise-wide or Internet-wide networks. SQL-based applications that begin on
single-user systems can be moved to larger server systems as they grow. Data from
corporate SQL-based databases can be extracted and downloaded into departmental or
personal databases. Finally, economical personal computers can be used to prototype a
SQL-based database application before moving it to an expensive multi-user system
SQL Standards
An official standard for SQL was initially published by the American National Standards
Institute (ANSI) and the International Standards Organization (ISO) in 1986, and was
expanded in 1989 and again in 1992. SQL is also a U.S. Federal Information Processing
Standard (FIPS), making it a key requirement for large government computer contracts.
Over the years, other international, government, and vendor groups have pioneered the
standardization of new SQL capabilities, such as call-level interfaces or object-based
extensions. Many of these new initiatives have been incorporated into the ANSI/ISO
standard over time. The evolving standards serve as an official stamp of approval for
SQL and have speeded its market acceptance.
IBM Endorsement (DB2)
SQL was originally invented by IBM researchers and has since become a strategic
product for IBM based on its flagship DB2 database. SQL support is available on all
major IBM product families, from personal computers through midrange systems (AS/400
and RS/6000) to IBM mainframes running both the MVS and VM operating systems.
IBM's initial work provided a clear signal of IBM's direction for other database and system
vendors to follow early in the development of SQL and relational databases. Later, IBM's
commitment and broad support speeded the market acceptance of SQL.
Microsoft Commitment (ODBC and ADO)
Microsoft has long considered database access a key part of its Windows personal
computer software architecture. Both desktop and server versions of Windows provide
standardized relational database access through Open Database Connectivity (ODBC), a
SQL-based call-level API. Leading Windows software applications (spreadsheets, word
processors, databases, etc.) from Microsoft and other vendors support ODBC, and all
leading SQL databases provide ODBC access. Microsoft has enhanced ODBC support
with higher-level, more object-oriented database access layers as part of its Object
Linking and Embedding technology (OLE DB), and more recently as part of Active/X
(Active/X Data Objects, or ADO).
Relational Foundation
SQL is a language for relational databases, and it has become popular along with the
relational database model. The tabular, row/column structure of a relational database is
intuitive to users, keeping the SQL language simple and easy to understand. The
relational model also has a strong theoretical foundation that has guided the evolution
and implementation of relational databases. Riding a wave of acceptance brought about
by the success of the relational model, SQL has become the database language for
relational databases.
High-Level, English-Like Structure
SQL statements look like simple English sentences, making SQL easy to learn and
understand. This is in part because SQL statements describe the data to be retrieved,
rather than specifying how to find the data. Tables and columns in a SQL database can
have long, descriptive names. As a result, most SQL statements "say what they mean"
and can be read as clear, natural sentences.
Interactive, Ad Hoc Queries
SQL is an interactive query language that gives users ad hoc access to stored data.
Using SQL interactively, a user can get answers even to complex questions in minutes or
seconds, in sharp contrast to the days or weeks it would take for a programmer to write a
custom report program. Because of SQL's ad hoc query power, data is more accessible
and can be used to help an organization make better, more informed decisions. SQL's ad
hoc query capability was an important advantage over nonrelational databases early in its
evolution and more recently has continued as a key advantage over pure object-based
databases.
Programmatic Database Access
SQL is also a database language used by programmers to write applications that access
a database. The same SQL statements are used for both interactive and programmatic
access, so the database access parts of a program can be tested first with interactive
SQL and then embedded into the program. In contrast, traditional databases provided
one set of tools for programmatic access and a separate query facility for ad hoc
requests, without any synergy between the two modes of access.
Multiple Views of Data
Using SQL, the creator of a database can give different users of the database different
views of its structure and contents. For example, the database can be constructed so that
each user sees data for only their department or sales region. In addition, data from
several different parts of the database can be combined and presented to the user as a
simple row/column table. SQL views can thus be used to enhance the security of a
database and tailor it to the particular needs of individual users.
Complete Database Language
SQL was first developed as an ad hoc query language, but its powers now go far beyond
data retrieval. SQL provides a complete, consistent language for creating a database,
managing its security, updating its contents, retrieving data, and sharing data among
many concurrent users. SQL concepts that are learned in one part of the language can
be applied to other SQL commands, making users more productive.
Dynamic Data Definition
Using SQL, the structure of a database can be changed and expanded dynamically, even
while users are accessing database contents. This is a major advance over static data
definition languages, which prevented access to the database while its structure was
being changed. SQL thus provides maximum flexibility, allowing a database to adapt to
changing requirements while on-line applications continue uninterrupted.
Client/Server Architecture
SQL is a natural vehicle for implementing applications using a distributed, client/server
architecture. In this role, SQL serves as the link between "front-end" computer systems
optimized for user interaction and "back-end" systems specialized for database
management, allowing each system to do what it does best. SQL also allows personal
computers to function as front-ends to network servers or to larger minicomputer and
mainframe databases, providing access to corporate data from personal computer
applications.
Extensibility and Object Technology
The major challenge to SQL's continued dominance as a database standard has come
from the emergence of object-based programming, and the introduction of object-based
databases as an extension of the broad market trend toward object-based technology.
SQL-based database vendors have responded to this challenge by slowly expanding and
enhancing SQL to include object features. These "object/relational" databases, which
continue to be based on SQL, have emerged as a more popular alternative to "pure
object" databases and may insure SQL's continuing dominance for the next decade.
Internet Database Access
With the exploding popularity of the Internet and the World Wide Web, and their
standards-based foundation, SQL found a new role in the late 1990s as an Internet data
access standard. Early in the development of the Web, developers needed a way to
retrieve and present database information on web pages and used SQL as a common
language for database gateways. More recently, the emergence of three-tiered Internet
architectures with distinct thin client, application server and database server layers, have
established SQL as the standard link between the application and database tiers.
Java Integration (JDBC)
One of the major new areas of SQL development is the integration of SQL with Java.
Seeing the need to link the Java language to existing relational databases, Sun
Microsystems (the creator of Java) introduced Java Data Base Connectivity (JDBC), a
standard API that allows Java programs to use SQL for database access. Many of the
leading database vendors have also announced or implemented Java support within their
database systems, allowing Java to be used as a language for stored procedures and
business logic within the database itself. This trend toward integration between Java and
SQL will insure the continued importance of SQL in the new era of Java-based
programming.
No comments:
Post a Comment