A physician office (private practice) approached you in an effort to convert their paperwork process into a health information database. The physician provided a copy of the paperwork used in the office to collect patient information. The physician also explained the need to secure patient information according to HIPPA rules and regulations. Additional information will be provided upon request. The physician would like to add to patient records results from labs and imaging. In addition, the physician would like to link with a list of pharmacies in the area and send patient prescription electronically. As a database expert you will be developing patient database and all required tables using relational database approach. Requirement:

1. Analyze the provided forms below identifying the entities that should be tracked in the system.

2. Identify the fields (attributes) associated with each entity, listing possible candidate keys (determinants).

3. Based on your analysis and the given forms identify all possible functional dependencies (remember that those functional dependencies will not only assist in creating the proper tables but also will assist in understanding relationships between tables).

4. Create a separate entity to secure database (just write the name of the entity and its attributes) Forms and Documentation Provided: The following are copies of the paper forms used in the office. Use these form to complete the assignment requirements above:

Questions:

How do I create tables that represent the functional dependenices (see requirements, tables should be patient info, insurance, health history, and include pharmacy with the lab records), along with the relationships between the tables?

What are the fields (attributes) associated with each entity, and what is the candidate keys?

How do I secure the database?

PATIENT INFORMATION Thank you for choosing our office! In order to serve you properly, we need the following information. Please print. All information will be confidential Date SSN atient Name Patient Home phone MaleFemale Bithdate City Check appropriate box:Minor Single Married Divorced Widowed Patient's or parent's employer Business address Spouse or parent's name If patient is a student, name of schoolcollege Whom may we thank for referring you Person to contact in case of emergency In case of a medical emergency, if the patient is of school age 15+, it is all right to treat in my absence Separated Work phone State Work phone State Parent or guardian signature Responsible Party Name of person responsible for this account Relationship to patient Home phone Financial institution Work phone Driver's license Is this person currently a patient at our office Insurance Information Name of insured Relationship to patient Date emplayed Social Security Number Work phone Name of employer Addeess of employer Insurance company Ins. Co. address How much is your deductibler State Union or local State Zip Group # City How much have you used? Max. annual benefit? No If Do you have any additional insurance? Sociall Security Number City City yes, complete the Relationship to patient Date employed Yes Name of insured Name of employer Address of employer Insurance company Ins. Co adkdress How much is your deductible? Work phone tate -Union or local # State How much have you used Max, annual benefit I authorize release of any information concerning my for my child's) health care, advice and treatment provided for the purpose of evaluating and administering caims for insurance benefts. also hereby authorize payment of insurance benefits ctherwise payable to me directly to the doctor Signature of patient or parent if minor NEW PATIENT HEALTH HISTORY FORM Patient Name: Birth date: Date: Referring Physician: Address Pharmacy Name: Phone Number Reason for today's visit Please describe this problem PRIOR SURGERIES CURRENT/PRIOR ILLNESSES/INJURIES Please list ALL medications (prescription and non-prescription) that you take. (Include herbal remedies, vitamins, over- the-counter, street MEDICATION DOSAGE MEDICATION DOSAGE Do you take any blood thinning products such as Vitamin E, Plavix, Coumadin, or Aspirin? NO DYES Do you have a YES ALLERGY TYPE Do you smoke?n TYPE OF SMOKING NO and Never have YES (Please explain below) HOW MUCH HOW LONG Do you drink alcohol? NO and Never have O Socially Only Daily Hand Dominance: RIGHT Beer/ Wine Hard Liquor LEFT Please describe health issue below GOOD/ NONE FAMILY HISTORY UNKNOWN REASON FOR DEATH MOTHER FATHER SIBLING(S) OTHER HEREDITARY ILLNESS Patient Signature: Date:- Physician Signature: Date Redewed:--. NO YES, Explain NO YES, Explain Weight Loss Changes in Moles Bleeding Disorders story of Kelolds Neurological Problems enital or Oral Herpes Diarrhes Urinary Tract Infection Blood in Stool Kidney Problems Eyes Hearing Probiems High Blood Pressure Sinus Problems Mood Swirgs Araiety/ Depression sthma Sleep Aprea Please Est any other conditionsiinesses not indicated above Petient Sgnature: Physician Signature: Date Reviewed: Patient Name Outpatient Lab Order Form Mercy Lab Fax Number 641-428-7886 Send Patient Home Send Patient to Office Page Provider on Pager# Call Results to Fax Results to Courtesy Copy to "Tests wth anbehind them means that reflex testing may be performed- Please refer to the Mercy Lab Test Index Tests Tests Tests ALT ALTISGPT ASTISGOT ANA BUN C Reactive Protein CRP Sensitive CA 125 CBC with Auto Dr CBC with No Dif CBC ith Manual Din T3 Free T4 Free Testosterone, Total and Free Hepanis B Surace Chronic Profie* TSTF HIV Medicare Screen Iron and Iron Latex RA TRP Troponin CA125 LIPS URIC Uric Acid Magnesium Vitamin B12/Folate arathyroid Harmone Intact HDL Prenatal Profile WHV Profile no MIv LDL Direct Cholesteral ATPN PTR PSA CREAT Creatinine Creatinine Clearance 24 hr Urine Urine Total Volume Panel (TSHT PSAS PSA, Medicare Screen CRTMM CMPL LYTE RNPL Comprehensive Metabolic Panel Screen Random Urine Protein 24 hr Urine otal Valume Quad Maternal Screen Electradyte Renal Panel VPRT General Heath Panel (CBCAD, TSH, CMPL) RETIC ESR Drug Levels HCG Quant Serum HGBX Marker AFP Last Dose Date and Time . 4. Mayo Physician Order Form - Imaging Services OREGON HEALTH Diagnostic Imaging Services 3181 S.W. Sam Jackson Park Road, Portland OR 97239 &SCIENCE Date: UNIVERSITY Phone: 503-418-0990 Fax: 503-494-4621 PATIENT INFORMATION Patient Name: Patient Phone ICD 9 Code: Reason for Exam: Date of Birth Please call Patient Patient will call to schedule Authorization #: REQUESTING PHYSICIAN INFORMATION Referring Physician: Referring Physician Signature: Results (check all that apply): D E-mail report (e-mail) 0 Fax report: (fax #) Phone O CD with Images Special Request Phone Report: (phone #) EXAM FOCUS Brain MRI Brain MRA Cervical Spine Extremity (specify) Neck MRI O Neck MRA ThoraccLumbar wol contrast O wwo contrast Vagal Nerve Stimulator: Program both generator output current and magnet output current to OMA to the MR Brain Sinus Cervical Spine am device to CT Chest Thoracic O Abdomen O Pelvis Lumbar w contrast wol wwo contrast contrast Extremity (specify) Other Mammogram Abdomen Bone Other Pelvis OBVGYN SPEC PETICT Barium Enema (please select) O With air contrast OWithout air contrast O Upper G.I. (please select) O With small bowel series OWithout small bowel series General Radiology Volding Cystourethrogram X-ray (specify): Fluoro Other (specify) Peripheral Arial Exam | O Nielsen Cold Challenge Vascular Lab PPG's Venous Carotid Temporal Artery ABrs with waveform Graft Flow Upper ExtemityTranscranial Doppler O Lower Extremity Arterial Duplex O Dialysis Graft Eval. O Abdomen (please select) FingerToe(s) O Renal O Mesenteric OPortal Hepatic OAAA ORenal Transplant O Other (specify) O R Left Other Specify: Scan to P0-7070 PATIENT INFORMATION Thank you for choosing our office! In order to serve you properly, we need the following information. Please print. All information will be confidential Date SSN atient Name Patient Home phone MaleFemale Bithdate City Check appropriate box:Minor Single Married Divorced Widowed Patient's or parent's employer Business address Spouse or parent's name If patient is a student, name of schoolcollege Whom may we thank for referring you Person to contact in case of emergency In case of a medical emergency, if the patient is of school age 15+, it is all right to treat in my absence Separated Work phone State Work phone State Parent or guardian signature Responsible Party Name of person responsible for this account Relationship to patient Home phone Financial institution Work phone Driver's license Is this person currently a patient at our office Insurance Information Name of insured Relationship to patient Date emplayed Social Security Number Work phone Name of employer Addeess of employer Insurance company Ins. Co. address How much is your deductibler State Union or local State Zip Group # City How much have you used? Max. annual benefit? No If Do you have any additional insurance? Sociall Security Number City City yes, complete the Relationship to patient Date employed Yes Name of insured Name of employer Address of employer Insurance company Ins. Co adkdress How much is your deductible? Work phone tate -Union or local # State How much have you used Max, annual benefit I authorize release of any information concerning my for my child's) health care, advice and treatment provided for the purpose of evaluating and administering caims for insurance benefts. also hereby authorize payment of insurance benefits ctherwise payable to me directly to the doctor Signature of patient or parent if minor NEW PATIENT HEALTH HISTORY FORM Patient Name: Birth date: Date: Referring Physician: Address Pharmacy Name: Phone Number Reason for today's visit Please describe this problem PRIOR SURGERIES CURRENT/PRIOR ILLNESSES/INJURIES Please list ALL medications (prescription and non-prescription) that you take. (Include herbal remedies, vitamins, over- the-counter, street MEDICATION DOSAGE MEDICATION DOSAGE Do you take any blood thinning products such as Vitamin E, Plavix, Coumadin, or Aspirin? NO DYES Do you have a YES ALLERGY TYPE Do you smoke?n TYPE OF SMOKING NO and Never have YES (Please explain below) HOW MUCH HOW LONG Do you drink alcohol? NO and Never have O Socially Only Daily Hand Dominance: RIGHT Beer/ Wine Hard Liquor LEFT Please describe health issue below GOOD/ NONE FAMILY HISTORY UNKNOWN REASON FOR DEATH MOTHER FATHER SIBLING(S) OTHER HEREDITARY ILLNESS Patient Signature: Date:- Physician Signature: Date Redewed:--. NO YES, Explain NO YES, Explain Weight Loss Changes in Moles Bleeding Disorders story of Kelolds Neurological Problems enital or Oral Herpes Diarrhes Urinary Tract Infection Blood in Stool Kidney Problems Eyes Hearing Probiems High Blood Pressure Sinus Problems Mood Swirgs Araiety/ Depression sthma Sleep Aprea Please Est any other conditionsiinesses not indicated above Petient Sgnature: Physician Signature: Date Reviewed: Patient Name Outpatient Lab Order Form Mercy Lab Fax Number 641-428-7886 Send Patient Home Send Patient to Office Page Provider on Pager# Call Results to Fax Results to Courtesy Copy to "Tests wth anbehind them means that reflex testing may be performed- Please refer to the Mercy Lab Test Index Tests Tests Tests ALT ALTISGPT ASTISGOT ANA BUN C Reactive Protein CRP Sensitive CA 125 CBC with Auto Dr CBC with No Dif CBC ith Manual Din T3 Free T4 Free Testosterone, Total and Free Hepanis B Surace Chronic Profie* TSTF HIV Medicare Screen Iron and Iron Latex RA TRP Troponin CA125 LIPS URIC Uric Acid Magnesium Vitamin B12/Folate arathyroid Harmone Intact HDL Prenatal Profile WHV Profile no MIv LDL Direct Cholesteral ATPN PTR PSA CREAT Creatinine Creatinine Clearance 24 hr Urine Urine Total Volume Panel (TSHT PSAS PSA, Medicare Screen CRTMM CMPL LYTE RNPL Comprehensive Metabolic Panel Screen Random Urine Protein 24 hr Urine otal Valume Quad Maternal Screen Electradyte Renal Panel VPRT General Heath Panel (CBCAD, TSH, CMPL) RETIC ESR Drug Levels HCG Quant Serum HGBX Marker AFP Last Dose Date and Time . 4. Mayo Physician Order Form - Imaging Services OREGON HEALTH Diagnostic Imaging Services 3181 S.W. Sam Jackson Park Road, Portland OR 97239 &SCIENCE Date: UNIVERSITY Phone: 503-418-0990 Fax: 503-494-4621 PATIENT INFORMATION Patient Name: Patient Phone ICD 9 Code: Reason for Exam: Date of Birth Please call Patient Patient will call to schedule Authorization #: REQUESTING PHYSICIAN INFORMATION Referring Physician: Referring Physician Signature: Results (check all that apply): D E-mail report (e-mail) 0 Fax report: (fax #) Phone O CD with Images Special Request Phone Report: (phone #) EXAM FOCUS Brain MRI Brain MRA Cervical Spine Extremity (specify) Neck MRI O Neck MRA ThoraccLumbar wol contrast O wwo contrast Vagal Nerve Stimulator: Program both generator output current and magnet output current to OMA to the MR Brain Sinus Cervical Spine am device to CT Chest Thoracic O Abdomen O Pelvis Lumbar w contrast wol wwo contrast contrast Extremity (specify) Other Mammogram Abdomen Bone Other Pelvis OBVGYN SPEC PETICT Barium Enema (please select) O With air contrast OWithout air contrast O Upper G.I. (please select) O With small bowel series OWithout small bowel series General Radiology Volding Cystourethrogram X-ray (specify): Fluoro Other (specify) Peripheral Arial Exam | O Nielsen Cold Challenge Vascular Lab PPG's Venous Carotid Temporal Artery ABrs with waveform Graft Flow Upper ExtemityTranscranial Doppler O Lower Extremity Arterial Duplex O Dialysis Graft Eval. O Abdomen (please select) FingerToe(s) O Renal O Mesenteric OPortal Hepatic OAAA ORenal Transplant O Other (specify) O R Left Other Specify: Scan to P0-7070