2 Chapter 1: Databasics
■
Basic lists may work for very simple databases. However, the limitations of this
approach can make even simple tasks difficult. Let’s look at our address book example
again. One problem is that it has space for information we don’t know/need (e.g., home
address), whereas it does not have space for information we need to know (e.g., pager
number). Searching presents another problem. An address book is typically organized
alphabetically by name. What if we want to search using some other criteria? Finding all
of our friends who live in a particular city requires an exhaustive search. Data entry and
maintenance can also be problematic. Paper-based address books are fine for a small set
of people; however, it would be very time-consuming to add all of the people in your com-
pany. Keeping up with the changes quickly becomes unmanageable. Another problem is
relating our paper-based address book with other databases. If we wanted to call all of
the individuals on our softball team, we would need to combine the information from our
team roster with our address book. Clearly, additional complexity makes it harder and
harder to effectively represent the various data relationships. Likewise, as the volume of
data increases, the complexity of managing and querying information grows.
Lucky for us, computers are especially adept at managing and quickly accessing infor-
mation. Software designed to store, manipulate, and retrieve data in a database is called a
Database management system (DBMS). Here, we focus exclusively on a specific (and by far
the most common) type of DBMS, the relational DBMS. There are many relational DBMSs
(see the book Web site for pointers to some examples). Fortunately, relational DBMSs speak
a common language called SQL. Using SQL, we can define, manipulate, and query our data.
SQL is correctly pronounced as S-Q-L; however, many people also pronounce it see-kwel.
This text is based on the ANSI 2003 SQL standard. NOTE: Each DBMS does things dif-
ferently, and no major DBMS follows the specification exactly. The specifics of a particular
DBMS can be found in its documentation. There are many reasons a DBMS may vary from
the standard, including performance, legacy, or marketing. Fortunately, the basic syntax
of SQL is the same for all DBMS. See the book Web site for information on DBMS-specific
syntax.
SQL is divided into three major parts. Data manipulation language (DML) is used to
store and retrieve data from the database. The majority of this book is on SQL DML, and
that will be the part of SQL used by the most people. Data description language (DDL) is
used to define the structure of the data. Chapters 9 and 10 cover the basics in SQL DDL.
Data control language (DCL) is used to restrict access to data by certain users. We introduce
DCL in Chapter 12.
We assume that you have a DBMS already installed and are able to enter SQL com-
mands. Consult your DBMS documentation for instructions on how to do this. If you do
not have a DBMS, consider downloading one from the Internet. There are some free rela-
tional databases, and several commercial DBMSs provide a free version of their product
for experimentation. See the book Web site for suggestions of DBMSs.
The best way to understand how a relational database works is to consider a spe-
cific application. Imagine that you are the proud owner of a restaurant named Garden
Variety Salads. You need to manage information about a variety of real-world entities to
run your establishment. For example, you need to maintain a list of food items so your
customers will have something to purchase. Each food item is made from one or more