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.
|
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). |
|
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.
|
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). |
|
publisherID |
publisherName |
|---|---|
|
1 |
Prentice Hall |
|
2 |
Prentice Hall PTG |
|
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.] |
|
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.]
|
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. |
|
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 (
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.