Relational Database Overview: The books Database

Relational Database Overview The books Database

This section overviews relational databases in the context of a sample books database we created for this chapter. Before we discuss SQL, we overview the tables of the books database. We use this database to introduce various database concepts, including how to use SQL to obtain information from the database and to manipulate the data. We provide a script to create the database. You can find the script in the examples directory for this chapter on the CD that accompanies this book. Section 25.5 explains how to use this script.

The database consists of four tables: authors, publishers, authorISBN and titles. The authors table (described in Fig. 25.3) consists of three columns that maintain each author's unique ID number, first name and last name. Figure 25.4 contains sample data from the authors table of the books database.

Figure 25.3. authors table from books.

Column	Description
authorID	Author's ID number in the database. In the books database, this integer column is defined as autoincremented. For each row inserted in this table, the authorID value is increased by 1 automatically to ensure that each row has a unique authorID. This column represents the table's primary key.
firstName	Author's first name (a string).
lastName	Author's last name (a string).

Figure 25.4. Sample data from the authors table.

authorID	firstName	lastName
1	Harvey	Deitel
2	Paul	Deitel
3	Tem	Nieto
4	Sean	Santry

The publishers table (described in Fig. 25.5) consists of two columns representing each publisher's unique ID and name. Figure 25.6 contains the data from the publishers table of the books database. The titles table (described in Fig. 25.7) consists of seven columns that maintain general information about each book in the database, including the ISBN, title, edition number, copyright year, publisher's ID number, name of a file containing an image of the book cover and price. The publisherID column is a foreign keya column in this table that matches the primary key column in another table (i.e., publisherID in the publishers table). Foreign keys are specified when creating a table. The 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 publisherID value for a particular book is valid. Foreign keys also allow related data in multiple tables to be selected from those tables for analytic purposesthis is known as joining the data. There is a one-to-many relationship between a primary key and a corresponding foreign key (e.g., one publisher can publish many books). This means that a foreign key can appear many times in its own table, but can only appear once (as the primary key) in another table. Figure 25.8 contains sample data from the titles table.

Figure 25.5. publishers table from books.

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

Column	Description
publisherID	The publisher's ID number in the database. This autoincremented integer is the table's primary key.
publisherName	The name of the publisher (a string).

Figure 25.6. Data from the publishers table.

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

publisherID	publisherName
1	Prentice Hall
2	Prentice Hall PTG

Figure 25.7. titles table from books.

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).
publisherID	Publisher's ID number (an integer). A foreign key that relates this table to the publishers table.
imageFile	Name of the file containing the book's cover image (a string).
price	Suggested retail price of the book (a real number). [Note: The prices shown in Fig. 25.8 are for example purposes only.]

Figure 25.8. Sample data from the titles table of books.

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

isbn	title	edition Number	copyright	publisher ID	image File	price
0131426443	C How to Program	4	2004	1	chtp4.jpg	85.00
0130384747	C++ How to Program	4	2003	1	cpphtp4.jpg	85.00
0130461342	Java Web Services for Experienced Programmers	1	2003	1	jwsfep1.jpg	54.99
0131483986	Java How to Program	6	2005	1	jhtp6.jpg	85.00
013100252X	The Complete C++ Training Course	4	2003	2	cppctc4.jpg	109.99
0130895601	Advanced Java 2 Platform How to Program	1	2002	1	advjhtp1.jpg	69.95

The authorISBN table (described in Fig. 25.9) consists of two columns that maintain each ISBN and the corresponding author's ID number. This table associates authors with their books. Both columns are foreign keys that represent the relationship between the tables authors and titlesone row in table authors may be associated with many rows in table titles, and vice versa. Figure 25.10 contains sample data from the authorISBN table of the books database. [Note: To save space, we have split the contents of this table into two columns, each containing the authorID and isbn columns.]

Figure 25.9. authorISBN table from books.

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

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 25.10. Sample data from the authorISBN table of books.

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

authorID	isbn	authorID	isbn
1	0130895725	2	0139163050
2	0130895725	3	0130829293
2	0132261197	3	0130284173
2	0130895717	3	0130284181
2	0135289106	4	0130895601

Figure 25.11 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 green are primary keys. A table's primary key uniquely identifies each row in the table. Every row must have a value in the primary key, and the value of the key must be unique in the table. This is known as the Rule of Entity Integrity.

Figure 25.11. Table relationships in books.

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

Common Programming Error 25.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 25.2

Providing the same value for the primary key in multiple rows causes the DBMS to report an error.

The lines connecting the tables in Fig. 25.11 represent the relationships between the tables. Consider the line between the publishers and titles tables. On the publishers end of the line, there is a 1, and on the titles end, there is an infinity symbol (), indicating a

Common Programming Error 25.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 authorISBN and authors tables indicates that 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. The authorID column in the authorISBN table is a foreign key matching the authorID column (the primary key) of the authors table. Note again that the line between the tables links the foreign key in table authorISBN to the corresponding primary key in table authors. The authorISBN table associates rows in the titles and authors tables.

Finally, the line between the titles and authorISBN tables illustrates a one-to-many relationship; a title can be written by any number of authors. In fact, the sole purpose of the authorISBN table is to provide a many-to-many relationship between the authors and titles tablesan author can write any number of books and a book can have any number of authors.