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.

Introduction to Computers, the Internet and the World Wide Web

Introduction to Java Applications

Introduction to Classes and Objects

Control Statements: Part I

Control Statements: Part 2

Methods: A Deeper Look

Arrays

Classes and Objects: A Deeper Look

Object-Oriented Programming: Inheritance

Object-Oriented Programming: Polymorphism

GUI Components: Part 1

Graphics and Java 2D™

Exception Handling

Files and Streams

Recursion

Searching and Sorting

Data Structures

Generics

Collections

Introduction to Java Applets

Multimedia: Applets and Applications

GUI Components: Part 2

Multithreading

Networking

Accessing Databases with JDBC

Servlets

JavaServer Pages (JSP)

Formatted Output

Strings, Characters and Regular Expressions

Appendix A. Operator Precedence Chart

Appendix B. ASCII Character Set

Appendix C. Keywords and Reserved Words

Appendix D. Primitive Types

Appendix E. (On CD) Number Systems

Appendix F. (On CD) Unicode®

Appendix G. Using the Java API Documentation

Appendix H. (On CD) Creating Documentation with javadoc

Appendix I. (On CD) Bit Manipulation

Appendix J. (On CD) ATM Case Study Code

Appendix K. (On CD) Labeled break and continue Statements

Appendix L. (On CD) UML 2: Additional Diagram Types

Appendix M. (On CD) Design Patterns

Appendix N. Using the Debugger

Inside Back Cover



Java(c) How to Program
Java How to Program (6th Edition) (How to Program (Deitel))
ISBN: 0131483986
EAN: 2147483647
Year: 2003
Pages: 615

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net