Platinum Edition Using HTML 4, XML, and Java 1.2
(Publisher: Macmillan Computer Publishing)
Author(s): Eric Ladd
ISBN: 078971759x
Publication Date: 11/01/98

Listing 29.3 We Read Each Record and Display the Contents

use AnyDBM_File;
$database=tie(%db, ‘NDBM_File’, “database”, O_RDWR|O_CREAT, 0666);
while (($key,$value)= each(%db)) {
  ($name, $work_phone, $home_phone) = split(/,/, $value);
  print “Name: $name\n”;
  print “Email: $key\n”;
  print “Work Phone: $work_phone\n”;
  print “Home Phone: $home_phone\n”;
}
untie %db;
undef $database;

If desired, we could print out only those entries matching a query by matching a query against the value of each record. Also, if you know the email address you are looking for, you can retrieve the associated information by reading only that record (see Listing 29.4).

Listing 29.4 Using a DBM Database, You Can Access a Specific Record Without Reading the Whole Database

use AnyDBM_File;
$database=tie(%db, ‘NDBM_File’, “database”, O_RDWR|O_CREAT, 0666);
if ($db{‘mniles@itm.com’}) {
  $value = $db{‘mniles@itm.com’};
  ($name, $work_phone, $home_phone) = split(/,/, $value);
  print “Name: $name\n”;
  print “Email: mniles@itm.com\n”;
  print “Work Phone: $work_phone\n”;
  print “Home Phone: $home_phone\n”;
}
untie %db;
undef $database;

Searching a DBM Database

If your database starts to get large, it’s convenient to provide a means by which visitors to your site can search for a specific keyword. Performing a search works much the same as displaying the whole database, except that rather than immediately displaying each entry, you check it first to see whether it matches the keyword entered by the visitor. If the keyword matches the key, you print the line; otherwise, you simply skip ahead and check the next entry (see Listing 29.5).

Listing 29.5 By Matching Each Field Against a Query, You Can Limit What Information Is Returned to the Visitor

$database=tie(%db, ‘NDBM_File’, “database”, O_READ, 0660);
while (($key,$value)= each(%db)) {
  if ($key =~ /$query/i) {
    ($name, $work_phone, $home_phone) = split(/,/, $value);
    print “Name: $name\n”;
    print “Email: mniles@itm.com\n”;
    print “Work Phone: $work_phone\n”;
    print “Home Phone: $home_phone\n”;
  }
}

Now that you have seen how DBM databases work, you can take the same concepts from these scripts and apply them to something different. You could use them in a hotlinks script for example, in which you can store information for all your favorite Web sites or in a proper address book that stores the names, addresses, and phone numbers of all your customers. You can also create a database that stores names and email addresses and use it as a mailing list, providing friends and customers news about you or your organization—or your products. Three files, called dbbookadd.pl, dbbook.pl, and dbbooksearch.pl, are provided on the CD that accompanies this book. These files work together as a phone book application using DBM databases.

Relational Databases

Most relational database servers consist of a set of programs that manage large amounts of data, offering a rich set of query commands that help manage the power behind the database server. These programs control the storage, retrieval, and organization of the information within the database. This information can be changed, updated, or removed after the support programs or scripts are in place.

Unlike DBM databases, relational databases don’t link records together physically like the DBM database does by using a key/value pair. Instead, they provide a field in which information can be matched, and the results can be sent back to the person performing the query, as if the database were organized that way.

Relational databases store information in tables. A table is similar to a smaller database that sits inside the main database. Each table can usually be linked with the information in other tables to provide a result to a query. Take a look at Figure 29.1, which shows how this information could be tied together.

FIGURE 29.1  A relational database stores certain information in various parts of the database; this information can later be called with one query.

Figure 29.1 depicts a query in which it requests employee information from a database. To get a complete response, information is retrieved from three tables, each of which stores only parts of the information requested. In the figure, information about the person’s pay rate is retrieved, and departmental information and personal information are retrieved from other tables. This can produce a complete query response with an abundant amount of information on an individual.

Introduction to Database Design

SQL databases consist largely of tables, records, and fields. The table(s) hold all information that is stored in the database, and each table contains one or more records. A record contains one or more fields grouping the fields for a specific entry. A field is given a specific data type, which specifies what kind of information is to be stored in that field. For instance, a field may contain a date, a number, a set of characters, and so on.

---

NOTE:  Tables are formally called relations, records are called tuples, and fields are called attributes. More often, you will hear a record being called a column and a field being called a row. The rows and columns seem to be the most used when talking among database developers.

---

As stated, each table consists of columns and rows. The columns identify the data by a name, and the rows store information relevant to that name. Take a look at the following example:

The column heads give a name to each item below it. Information within a table is stored in much the same way.

If you add more tables to the database, you could have something that looks like the following:

You could have department information as well:

With this information, you can perform a query to get a complete look at an individual.

When designing a database, you need to decide what tables you require and what data each table will contain. You will also need to decide how each table will work with other tables.