WHAT IS A DATABASE? 875

Exploring the Pubs Database

Before looking at SQL and more practical techniques for manipulating tables, let’s look at the structure of another sample database that comes with SQL Server, the Pubs database. Pubs is a database for storing book, author, and publisher information, not unlike the database you may have to build for an online bookstore (since online bookstores are so common).

The Pubs database is made up of really small tables, but it was carefully designed to demonstrate many of the features of SQL, so it’s a prime candidate for sample code. Just about any book about SQL Server uses the Pubs database. In the examples of the following sections, I will use the Northwind database, because it’s a commercial database and the type of information stored in the Northwind database is closer to the needs of the average VB programmer than the Pubs database. Some of the fine points of SQL, however, can’t be demonstrated with the data of the Northwind database, and this is where I’ll show examples that make use of the ubiquitous Pubs database.

Titles Table

The Titles table contains the information about individual books (the book’s title, ID, price, and so on). Each title is identified by an ID, which is not a numeric value. The IDs of the books look like this: BU2075.

Authors Table

The Authors table contains information about authors. Each author is identified by an ID, which is stored in the au_id field. This field is a string, with a value like 172-32-1176—that is, resembling U.S. Social Security numbers.

TitleAuthor Table

The Titles and Authors tables are not directly related. The reason is the two tables can’t be joined with a one-to-many relation. The relation between the two tables is many-to-many. Some authors have written many books, and some books are written by multiple authors. If you stop and think about the relationship between the two tables, you’ll realize that it can’t be implemented with a primary and a foreign key.

To establish a many-to-many relationship, you must create a table between the other two. This table must have a one-to-many relationship with either table. Figure 20.4 shows how the Titles and Authors tables of the Pubs database are related to one another. The table between them holds pairs of title IDs and author IDs. If a book was written by two authors, the TitleAuthor table contains two entries with the same title ID and different author IDs. The book with title_id of TC7777 was written by three authors. The IDs of the authors appear in the TitleAuthor table along with the ID of the book. The IDs of these three authors are 267-41-2394, 472-27-2349, and 672-71-3249. Likewise, if an author has written more than one book, the author’s ID will appear many times in the TitleAuthor table, each time paired with a different title ID.

There will be situations where you won’t be able to establish the desired relationship directly between two tables, and the reason is that the relationship is many-to-many. When you discover a conflict between two tables, you must create a third one between them.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

876 Chapter 20 DATABASES: ARCHITECTURE AND BASIC CONCEPTS

Figure 20.4

The TitleAuthor table links titles to authors

Publishers Table

This table contains information about publishers. Each title has a pub_id field, which points to the matching row of the Publishers table. Unlike the other major tables of the Pubs database, the Publishers table uses a numeric value to identify each publisher.

Other Tables

The Pubs database contains a few more tables. The Sales table contains sale information, while the RoySched table contains royalty information about each author. The author’s payment is determined by the sales of the corresponding titles and the author’s royalty schedule (how the royalties escalate with sales). We are not going to use these tables in our examples, so I won’t discuss them here.

Understanding Relations

In a database, each table has a field with a unique value for every row. This field is the table’s primary key. The primary key does not have to be a meaningful entity, because in most cases there’s no single field that’s unique for each row. The primary key need not resemble the entity it identifies either.

The only requirement is that primary keys are unique in the entire table. In most designs, we use an integer as the primary key. To make sure they’re unique, we even let the DBMS generate a new integer for each row added to the table. Each table can have one primary key only, and this field can’t be Null. The DBMS can automatically generate an integer value for a primary key field every time a new row is added. SQL Server uses the term Identity for this data type, and you can have only one Identity field in each table.

The related rows in a table repeat the primary key of the row they are related to, in another table. The copies of the primary keys in all other tables are called foreign keys. Foreign keys need not be unique (in fact, they aren’t), and any field can serve as a foreign key. What makes a field a foreign key is that it matches the primary key of another table. The CategoryID field is the primary key of the Categories table, because it identifies each category. The CategoryID field in the Products table

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

WHAT IS A DATABASE? 877

is the foreign key, because the same value may appear in many rows (many products may belong to the same category).

Referential Integrity

Maintaining the links between tables is not trivial task. When you add an invoice line, for instance, you must make sure that the product ID corresponds to a row in the Products table. An important aspect of a database is its integrity. To be specific, you must ensure that the relations are always valid, and this type of integrity is called referential integrity. There are other types of integrity (for example, setting a product’s value to a negative value will compromise the integrity of the database), but this is not nearly as important as the referential integrity. The wrong price can be easily fixed. But issuing an invoice to a customer that does not exist isn’t easy (if at all possible) to fix. Modern databases come with many tools to help ensure their integrity. These tools are constraints you enter when you design the database, and the DBMS makes sure the constraints are not violated as the various programs manipulate the database.

When you relate the Products and Categories tables, for example, you must also make sure that:

Every product added to the foreign table points to a valid entry in the primary table. If you are not sure which category the product belongs to, you can leave the CategoryID field of the Products table empty. Or, you can create a generic category, the UNKNOWN or UNDECIDED category, and use this category if no information is available.

No rows in the Categories table are removed if there are rows in the Products table pointing to the specific category. This would make the corresponding rows of the Products table point to an invalid category.

These two restrictions would be quite a burden on the programmer if the DBMS didn’t protect the database against actions that could impair its integrity. The integrity of your database depends on the validity of the relations. Fortunately, all DBMSs can enforce rules to maintain their integrity. You’ll learn how to enforce rules that guarantee the integrity of your database later in this chapter. In fact, when you create the relationship, you can check a couple of boxes that tell SQL Server to enforce the relationship (that is, not to accept any changes in the data that violate the relationship).

The Visual Database Tools

To simplify the development of database applications, Visual Studio.NET comes with some visual tools, the most important of which are discussed in the following sections.

The Server Explorer This is the first and most prominent tool. The Server Explorer is the Toolbox for database applications, in the sense that it contains all the basic tools for connecting to databases and manipulating their objects.

The Query Builder This is a tool for creating SQL queries (statements that retrieve the data we want from a database, or update the data in the database). SQL is a language in its own right, and we’ll discuss it later in this chapter. The Query Builder lets you specify the operations you want to perform on the tables of a database with point-and-click operations. In the background, the Query Builder builds the appropriate SQL statement and executes it against the database.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com