Comp 100 - Exam #2

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

This exam can be found at 

http://www.owlnet.rice.edu/~comp100/06-fall/exams/02/

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.

• The exam database books.mdb is available here.  The exam Excel spreadsheet exam2.xls is available here.  Save them to your U: drive.  You need not re-download these files if you have already done so in Owl-space.
• Use the database books.mdb and the spreadsheet exam2.x s on your U: drive to do all the following questions.  
• After you are all done, 
  • CLOSE EXCEL and MS ACCESS (A VERY IMPORTANT STEP!)
  •  upload your EXCEL spreadsheet and database solution to comp 100 OWLSPACE: Assignment page, exam2.
  • Hand in your signed exam papers in class on Friday, November 10, 2006.
• Do not modify the solutions on your U: drive until you are given permission to do so.

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
   • the ISBN number,
   • the title, and
   • the authors' names of each book in the database.

   

   
   

2. (10  pts) Create a query called BookChapters that displays
   • the ISBN number,
   • the chapter number,
   • the chapter title,
   • the start page,
   • the end page, and
   • the page count for each chapter of each of the books in the database.  Note: page count is a calculated field that requires a simple formula.

   

    

3. (5  pts) Create a query called BookPageCount that displays
   • the ISBN number,
   • the title, and
   • the total page count for each of the books in the database.  Note: the total page count is a calculated field that makes use of the aggregate function SUM on the page count of each chapter (see question 2).

   
    

4. (5 pts) Write a parameter query called ISBNParam that asks the user for the ISBN number of a book and displays the
   • the ISBN number,
   • the title, and
   • all the authors of the book whose ISBN number is entered by the user.
     
     
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
   • the ISBN number and
   • the title, and
   • the authors of ALL the books whose title contains the word entered by the user.
     

   
   

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

7. (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)

8. 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)

9. (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.

• At the end of the first month (month #1), the interest earned is $1000 * 5%/12 = $4.17, and the new balance is $1000 + $4.17 = $1004.17.  Note since 5% is the annual interest rate, the monthly interest rate is 5%/12.
• At the end of the second month (month #2), the interest earned is $1004.17 * 5%/12 = $4.18, and the new balance is $1004.17 + $4.18 = $1008.35.

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.

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

• 7.5% (0.075) for used car loan
• 6.5% (0.065) for new car loan.
  
  Complete the Interest Rate spreadsheet of the provided exam2.xls workbook to display the car loan rate given the car type: 0 is for used cars and any other number is for new cars.  Use the IF function (again a car type 0 means used cars, and any other number means new car).