IT 650 Principles of Design and Database

Please review the attached. 8 pages needed not included reference page for the Wild Wood Apartments. $5 per page.

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

Scenarios

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)

ISBN: 978-0-13-302441-8

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

Resolution
Date

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

expenses

utilities 2,450.00

Maintenance 11,298.00

Repairs 9,790.00

Insurance 5,340.00

new Tenant Cleaning 10,400.00

Wages 19,200.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.

To do

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’S VinyL

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.

Date 5/14/2013

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

fair 4

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

excellent 12.25

Laura Hall 206.555.2080 Dark Side of the Moon good 4.45

Here is an example of a sale to a customer:

Date 5/12/2013

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

15.50

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.

To do

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?

GrAnfieLD coLLeGe

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

PhotoShop 6/4/2013

DreamWeaver 6/4/2013

CCS number location assigned user

3114 Rm212 Larson

Software Install Date Rmv Date

Vista Business 4/15/2013

MsOffice 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:

Requests

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

5/24/2013 Pending

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.”

To Do

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 _______________

Address _______________

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

(continued)

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 _______________

Weight _____________________

Pulse ______________________

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.

__________________________________________________________________________________
__________________________________________________________________________________

Doctor’s Notes

Recommendation:

□ 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.

To do

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

(Central District)

map

_________________________________________________________________________________

Staff pickall ages

Damien, naomi, Guests

Fri Dec 14 at 8 pm.
Live

St Joseph’s Cathedral

323-0300

1245 10th Ave E

(Capitol Hill)

map

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.

event Form

Venue Name

Venue Location

Show Description

Featured Act

Type of Act (live/dj)

Other Acts

Times

Cover Charge

Age Restriction

Other Restrictions

Ticket Availability

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.”

To Dos

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

IT 650 Milestone Two Rubric

Submit the preliminary design of the database for the final project. Devise a conceptual model that will best address the case study scenario you selected for the
final project. Your model should include all necessary entities, relationships, attributes, and business rules. Based on the conceptual model, illustrate a logical
model for your DBMS that accurately represents all necessary aspects of the DBMS to address the solution. Create a physical database design that builds on the
nonphysical (conceptual and logical) models you crafted. This design includes the conceptual and logical models of the database, as well as the physical design of
the database.

Specifically, the following critical elements must be addressed:

 Analysis and Design
A. Conceptual Model: Based on the business problem or challenge, devise a conceptual model that would best address the problem. Your model

should include all necessary entities, relationships, attributes, and business rules.
B. Logical Model: Based on the conceptual model, illustrate a logical model for your DBMS that accurately represents all necessary aspects of the

DBMS to address the solution.
C. Physical Design: Create a physical database design that builds on the nonphysical (conceptual and logical) models you crafted.

Guidelines for Submission: Your paper must be submitted as a 6- to 12-page Microsoft Word document using appropriate modeling language and/or diagrams. It
should use double spacing, 12-point Times New Roman font, and one-inch margins. It should include at least three sources, which should be cited in APA format.

Critical Elements Exemplary (100%) Proficient (90%) Needs Improvement (70%) Not Evident (0%) Value

Analysis and Design:
Conceptual Model

Meets “Proficient” criteria, and
model evidences detailed
attention or keen insight into
conceptual design needs and skills

Devises a comprehensive
conceptual model that would
logically address the identified
problem

Devises a conceptual model that
does not attend to all necessary
entities, relationships, attributes,
and business rules or would not
logically address the identified
problem

Does not devise a conceptual
model

30

Analysis and Design:
Logical Model

Meets “Proficient” criteria and
evidences keen insight into
representation, communication, or
depiction of logical model design
needs for business scenarios

Illustrates an accurate logical
model that details the necessary
aspects of the DBMS needed to
address the business needs and
identified problem

Illustrates a logical model that
lacks accuracy or necessary detail
regarding aspects of the DBMS
needed to address the business
needs and identified problem

Does not illustrate a logical model 30

Analysis and Design:
Physical Design

Meets “Proficient” criteria and
exemplifies the knowledge and
skills necessary to extrapolate a
physical design from nonphysical
models for solving data challenges

Creates a physical design that
precisely builds on the nonphysical
models with incorporation of
physical aspects

Creates a physical design that
builds on the nonphysical models
with incorporation of physical
aspects, but with a lack of
precision

Does not create a physical design
that builds on the nonphysical
models with incorporation of
physical aspects

30

Articulation of
Response

Submission is free of errors related
to citations, grammar, spelling,
syntax, and organization and is
presented in a professional and
easy to read format

Submission has no major errors
related to citations, grammar,
spelling, syntax, or organization

Submission has major errors
related to citations, grammar,
spelling, syntax, or organization
that negatively impact readability
and articulation of main ideas

Submission has critical errors
related to citations, grammar,
spelling, syntax, or organization
that prevent understanding of
ideas

10

Total 100%

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