CIS200 – Homework #7 – Simple Queries



Name_______________________________ Lecture Time ________________

CSE1111 – Homework #6 (10 points) – Simple Queries

Books

|Book# |Title |Author |Type |Price |

|23456 |1812 |Vidal |4 |$39 |

|834592 |Gone With the Wind |Mitchell |4 |$19 |

|4539 |Webster’s Unabridged Dictionary |Webster |3 |$86 |

|33982 |Hunt for Red October |Clancy |1 |$22 |

|33983 |Hunt for Red October |Clancy |1 |$22 |

|43928 |Joy of Cooking – 5th Edition |Smith |2 |$45 |

Circulation

|Borrow# |Book# |Date Borrowed |Returned |

|A83765 |4539 |7/1/98 |Yes |

|A83765 |33982 |7/1/98 |No |

|C52938 |4539 |7/4/98 |No |

|C77328 |43928 |7/6/98 |Yes |

Cardholders

|Borrower# |LastName |FirstName |Address |City |State |Zipcode |

|A23457 |Smith |Michael |222 East 7th ST |Bexley |OH |43209 |

|C52938 |Jones |Jennifer |18 Main Street |Dublin |OH |43218 |

|A83765 |Walters |John |55 Elm Street |Columbus |OH |43213 |

The tables above represent the database system for the Fergie Library. The Book table lists the names of all the books and periodicals owned by the library. The Books table describes the materials the library owns. The Cardholder table lists the names of all authorized library users. The Circulation table is a running list of books that are borrowed. When books are borrowed they are entered onto this table and when they are returned the original entry is noted returned.

1. (1 points ) Database Relationships. Set up the relationships of this database. Using the boxes below, fill in the primary key (if any) of each table and draw relationship lines between tables. Label each relationship with the name of the foreign key(s) just above the line you have drawn.

2. (1 points) What field type (Text, Number, Currency, Date/Time, Yes/No, AutoNumber) is best suited for each of the following fields:

Borrower# (Cardholder Table) _______________________________

Date Borrowed (Circulation Table) _______________________________

Title (Books Table) _______________________________

Returned (Circulation Table) _______________________________

Zipcode _______________________________

Price _______________________________

3. (1/2 point) If in table Books, primary key Book# has type of Text, and in table Circulation, foreign key Book# has type of Number, will Access give you a valid relationship between the 2 tables ? Explain your answer.

Please note when writing query grids, to receive full credit you must use correct Access syntax. The query should look identical to how it looks when the computer runs it. Be sure to include Like, “”, and # symbols as needed. Do not use unnecessary tables, fields or criteria in your query.

4. (1 points) The librarian wants to get an idea of what books are circulating. Using the query design view below, construct a query to list all books that have been borrowed at any time from the library. Include book#, borrower# and date borrowed. Sort list by book#.

Tables Used _______________________________

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

|OR | | | | |

5. (1 points) Sometimes people forget their library card and the librarians would like a quick paper list of cardholders for reference. Using the query design view below, construct a query to list the full name and borrower# of all cardholders, sorted alphabetically by last name and then by first name.

Tables Used ___________________________

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

6. (1 points) One of the library staff is considering additional purchases of adventure novels (type 1 books) and has requesting a list of all existing books in that category. The list may contain duplicate titles. Using the query design view below, construct a query to list the book titles and authors of all type 1 books.

Tables Used ________________________________-

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

7. (1/2 point) What dynaset will result from the previous query? (include field titles)

| | | |

| | | |

| | | |

| | | |

| | | |

8. (1 points) Another librarian is re-organizing the shelves alphabetically and needs a list of all books whose author’s last name begins with C or S. Prepare a list of the Title and Author of all books which author’s last name begins with the Letters S or C. Sort the list alphabetically by Author.

Tables Used ________________________________

| Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

|OR | | | | |

9. (1 points) Again additional purchases are being considered to the library’s collection and the librarian would like a feel for the current books and prices of specific types. Thus you are requested to prepare a list of book titles for all books that are either type 1 and cost less than $25 or type 2 and cost less than $50.

Tables Used _______________________________

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

|OR | | | | |

10. (1 points) Certain authors are more popular than others, to understand how many of which author’s books are in the library’s collection, write a query that will summarize the books by author. The query should list the author’s name and the total number of books in the collection by that author and the total value (based on price) of all of their books.

Tables Used ___________________________________

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

|OR | | | | |

11. (1 points) The library has a continuing problem with lost books and has found that the purchase price of the book does not cover the replacement costs. These additional costs include shipping and administrative expenses. Therefore, the library has decided to charge the price of the book plus 10% to all borrowers who fail to return a library book. Prepare a list by book# to display these costs for each book in the library’s collection. Include the book#, title, price of the book, additional costs (10% of book value), and total replacement cost.

Tables Used _____________________________________

|Field | | | | | |

|Table | | | | | |

|Total | | | | | |

|Sort | | | | | |

|Show | | | | | |

|Criteria | | | | | |

|OR | | | | | |

|OR | | | | | |

-----------------------

Table name:

______________

Primary Key

_______________

Table name:

Primary Key

Table name:

Primary Key

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download