20.1 RELATIONAL DATABASES

Consider, for example, a database for storing information on all the books in a library. Let's say that we want to store the following information on each book:

      Title      Author      Year      ISBN      NumberOfCopies      Publisher      PublisherLocation      PublisherURL      PublisherRep      PublisherRepPhone      PublisherRepEmail

Let's assume that the library has 100,000 books that are published by, say, 100 publishers. For the sake of making a point, let's also assume that each publisher is represented equally well in the library. If we represented all the books in a single "flat" table with eleven columns, one for each of the items listed above, the information in at least three of the columns—those under the column headings "Publisher," "PublisherLocation," and "PublisherURL"—would be the same for the 1000 rows corresponding to each publisher. That obviously is not an efficient way to store the information. There would be too much "redundancy" in the table. Since it goes without saying that the larger the number of entries that need to made to create a table, the greater the probability of an error creeping into one or more of the entries, our table would be at an increased risk of containing erroneous information. The table with the column headings as shown above will also have redundancies with regard to the PublisherRep information.

Now consider an alternate design consisting of three tables, one containing information generic to each book, the other containing information generic to each publisher, and the third containing information generic to each publisher rep:

 BookTable:     Title Author Year ISBN PublisherID PublisherRepID PublisherTable:     PublisherID PublisherName PublisherLocation  PublisherURL PublisherRepTable: PublisherRepID RepName RepPhone RepEmail

where we have assumed that the PublisherRep might be specific to each book and that the same rep may represent multiple publishers. We now associate unique identifiers, possibly numerical in nature, in the form of PublisherIDand PublisherRepID to "link" the main book table, BookTable,with the other two tables, PublisherTableand PublisherRepTable.

These three tables together would constitute a typical modern relationaldatabase. Given this database, we may now query the database for information that for simple queries can be extracted from a single table, but that for more complex queries may require simultaneous access to multiple tables. Here are examples of simple queries that can be fulfilled from just a single table:

       Retrieve all book titles published in a given year.       Retrieve all book titles published by a given author.             Retrieve all publishers located in France.             Retrieve all publisher rep names.             etc.

and here are examples of queries that require simultaneous access to more than one table in the database:

       Retrieve all book titles along with the name of                                   the publisher for each book.       Retrieve all books for which the designated                                   publisher rep is given.       Retrieve all book titles published last year along                with the name of the publisher for each                and the name of the publisher's rep.       etc.

Other possible interactions with the database could consist of updating the database as the library acquires additional books, modifying the entries, and so on.

Over the years, a command language called SQL for Structured Query Language(SQL) has come into widespread use for communicating with databases, especially the server-based databases.^[1]Since JDBC and Mysql++ programs serve as interfaces to SQL, it is important to get a sense of the syntax of SQL before launching into the syntax of JDBC and Mysql++. JDBC and Mysql++ programs send SQL queries to a database, analyze the results returned by the database, and display these results in forms desired by the user.

^[1]The desktop-based databases are usually accessed through graphical user interfaces.