IT 650 Prinicples of Database Design

IT 650 Prinicples of Database Design

Chapter 1 • Who Needs a Database 17

Scenarios

These scenarios are designed to give you the opportunity to experience database development from beginning to end. Each has its own unique challenges. The scenarios can be pursued individually or in small groups. I would suggest choosing one scenario that interests you to follow throughout the term. Later, if you are so inclined you can return and work through some of the others.

WiLD WooD ApArTmenTS

Wild Wood Apartments owns 20 different apartment com- plexes in Washington, Oregon, California, and Idaho. Each apartment complex contains anywhere from 10 to 60 separate apartments, of varying sizes. All apartments are leased with a 6 month or yearlong lease.

M01_CONG4418_02_SE_C01.indd 17 03/07/13 6:32 PM

Case Study Scenarios from Hands-On Database

Steve Conger Prentice Hall, Second Edition (2014)

ISBN: 978-0-13-302441-8

18 Chapter 1 • Who Needs a Database

It is the company’s practice to hire one of the tenants to manage each apartment complex. As manager, he or she needs to admit new tenants to the building, collect rents from existing tenants, and close out leases. The manager also needs to main- tain the apartments by executing any repairs, replacements, or renovations. These can be billed back to the parent company. For acting as manager, the tenant gets free rent and a stipend. The stipend varies depending on the size of the apartment building.

Each manager is expected to send a report to the Wild Wood Apartments company headquarters in San Francisco every quarter. This report summarizes the occupancy rate, the total revenues in rent, the total expenses in maintenance and repairs, and so on. Currently, managers fill out a paper form and mail it back to headquarters. Many apartment managers have complained that preparing this report is a very difficult and time-consuming process. Also, the managers at corporate headquarters have expressed concerns about the accuracy and verifiability of the reports.

To allay these concerns and to improve the ease and effi- ciency with which the apartment managers conduct their daily business, the company is proposing to develop a centralized database that can be used by the managers to track the daily business of their apartment building and to prepare their reports.

To do

1. List the major topics for this database. 2. Write a draft statement of work. Include a brief his-

tory, a statement of scope, objectives, and a preliminary timeline.

3. Documentation: Start a notebook, either electronically or physically, to record your progress with the scenario database. Add the statement of work and any notes to the notebook.

Vince’S VinyL

Vince Roberts runs a vintage record shop in the University dis- trict. His shop sells 45’s, LPs, and even old 76 RPM records. Most of his stock is used—he buys used vinyl from customers or finds them at yard sales and discount stores—but he does sell new albums that are released on vinyl. For a couple of years, he has kept most of his inventory either in his head or in a spiral notebook he keeps behind the sale counter. But his inventory and his business have grown to where that is far from sufficient.

Vince is looking for someone to make him a database. He knows he needs to get a better handle on several aspects of his business: He needs to know the extent and condition of his inventory. He needs to know the relative value of his inven- tory—some records are worth a fortune; some are nearly worth- less. He also needs to track where, from whom, and for how much he purchased his stock. He needs to track his sales. He often is not entirely sure how much money he has spent or how much money he has earned.

In addition he would like to allow customers to make spe- cific requests and notify them if a requested item comes in. More generally he would like to make an email list of interested cus- tomers in order to let them know about new items of interest.

Someday, he would like to expand his business online. But he knows he needs to have everything under control before then.

To do

1. List the major topics for this database. 2. Write a draft statement of work. Include a brief his-

tory, a statement of scope, objectives, and a preliminary timeline.

3. Documentation: Start a notebook, either electronically or physically, to record your progress with the scenario database. Add the statement of work and any notes to the notebook.

GrAnDfieLD coLLeGe

The law requires that any business, including a school, track its software. It is important to know what software the school owns, in what versions, and what the license agreement for that software is. There are several different licensing schemes. The least restrictive is a “site” license that allows an institution to have a copy of the software on any machine on the business property. Other licenses specify a certain number of active cop- ies for an institution but don’t worry about which machine or user has the copy. The more restrictive licenses do specify one copy per specific machine or user.

Whatever the license agreement for particular software, it is essential for the institution to know which software is installed on which machine, where that machine is located, and which users have access to that machine. It is also important to track when the software is uninstalled from a machine, and when a machine is retired.

An additional useful feature of any software-tracking database would be to track software requests from users to determine (1) if a copy of the software is available and (2) if it is something that should be purchased. All installations are reviewed and must be approved.

For now, the school just wants the database to track fac- ulty and staff computers and software. Software for student machines is a separate and complex issue and will be treated as a separate project at a later time.

To do

1. List the major topics for this database. 2. Write a draft statement of work. Include a brief his-

tory, a statement of scope, objectives, and a preliminary timeline.

3. Documentation: Start a notebook, either electronically or physically, to record your progress with the scenario database. Add the statement of work and any notes to the notebook.

WeSTLAke reSeArcH HoSpiTAL

A hospital is conducting a double blind test of a new depression drug. It will involve about 20 doctors and about 400 patients. Half of the patients will get the new drug and half will get tra- ditional Prozac. Neither the doctors nor the patients will know who is getting which drug. Only two test supervisors will know who is getting what. The test will last about 18 months. Each doctor will see 20 patients initially, though it is expected some patients will drop out over time. Each patient will be coming in twice a month for a checkup and interviews with their doc- tor. The drugs will be dispersed in a generic bottle by the two supervisors one of whom is a pharmacist.

M01_CONG4418_02_SE_C01.indd 18 03/07/13 6:32 PM

Chapter 1 • Who Needs a Database 19

To track this study, the hospital will need a database. It will need to track patients’ information from their first screening through each of their interviews. In particular, they are looking at whether the patient seems more depressed or less, what their appetite is like, are they sleeping, and what kind of activities they are engaged in, if any. Also, they will be looking for spe- cific physical side effects such as rashes, high blood pressure, irregular heart rhythms, or liver or kidney problems.

Doctors need to be able to see their own patient’s informa- tion, but not that of any other doctor’s patients. They also need to be able to enter blood pressures, blood test results, the depres- sion indicators, their own notes, and so on for each session.

Patients should be able to see their own medical profile, the doctor’s notes, and nothing else.

Only the two researchers should be able to see everything: all patient information, all doctors’ notes, and which drug each patient is being given.

There is always some danger of spying by other companies interested in similar drugs, so in addition to the security of the blind test, the database needs to be secured against outside intrusion as well.

To do

1. List the major topics for this database. 2. Write a draft statement of work. Include a brief his-

tory, a statement of scope, objectives, and a preliminary timeline.

3. Documentation: Start a notebook, either electronically or physically, to record your progress with the scenario database. Add the statement of work and any notes to the notebook.

SHoW TimeS: LocAL SHoWS AnD AcTS

Patti and Dennis like to follow local bands. They often miss concerts because they only hear about them after the event. Typically, the only advertisement of an upcoming performance for some of these artists is a paper bill tacked to a street lamp or pasted on the side of a building. Sometimes there will be ads in the free community papers, but there is no one place to locate the information. Many of their friends share similar frus- trations. It is impossible to have a clear idea of who is playing where at any given time.

Patti and Dennis came up with the idea of a database that would store all of the information about artists and shows in one place. Ultimately they would build a Web page based on the database that everyone could access and use. They started by pedaling their idea to some of the more popular venues. The venues expressed interest. For the most part, they liked the idea of a central place where people could get a complete picture of the current music scene. It could result in more customers. Some even inquired about advertising opportunities.

Patti and Dennis also talked to some artists they knew. The artists also thought it was a good idea. They knew the hand bills were not very effective, though some of them liked the artistic effort of designing them. Another idea they had was that fans could register and select which artists or genres of music they liked and be informed of upcoming shows.

Encouraged by the response, Patti and Dennis are looking for someone to help design the database.

To do

1. List the major topics of the database to track concerts and venues.

2. Write a draft statement of work, including a brief his- tory, a statement of scope, objectives, and preliminary timeline.

3. Documentation: Start a notebook, either electronically or physically, to record your progress with the scenario database. Add the statement of work and any notes to the notebook.

SUGGeSTionS for ScenArioS

Scan the scenario descriptions and list the nouns. Identify the important nouns, the ones that describe features of the poten- tial database. These should be your major topics. Each scenario should have at least four major themes. Some have more.

All of what you need for the history and statement of scope is present in the scenario descriptions. You are not expected to invent anything new at this stage, even though you might have ideas about other things the database could do.

At this point, the timeline is pure guesswork. Just give it your best guess. Think about what the deliverables will be, even though a lot of them involve things you haven’t worked with yet. Use the statement of work in the chapter as a guide.

M01_CONG4418_02_SE_C01.indd 19 03/07/13 6:32 PM


Comments are closed.