Database Management Systems

© 2007 by Jerry Post

Database Exercises

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

1. Managers at a local hospital want a scheduling system to make it easy for nurses to sign up for shifts. The hospital uses 12-hour shifts, running from 7 to 7 and has specific minimum staffing needs for each shift in various departments (ER: emergency room, Surgery, CCU: critical care unit, and General). Nurses generally work three shifts a week (36 hours), but sometimes extra help is needed and they can sign up for one additional shift per week for extra pay. The managers want a simple form similar to the one shown here, where nurses simply sign up for a shift and can trade shifts as long as the basic minimums are met. If additional nurses are needed for a specific shift, the managers will send a separate message to nurses.

Day	Shift	ER	Surgery	CCU	General
Sun	Day	2	1	2	4
	Night	2	0	1	4
Mon	Day	2	2	2	4
	Night	2	0	1	4
Tue	Day	2	2	2	4
	Night	2	0	1	4
Wed	Day	2	2	2	4
	Night	2	0	1	4
Thu	Day	2	2	2	4
	Night	2	0	1	4
Fri	Day	2	1	2	4
	Night	2	0	1	4
Sat	Day	2	1	2	4
	Night	2	0	1	4

2. You have been hired by a small county government office to track properties for tax purposes. The annual property tax rate is set by the local governments, but the assessor’s office is responsible for tracking the value of each property. Whenever a house is sold, the sale price is reported to the office. Since this price is a concrete measure of the price, it has to be recorded permanently. In other cases, the assessor estimates the value of a property from its primary features, such as the lot size (measured in fractions of an acre), house size (square feet), age (year built), neighborhood, and general condition. New values are estimated every year if a house has not been sold. If a property has not sold for more than five years, an assessor performs a simple inspection of the property and takes pictures from the road. Property owners can appeal an assessment to a board. The appeal date and any comments are recorded. The data is currently recorded on a form organized by each property. Recently, the office has been assigning GPS coordinates (latitude, longitude, altitude) to each property. Many do not have data yet but will be added when the properties are inspected.

Property ID Year Built       Construction Price Year Remodel Address Tax Code Area Zone Code Subdivision     Township/Range/Section	Latitude Longitude Altitude
Lot size House size Sewer Water Utilities	Bedrooms Bathrooms Fireplaces Garage Basement
Property Description Year Valuation Method Comments
Appeals Date Claimed Value Resulting Value Comments

3. A local music store wants help with marketing. Specifically, the owner knows that some customers are better than others and wants to encourage them to purchase more items from the store by offering discounts. However, she does not want to offer the same level of discounts to everyone who walks in from the street because profits would drop. She needs a computer system to help employees give the correct discounts to each customer. Basically, she wants a frequent buyer program, where customers are placed into categories based on the amount of money they spend in a year. However, she also wants the ability to move some customers into higher categories based on other factors (such as name recognition). The heart of the system is a list of the customer categories, the sales amount needed to reach each level, and the discount applied. However, some products or sales will not count towards the reward levels. For instance, close-out sales items will not be included in sales totals, nor are they eligible for additional discounts. So, she needs a list of inventory items along with an indicator for the discount eligibility.

Level	Sales	Discount
Platinum	$50,000	20%
Gold	$25,000	15%
Silver	$10,000	10%
Bronze	$5,000	5%




SaleID                                Sale Date                                           Employee CustomerID Last Name, First Name Band E-mail Address Phone Reward Level Delivery Address City State, ZIP

Item Description Price Quantity Discount Value Total Reward Points Current annual total of reward points

4. A friend of yours has a business that translates documents for other companies. She knows several languages and started out doing most of the translations herself. As her business grew, she found more people to work as part-time translators. Most translators specialize in translating from one language into their native tongue. A few can translate from several languages. Several companies send documents for translation on a regular basis. Others are one-time translations. With more business and several translators, your friend needs to track which people are working on documents. The translators set the price for each translation, but pay a commission to your friend. The commission is generally 25 percent of the price, with a minimum of $5 per contract. The main data will be tracked through a Web site form that is filled in by the translators.

Contract ID                                 Contract date

Client Contact                                Department Phone                                   E-mail Address, City, State/Province, Postal Code, Country

Translator E-mail                                   Phone (with country code) Native language Language Skill Level

Documents                              Target language Name Language Date Received Date Due Initial Date Review Date Price Difficulty Total

5. A local cleaning service business wants an application to help track its customers, employees, and appointments. The firm employs a staff of full-time cleaners, but can also hire part-time workers when necessary. Many of the customers sign up for regular cleanings (once a week, once every two weeks, or once a month). Some customers use the service only once or twice a year. The company needs two basic pieces: (1) A list of cleanings scheduled for the week, and (2) A form that workers fill out to record the work done. Employees are paid based on the number of hours worked in a week.

Sunday	Customer Address Job Type Instructions	Start Time Crew Leader Worker 1 Worker 2
	Customer Address Job Type Instructions	Start Time Crew Leader Worker 1 Worker 2
Monday	Customer Address Job Type Instructions	Start Time Crew Leader Worker 1 Worker 2
Tuesday
Wednesday
Thursday
Friday
Saturday

Date Customer
 Kitchen  Dining Room  Living Room  Den	__ Bathrooms __ Bedrooms Other
Worker Start Hours Comments Cleaning comments, suggestions

6. A club member just approached you with a problem. The club’s last event was a disaster. There was not enough food or drink, there were too many flowers (or too many tables depending on your perspective), and no one knew what time various events started (including the participants). It’s a good thing one-third of the people invited did not show up. You need to design an application to track details for the next event. One of the main steps is tracking the invited guests to see if they require special food and whether they have replied to the invitation. The planners want to print a report to show the current status of the guests. The caterer will also need a list of the number people for each meal preference, but it uses the same data. However, the organization also needs a list of the equipment that needs to be rented for the event, such as the sound system or tables.

Event and Date Report Date Guest Invited By Response Date # Attending -----------    Mean Choice: -----------    Mean Choice: Guest Invited By Response Date # Attending -----------    Mean Choice: -----------    Mean Choice:                       Total Invited:                    Total Confirmed:




Event Title Event Date Start Time                  End Time

Location Maximum People Room Cost Caterer          Phone       Member in Charge

Rentals and Purchases Item Vendor Phone Number Date Ordered Date Confirmed Cost

7. Your network manager needs help. The network was installed over several years by many different people. Although the network works fine, the network administrator needs a consolidated list of equipment as well as a list of computers and people connected to the ports. When someone calls with a problem, he wants to use the application to get a list of where the person is connected, the switch involved, and eventually a list of nearby ports. He has created a form that network employees will fill out as they inspect the network. The manager also wants to track basic information about the switches (and routers). Later he will document the cable routing for the switch connections but that data does not need to be stored now.

Network Access Point Date Installed (if known) Installer Date Updated
Endpoint Location   Building     Floor    Wing   Room     Wall   Jack # Security Comments:	Person or Manager Name Title Phone
Equipment Connected   Type (computer, printer, switch, other)   Description   MAC Address   Maximum network speed (10 mbps, 100 mbps, 1 gbps)
Switch connection   Switch number   Distance form jack to switch (meters)   Line comments, including interference issues




Switch

ID Number                          MAC           Mgt. IP Address Building, Floor, Room, GPS Model, Description, Total Ports

Port Speed Quantity 100 mbps 1 gbps Other

Upstream switch number Connection method (fiber, wireless) Connection speed (gigabits per second) Distance (meters) Connection muting comments



8. Experience exercise: Talk to a manager of a local store and create a class diagram for the store’s system.
9. Identify the typical relationships between the following entities. Write down any assumptions or comments that affect your decision. Be sure to include minimum and maximum values. Use the Internet to look up terms and examples.
   1. Newspaper, Reporter
   2. Movie, Director
   3. Company, External audit firm
   4. Truck, Driver
   5. Restaurant, Sous chef
   6. Credit card number, Person
   7. Corporation, Name
   8. Song, Song writer
   9. Radio station, Call letters
   10. Class, Classroom
   11. Company, NAICS 6-digit code
   12. Business, Federal employer number
   13. Customer, Waitperson
   14. New car, Sale date
   15. Bedroom, Paint color
   16. Song, Music key
   17. Ski hill, Difficulty classification
   18. Web shopping cart, Total value
10. For each of the entities in the following list (left side), identify whether each of the items on the right should be an attribute of that entity or a separate entity.

    a) Sale	Date, Item, Tax, Customer, Salesperson
    b) Package	Size, Weight, Address, Customer, Delivery truck, Employee
    c) Camera	Customer, Megapixels, Battery, Lens, Price, Weight
    d) Club	Name, School, Purpose, Member, Advisor, Activity
    e) Book	Title, Author, Chapter title, Price, Subject, Size, Format

Sally’s Pet Store

11. Do some initial research on retail sales and pet stores. Identify the primary benefits you expect to gain from a transaction processing system for Sally’s Pet Store. Estimate the time and costs required to design and build the database application.
12. Extend the class diagram by adding comments about each animal, beginning with breeder remarks and including comments by employees and customers.
13. Write classes for the pet store case to track special sales events. Every couple of months the store has clearance sales and places specific items on sale. Eventually, Sally wants to evaluate the sales data to see how customers respond to the reduced prices.
14. Extend the pet store class diagram to include scheduling of appointments for pet grooming.

Rolling Thunder Bicycles

15. The Bicycle table includes entries for several employees who worked on the bike. The advantage to this approach is that it leaves all the work in one table and identifies the work performed, making it easier to enter the data. The drawback is that it is more difficult to query (and would require several links to the Employee table). Redesign the table to eliminate these problems.
16. Rolling Thunder Bicycles is thinking about opening a chain of bicycle stores. Explain how the database would have to be altered to accommodate this change. Add the proposed components to the class diagram.
17. If Rolling Thunder Bicycles wants to add a Web site to sell bicycles over the Internet, what additional data needs to be collected? Extend the class diagram to handle this additional data.

Corner Med

18. One of the first things Corner Med needs for the database is the ability to enter multiple numbers for the physicians, such as pager and cell phone. Add the necessary class.
19. Corner Med needs more information about insurance companies. Each company requires claims to be submitted to a specific location. Today, much of the data can be submitted electronically, so there will be an electronic address as well as a physical address. There will also be an account number and password, as well as a phone number and contact person. Add these elements to the class diagram.
20. In theory, prescriptions could be handled as ICD9 procedures. However, because of various drug laws, including pharmacy verification and tracking needs, it is easier to store the data separately. Add the class(es) to the diagram to handle drug prescriptions. Be sure to include the drug name, the dosage, instructions for taking the drug, and the time period. Note that you do not need to add a Drug table because it would be too large and change too often; although the physicians might want to add the Physician’s Desk Reference (PDR) on CD later.