Database Design

Project

Choose one of the first three scenarios: "Wild Wood Apartments," "Vince's Vinyl," or "Grandfield College."

Do NOT choose the scenario titled "Westlake Research Hospital" because this scenario will act as an example throughout this course.

Instructions:

1. Read your selected scenario.

2. Review the "Deliverables" section below.

Database Design and Implementation

3. Review the "Suggestions for Scenarios" section on your textbook for important details and ideas to assist you with your deliverables.

4. Review "The Statement of Work". Use this as a model for your own statement of work.

5. When you have completed all of the deliverables, compress the files together into a single .zip file for upload.

Contact your instructor if you have any questions.

Deliverables:

1. Major Topics: List the major topics for this database, and explain why each should be considered a major topic ofthe database.

2. Statement of Work: Write a draft statement of work. Include a brief history, a statement of scope, objectives, and a preliminary timeline.

Scenario Page Number for "To Do" List

Wild Wood Apartments

Vince's Vinyl

Grandfield College

Database Design and Implementation

Unit I: Westlake Hospital Example

1. List the major topics for t his database.

Topics should include patient, doctor, drug, appointment, possibly medical history, and researchers.

2. Write a draft statement of work. Include a brief history, a statement of scope, objectives, and a preliminary timeline.

Most of the materials needed for the statement of work are in the scenario description. The timeline is also copied from the example in the chapter. Student timelines should reflect the same basic tasks, though they can vary in times allotted and deliverables. Below is a possible statement of work:

HISTORY

Westlake Hospital will be 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 traditional 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 interview with his or her doctor. The drugs will be dispersed in a generic bottle by the two supervisors, one of whom is a pharmacist.

To track this study, the hospital will need a database.

SCOPE

The database will need to track patient information from their first screening through each of their interviews. It will track the symptoms of their depression and any specific physical side effects. Each doctor will need to be able to see the information of his or her own patients, but not the information of other doctors' patients. 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. Reports should be generated comparing the results of the two groups over time. Ther e 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.

OBJECTIVES

. Track patients in a double-blind drug study.

. Track symptoms and side effects.

. Maintain the double blind on the part of doctors and patients.

. Generate reports that compare the results of the test groups.

Database Design and Implementation

TASKS AND TIMELINE

1. Gathering Data: This task will consist of a number of interviews, questionnaires, and observations.

2. Analyzing Data: The data gathered will be analyzed to determine business rules and preliminary data modeling.

3. Normalization: The data model will be completed with entities and relationships normalized.

4. Building the Physical Database: The data model will be translated to the relational database management system: Tables, columns with specific data types, and relational and other constraints created.

5. Testing and Security: Sample data will be entered and each of the business rules and requirements will be tested. General database security and security related to business rules will also be tested.

6. Database Completion and Installation: Final changes and corrections are made. Sample data will be removed and the database installed on a server. Final testing for server access and conne ctions.

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 dear 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 history, 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 potential 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.