Relational Database Overview: Books Database

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 tables of the Books database. Then 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 fileBooks.mdfwith the examples for this chapter (downloadable from www.deitel.com/books/csharphtp2/). SQL Server database files typically end with the .mdf ("master data file") filename extension. Section 20.6 explains 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. 20.3) consists of three columns that maintain each author's unique ID number, first name and last name, respectively. Figure 20.4 contains the data from the Authors table. We list the rows in order by the table's primary keyAuthorID. You will learn how to sort data by other criteria (e.g., in alphabetical order by last name) using SQL's ORDER BY clause in Section 20.4.3.

Figure 20.3. Authors table of the Books database.

(This item is displayed on page 997 in the print version)

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 columnfor 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).

Figure 20.4. Data from the Authors table of the Books database.

(This item is displayed on page 997 in the print version)

AuthorID	FirstName	LastName
1	Harvey	Deitel
2	Paul	Deitel
3	Andrew	Goldberg
4	David	Choffnes

 

Titles Table of the Books Database

The Titles table (described in Fig. 20.5) consists of four columns that maintain information about each book in the database, including the ISBN, title, edition number and copyright year. Figure 20.6 contains the data from the Titles table.

Figure 20.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.
Title	Title of the book (a string).
EditionNumber	Edition number of the book (an integer).
Copyright	Copyright year of the book (a string).

Figure 20.6. Data from the Titles table of the Books database.

ISBN	Title	Edition-Number	Copy-right
0131426443	C How to Program	4	2004
0131450913	Internet & World Wide Web How to Program	3	2004
0131483986	Java How to Program	6	2005
0131525239	Visual C# 2005 How to Program	2	2006
0131828274	Operating Systems	3	2004
0131857576	C++ How to Program	5	2005
0131869000	Visual Basic 2005 How to Program	3	2006

AuthorISBN Table of the Books Database

The AuthorISBN table (described in Fig. 20.7) consists of two columns that maintain IS-BNs for each book and their corresponding authors' ID numbers. This table associates authors with their books. The AuthorID column is a foreign keya 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 keyit 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 20.8 contains the data from the AuthorISBN table of the Books database.

Figure 20.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.

Figure 20.8. Data from the AuthorISBN table of Books.

AuthorID	ISBN
1	0131869000
1	0131525239
1	0131483986
1	0131857576
1	0131426443
1	0131450913
1	0131828274
2	0131828274
2	0131450913
2	0131426443
2	0131857576
2	0131483986
2	0131525239
2	0131869000
3	0131450913
4	0131828274

Foreign Keys

Foreign keys can be specified when creating a table. A foreign key helps maintain the Rule of Referential Integrityevery 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 tablesthis is known as joining the data. (You will learn how to join data using SQL's INNER JOIN operator in Section 20.4.4.) There is a one-tomany 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 can appear only once (as the primary key) in another table. For example, the ISBN 0131450913 can appear in several rows of AuthorISBN (because this book has several authors), but can appear only once in Titles, where ISBN is the primary key.

Entity-Relationship Diagram for the Books Database

Figure 20.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 italictogether these form a composite primary key for the AuthorISBN table.

Figure 20.9. Entity-relationship diagram for the Books database.

Common Programming Error 20 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 20 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. 20.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, there is an infinity symbol (). This indicates a

Common Programming Error 20 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 relationshipa 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. 20.9 illustrate that the sole purpose of the AuthorISBN table is to provide a many-to-many relationship between the Authors and Titles tablesan author can write many books, and a book can have many authors.