Comp 100 Spring 2009 - Exam 2

March 11, 2009

PRINT NAME:________________________________________

Pledge of Honor

General Instructions:

AFTER THE EXAM, DO NOT TOUCH YOUR EXAM FILES ON YOUR U: DRIVE UNTIL AFTER YOU ARE INSTRUCTED TO DO SO!! THE RICE HONOR CODE FORBIDS YOU FROM DISCUSSING ANYTHING ABOUT THE EXAM TO ANYONE UNTIL THE INSTRUCTORS SAY IT IS OK TO DO SO.

 

  1. The table below shows a few Grand Slams tennis champions with the tournaments they won.  (This is not a complete list, by the way.)

    PlayerID (key)

    Name

    Country

    Tournaments

    RF1

    Roger Federer

    Switzerland

    Wimbledon 2007

    Wimbledon 2006

    US Open 2008

    US Open 2007

    VW1

    Venus Williams

    USA

    Wimbledon 2007

    Wimbledon 2008

    US Open 2001

    French Open 2002

    JH1

    Justine Henin

    Belgium

    US Open 2007

    Australian Open 2004

    French Open 2007

    French Open 2006

    Wimbledon 2006

    RN1

    Rafael Nadal

    Spain

    French Open 2008

    French Open 2007

    Australian Open 2009

    Wimbledon 2008

     a/  (5 pts) What normal form does the above table design violate?  Justify your answer. 

     

     

     

      

     

    b/  (20 pts) Create a database called GrandSlams to correct the design problem of the above table by designing a new set of appropriate related tables that do not violate any normal forms. Create the appropriate referential integrity relationship.   Be sure to use the Lookup wizard to create the appropriate link.  Save the relationship diagram.  Enter the above data in the appropriate tables.


     

  2. Open the Agency.accdb database.

     

    1. (15 pts) Create and save a query called qryRorWagencyDonation that find and displays all the records in the tblDonation table in which the AgencyID starts with either the letter R or the letter W.  Display all the fields from the table in the result.  Sort the results in descending order of the DonationValue field.
       
    2. (15 pts) Create and save a query called qryDonationAfterPickupCharge that displays the DonorID, the AgencyName, the DonationDesc, the DonationValue and a calculated field called NetDonation  of only those donations that require pickup.  The NetDonation is computed by subtracting $8.75 from the DonationValue.  Rename the caption of this calculated field to Net Donation.
       
    3. (10pts)  Create and save a query called qryAgencyContactName that displays all records from the tblAgency table, showing all fields except the ContactFirstName and ContactLastName.  Add a calculated field called ContactNameas the third column that concatenates the ContactFirstName, a space and ContactLastName.  Set the caption property of this calculated field to show Contact Name. 
       
    4. (10 pts) Create a find duplicates query based on the provided qryNetDonations query that selects the DonorID and the AgencyName as the fields that might contain duplicates.  Display all available fields.  Save the query as qryMultipleDonorDonations.
       
    5. (10 pts) Create a parameter query named qryDonationDescParameter that prompts for the description of the donation (the DonationDesc field) and displays all fields of only the records from the tblDonation table that match the input description.  If the user does not enter a value, then the query should display all the records.  Save the query.
       
    6. (15pts) Create and save a crosstab query named qryNetDonationCrosstab based on the provided qryNetDonation.  Use the AgencyName field values for the row headings, the PickupRequired field for the column headings, the sum of the NetDonation field values as the summarized value, and include row sums.  Change the Resize the columns in the query recordset to their best fit.  The query result should look like the one shown in the file qryNetDonationCrosstab.pdf.

    When finished, be sure to close MS ACCESS before uploading any database files.

BE SURE YOU UPLOAD ALL FILES (GrandSlams.accdb and Agency.accdb),  TO YOUR OWL-SPACE EXAM 2 LOCATION.

Hand in in class, your signed Honor Pledge AND all exam materials.