Return to main page

Chapter 4 Exercises

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

Principles of MIS

© 2008 by Jerry Post
  1. List the customers who live in Denver. (1)
  2. List the Salespeople who were hired in 1991. (1)
  3. List the Items in the Electronics category. (1)
  4. How many sales were made in June? (2)
  5. How many customers have an account balance of more than $300? (2)
  6. What is the average price of items in each category? (3)
  7. List the customers who have purchased LCD TVs. (3)
  8. List the items sold in June, with the most expensive listed first. (3)
  9. How many sales have been made by each salesperson? (4)
  10. Who are the best customers in terms of total purchase value? (4)
  11. Which item category is the best seller in terms of value? (4)
  12. What is the total value of sales in June? (4)
  13. Which salesperson had the highest sales value in the first half of the year? (4)
  14. Write queries to test if the Amount value in the Sales table is correct for all sales. (5) Hint: Save a separate query to perform the computations.
  15. Which items had no sales in June? (5) Hint: This query requires a LEFT JOIN or a subquery, which are not covered in this book.
  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.

    18. Toolbox logoTechnology Toolbox

  18. For the Sales database, create input screens for Items, Customers, and Salespeople. Add an ItemCategory table that holds a list of the categories so you can add a combo box to the Items form to select from the list of categories.
  19. Using a different design, rebuild the Sales form.
  20. Add a button to the Sales form that enables users to open the Customer form to edit the data or enter a new customer.
  21. Create an inventory report that lists all of the products; group them by category; and, within each category, sort them by price.
  22. Create the customer sales report that is described in the chapter.
  23. Create a start-up 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.

    24. Teamwork logoTeamwork

  24. 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.
  25. Assign the tables in the Sales database to each person on the team. As a group, identify the primary users of the system. Each person should then specify the security access rights for each user on each table: SELECT, INSERT, UPDATE, or DELETE.
  26. 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.
  27. 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.
  28. 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.

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

  29. List the SalePrice of Road bicycles ordered in July 2005. (1)
  30. List the Road components introduced after 2003 with a weight greater than 400 grams but do not include wheels. (2)
  31. List all of the employees with a salary of more than $40,000. (1)
  32. List the purchase orders placed in January 2007 in descending order by total list price. (1)
  33. List the cities with a 2000 population over 1,000,000 in ascending order of population. (1)
  34. List the customers who live in Miami, Florida. (2)
  35. List the mountain bikes, including full suspension, that were ordered in November 2007 and have red in their color. (2)
  36. List the components that were installed on bicycle 35851. (2)
  37. List the employees who painted Race bicycles in July 2006. (2)
  38. List the components of the Campy Record 2006 groupo sorted by item weight. (2)
  39. What is the most popular letter style on Race bikes ordered in December 2006? (3)
  40. Which employee sold the most bikes by value in 2007? (3)
  41. Who were the best customers in 2006 based on sales value? (3)
  42. With which manufacturer did Rolling Thunder spend the most money in 2007? (3)
  43. What is the average weight of a rear derailleur built after 2000 for road bikes? (3)
  44. How many bicycles were sold in each state in 2007? (3)
  45. Based on the number of bikes, what was the most popular crank installed on mountain or full suspension mountain bikes in 2007? (3)
  46. Compare the average percent discount (ListPrice-SalePrice)/ListPrice for the various model types sold in 2006. (4)
  47. What is the average revenue received by month for 2005-2007? (4) Hint: Use Format(OrderDate, “yyyy-mm”).
  48. On average, which manufacturer has given the greatest percentage discount (Discount/TotalList) for purchases in 2006? (4)
  49. Which customers who have purchased mountain bikes have also bought Road bikes at any time? (5) Hint: Create two separate queries and save them.
  50. Which customers purchased two or more bikes in at least two different years? (5)