Database Management Systems

© 2007 by Jerry Post
Database Exercises

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

  1. Extend the example in Figure 10.19. Add Title to the Employee table. Now create two phone books. One is accessible to all employees and lets them see phone numbers for anyone without the word Executive in his or her title. The second book is for executives and lists phone numbers of all employees.
  2. A DBMS is halfway through recording a transaction with several related updates, when someone trips over the computer’s power cord. Describe the steps the DBMS uses to protect the data integrity and restore the database.
  3. Briefly describe how you would protect a computer system from the following problems. List steps you will take before the event and after the event has occurred.
    1. The owner of a small company backs up the database onto a removable drive every day, and brings it home in his car. One night, someone steals the drive from the car and it contains personal and financial data on customers.
    2. A child downloads a hacking tool from the Internet and uses it to find an unpatched hole in your operating system. The tool installs a spyware package on your database server, and she used it to capture the DBA’s password.
    3. For the third year in a row, a hurricane hits your operations center in New Orleans. Everyone has to evacuate, and you shut down the center for three weeks until power is fully restored.
    4. A disk drive fails.
    5. A squirrel builds a nest in a nearby power transformer and blows out the electric grid when it bites the main line.
    6. An employee quit and took customer credit card data with him and he is using it to steal money.
    7. An executive’s administrative assistant inflated supplier payments and moved the excess money to her bank account.
    8. The CFO loses his cell phone/PDA and someone uses the stored passwords to steal money from his benefits accounts.
  4. Describe the problems you can expect while running an e-commerce Web site.
  5. Research the costs of adding 4 RAID drives to a server. Find at least two methods and compare the features and costs.
  6. You are setting up security for an e-commerce site. The application uses the following tables. Define the access rights to each table for the following users: managers, shipping clerks, and customers who use the Web site. Define any queries you need to add security conditions.
    1. Customer(CustomerID, Name, Address, …)
    2. Items(ItemID, Description, ListPrice, QOH)
    3. Sale(SaleID, CustomerID, SaleDate, IPAddress, CCNumber, …)
    4. SaleItem(SaleID, ItemID, SalePrice, Quantity)
  7. You are setting up a small billing database for a physician’s office with the following tables. Define the access rights to each table for the following users: physicians, accountants, clerks (reservations), clients (if you create a Web site), insurance company. Define any queries you need to add additional security conditions.
    1. Client(ClientID, Name, Address, DOB, Gender, …)
    2. Staff(StaffID, Name, Specialty, …)
    3. Visit(VisitID, ClientID, DateTime, Charge, InsuranceCompany, InsuranceID)
    4. TreatmentCode(TreatmentCode, Description, Cost)
    5. Treatment(VisitID, TreatmentCode, Comments, Charge)
    6. Insurance(CompanyID, Contact, Phone, EDIAddress)
  8. Employees and other insiders present the greatest security problems to companies. Outline basic policies and procedures that should be implemented to protect the computer systems. (Hint: Research employee hiring procedures.)

    9. Sally’s Pet Store

  9. Devise a security plan for Sally’s Pet Store. Identify the various classes of users and determine the level of access required by each group. Create any queries necessary to provide the desired security.
  10. If it does not already exist, create a sales query that uses data from the Customer, Sales, SaleItems, Employee, and City tables to produce a report of all sales sorted by state. Use a query analyzer to evaluate the query and identify methods to improve its performance.
  11. Create a backup and recovery plan that will be used at Sally’s Pet Store. Identify the techniques used, who will be in charge, and the frequency of the backups. Explain how the process will change as the store and the database grow larger.
  12. What physical security controls will be needed to protect the database and hardware?

    13. Rolling Thunder Bicycles

  13. Devise a security plan for Rolling Thunder Bicycles. Identify the various classes of users and determine the level of access required by each group. Create any queries necessary to provide the desired security.
  14. Devise a backup and recovery plan for Rolling Thunder Bicycles. Be sure to specify what data should be backed up and how often. Outline a basic disaster plan for the company. Where are security problems likely in the existing application? How should duties be separated to improve security?
  15. Use the performance analyzer tools available for your DBMS to evaluate the tables, queries, forms, and reports. Provide an explanation of the top five recommendations.
  16. The company is planning to set up a Web site to enable customers to enter and track their orders using the Internet. Explain the additional security procedures that will be needed.

    17. Corner Med

  17. Specify the access permissions needed at Corner Med. Focus on the two main user groups: clerical and medical staff, but also include a managerial group that reviews financial and treatment summary data—without needing access to individual patient data. The plan should address the privacy implications.
  18. The company would like to give wireless devices to the medical staff to give them access to the data while talking with patients. Research the potential security issues and describe a solution that would protect the privacy and security, and remain usable.
  19. Describe the physical security precautions that need to be taken. How will users be authenticated? Where should the servers and data be stored? How will backups be handled?
  20. Identify the elements of the database that should be stored in encrypted form. Pick a DBMS and research the support available to encrypt individual data elements.