Set Based Data Retrieval

When you access data from a file, you often scroll through data line-by-line, processing values when you find specific values in the line or processing each line. Relational databases, although you may think about them as individual lines containing records, don't work that way. Instead, they work on sets of data. A data set is a combination of one or more rows and columns; in other words, a set of records, also called a recordset or a result set. Note that the columns in the recordset do not have to come from a single table. In fact, with relational databases, most of your queries will not come from a single table— if they do, you're probably making poor use of the relational capabilities of modern databases.

Note When I say relational databases, I'm excluding Microsoft Access and other file-based databases. Although you can use Access for practice, it's not suitable for a full-scale Web application with more than a small number of users. It's not suitable for any users if the data is critical. If you were considering using Access for your production Web application—don't. In fact, I recommend that you not use it at all, even for practice. Consider using the newer MSDE (Microsoft Data Engine) instead. It supports the same syntax as SQL Server, which means you won't have to change your application when you deploy it for use. MSDE is no more suited for a large-scale or critical Web application than is Access, but it is considerably easier to upgrade.

For all discussion of databases throughout the rest of this book, you can assume that I mean both MSDE and SQL Server, even though, for brevity, I will omit the term MSDE. I'm not trying to ignore Oracle, Sybase, Informix, DB2, or any other relational database management system—all the general database information (but not the Transact-SQL code itself) applies equally well to those RDBMSs as to SQL Server. This is, after all, a book about Microsoft technology, so it is Microsoft-centric.

Working with sets requires a different mindset from working with rows of data. It forces you to think in terms of relationships rather than in terms of rows. You also begin thinking about data as collections of records rather than as individual data items. Typically, you join a column from one table with a matching column in another table to retrieve a set of records. A join finds matching values in the columns then selects a specified set of columns from the intersection of the two sets.

For example, consider a teacher-student relationship. One teacher has many students. Conversely, one student may have several teachers. In other words, for any given row on one side of the relationship, many rows may be on the other side—an example of a many-to-many relationship. For your application, you want to select the set of students taught by any given teacher, as well as the set of teachers for any given student. To create this relationship in a database, you must model that many-to-many relationship in table relationships. If the Teachers table contains a unique or primary key index on a TeacheriD field and the Students table contains a unique or primary key index on a studentiD field, you can create a separate table called TeacherStudent that contains only two columns: the TeacheriD and StudentiD values.

Using the TeacherStudent table, it's easy to determine which students have which teachers or obtain the set of students taught by any teacher. For example, the teacher Marsha Franklin (TeacheriD=32) teaches Barry Jones, Meredith Jones, and Mohammed Said. If you query a database for this information, you need to find the intersection of the three tables, as shown in Figure 12.1.

Figure 12.1: Teachers-to-students relationship query The result of this query is another table—a recordset, shown in Table 12.4. Table 12.4: Teachers-to-Students Relationship Query Result

Teacher ID

Teacher LastName

Teacher FirstName

StudentID

Student LastName

Student FirstName

32

Franklin

Marsha

1

Jones

Barry

32

Franklin

Marsha

3

Jones

Meredith

32

Franklin

Marsha

5

Said

Mohammed

To obtain the information from the database, you must frame the question in SQL. In the next section, I'll show you how to do that, but first, you'll need a database with which you can test your SQL statements.

In the remainder of this chapter, you'll work with a database called ClassRecords. You will need MSDE (a time-limited version ships with Visual Studio) or SQL Server 2000 to work through the examples and code in the remainder of this book, although with a little work, you can adapt the examples to any modern relational database. You've already seen a few examples from the database in previous sections in this chapter. The ClassRecords database contains information from an imaginary school in which teachers teach classes to different grades. All the students in the school move from class to class and from teacher to teacher.

Was this article helpful?

0 0

Post a comment