Chapter 1 2 3 4 5 6 7 8 9 10 11 12 13 14

Principles of MIS

© 2005 by Jerry Post

McGraw-Hill/Irwin

C04Ex15.mdbIt is best to answer the first 15 exercise questions using a DBMS, but if one is not available, you can use the tables in the text and write your queries by hand.

1. List the customers who live on Main Street (in any city).
2. List the customers who have an account balance between $200 and $600, sorted by account balance in descending order.
3. What is the total amount of money owed us by customers who live in Denver?
4. How many orders were placed in May?
5. What is the smallest order ever placed (in terms of amount)?
6. Which salesperson sold the most expensive item in May?
7. What was the best day in terms of highest total value of orders?
8. Calculate the total commissions owed to salesperson West. Hint: You need to compute the total of commission multiplied by order amount.
9. Get the name and phone number of customers who bought blue jeans in June 2006.
10. Who is the best salesperson?
11. Which customer bought the most number of brooms?
12. Which salespeople placed orders in June?
13. In which city did the company sell the most brooms?
14. What is the lowest price item available for sale?
15. Who was the first customer (made the earliest purchase)?
16. A friend of yours has just opened a photofinishing operation. She wants you to create a database system to help her run the business. The basic processing is straightforward: A customer drops or mails in one or more rolls of film. A clerk records the basic data on the customer and the film. The rolls are assigned a number, sorted, and run through the processor. Processing varies slightly depending on the type of film, film speed, and processing options. Your friend wants to keep track of which clerk performed the processing and match the names with any complaints that might arise. She also wants to offer a frequent-buyer program to reward the most active customers. It is also important to track the chemical usage for the processing—both to keep track of orders and expenses, and to make sure the processors always have fresh chemicals. The clerks are also responsible for cleaning the processing equipment. Create a set of normalized tables for this company. Identify attributes where possible. (Hint: Obtain a film mailer that lists various options.)



17. You have been hired by an environmental consulting company (ECC) that specializes in creating environmental impact statements (EISs) for large projects. It needs a database to track the progress of each EIS. The company is particularly concerned about tracking public comments, questions from state and federal officials, and the responses to all of these comments. All comments are scanned and stored as digital files. Create a list of normalized tables needed to build this database.

Technology Toolbox

18. Create an input screen that enables a clerk to update information and add new customers to the database.
19. Using the tables in the chapter, create an order-entry input screen that can be used by a clerk who knows nothing about databases.
20. Create an inventory report that lists all of the products, group them by category, and within each category, sort them by ID number.
21. Create the customer order report that is described in the chapter. Hint: First create a view that joins the appropriate tables together.
22. Create a startup form that can be used as a menu. Begin in Design View and add buttons that open the other forms and reports. Use colors or images to enhance the appearance of the form.

Teamwork

23. Select a business. Each person should choose five entities (objects) that might be used as database tables. Identify primary keys for each table. Share your tables with the rest of the team and combine the results to one set of consistent tables.
24. As a team, identify features on a website that would indicate that is has a database behind it. Using this discussion, each person should find at least three websites that rely on a DBMS. Combine the results into a team report.
25. Assume that you need to buy a DBMS for a midsize company. Research the components needed and have each person find information and evaluate a DBMS package. Try to identify costs as well as strengths and weaknesses of the package. Share the individual results and create a report that makes a recommendation.
26. With the cooperation of a local small business, create a database for that company. Note that you should verify the initial layout of the tables with your instructor or someone who has studied database design. Assign specific forms and reports to individual team members and combine the pieces.
27. Each team member should write up three business questions related to either the C04Ex15.mdb or Rolling Thunder database. Exchange the questions with the other team members, and then create the queries to answer each question. Share your answers.

Rolling Thunder Database
Create queries to answer the following questions. (Difficulty level: 1 = easiest.)

28. List the customers who live in Colorado. (2)
29. How many employees does the firm have? (2)
30. List the bikes shipped to Oregon in December 2004. (1)
31. What is the most number of race bikes ever assembled (StartDate) in one day? (3)
32. Which employee has painted the most mountain bikes? (3)
33. What is the total estimated cost of components of all bikes ordered in October 2004? (3)
34. Which paint color was most popular on mountain bikes in 2003? (3)
35. List the phone numbers for all California customers who bought race bikes from Ochirbat in November 2004. (2)
36. What is the most expensive race bike built? (2)
37. What is the most popular crank installed on race bikes in 2005? (3)
38. Assuming that a race frame weighs two pounds, what is the total weight of the lightest race bicycle built in 2005? Note: component weights are in grams so be sure to convert them. (4)
39. How many mountain bikes, or full suspension mountain bikes larger than 17 inches (FrameSize) were sold in Florida in 2003? (2)
40. What is the total salary cost? (2)
41. What is the average price Rolling Thunder Bicycles paid for Shimano XT derailleurs (rear)? (4)
42. On average, shipping costs are what percentage of the total order for merchandise purchased in August 2004? (3)
43. List the component items that had no sales in July 2005. (5)
44. On average, which costs more, tires for road bikes or tires for mountain bikes? (3)
45. Compute the sales by model type by month for all years. (5 Hints: Use Crosstab and Format(date, “yyyy-mm”))
46. What percentage of sales came through retail stores in December 2001? Hint: Use two queries and compute the percentage with a calculator (4)
47. List all of the retail bicycle stores in your state. (2)