We now consider a simple Books
database that stores information about some Deitel books. First, we overview the database’s tables. A database’s tables, their fields and the relationships among them are collectively known as a database schema. The ADO.NET Entity Framework uses a database’s schema to define classes that enable you to interact with the database. Sections 22.5–22.8 show how to manipulate the Books
database. The database file—Books.mdf
—is provided with this chapter’s examples. SQL Server database files have the .mdf
(“master data file”) filename extension.
Authors
TableThe database consists of three tables: Authors
, Titles
and AuthorISBN
. The Authors
table (described in Fig. 22.3) consists of three columns that maintain each author’s unique ID number, first name and last name, respectively. Figure 22.4 contains the sample data from the Authors
table.
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). |
Authors
table.AuthorID |
FirstName |
LastName |
---|---|---|
1 |
Paul |
Deitel |
2 |
Harvey |
Deitel |
3 |
Abbey |
Deitel |
4 |
Sue |
Green |
4 |
John |
Purple |
Titles
TableThe Titles
table (described in Fig. 22.5) consists of four columns that maintain information about each book in the database, including its ISBN, title, edition number and copyright year. Figure 22.6 contains the data from the Titles
table.
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. |
Title |
Title of the book (a string). |
EditionNumber |
Edition number of the book (an integer). |
Copyright |
Copyright year of the book (a string). |
Titles
table of the Books
database.ISBN |
Title |
EditionNumber |
Copyright |
---|---|---|---|
0132151006 |
Internet & World Wide Web How to Program |
5 |
2012 |
0133807800 |
Java How to Program |
10 |
2015 |
0132575655 |
Java How to Program, Late Objects Version |
10 |
2015 |
0133976890 |
C How to Program |
8 |
2016 |
0133406954 |
Visual Basic 2012 How to Program |
6 |
2014 |
0134601548 |
Visual C# How to Program |
6 |
2017 |
0134448235 |
C++ How to Program |
10 |
2016 |
0134444302 |
Android How to Program |
3 |
2016 |
0134289366 |
Android 6 for Programmers: An App-Driven Approach |
3 |
2016 |
0133965260 |
iOS 8 for Programmers: An App-Driven Approach with Swift |
3 |
2015 |
0134021363 |
Swift for Programmers |
1 |
2015 |
AuthorISBN
Table of the Books
DatabaseThe AuthorISBN
table (described in Fig. 22.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 (that is, AuthorID
in the Authors
table). The ISBN
column is also a foreign key—it matches the primary-key column (that is, ISBN
) in the Titles
table. A database might consist of many tables. A goal of a database’s designer is to minimize the amount of duplicated data among the database’s tables. Foreign keys, which are specified when a database table is created in the database, link the data in multiple tables. 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 22.8 contains the data from the AuthorISBN
table of the Books
database.
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. |
AuthorISBN
table of the Books
database.AuthorID |
ISBN |
AuthorID |
ISBN |
---|---|---|---|
1 |
0132151006 |
1 |
0132575655 |
1 |
0133807800 |
1 |
0133976890 |
1 |
0133406954 |
2 |
0133406954 |
1 |
0134601548 |
2 |
0134601548 |
1 |
0134448235 |
2 |
0134448235 |
1 |
0134444302 |
2 |
0134444302 |
1 |
0134289366 |
2 |
0134289366 |
1 |
0133965260 |
2 |
0133965260 |
1 |
0134021363 |
2 |
0134021363 |
2 |
0132151006 |
3 |
0132151006 |
2 |
0133807800 |
3 |
0133406954 |
2 |
0132575655 |
4 |
0134289366 |
2 |
0133976890 |
5 |
0134289366 |
Every foreign-key value must appear as another table’s primary-key value so the DBMS can ensure that the foreign key value is valid. For example, the DBMS ensures that the AuthorID
value for any particular row of the AuthorISBN
table (Fig. 22.8) is valid by checking that there is a row in the Authors
table with that AuthorID
as the primary key.
Foreign keys also allow related data in multiple tables to be selected from those tables— this is known as joining the data. There’s a one-to-many relationship between a primary key and a corresponding foreign key—e.g., an author can write many books and a book can be written by many authors. So 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 0132151006
can appear in several rows of AuthorISBN
but only once in Titles
, where ISBN
is the primary key.
Books
DatabaseFigure 22.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—AuthorID
in the Authors
table, AuthorID
and ISBN
in the AuthorISBN
table, and ISBN
in the Titles
table. Every row must have a value in the primary-key column (or group of columns), and the value of the key must be unique in the table; otherwise, the DBMS will report an error. The names AuthorID
and ISBN
in the AuthorISBN
table are both italic—together these form a composite primary key for the AuthorISBN
table.
The lines connecting the tables in Fig. 22.9 represent the relationships among the tables. Consider the line between the Authors
and AuthorISBN
tables. On the Authors
end of the line, there’s 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 (that is, 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.
The line between the Titles
and AuthorISBN
tables illustrates a one-to-many relationship—one book can be written by many authors. Note that the line between the tables links the primary key ISBN
in the Titles
table to the corresponding foreign key in the AuthorISBN
table. Figure 22.9 illustrates 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.