Chapter 2 • Gathering Information 37
apartment number lease number lessee name Start Date end Date Rent amount ($) Deposit($) Current
201 #201050109 Charles Summers 5/1/2013 5/1/2014 1,500.00 3,500.00 1
110 #110060109 Marilyn Newton 6/1/2013 12/1/2013 1,200.00 2,900.00 1
306 #306060109 Janice Lewis 6/1/2013 6/1/2014 1,250.00 3,000.00 1
102 #102060109 Larry Thomas 6/1/2013 6/1/2014 1,250.00 3,000.00 1
209 #209060109 Mark Patterson 6/1/2013 12/1/2013 1,450.00 3,400.00 1
Each of the scenarios has different requirements. Each is docu- mented differently.
WiLD WooD APArTmenTS
As a follow-up on your initial interview with the project coor- dinators, Wild Wood Apartments has agreed to show you some
samples of various forms and reports. The first example is of a spreadsheet to keep track of leases at one apartment complex.
M02_CONG4418_02_SE_C02.indd 37 03/07/13 6:31 PM
Case Study Scenarios from Hands-On Database
Steve Conger Prentice Hall, Second Edition (2014)
38 Chapter 2 • Gathering Information
The second example is of a spreadsheet used to track rent payments.
Date name apartment lease number amount paid($) late
7/1/2013 Martin Scheller 203 #203011208 1,200.00
7/1/2013 Roberta Louise 311 #311060108 1,400.00
7/1/2013 Sue Tam 111 1,400.00
7/1/2013 Laura Henderson 207 #207020209 1,350.00
7/1/2013 Thomas Jones 110 #110010109 1,200.00
7/2/2013 Shannon Hall 205 #205010109 1,350.00
7/2/2013 Bob Newton 104 #104030209 1,250.00
7/9/2013 Dennis Smith 209 1,400.00 X
Job Shadow Report
I followed the apartment manager for the Eastlake Apartments, Joe Kindel, for 4 hours on March 1, 2013. It was the day the rents were due. Joe’s apartment is also his office. The first thing he did after he opened up and let me in was to pick up a locked
box that was chained to the floor just outside his apartment door. “The tenants can drop in their rents here,” he told me.
Joe took the box inside, unlocked it, and pulled out the checks while his computer started up. When it was ready, he began entering the renter’s names, apartment numbers, and
apartment number Date problem Type Resolution
B expense ($)
T expense ($)
303 7/5/2013 Left burner out on range electrical Electrician rewired 7/10/2013 150.00 –
201 7/5/2013 Water wastage from overflowing bathtub
floor Replaced flooring new tile 7/21/2013 200.00 350.00
101 7/6/2013 Dishwasher backing up plumbing Filter clogged; cleared it 7/6/2013 35.00 –
207 7/15/2013 Hole in plaster walls Patched hole 7/17/2013 – 250.00
113 7/15/2013 Refrigerator failed utilities New refrigerator 7/20/2013 690.00 –
FIgure 2-11 Wild Wood Quarterly Report
Wild Wood apartments Quarterly Report Building # #12
address 1321 EastLake, Seattle, WA. 98123
Quarter Spring Year 2013
Total apartments Currently occupied percent no. Changing Tenants
45 40 89% 13
Revenues Total Rent Revenue 175,500.00
new Tenant Cleaning 10,400.00
Total expenses 58,478.00
unrecovered Rents 3,200.00
Total profit/loss 113,822.00
Finally, here is an example of the report that each apartment manager must turn in to the main office quarterly.
The third is an example of tracking maintenance requests and responses.
M02_CONG4418_02_SE_C02.indd 38 03/07/13 6:31 PM
Chapter 2 • Gathering Information 39
payment amounts into a spreadsheet. While he was working, a tenant came in and handed him a check. Joe thanked the ten- ant and added the check to the pile. When he had finished, he checked his list against a list of tenants. He told me that three had not paid their rent yet.
He called each of the three. The first did not answer, so he left a message. “I am not too worried about him,” Joe told me. “He isn’t always on time, but he always pays within the 5-day grace period.”
I asked about the grace period. Joe answered, “The company allows a renter to be up to 5 days late without a penalty. If you pay after that there is a $100.00 penalty tacked on to the rent.”
He called the second renter. She was at home and asked if he could wait until the 10th. Joe said OK and then explained to me, “She’s an older woman and dependent on Social Security and retirement checks. I give her a little more leeway. The com- pany lets me because she has lived here forever and has always been a good tenant. This last one though is just no good.” He picked up the phone and called. He got no answer, and there was no answering machine. Joe told me that he was about ready to evict this last tenant. He is habitually late, and he is actually 2 months behind in his rent. Joe tells me how difficult it is to actually evict someone.
While he is telling me stories about past evictions, the phone rang. A woman in apartment 211 told him that her stove wasn’t working. Joe opened a second spreadsheet and entered some
of the details. He also wrote some notes on a pad of paper. He reassured the woman that he would deal with it quickly and promised to come by in the afternoon.
After 4 hours, I thanked Joe for his time and left him to his lunch.
1. Make a list of questions that you would ask about these forms and reports.
2. Identify the stakeholders for Wild Wood Apartments. 3. Create a plan for an hour-long interview with represen-
tatives of these stakeholders. Then meet with the instruc- tor to discuss possible answers to the questions.
4. Create a questionnaire of at least five questions for the managers of the 20 apartment buildings.
5. Look at the Job Shadow Report. Do you see any excep- tions to the general rules? Do you see any new business rules uncovered? What additional questions arise from the report?
Vince hasn’t kept very complex records, but he does have a few things he can show you. The first thing he has is an exam- ple of the notes he takes when he purchases an album from a customer.
Seller’s name Seller’s phone number album notes Condition paid ($)
John Raymond 206.555.2352 Rubber Soul Amer. Not British vers. 2nd edition, good Sleeve
Marilyn Taylor 206.555.0945 Led Zepplin IV Not orig. Sleeve damaged, vinyl good good 4.75
Jennifer Louis 206.555.4545 Gift of the flower to the Gardner
Rare Donovan, box set, box condition poor, but vinyl excellent
Laura Hall 206.555.2080 Dark Side of the Moon good 4.45
Here is an example of a sale to a customer:
Customer album price ($) Tax ($) Total ($)
John Larson Dylan, Blond on Blond 19.95 1.65 21.60
Tabitha Snyder America 5.95
Joni Mitchell, Blue 6.25
Joan Baez, Ballads 4.20 1.36 17.76
Brad Johnson McCartney, Venus and Mars
5.00 0.42 5.42
Maureen Carlson Decembrists, The Crane wife
Muddy Waters 7.75 1.92975 25.18
Job Shadow Report
I sat with Vince for a full day of work. The morning was quiet, and Vince spent the time sorting through a stack of albums that he had purchased earlier in the week. He took each one out of the sleeve and inspected it carefully. “Sometimes I catch things
that I didn’t see when I actually purchased it,” he explained to me. “It is too late now, of course, to do anything about it, but I want to be fair to the people I sell it to.” He put a sticker on the cover and put “good” and a price of $6.50. I asked him about how he classified and priced things. He told me he had four levels: mint, good, fair, and poor. Mint was only for things that were nearly perfect. Good meant there were no scratches and the vinyl was not warped and not too worn. Fair meant the vinyl was a bit more worn and might have a light scratch or two. Poor meant the vinyl was scratched and probably warped. He didn’t buy poor vinyl unless it was an extremely rare album. Prices were based on what he thought the album would bring. He based it mostly on experience.
After a while, a customer came in. He asked if Vince had seen a copy of an old album. He commented that he didn’t think it had ever made the transition to CD. Vince said he had seen it, but he didn’t have a copy currently, but if the customer wanted he would take his name and number and let him know when he next got a copy. The customer agreed and then, after looking around for about 20 minutes, returned to the counter with five albums. Vince wrote down each album title and the price and then added the prices on a hand calculator. The total came to $35.50. Vince said, “Make it thirty, and we’ll call it good.” Vince
M02_CONG4418_02_SE_C02.indd 39 03/07/13 6:31 PM
40 Chapter 2 • Gathering Information
explained that it was good for business. It made the customer feel good, and they were more likely to come back. Several more customers came in, and their transactions followed a simi- lar pattern.
In the afternoon, a customer came in with a stack of albums he wanted to sell to Vince. Vince went through the albums, tak- ing each one out of its sleeve and inspecting it. In the end, he split the albums into two piles. He told the customer he was interested in the first pile of about 12 albums and would offer him $20.00 for them. The customer pulled one album out of the pile Vince had selected and said “I thought this one might be worth a little more. It is a first print.” Vince looked at it again. “Yes it is, but it is scratched and only in fair condition. Still, I’ll make it $25 if that makes it seem more fair to you.” The cus- tomer agreed. Vince told him he wasn’t really interested in the second pile of albums. The customer could either take them back or Vince would put them on his 5-for-a-dollar pile. The customer chose to leave them.
Vince put the albums in a pile by his desk. Several more customers came and went. Vince chatted pleasantly with all of them. Several purchased an album or two. At about four, Vince turned the open sign in his window to closed, and I thanked him for his time and left.
1. Study Vince’s sample notebook entries. Make a list of questions you would ask about the data in them.
2. Identify the stakeholders in Vince’s record store.
3. Prepare an interview with Vince and two of his best customers: one who both sells albums to Vince and buys, and one who mostly just buys. Then meet with the instructor to discuss possible answers to the questions.
4. Create a questionnaire for those who sell albums to Vince about changes they would like to see in the process.
5. Look at the Job Shadow Report for Vince. Do you see any exceptions? What additional business rules do you see? What additional questions does the report raise?
The software management team has several spreadsheets to keep track of software. They show you several samples. The first is just a listing of software:
Software Version Company license Type
Windows Vista Business, Service Pack 2
Microsoft MS Site
MS Office 2007 Microsoft MS Site
Visual Studio Professional 2008 Microsoft MS Instructional
PhotoShop CSS3 Adobe Adobe1
FileZilla 5 FileZilla Open Source
German 2.5 LanguageSoft LanguageSoft1
The second is a key to the different licensing agreements and types:
license Type Start Date end Date Terms pricing pricing unit
MS Site 7/1/2009 7/1/2013 Can install as many copies as needed on campus and on laptops controlled by the school. Includes all service patches, updates, and version changes
12500 5 yrs
Ms Instructional 7/1/2010 7/1/2015 Used for instructional purposes only. Cannot be used for school development projects
3000 5 yrs
Adobe1 7/1/2009 7/1/2015 Reduced price per installed copy, maximum of 25 active copies 450 Per active copy
Open Source 7/1/2009 7/1/2020 Free for use as long as registered 0
LanguageSoft1 7/1/2012 7/1/2016 25 copies 5200 For 25 copies
CCS number location assigned user
3214 Rm214 Cardwell
Software Install date Rmv Date
Vista Business 5/3/2013
Ms Office 5/3/2013
CCS number location assigned user
3114 Rm212 Larson
Software Install Date Rmv Date
Vista Business 4/15/2013
Visual Studio Pro 6/12/2013
DreamWeaver 6/14/2013 7/12/2013
Here is an example of the list of who has what software:
M02_CONG4418_02_SE_C02.indd 40 03/07/13 6:31 PM
Chapter 2 • Gathering Information 41
And, finally here is sample of a request for new software:
CCS number user Request Date Software Reason Response Res Date Status
2123 Johnson 5/20/2013 Camtasia I am conducting several online classes. I need to be able to create visual demos to post to the class Web site
We don’t currently have a license for Camtasia but will explore acquiring one
Job Shadow Report
I spent the day on 4/12/2013 following Sheri, a member of the software management team at Grandfield College. The first thing she did after settling into her office was check a spreadsheet that listed pending installations. She showed me the list and told me that she had about six installations to do that morning. She also noted that it was the most boring part of her job. “Nothing like watching the progress bar on the monitor for hours at a time,” she said. Next, she checked her emails. There were three requests for additional software. She opened a spreadsheet and entered the request information. She told me that she would check later to see if the school had the software or if it was something they would have to purchase. If it was a purchase, she would have to get permis- sion. She replied to each of the emails to acknowledge their request.
After noting the requests, she looked again at the installa- tion to be done. She went to a cupboard and pulled out some disks. She told me that some software can be installed from a network drive, but for some she has to bring the media. She also grabbed a notebook. We went to the first office. She spoke for a few moments with the woman who occupied the office. They laughed at a few things. Sheri said that with luck the installations should take no more than 30 minutes. The woman left the office to let Sheri work. Sheri logged into the computer as administrator and slipped in a DVD. She started the install.
I asked her about the notebook. Sheri told me that she car- ried it for two reasons. If there were any problems with the install that she couldn’t solve, she would write down the error messages and take them to the other techs to resolve. She also would note in the book whether the installation was a success or not. She didn’t put it in the spreadsheet until the installation was complete and successful.
The rest of the morning, Sheri moved from office to office installing software. On that day, at least, there were no major installation issues. While we waited, she told me about other days that didn’t go so easily. She told me about how difficult it could be to troubleshoot a bad install, and how obscure and undocumented settings could require hours of research before they were discovered and resolved.
The installations were finished by lunch. After lunch, Sheri checked with the department receiving new software and packages. There were several that had arrived. Sheri carefully unpackaged each arrival and noted it in a spreadsheet. Then she checked the licensing agreements. Some she knew, others she had to check, often looking up the licensing agreement online. “Everybody is different,” she told me. “Some let you
install the software anywhere on-site. Some will only allow a certain number of copies. Some can be placed on a server, while some only allow client installations. Some are tied to a particular user. It would make my life easier if things were consistent.”
Late in the afternoon, Sheri received a call for an instruc- tor requesting disks for a piece of software. She told him “sure,” if he would come up and get it. He arrived at the door shortly afterward. She gave him the disks and made him sign for them in a notebook. “I’ll have them back to you tomor- row morning,” he said. Sheri explained, “There are two or three instructors who have administrative privileges on their machines. They do their own installations and their own support.” I asked if they track the software on those instruc- tors’ machines. Sheri told me that they do as best as they can, but the instructors can do pretty much as they want. To get the admin privileges, they have to sign a release saying they won’t violate any licensing agreements and that they accept the fact that the school IT staff will not support their computers.
Following this, it was time to quit. Sheri shut down her com- puter. I thanked her for allowing me to follow her and wished her “good evening.”
1. Study the samples given earlier. Make a list of questions you would ask about the data in them.
2. Identify the stakeholders in the software-tracking system.
3. Prepare a plan for a 1-hour interview with representa- tives of the stakeholders listed earlier. Then meet with the instructor to discuss possible answers to the questions.
4. Create a questionnaire for faculty and staff about changes they would like to see in the request process.
5. Review the job shadowing report. Do you see any excep- tions? Do you see any additional business rules? What additional questions does the report raise?
WeSTLAke reSeArcH HoSPiTAL
The drug study is unique in many ways. For one, the forms and the type of information they capture are more complex. For another, privacy rules make it difficult to shadow doctors or researchers. But, still, if you are going to create a database, you must begin to gather the requirements and figure out what data are needed to be tracked.
Here is the Initial Medical Form that each patient is asked to fill out:
M02_CONG4418_02_SE_C02.indd 41 03/07/13 6:31 PM
42 Chapter 2 • Gathering Information
Initial Medical History Form Name _______________ Date _______________
Birth Date _______________
City _______________ State _______________ Zip _______________
Phone _______________ Email _______________
List any prescription or nonprescription medicines you are currently taking.
List any known allergies to medicines.
Have you ever been told you had one of the following?
Lung disorder: □ yes □ no
High blood pressure: □ yes □ no Heart trouble: □ yes □ no Nervous disorder: □ yes □ no Disease or disorder of the digestive tract: □ yes □ no Any form of cancer: □ yes □ no Disease of the kidney: □ yes □ no Diabetes: □ yes □ no Arthritis: □ yes □ no Hepatitis: □ yes □ no Malaria: □ yes □ no
If you answered yes to any of the above, please explain:
Disease or disorder of the blood? (describe) _______________
Any physical defect or deformity? (describe) _______________
Any vision or hearing disorders? (describe) _______________
Any life-threatening conditions? (describe) _______________
How would you describe your depression?
a. Mild and continuous
b. Mild but intermediate
c. Moderate and continuous
d. Moderate but intermittent
e. Severe and continuous
f. Severe but intermittent
When were you first diagnosed with depression? _______________
Which of the following symptoms have you experienced?
□ Sleep difficulties □ Loss of appetite □ Loss of libido □ Inability to leave house □ Anxiety in social situations □ Thoughts of suicide
M02_CONG4418_02_SE_C02.indd 42 03/07/13 6:31 PM
Chapter 2 • Gathering Information 43
Briefly describe your history of depression. Include any earlier attempts at treatment.
Is there a history of depression in your family?
□ Yes □ No If yes, explain.
The next form is the form the doctor would fill out for each patient visit.
patient Visit Form Vitals
Blood Pressure _______________
How does the patient rate his/her depression for this period on a scale of 1 to 5, with 5 being the most severe?
Rate each of the symptoms the patient has experienced on a scale of 0 to 5, with 0 being not at all, and 5 being severe
□ Sleep difficulties □ Loss of appetite □ Loss of libido □ Inability to leave house □ Anxiety in social situations □ Thoughts of suicide List any additional symptoms or side effects.
□ Continue with study □ Drop from study If drop, explain.
Job Shadow Report
The doctors and the directors of the study were reluctant to allow me to observe them with an actual patient, but one of the doctors, Dr. Lewis, did agree to sit with me and walk me through the process of a patient visit.
“The first thing I do in the morning,” he told me, “is review the day’s appointments.” He turned on the computer and showed me the way it is currently done. The secretary sends an email with a table of the patients and times of the appoint- ments. He prints out the list and then goes to his cabinet to pull out the files of the individual patients for review. He reviews
M02_CONG4418_02_SE_C02.indd 43 03/07/13 6:31 PM
44 Chapter 2 • Gathering Information
their initial medical history and the notes of previous visits. He makes some notes on a notepad for each patient.
When the first patient arrives, Dr. Lewis greets them and asks how they are doing. He told me he keeps it casual, but he notes any complaints or signs of deepening depression. Then he goes through the parts of the Patient Visitation Form. The nurse has already taken the patient’s blood pressure, heart rate, and weight. He looks at them, and if the blood pressure is high, or if there has been a dramatic change in one of the measures since the last visit, he asks the patient about it. Then he asks about their depression. He doesn’t necessarily use the exact words of the form or follow it in order, but he makes sure he covers all of it. He records a few notes in a notebook while the patient talks but waits until the patient leaves to write most of the summary. He also waits until the end to make his recommendation to con- tinue or to drop the patient from the study.
I asked Dr. Lewis how he makes that determination. He told me that it is a judgment call. Most of the time it’s in the patient’s interest to continue with the study, but if the patient is showing signs of significant side effects or if the patient seems in eminent danger of doing harm to himself or herself, I would recommend the patient be dropped and given alternative or more aggressive
treatment. I asked if there were any other reasons for dropping a patient. He said that some patients were dropped from the study because of lack of participation, because they didn’t show up for appointments, or were inconsistent in taking their medi- cations. He also noted he always worried that such patients were possibly the most depressed and needed the most help.
1. Study the forms presented earlier. Make a list of ques- tions you would ask about the data in them.
2. Identify the stakeholders in the drug study. 3. Prepare for a 1-hour interview with representatives
of the stakeholders listed earlier. Then meet with the instructor to discuss possible answers to the interview questions.
4. Create a questionnaire for doctors about what they think would help improve the process.
5. Review the Job Shadow Report. Do you see any excep- tions? Do you find any additional business rules in the account? What additional questions does the report raise?
Show Times: local Shows and acts Patty and Dennis thought for a long while about what documents would be relevant to their database. Patti went around town and looked at the posts on shop windows and power poles. She copied some of the text. Here are two typical examples:
Turquoise Cadillac,Tuesday Nov 6with Unibrow and Breazy, The Croc, $15 adv ∣ 8 pm doors, All ages + Bar W/ID, Tickets available at http://theCroc.com, Team5er Phase 2 events presents Winter White[Q13] Featuring Willoby and Monkey Tree, also Tic Toc, T Funk, Jelly Brown. Tickets go on sale 10/20/2014, Start at only $10. General admission only. Physical tickets available at __________________________________________________________
Dennis searched out ads in local papers. Here are two examples of these:
all ages artemis, Whitney, lord Bird Fri Dec 14 at 8 pm. Live
Hollow Planet Radio
2018A E. Lewis St
Staff pickall ages
Damien, naomi, Guests
Fri Dec 14 at 8 pm.
St Joseph’s Cathedral
1245 10th Ave E
M02_CONG4418_02_SE_C02.indd 44 03/07/13 6:31 PM
Chapter 2 • Gathering Information 45
Finally, Dennis got a form from a paper for a band to enter information for their upcoming music section.
Type of Act (live/dj)
Job Shadow Report
Dennis and Patty thought they should get greater insight into just how acts are promoted. Dennis decided to follow a friend of his named Ken who was a musician. His band had a show coming up. Patty decided to follow a program director at a pop- ular venue around for an afternoon.
Dennis greeted his friend Ken with a cup of coffee. Ken took it gratefully and said they had a busy day ahead of them. He had created a poster for the show on his computer. The first order of business was to go to a local printer and get about 200 copies printed. Dennis asked “why don’t you just print them out yourself? Ken explained that sometimes they did, but the quality wasn’t as good, and the ink often ran if it got wet. Also, it was actually cheaper to go to a printer than to print them on his own machine. After he got the copies, he went to the venue where the show was going to be held. He gave them about 50 copies. They put one in the window. Ken told Dennis, “They hire a kid to go post the bill on the local power poles and walls.” Then he called two of the local free papers. He had placed adds in both of them and he wanted to make sure they came out soon and correctly. He went over the information again. He gave them the venue name, the name of his band, and the name of another band that was also playing. “No tickets,” he said. “Fifteen dollar cover at the door, 21 and over.” After he hung up he noted: “It costs about $30 dollars to run an ad for a week. We don’t always bother, but we want a good turnout at this show. We will also email the fans on our fan list.”
“What’s your fan list?” “We keep a spreadsheet of fans who want to sign up for
notifications. We just take their name and email. It helps.” “Do most artists keep fan lists?” “Some do and some don’t. It depends a bit on how long they
have been around and how many concerts they have done.” They spent the rest of the afternoon asking shop owners
to take and display posters and pasting a few on power poles along the city sidewalks.
Patty followed Ed around. His job was to recruit and sched- ule acts for his club. She asked, “how do you find acts?”
He replied, “Sometimes it is word of mouth. We hear good things about some act, so we look them up. We usually ask for an audition. If they sound right for us, we’ll sign them up.”
While they were talking, a woman came in. She had a gui- tar slung on her back. She handed Ed a business card. “I am
looking for some gigs,” she said. He asked what kind of music she played and if she had a band. He also asked if she would be willing to open for another act. She was. He scheduled a time to hear her play the next day. “Sometimes we need an opening act on short notice,” he said. “It’s always good to have a variety of acts on call.“
Patty thinks about this for a moment. “So you might not know ahead of time which act is going to be the opening act? How do you promote that?”
Ed replies, “we just list the feature act and say with guest performers. Keep it generic.” After a while he added, “Your database idea could be a real help to get the word out. I am pretty sure my company would even pay a bit to ensure that all our shows were listed.”
1. Study the documents just presented. Make a list of ques- tions you would ask about the data in them.
2. Identify the stakeholders in the music tracking database. 3. Prepare for a 1-hour interview with representatives of
the stakeholders listed in the report. Then meet with the instructor to discuss possible answers to the interview questions.
4. Create a questionnaire for venues or artists about what they think would help improve the process of getting the information out about shows.
5. Review the Job Shadow Report. Do you see any excep- tions? Do you find any additional business rules in the account? What additional questions does the report raise?
SUGGeSTionS for ScenArioS
It is obvious these scenarios don’t have all the information that you need. Focus your questions on making sure you understand all the bits of data you will need to make your database. You, your team, if you are working with a group, and your instruc- tor can decide on the answers to these questions. As you dis- cuss possible answers, several real-world issues may arise that add a great deal of complexity to the database design. Handling some of these complexities can be a good exercise, but students and instructors should feel free to simplify where needed. Too much complexity can be overwhelming to someone just begin- ning to develop databases.
M02_CONG4418_02_SE_C02.indd 45 03/07/13 6:31 PM