- 21.1 Introduction
- 21.2 Relational Databases
- 21.3 Relational Database Overview: Books Database
- 21.4 SQL
- 21.5 LINQ to SQL
- 21.6 LINQ to SQL: Extracting Information from a Database
- 21.7 More Complex LINQ Queries and Data Binding
- 21.8 Retrieving Data from Multiple Tables with LINQ
- 21.9 Creating a Master/Detail View Application
- 21.10 Programming with LINQ to SQL: Address-Book Case Study
- 21.11 Wrap-Up
- 21.12 Tools and Web Resources
21.2 Relational Databases
A relational database is based on a logical representation of data that allows the data to be accessed independently of its physical structure. A relational database organizes data in tables. Figure 21.1 illustrates a sample Employees table that might be used in a personnel system. The table stores the attributes of employees. Tables are composed of rows and columns in which values are stored. This table consists of six rows and five columns. The ID column of each row is the table’s primary key—a column (or group of columns) requiring a unique value that cannot be duplicated in other rows. This guarantees that a primary-key value can be used to uniquely identify a row. A primary key composed of two or more columns is known as a composite key. Good examples of primary-key columns in other applications are an employee ID number in a payroll system and a part number in an inventory system—values in each of these columns are guaranteed to be unique. The rows in Fig. 21.1 are displayed in order by primary key. In this case, the rows are listed in increasing (ascending) order, but they could also be listed in decreasing (descending) order or in no particular order at all. As we will demonstrate in an upcoming example, programs can specify ordering criteria when requesting data from a database.
Fig. 21.1 Employees table sample data.
Each column represents a different data attribute. Rows are normally unique (by primary key) within a table, but some column values may be duplicated between rows. For example, three different rows in the Employees table’s Department column contain the number 413, indicating that these employees work in the same department.
Different database users are often interested in different data and different relationships among the data. Most users require only subsets of the rows and columns. To obtain these subsets, programs use SQL to define queries that select subsets of the data from a table. For example, a program might select data from the Employees table to create a query result that shows where each department is located, in increasing order by Department number (Fig. 21.2). SQL queries are discussed in Section 21.4.
Fig. 21.2 Distinct Department and Location data from the Employees table.
Department Location 413 New Jersey 611 Orlando 642 Los Angeles