CSC $2660$ Final Project Part $1$

You have been asked by a local doctor's office to design a database for their needs. They were

recently contacted by the local state authority and now need to begin the process of digitizing

their records in a more formal way. They currently have $3,000$ patients that are in an Excel

spreadsheet, but now they want to convert this database into an SQL database because of the

potential growth they see in the next few years and the requirement by the local state authority.

The spreadsheet that they have been using is attached to the Brightspace drop box and it has

many issues since they repeat data, there is missing data, additional columns need to be added to

many sheets as well as other issues.

The first step of the process is to evaluate their current method of tracking patients. You will

want to ask the contact person at the health care facility $($the contact person is the instructor$)$

during the evaluation if you have any questions and recommendations.

You need to modify the spreadsheet in any way you desire to show how you would make

changes to begin making the transition to an SQL database. You will want to think about how

data is repeated, what additional fields and sheets that might be needed, and the business rules

that will need to be reflected in the database as it gets designed.

For instance, a business rule might be:

A Patient sees only one Doctor and a Doctor sees many patients.

A Patien$($t$)/($c$)$ustomer has only one Insurance provider and an Insurance provider has many

Patien$($t$)/($c$)$ustomers$.$

Your first steps are to modify the spreadsheet to more closely show the data needed as well as

begin to develop the spreadsheet to be converted to an SQL database that has good design

aspects. Open the spreadsheet provided. Note at the bottom of sheet there are tabs for the

following sheets:

Patient

Doctors

Insurance company

Schedule

Look over each sheet to see how it is setup and then think about how you might improve the

current design. To do this, think about how the data might be more meaningful. For example,

perhaps separating the name into a First Name column and a Last Name column. If you decide

to do this, then indicate by color code of the columns that this was changed. If you think you

need to add a column or two, indicate those by color coding them. Also, think about repeat data.

Notice that the address is in two places, on the Patient sheet and the Schedule sheet. Decide if

this is a good design, if not then make the changes you think would be a better design and

explain on the sheet why you changed it$.$ Put notes below the spreadsheet about the changes that

you have made and why you made the changes. Do not be vague. Explain as much as possible.

Second, you need to develop the business rules that will be reflected in your design of the Excel database. My expectation is that this description is vague enough that you will ask me questions on what is needed in the final product you are developing. Email me with questions you have on the direction of your design. Remember I am the customer in the project, and I know what I want, but I don$$t know databases. You know databases but you do not know all I want. You need to submit the modified spreadsheet with updates to fields, add columns where you think it is necessary, drill down into data where appropriate. Add additional sheets if you see there is something missing or after contacting me and you see additional data is needed. The products you submit should be very clear to me as the direction you are taking this project. Treat this as a product you are producing for a customer, not a product for a college paper. You can put your name at the top of the documents, but my expectation, as the customer, that these will be professional business documents. Create a fake business and put that at the top of the documents along with contact information. You will also need to submit a list of Business rules in a Word document. You will need to put these two products in a folder that has your First Name and Last Name initial and $-$P$1.$ An example with my name would be: JeffD$-$P$1.$ This is the folder name

Excel document:

Dr$.$ Davis and associates Medical Practice Sample Data Name Address Sex DOB Insurance provider Doctor Visits Medications John Doe $123$ Main St$,$ Jamestwon NY $14111$ M $1/1/1990$ Univera Dr$.$ Jones $11/10/2015$ and $2/22/2018$ Asprin one time a day, prozak Jane Smith $45$ Maple Ave, Falconer NY $14333$ F $2/2/1992$ Blue Cross$/$Blue Shield Dr$.$ Wilds $7/7/2013,8/8/2015$ and $9/9/2017$ Diamox, statin Jane Smith has two children, Betty and Barney that are on her insurance and are patients of the medical practice Betty Smith $46$ Maple Ave, Falconer NY $14333$ F $6/6/2015$ Dr$.$ Wilds $7/1/2016$ none Barney Smith $47$ Maple Ave, Falconer NY $14333$ M $7/29/2016$ Dr$.$ Wilds $8/1/2017$ none Second, you need to develop the business rules that will be reflected in your design of the Excel database. My expectation is that this description