Fall Semester 2003

Similar to the management of Premiere Products, Ray Henry, the owner of a bookstore chain named Henry Books, has decided to store his data in a database. He wants to achieve the same benefits: that is, he wants to ensure that his data is current and accurate. He also needs to create forms to interact with the data and to produce reports from that data. In addition, he wants to be able to ask questions concerning the data and to obtain answers to these questions easily and quickly.

In running his chain of bookstores, Ray gathers and organizes information about branches, publishers, authors, and books. Figure 1.16 shows sample branch and publisher data for Henry Books. Each branch has a number that uniquely identifies the branch. In addition, Ray tracks the branch's name, location, and number of employees. Each publisher has a code that uniquely identifies the publisher. In addition, Ray tracks the publisher's name and city.

Figure 1.17 shows sample author data for Henry Books. Each author has a number that uniquely identifies the author. In addition, Ray records each author's last and first names.

Figure 1.18 shows sample book data for Henry Books. Each book has a code that uniquely identifies the book. For each book, Ray also tracks the title, publisher, type of book, price, and whether the book is a paperback.

The table named Wrote, shown in Figure 1.19a, is used to relate books and authors. The Sequence field indicates the order in which the authors of a particular book are listed on the cover. The table named Inventory in the same figure (1.19b)is used to indicate the number of copies of a particular book that are currently on hand at a particular branch of Henry Books. The first row, for example, indicates that there are two copies of the book with the number 0180; they are on hand at branch 1.

In later chapters you will be asked to perform many tasks to help Ray Henry manage and manipulate his database. In order to familiarize yourself with this database in preparation for these tasks, answer each of the following questions using the Henry Books data shown in Figure 1.16 through 1.19 (see the links above).

No computer work is required.

1. How many total employees work at all branches of Henry Books?

2. List the name of each publisher that's not located in New York.

3. List the title of each book published by Scribner.

4. List the title of each book that has the type ART.

5. List the title of each book that has the type MYS and that is in paperback.

6. List the title of each book that has the type HOR or whose publisher code is SC.

7. List the titles of paperback books that have the type FIC and a price of less than $8.00.

8. List the title of each book that has the type HIS or PHI

9. List the title and publisher name for all books with a price greater than $20.00.

10. For each book with co-authors, list the title, price and author names (in the order listed on the cover).

11. For each book with more than six total copies on hand across all branches, list the book's title and price.

12. List the title of each book that has the type FIC and that was written by John Steinbeck.