Home > Articles > Programming > Windows Programming

  • Print
  • + Share This
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.

Figure 21.9

Fig. 21.9 Entity-relationship diagram for the Books database.

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.

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.

  • + Share This
  • 🔖 Save To Your Account