Comp 100 - Exam #2

Rice University - Fall 2006 (November 08, 2006) 100 points total

This exam can be found at


  1. The examination is open book/notes
  2. Do not discuss the exam with anyone else until you are given the permission by the instructors to do so.
  3. Fill in and sign the honor pledge below.
  4. You have 2.5 (two and a half) hours to complete the exam.
Pledge of Honor (Sign and PRINT your name)

Queries, Forms and Reports (55 points total)

The tables in books.mdb represent books, their authors and the chapters and pages in each of of the books.

  1.  (5  pts) Create a query called BookAuthors that displays

  2. (10  pts) Create a query called BookChapters that displays


  3. (5  pts) Create a query called BookPageCount that displays


  4. (5 pts) Write a parameter query called ISBNParam that asks the user for the ISBN number of a book and displays the
  5. (10 pts) Write a parameter query called TitleParam that asks the user for a word that may appear anywhere in the title and displays the

  6. (10 pts) Create a multi-page form based on the Books table, the Chapters table and the BooksAuthors table as shown below.

  1. (10 pts) Use the Report Wizard to create a report called BookDetails as shown in the following file: BookDetailsReport.pdf


Normal Forms (15 points total)

  1. In the books.mdb database, there is a table called Movies.  This table is designed to record information about movies, their directors and their casts.
    1. (5 pts) Carefully examine the data and the design of the Movies table and explain what normal forms the table violates and why. Place your answer in the description of the table design next to the offending field(s).

    2. (10 pts) In the books.mdb database, create appropriate normalized tables with appropriate referential integrity links, to fix the above problem and re-enter the data.  Use the lookup wizard for the relationship links.  Display the normalized table clearly in the relationship diagram.  Write the name of the tables you created here.   Do NOT modify the Movies table. 


EXCEL (30 points total)

  1. (20 pts) A bank advertises a savings account with an annual percentage rate (APR) of 5% compounded monthly.  What this means is the interest is computed each month based on the current balance and added to the current balance resulting in a higher new balance.  Then when the next month comes, the interest is computed on this balance and added to the balance to obtain yet a higher new balance.  For example, suppose the initial deposit is $1000.00.

Complete the Compound Interest spreadsheet in the provided exam2.xls workbook to model the above problem and compute the monthly earned interest, the ending balances and the cumulative earned interests for the first 36 months for a given initial deposit.

  1. (10 pts) A bank advertises two interest rates for car loan.