CSCI 100 Access Exam

1. Download the file http://employees.oneonta.edu/allisodl/csci100/library3.zip and save it to your P: drive. Open the database.

2. (Database maintenance: 50 points) Open the Libraries table. Add a new library named Don Allison Personal Library, with your name replacing the words Don Allison. List yourself as the head librarian, the city as Oneonta, the state as NY, the zip as 13820, the street address as 1 Ravine Pkwy, and the phone number as 607-436-2000.

Open the Books table. Add three books with yourself as the author, SUCO Press as the publisher, 2005 as the publication year, and BOOKF as the call number. The first book is called The Color Blue, and is 941 pages long; the second is called The Number Seven and is 658 pages long; and the third is called The Letter Q and is 387 pages long.

Open the Holdings table. Add libraries 16, 31, 36, and 42 for book 104; add libraries 12, 18, 23, and 42 for book 105; add libraries 1, 6, 17, 24, and 42 for book 106. Change the library for book 68 from library 13 to 42, and for book 36 from library 36 to 42.

Print out the book ID from the Holdings table for all books in Library 42. Print out the number, call number, author, title, publisher, year, and pages for all books in the Books table that were published in 2005. Print out the library ID, library name, address, city, state, zip, phone number, and head librarian for all libraries in the town of Oneonta.

3. (Forms: 10 points) Generate an autoform for the Libraries table. Print out only the record for library ID 42.

4. (Relationships: 20 points) Set up relationships between the Books and Holdings tables, and between the Holdings and Libraries tables. Enforce referential integrity, and cascade updates and deletes for both relationships. Print out the author, title, year, library name, head librarian, and zip code of all the books that have you as the author. There should be 13 of them, based on the changes you made in question 2.

5. (Reports: 10 points) Create a report from the Books table listing the authors, titles, publishers, years, and numbers of pages. Group by publisher, and under publisher, group by author. Sort ascending by number of pages. Use a stepped layout, portrait orientation, and adjust so that all fields fit on one page. Use Casual style, and name your report Book List. Print out only the page containing SUCO Press of the report.

6. (Queries: 10 points) Print the call number, author, and title of all books that were published in 1995 or later and whose titles start with the letter T. Print the library name, city, state, and head librarian for all libraries in area code 607 that are in towns whose names start with a F, O, or S.

7. Arrange your printouts in the order generated and staple this sheet to the top of them. Write your name on this sheet.