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
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. PublisherRepTable: PublisherRepID RepName RepPhone RepEmail
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.
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
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. PublisherRepTable: PublisherRepID RepName RepPhone RepEmail
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.
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
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. PublisherRepTable: PublisherRepID RepName RepPhone RepEmail
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.
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
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. PublisherRepTable: PublisherRepID RepName RepPhone RepEmail
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.
