Database Management Systems

© 2007 by Jerry Post
Database Exercises

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

  1. Create a function to compute commissions on sales. It has an input parameter of Sales. Return the total value of commissions, where the company pays 5 percent on sales less than $10,000, 6 percent for sales between 10,000 and 50,000, and 8 percent for sale amounts over 50,000.
  2. Assuming your DBMS does not support financial functions, write the function to compute the payment amount needed to pay off a loan given parameters for the loan amount, the number of periods, and the interest rate. Create a simple Loan table with a couple of rows of data to test the function.
  3. Create a table that lists item category and the level of tax on that category. For example, food (0 percent), clothing (3 percent), entertainment (10 percent). Write a function with category and price as parameters. Compute and return the appropriate tax. Normally, you would use an SQL statement for this computation, but if the tax table is provided on a separate system, you might need to write code.
  4. Create a data trigger that writes a row in a new table whenever employee salary is changed. Store the date changed, the employee, the old salary and the new value.
  5. Create a data trigger that will prevent anyone from increasing an employee salary by more than 75 percent.
  6. Create a data trigger (or form code if triggers are not available) that adjusts inventory quantity on hand whenever an item is sold. You need a SaleItem and Item table.
  7. Create the traditional bank account table (AccountID, CustomerID, TransactionDate, AccountType, Amount). Add a checking account and a savings account for the same person. Write the transaction code to safely transfer a specified amount from savings into checking.
  8. Create a small table and build a form to edit data for that table. Write a procedure to change a value in the table. Set the form and write the code to correctly handle a pessimistic lock on the table.
  9. Create a small table and build a form to edit data for that table. Write a procedure to change a value in the table. Set the form and write the code to correctly handle an optimistic lock on the table.
  10. Create a table for LoanPayments(LoanID, PaymentNo, DueDate, Amount). Write a function that is called whenever a new loan is created, to load the payments table with the scheduled payments and amount due.
  11. Create a small table that lists sales by month and includes a column for PercentChange. Write a cursor-based procedure to loop through the table and compute the percent change from the prior month and store that value in the current row.

    12. Sally’s Pet Store

  12. The animal suppliers allow Sally’s to maintain an account balance and pay for purchases later. Add a Balance column to the Supplier table. Create a form to record purchases of animals. Display the subtotal for the order and the current balance due for the chosen supplier. Add a button or a trigger so that new orders are added to the balance due. Be sure to control for collisions if another process is changing the balance.
  13. Create a table that records payments to suppliers. Write a procedure that can be called to validate and correct the supplier totals described in Exercise 12.
  14. Create a table to hold separate totals of animals and merchandise by month and a percentage increase in sales from the prior month. Write a function to compute the desired totals and enter them into the table. Add code to compute the percentage changes.
  15. Write the code to increase quantity on hand when an item is purchased—specifically when the receive date is set. Be sure to handle it as a transaction, since quantity on hand can also be affected by sales.
  16. The Pet Store is thinking about purchasing scanners to use at checkout. These scanners will pick up the ItemID of each merchandise item scanned. Assume that this data will trigger an event when an item is scanned. Write a function that can be called by this event. This function should create a new sale, and store the data for the items sold. You can emulate the scanner trigger by creating a form with a control to select an ItemID and a button to fire the trigger.

    17. Rolling Thunder Bicycles

  17. Create a function to compute the great circle route (shortest) distance between two geographic locations.
  18. The company wants to record sales of individual components—largely used for repairs. Add tables and create a new form to record these sales. Add the Component table to the subform and display the QuantityOnHand. Write the event code to handle the updates to the QuantityOnHand as items are purchased.
  19. Look through the forms and documentation and identify all of the transactions that take place. In particular, specify all of the events where two or more operations must succeed or fail together.
  20. Look through the forms and documentation and identify all of places where concurrent operations might cause a problem with data integrity. Specify whether optimistic or pessimistic concurrency should be used and why.
  21. A few states do not collect sales taxes on shipping charges, while a few require they be included. Modify the StateTaxRate table to handle this issue, and enter sample data. Write a function with parameters for the bicycle price, the shipping cost, and the state. This function will return the proper tax computation.
  22. Create a query to compute sales by month for each model type. Create a temporary table to hold that data and to hold the percentage change. Write a program that executes the query, placing the data into the table. Then cursor-based code computes the percentage change in sales.
  23. Write a procedure to add an interest charge to customer accounts with a balance due. Make sure to handle concurrency/locking problems.
  24. Write a program to automatically generate a new purchase order when quantity on hand falls below a specified level. Add the ReorderPoint column to the Component table and enter sample data.

    25. Corner Med

  25. Add a Weight column to the Visit table to track each patient’s weight over time. Add a button to the Visit form that computes the average percent change per month (Current weight-earliest weight)/earliest weight / months * 12. Alternatively, you could use an internal rate of return function to compute the growth rate.
  26. Create a table to hold revenue earned per week, using a date format of yyyy-ww. Include a column to hold percentage change from the prior week. Write a query to compute the totals and a routine to compute and store the percentage change.
  27. To facilitate loading data from the company’s older system, create a function that creates a new patient record given LastName, FirstName, Gender, DateOfBirth as input parameters, and creates a new visit record for that patient for a VisitDate parameter. The function should return the newly generated VisitID.
  28. Write a database trigger to record the date, user, and patient name any time a patient row is deleted.
  29. Eventually the database will be used by several employees at the same site at the same time. Identify any forms or transactions that might suffer from concurrent access issues. How will you prevent data integrity problems?