Comp 100 Spring2007 - Exam 1

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.

MS Powerpoint Questions (30 Points Total)

If you have not done it already prior to starting the exam, click here to download the PowerPoint exam file: exam1.ppt.  Unless you are working on your computer, be sure to save in your drive U:. 

Modify the presentation as indicated on the presentation itself.  Remember to save your work often.

When finished, be sure to save the file and close MS PowerPoint.

Because you can only submit once to OWLSPACE, do not upload to OWLSPACE until you finish the Access part of the exam as well.

 

MS Access Questions (70 Points Total)

  1. (20 pts) Track and Field consists of many events such as 100 m dash, 200 m dash, long jump, high jump, etc.  Rice has many talented track and field athletes that can compete in many such events.  For example, a Rice athlete may compete in the 100 m, 200 m and the long jump.  What kind of relationship exists between the track and field events and the Rice athletes? Write down your answer here:


     

    Design a MS ACCESS database called TrackField.mdb that can help a coach keep track of all the track and field events, all the track and field athletes, all the events that each one of these athletes can compete in, and his/her best time or height or distance.

    Create the relationship diagram to set and display all appropriate referential integrity links.  Be sure to set cascade update and cascade delete also. 

    Do not enter any data.

     

  2. If you have not done it already prior to starting the exam, click here to download the exam1.mdb database.  This database keeps track of the products (Products Table) sold by some business, the customers (Customers table), each of the customers'  orders (CustomersOrders table), and the products purchased in each of the orders (OrdersProducts table).  Create the following queries (10 pts each)
    1. Create a query called Prior2007Orders that displays the OrderID, the CustomerID and the dates of all orders made prior to the year 2007.
    2. Create a query called All2006Orders that displays the OrderID, the CustomerID and the dates of all orders made in the year 2006 only.
    3. Create a query called AllOrders that displays all the customers' names, their OrderID and their dates, the product names, their prices, and their quantities.
    4. Create a query called AllOrdersTotals that displays the customers' names, their OrderID and their dates, the product names and the total amount of each of the ordered products as shown in the below.  Note: total amount = price * quantity.

       
      AllOrdersTotals
      Name OrderID OrderDate Product Totals
      Wilson, Rachel OR10311 6/16/2007 Long Sparklers (Box 20) $19.95
      Wilson, Rachel OR10311 6/16/2007 Goblin Fountain $29.95
      Wilson, Rachel OR10311 6/16/2007 Dragon Fountain $25.50
      Wilson, Rachel OR10311 6/16/2007 Nighteyes (box 10) $119.80
      Wilson, Rachel OR10311 6/16/2007 Assorted Items Box #1 $59.85
      Wilson, Rachel OR09114 6/13/2006 Assorted Items Box #2 $59.90
      Wilson, Rachel OR09114 6/13/2006 Phoenix Fountain $68.84
      Wilson, Rachel OR09114 6/13/2006 Firecracker 80 Strings of 16 $52.50
      Wilson, Rachel OR09114 6/13/2006 Bottle rockets with stars and report (box 20) $55.96
      Kemper, Kevin OR10899 5/14/2007 Moon Rockets 12 packs of 12 $14.52
      Kemper, Kevin OR10899 5/14/2007 Firecracker 80 Strings of 16 $7.50
      Kemper, Kevin OR10899 5/14/2007 Crackling Balls (box 16) $19.95
      Kemper, Kevin OR10899 5/14/2007 Long Sparklers (Box 20) $39.90
      Kemper, Kevin OR10899 5/14/2007 Goblin Fountain $59.90
      Kemper, Kevin OR09916 5/11/2006 Time Bomb $297.50
      Kemper, Kevin OR09916 5/11/2006 Bucket of Fireworks $29.00
      Kemper, Kevin OR09916 5/11/2006 Crackling Balls (box 16) $39.90
      Kemper, Kevin OR09916 5/11/2006 Long Sparklers (Box 20) $19.95
      Kemper, Kevin OR09916 5/11/2006 Nighteyes (box 10) $149.75
      Kemper, Kevin OR09916 5/11/2006 Firecracker 80 Strings of 16 $15.00
      Kemper, Kevin OR08892 5/17/2005 Nightair Fountain $149.75
      Kemper, Kevin OR08892 5/17/2005 Firecracker 80 Strings of 16 $7.50
      Kemper, Kevin OR08892 5/17/2005 Crackling Balls (box 16) $19.95
      Miller Foods OR10211 6/20/2007 Rock The Sky Rocket (case) $109.99
      Miller Foods OR10211 6/20/2007 Tracer Fire Rockets (case) $95.99
      Greenbush County OR11560 6/11/2007 Electric Reactor (case) $367.50
      Greenbush County OR11560 6/11/2007 Tracer Fire Rockets (case) $575.94
      Greenbush County OR11560 6/11/2007 Gold Ring With Tail (case) $285.00
      Greenbush County OR11560 6/11/2007 Blue Ring With Tail (case) $1,425.00
      Greenbush County OR11560 6/11/2007 Red Ring Shell (case) $275.00
      Greenbush County OR11560 6/11/2007 Green Ring Shell (case) $137.50
      Greenbush County OR11560 6/11/2007 Midnight Barrage (case) $267.00
      Greenbush County OR11560 6/11/2007 Blue Palm w\Tail (case) $486.00
      Greenbush County OR11560 6/11/2007 Red Palm w\Tail (case) $121.50
      Greenbush County OR11560 6/11/2007 Gold Palm w\Tail (case) $121.50
      Greenbush County OR11560 6/11/2007 Whistling Artillery (case) $187.00
      Greenbush County OR09917 6/15/2006 Star Cruiser (case) $143.99
      Greenbush County OR09917 6/15/2006 Strobing Night Parachute (case) $121.50
      Greenbush County OR09917 6/15/2006 Gold Ring With Tail (case) $142.50
      Greenbush County OR09917 6/15/2006 Blue Ring With Tail (case) $142.50
      Greenbush County OR09917 6/15/2006 Red Ring Shell (case) $412.50
      Greenbush County OR09917 6/15/2006 Green Ring Shell (case) $687.50
      Greenbush County OR09917 6/15/2006 Palm Party (case) $141.50
      Greenbush County OR09917 6/15/2006 Bootlegger Bomb (case) $554.00
      Greenbush County OR09917 6/15/2006 Assorted Box: Green, Blue, Gold, Ring W/Palm $327.00
      Greenbush County OR09917 6/15/2006 Whistling Artillery (case) $187.00
      Greenbush County OR08715 6/12/2005 Double Break Finale (case) $147.83
      Greenbush County OR08715 6/12/2005 Electric Reactor (case) $122.50
      Greenbush County OR08715 6/12/2005 Gold Ring With Tail (case) $285.00
      Greenbush County OR08715 6/12/2005 Blue Ring With Tail (case) $427.50
      Greenbush County OR08715 6/12/2005 Red Ring Shell (case) $550.00
      Greenbush County OR08715 6/12/2005 Green Ring Shell (case) $137.50
      Greenbush County OR08715 6/12/2005 Midnight Barrage (case) $267.00
      Greenbush County OR08715 6/12/2005 Assorted Box: Green, Blue, Gold, Ring W/Palm $327.00
      Greenbush County OR08715 6/12/2005 Whistling Artillery (case) $280.50
      Lungren, Uwe OR10115 6/29/2007 Long Sparklers (Box 20) $99.75
      Lungren, Uwe OR10115 6/29/2007 Assorted Items Box #3 $39.95
      Lungren, Uwe OR10115 6/29/2007 Pyro blast Assortment $59.95
      Lungren, Uwe OR10115 6/29/2007 Firecracker 40 Strings of 16 $149.75
      Adams, Bernard OR11058 6/2/2007 Pyro blast Assortment #2 $159.90
      Adams, Bernard OR11058 6/2/2007 Screaming Dragons $29.95
      Adams, Bernard OR11058 6/2/2007 Whistling Artillery (case) $93.50

     

    1. Create a query called MostPopular products that displays the product names and their respective total quantity ordered sorted in descending order, as shown in the below. Hint: use the aggregate function sum.
       
      MostPopularProducts
      Product Total Quantities
      Blue Ring With Tail (case) 14
      Firecracker 80 Strings of 16 11
      Long Sparklers (Box 20) 9
      Nighteyes (box 10) 9
      Red Ring Shell (case) 9
      Whistling Artillery (case) 8
      Green Ring Shell (case) 7
      Time Bomb 7
      Tracer Fire Rockets (case) 7
      Gold Ring With Tail (case) 5
      Firecracker 40 Strings of 16 5
      Nightair Fountain 5
      Electric Reactor (case) 4
      Blue Palm w\Tail (case) 4
      Midnight Barrage (case) 4
      Bootlegger Bomb (case) 4
      Bottle rockets with stars and report (box 20) 4
      Assorted Box: Green, Blue, Gold, Ring W/Palm 4
      Crackling Balls (box 16) 4
      Assorted Items Box #1 3
      Goblin Fountain 3
      Assorted Items Box #2 2
      Bucket of Fireworks 2
      Pyro blast Assortment #2 2
      Phoenix Fountain 2
      Palm Party (case) 1
      Pyro blast Assortment 1
      Double Break Finale (case) 1
      Rock The Sky Rocket (case) 1
      Gold Palm w\Tail (case) 1
      Strobing Night Parachute (case) 1
      Star Cruiser (case) 1
      Red Palm w\Tail (case) 1
      Moon Rockets 12 packs of 12 1
      Assorted Items Box #3 1
      Screaming Dragons 1
      Dragon Fountain 1


       

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

BE SURE YOU UPLOAD ALL FILES (PowerPoint file and Access files),  TO YOUR OWL-SPACE EXAM 1 LOCATION.

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