Database Management Systems

© 2007 by Jerry Post
Database Exercises

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

  1. A friend has asked for your help in training for a triathlon. No, you do not have to run with her at 5 AM. She wants a personal log to track her progress and well-being. She has been writing entries in a journal, but she wants to be able to plot the data and compute totals. The basic layout of her existing log is shown in the form. For each day, she tracks the distance and time of each run, bike ride, and swim. In all three cases, distance is recorded in miles; but often includes fractions. Eventually, when she plots the data, she wants the ability to exclude some days. For instance, one day her bike broke and she had to run home, so the time would not match with the others. She usually records her basic meals and estimates the total calories for the day. For the health category, she estimates the number of hours of sleep she gets at night, but the other entries are subjective comments and notes about the day.
    2. Date Run Bike Swim Food Health
    Sunday


    		 Distance
    Time
    Comment    		 Distance
    Time
    Comment    		 Distance
    Time
    Comment    		 Breakfast
    Lunch
    Dinner
    Snacks
    Calories    		 Sleep
    Illness
    Comments
  2. A small-town utility department needs some assistance. The organization is responsible for main water lines throughout the town. The managers want an application to track the various pipelines in terms of location, repairs, and inspections. The pipelines are divided into segments where each segment has a start and end (based on the direction of water flow) that is identified by the segments it connects to. Over time, the department has been adding GPS coordinates (latitude, longitude, and altitude) to each starting and ending point. The department tries to inspect major lines at least once a year. Several inspection methods are available including visual, acoustic, and magnetic sensors. (The U.S. EPA has a comprehensive white paper on structural integrity monitoring.) Usually, several methods are used when examining a large section of pipe. If work is performed, the department managers want to record the cause, such as a break, leak, or routine maintenance. They also track the name of the work manager and an estimate of the cost; which is used for planning and budgeting.
    3. Pipeline Segment ID
    Pipeline Description
    Location description and surface (road)
    Depth (feet)

    Start connection           GPS:
    End connection                  GPS:
    Length (feet)            Pipe size (inches)
    Material                         Date installed

    Maintenance and Repair
    Start Date End Date Cause Work Performed Manager, Phone, #Workers Cost

    Inspections
    Date Methods Results Comments and Recommendations

  3. A friend of yours has just purchased the distributor rights for a relatively small brewery. He needs an application to help him track the productivity of the drivers and help monitor the sales and returns (of stale or unsold items). He is particularly interested in identifying the best (and worst) customers so he knows where to focus his local marketing efforts using special discounts. He is thinking about equipping each driver with a laptop or maybe a hand-held computer, but is open to other ideas. The bulk of the territory is suburban with several supermarkets, many quick-stop stores, and several small restaurants and pubs. Although the brewery is small, it carries several products (light, wheat, red, ale, and so on). For now, he wants to keep the data input simple so drivers get used to the practice of recording the data.

    4. Delivery Date
    Delivery Person, Cell Phone, License Number
    Truck Number, Case Capacity
          Starting Miles       Ending Miles
    Vendor
    Type
    Location
    Owner/Manager
    Phone
    Arrival Time
    Departure Time
    Product Quantity Delivered Price Quantity Removed
    Comments
    Vendor
    Type
    Location
    Owner/Manager
    Phone
    Arrival Time
    Departure Time
    Product Quantity Delivered Price Quantity Removed
    Comments
  4. A charity that helps children with serious illnesses needs your assistance. This charity focuses on helping children and a family member travel to hospitals, or occasionally destinations such as theme parks that the children have requested. The charity primarily works with large companies with corporate jets that can often squeeze an extra passenger or two on a flight. But, many of the corporate flights are unscheduled and fly on short notice. Instead of having to call each company (or pilot) on a daily basis, the charity wants to set up a Web site. Pilots will be able to check for patients who need transportation, and will be able to list flights if no patients are currently on the list. For security reasons, the Web site will list only basic information on times and cities, but the database will contain the additional data. The forms shown here list the data the agency wants to collect, but only portions would be displayed on a Web site. Ultimately, the site would need search capabilities. For example, a search should be able to match requests even if the airport is a few miles away or the time is off by a few hours. Patients sometimes need additional space for wheelchairs or stretchers, and sometimes fly with a nurse or EMT. Some patients can fly with almost no advance notice when a flight is available. Others need a few days to make arrangements. Regardless of how the arrangements are made, the charity will record which children flew on each flight.
    5. Patient Request
    Departure City
    Destination City

    Departure Date                   +/- days
    Return Date                      +/- days

    Contact Phone           Advance notice (days)
    Purpose 

    Traveler Age/Birthday Space Needs Specialty

    Corporate Flight
    Departure Airport              Date/Time
    Destination Airport            Est. Arrival Time
    Return Date/Time

    Company       Person      Title     Contact Phone

    Aircraft Type

    Max # passengers to add

    Other space (equipment, stretchers, etc.)

  5. Are you aware of all of the pens, calendars, and other trinkets that businesses give out to customers? They have to come from someplace. You recently encountered a salesman who specializes in finding and customizing these marketing and commemorative items for companies. He has contacts with several manufacturers—many in Southeast Asia. Some manufacture the entire product. In more complex cases, he has to purchase the product from one vendor, ship it to a specialist to imprint the name and logo, and then ship it to the customer. The salesman has built his reputation and business on his ability to find unique products and deliver on time. To grow his business, he needs to spend more time on the road, and he needs an application to track the progress of the various orders. When he submits a complex item for production, he tracks the steps by dates: the date a company received an order or product and the date it shipped it. Each shipping step is recorded separately and the final step is receipt of the product by the customer. He asks each manufacturer and the customer to rate the quality of the item they receive.
    6. Pipeline Segment ID
    Pipeline Description
    Location description and surface (road)
    Depth (feet)

    Start connection           GPS:
    End connection                  GPS:
    Length (feet)            Pipe size (inches)
    Material                         Date installed

    Maintenance and Repair
    Start Date End Date Cause Work Performed Manager, Phone, #Workers Cost

    Inspections
    Date Methods Results Comments and Recommendations

  6. A cell-biology researcher wants a database to track data from some experiments. The main part of the data is just recording measurements of various effects obtained from applying different chemicals in combinations. She has lab assistants and instruments to measure the inputs and effects, but needs a way to record the data for later analysis. For each experiment, chemicals are added in a specified order, and sometimes there is a delay between the chemicals. A time variable records the time from the start of the experiment.
    7. Research Project            Sponsor        Purpose

    Experiment Number
    Person           Title      Phone

    Start Date              End Date
    Experiment Description
    Environment Description (especially external issues)
    Temperature
    Lighting
    Humidity

    Inputs
    Sequence Time Chemical Amount Method
    1.
    2.
    3.

    Measurements
    Date/Time Item and Units Value Comments Person

    Conclusions and comments
  7. A large farmer wants some help in tracking the work done on his fields. With GPS units, he can identify individual regions of each field and monitor soils, planting, water content, and chemical usage. The GPS units store each region as segments of latitude and longitude coordinates. The employees use a GPS map when working in the field. The farmer wants you to create a relatively generic application so he can use it for all of his fields, and perhaps sell it to other farmers. The first form he needs is one to enter the data for each field and region within the field. Ultimately, he would like to upload the segment data from the GPS unit to the database, but for now you can just store the segment ID. The terrain profile is a basic description such as flat, rolling, or hilly. The soil profile is a simple description using common USDA terms, such as sandy or loamy. (You might want to get a copy of a county soil survey and enter a list of the basic terms.) The farmer also wants a relatively generic activity form to record actions taken. The amount value is a measure of whatever was done to the field, and the unit of measure changes for each activity. For example, planting might be measured by pounds of seed per acre, or fertilizer by gallons per acre.
    8. Field ID
    Name
    Location
    Description
    Total Size

    Region ID GPS ID Size Description Terrain Profile Soil Profile Primary Use


    Activity Date
    Activity (till, plant, fertilizer, herbicide, pesticide, other)
    Brand (plant or chemical)
    Unit of Measure
    Description/Comments

    Weather

    Region ID Amount Employee Comments

    Sally’s Pet Store

  8. Define the tables needed to extend the Pet Store database to handle genealogy records for the animals.
  9. Define the tables needed to extend the Pet Store database to handle health and veterinary records for the animals.
  10. Sally wants to add payroll and monthly employee evaluation information to the database. Define the tables needed.
  11. Sally wants to add pet grooming services. Define the tables necessary to schedule appointments, assuming two workers will be dedicated to this area.

    12. Rolling Thunder Bicycles

  12. Using the class diagram, identify five business rules that are described by the table definitions and table relationships (similar to the ListPrice rules described by the Sale example).
  13. The company wishes to add more data for human resources, such as tax withholding, benefits selected, and benefit payments by the employees and by the company. Research common methods of handling this type of data and define the required tables.

    14. Corner Med

  14. Physicians and medical administrators are often interested in a hierarchical classification of illnesses and diagnoses. Some of the hierarchy is built into the ICD codes, but the managers and physicians want to be able to create reports that roll up the weekly diagnoses into specific categories. They also want the ability to define new categories. Essentially, the physician administrators will create a medical category and list the various conditions that apply to the category. For example, Broken Bones could be a general category, and specific fractures (e.g., S62.2 Fracture of first metacarpal bone using ICD10) would comprise the list of conditions. Define the table(s) needed to handle this summarization data. Optional: What if the physicians what to create multiple levels within the summary data? For example, Family Practice could be a parent category to Childhood Diseases, Accidents, Minor Illnesses, and Checkups. Each of these could have subcategories.
  15. The physicians would like to add another step in the patient examination process. They want more complete records and the ability to handle cases where the diagnosis is not immediately available. Specifically, the physicians want to record the symptoms described by the patient at each visit. This record would also include the severity of the symptom and whether it was observed by the physician (or nurse). At each visit the patient’s weight, blood pressure, and heart rate are also recorded (children are also measured for height). Along the same lines, they want to record any tests taken and the results of the tests. The tests can include simple physical tests such as reflexes as well as chemical tests. Define the tables and modify the class diagram to handle this additional data.
  16. In 2006, Benjamin Brewer, M.D., a practicing physician, listed common statistics for a medical office [“A Doctor Faces Tough Decision to Stop Taking New Patients,” The Wall Street Journal, February 7, 2006]. Read the article and use the numbers to estimate the size of the database after 1 year and 5 years of operation. Some basic data from the article: 3 physicians, 2,500 patients, 90 patients per week in office visits per physician. But, read the article to gain perspective on the situation. It is available in your library.