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.

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.

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.

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.

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

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.