Database Management Systems

© 2007 by Jerry Post
Database Exercises

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

Exercise 1

  1. Create a new database with the two tables shown in the figure. Feel free to add more data. Be sure to set a primary key for the underlined columns. Be sure to create a relationship that links the two tables. Use a report wizard to create the report shown. You should be able to use a visual tool to create the tables. Otherwise, check Chapter 3 for the syntax of the CREATE TABLE command.
  2. Read the documentation to your DBMS and write a brief outline that explains how to:
    1. Create a table.
    2. Create a simple query.
    3. Create a report.
  3. Interview a friend or a relative about his or her job and sketch two forms that could be used in a database application for that job.
  4. Find a reference or check Web sites so you can compare the specifications on three free DBMS products. At least one of the products should be from a major commercial vendor and one from an open-source or free source.
  5. Describe how a university club or student organization could use a database to improve its service operations.
  6. A company wants you to build a custom Web site to support sales of computer cables over the Internet. The company anticipates receiving an average of 100 orders per business day, with an average of $57.19 per order. Gross profit margins (including credit card costs) are about 15 percent. Shipping costs are priced directly so do not affect profits. No new workers will be needed to package and ship the orders, but the company expects to hire a designer with a salary of about $25,000, to keep the product list up to date with photos and descriptions. The initial costs include cost of the computer hardware and software ($10,000) and the development cost ($35,000). The ISP costs will be about $400 per month. Annual maintenance costs are expected to be $1000 per year. Assuming a project life of five years and an interest rate of 8 percent, compute the economic feasibility of the project. Compare the costs and benefits to the alternative of selling the items through Amazon instead.
  7. You have just been hired by a company and have wandered around talking to people. A few accountants have developed a database-driven application to handle fixed-assets and track depreciation. They claim the system has enabled them to function with one less accounting clerk ($30,000/year). A guy in finance has created a custom database in Microsoft Access to generate reports on a set of financial investments. Much of the data comes from brokerage firms and he notes that he is able to save 10 hours a week in clerical time (minimum wage). However, he complained about the difficulty of loading data from the main company database and says he spends 2 hours a week typing in data from printed reports. Two people in marketing have created separate databases to track survey and sales results for their projects. They claim the projects have not saved any labor costs, but enhance sales by at least $1 million a year. Yesterday, your manager said that all of these people complained about their existing systems and the inability to get data from the corporate database. You need to define projects for each group, identify the cost of developing a new system and the potential benefits. Rank the projects by economic return and make a recommendation to management.

    8. Sally’s Pet Store

  8. Install the Pet Store database or find it on your local area network if it has already been installed. Print out (or write down) the list of the tables used in the database. Use the Help command to find the version number of Microsoft Access that you are using.
  9. Visit a local pet store and make a list of 10 merchandise items and five animals for sale. Enter this data into the appropriate Pet Store database tables.
  10. Identify the hardware and software that would be required to install this system in a typical Pet Store. Estimate the costs and the time required to build and install the system.
  11. Outline the basic tasks that take place in running a pet store. Identify some of the basic data items that will be needed.

    12. Rolling Thunder Bicycles

  12. Install the Rolling Thunder database or find it on your local area network if it has already been installed. Using the BicycleOrder form, create an entry for a new bicycle.
  13. Use the Rolling Thunder Help system, or the Web site description, to briefly describe the firm and its major processes. Identify the primary business entities in the company.
  14. Concurrency is a problem in many applications. Problems arise when two people try to change the same data at the same time. Look through the Rolling Thunder application and identify possible areas where this problem is likely to arise.
  15. Refer to the relationship/class diagram to explain what a Component is and how it is connected to a Bicycle. Give an example from the data.

    16. Corner Med

  16. Install the Corner Med database if necessary. Use the Patient form to enter data for a new patient.
  17. Use the Patient Visit form to enter data for a patient with at least one diagnostic code and one procedure code. Describe any usability or performance issues that might arise.
  18. Make a list with a brief description of other items that the company might want to store in the database.