- 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
This chapter is from the book
This chapter is from the book
This chapter is from the book
21.3 Relational Database Overview: Books Database
We now overview relational databases in the context of a simple Books database. The database stores information about some recent Deitel publications. First, we overview the Books database’s tables. A database’s tables, their fields and the relationships between them are collectively known as a database schema. After overviewing the database, we introduce database concepts, such as how to use SQL to retrieve information from the Books database and to manipulate the data. We provide the database file—Books.mdf—with the examples for this chapter (downloadable from www.deitel.com/books/csharpfp3/). SQL Server database files typically end with the .mdf (“master data file”) file-name extension. Sections 21.6–21.9 explain how to use this file in an application.
Authors Table of the Books Database
The database consists of three tables: Authors, AuthorISBN and Titles. The Authors table (described in Fig. 21.3) consists of three columns that maintain each author’s unique ID number, first name and last name, respectively. Figure 21.4 contains the data from the Authors table. We list the rows in order by the table’s primary key—AuthorID. You’ll learn how to sort data by other criteria (e.g., in alphabetical order by last name) using SQL’s ORDER BY clause in Section 21.4.3.
Fig. 21.3 Authors table of the Books database.
|
Column |
Description |
|
AuthorID |
Author’s ID number in the database. In the Books database, this integer column is defined as an identity column, also known as an autoincremented column—for each row inserted in the table, the AuthorID value is increased by 1 automatically to ensure that each row has a unique AuthorID. This is the primary key. |
|
FirstName |
Author’s first name (a string). |
|
LastName |
Author’s last name (a string). |
Fig. 21.4 Data from the Authors table of the Books database.
|
AuthorID |
FirstName |
LastName |
|
1 |
Harvey |
Deitel |
|
2 |
Paul |
Deitel |
|
3 |
Greg |
Ayer |
|
4 |
Dan |
Quirk |
Titles Table of the Books Database
The Titles table (described in Fig. 21.5) consists of four columns that maintain information about each book in the database, including the ISBN, title, edition number and copyright year. Figure 21.6 contains the data from the Titles table.
Fig. 21.5 Titles table of the Books database.
|
Column |
Description |
|
ISBN |
ISBN of the book (a string). The table’s primary key. ISBN is an abbreviation for “International Standard Book Number”—a numbering scheme that publishers worldwide use to give every book a unique identification number. |
|
BookTitle |
Title of the book (a string). |
|
EditionNumber |
Edition number of the book (an integer). |
|
Copyright |
Copyright year of the book (a string). |
Fig. 21.6 Data from the Titles table of the Books database.
|
ISBN |
BookTitle |
Edition-Number |
Copy-right |
|
0131752421 |
Internet & World Wide Web How to Program |
4 |
2008 |
|
0132222205 |
Java How to Program |
7 |
2007 |
|
0132404168 |
C How to Program |
5 |
2007 |
|
0136053033 |
Simply Visual Basic 2008 |
3 |
2009 |
|
013605305X |
Visual Basic 2008 How to Program |
4 |
2009 |
|
013605322X |
Visual C# 2008 How to Program |
3 |
2009 |
|
0136151574 |
Visual C++ 2008 How to Program |
2 |
2008 |
|
0136152503 |
C++ How to Program |
6 |
2008 |
AuthorISBN Table of the Books Database
The AuthorISBN table (described in Fig. 21.7) consists of two columns that maintain ISBNs for each book and their corresponding authors’ ID numbers. This table associates authors with their books. The AuthorID column is a foreign key—a column in this table that matches the primary-key column in another table (i.e., AuthorID in the Authors table). The ISBN column is also a foreign key—it matches the primary-key column (i.e., ISBN) in the Titles table. Together the AuthorID and ISBN columns in this table form a composite primary key. Every row in this table uniquely matches one author to one book’s ISBN. Figure 21.8 contains the data from the AuthorISBN table of the Books database.
Fig. 21.7 AuthorISBN table of the Books database.
|
Column |
Description |
|
AuthorID |
The author’s ID number, a foreign key to the Authors table. |
|
ISBN |
The ISBN for a book, a foreign key to the Titles table. |
Fig. 21.8 Data from the AuthorISBN table of Books.
|
AuthorID |
ISBN |
|
1 |
0131752421 |
|
1 |
0132222205 |
|
1 |
0132404168 |
|
1 |
0136053033 |
|
1 |
013605305X |
|
1 |
013605322X |
|
1 |
0136151574 |
|
1 |
0136152503 |
|
2 |
0131752421 |
|
2 |
0132222205 |
|
2 |
0132404168 |
|
2 |
0136053033 |
|
2 |
013605305X |
|
2 |
013605322X |
|
2 |
0136151574 |
|
2 |
0136152503 |
|
3 |
0136053033 |
|
4 |
0136151574 |
Foreign Keys
Foreign keys can be specified when creating a table. A foreign key helps maintain the Rule of Referential Integrity—every foreign-key value must appear as another table’s primary-key value. This enables the DBMS to determine whether the AuthorID value for a particular row of the AuthorISBN table is valid. Foreign keys also allow related data in multiple tables to be selected from those tables—this is known as joining the data. (You’ll learn how to join data using SQL’s INNER JOIN operator in Section 21.4.4.) There is a one-to-many relationship between a primary key and a corresponding foreign key (e.g., one author can write many books). This means that a foreign key can appear many times in its own table but only once (as the primary key) in another table. For example, the ISBN 0136151574 can appear in several rows of AuthorISBN (because this book has several authors) but only once in Titles, where ISBN is the primary key.
Entity-Relationship Diagram for the Books Database
Figure 21.9 is an entity-relationship (ER) diagram for the Books database. This diagram shows the tables in the database and the relationships among them. The first compartment in each box contains the table’s name. The names in italic font are primary keys (e.g., AuthorID in the Authors table). A table’s primary key uniquely identifies each row in the table. Every row must have a value in the primary-key column, and the value of the key must be unique in the table. This is known as the Rule of Entity Integrity. Note that the names AuthorID and ISBN in the AuthorISBN table are both italic—together these form a composite primary key for the AuthorISBN table.
)
Fig. 21.9 Entity-relationship diagram for the Books database.
Common Programming Error 21.1
Not providing a value for every column in a primary key breaks the Rule of Entity Integrity and causes the DBMS to report an error.
Common Programming Error 21.2
Providing the same value for the primary key in multiple rows breaks the Rule of Entity Integrity and causes the DBMS to report an error.
The lines connecting the tables in Fig. 21.9 represent the relationships among the tables. Consider the line between the Authors and AuthorISBN tables. On the Authors end of the line, there is a 1, and on the AuthorISBN end, an infinity symbol (∞). This indicates a one-to-many relationship—for each author in the Authors table, there can be an arbitrary number of ISBNs for books written by that author in the AuthorISBN table (i.e., an author can write any number of books). Note that the relationship line links the AuthorID column in the Authors table (where AuthorID is the primary key) to the AuthorID column in the AuthorISBN table (where AuthorID is a foreign key)—the line between the tables links the primary key to the matching foreign key.
Common Programming Error 21.3
Providing a foreign-key value that does not appear as a primary-key value in another table breaks the Rule of Referential Integrity and causes the DBMS to report an error.
The line between the Titles and AuthorISBN tables illustrates a one-to-many relationship—a book can be written by many authors. Note that the line between the tables links the primary key ISBN in table Titles to the corresponding foreign key in table AuthorISBN. The relationships in Fig. 21.9 illustrate that the sole purpose of the AuthorISBN table is to provide a many-to-many relationship between the Authors and Titles tables—an author can write many books, and a book can have many authors.