Introduction:

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: ***PLEASE ANSWER ALL QUESTIONS CORRECTLY AND I WILL THUMBS UP AND COMMENT***

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 Patient Name Patient SSN Male Female Birthdate Home phone Address City State Zip Check appropriate box: B Minor Single Married Divorced Widowed Separated Patient's or parent's employer Work phone Business address City Slate Zip Spouse or parent's name Employer Work phone If patient is a student name of school/college City State Whom may we thank for referring you? Person to contact in case of emergency Phone In case of a medical emergency, if the patient is of school age 15+, it is all right to treat in my absence, X Parent or guardian signature Responsible Party Name of person responsible for this account Relationship to patient Adkiress Home phone Driver's license Birthdate Financial institution Employer Work phone Is this person currently a patient at our office! Yes No Insurance Information Name of insured Relationship to patient Birthdate Social Security Number Date employed Name of employer Work phone Address of employer State Zip Insurance company Group Union or local Ins. Co, address City State Zip How much is your deductible? How much have you used? Max. annual benefit? Do you have any additional insurance? Yes No If yes, complete the following: Name of insured Relationship to patient Birthdate Social Security Number Date employed Name of employer Work phone Address of employer City Zip Insurance company Group Union or local Ins. Co, adress City Zip How much is your deductible? How much have you used! Max. annual benefit Suite I authorize release of any information concering my for my child's health care, advice and treatment provided for the purpose of evaluating and administering claims for insurance benefits. I also hereby authorize payment of insurance benefits otherwise payable to me directly to the doctor X Signature of patient or parenti minor Date NEW PATIENT HEALTH HISTORY FORM Patient Name: Birth date:_ Date: Referring Physician: Pharmacy Name: Address: 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 drugs, prescriptions etc.) MEDICATION DOSAGE MEDICATION DOSAGE Do you take any blood thinning products such as Vitamin E, Plavix, Coumadin, or Aspirin? NO DYES YES Do you have any food, environmental, or drug allergies? | NO ALLERGY TYPE (Please explain below) REACTION Do you smoke? NO and Never have YES (Please explain below) TYPE OF SMOKING (cigarette, pipe marijuana, chew, etc.) HOW MUCH HOW LONG Do you drink alcohol? NO and Never have socially Only Daily Beer/ Wine Hard Liquor Occupation: Hand Dominance: RIGHT LEFT UNKNOWN ILLNESSES/ REASON FOR DEATH Please describe any family health issue below: FAMILY HISTORY GOOD/ NONE MOTHER FATHER SIBLING(S) OTHER HEREDITARY ILLNESS Patient Signature: Date: JJ- Date Reviewed: Physician Signature: HEALTH HISTORY FORM 2 Do you have or have you ever had any of the following: Symptoms/ illness NO YES, Explain Constitutional NO YES, Explain Symptoms/Illness Skin Fever or Chills Breast Abnormalities Weight Loss Hematologic Nipple Discharge Last Mammogram Date: Changes in Moles Hepatitis HIV/ Other Blood Diseases Lesions Bleeding Disorders Rashes Endocrine Thyroid Problems Diabetes History of Keloids Neurological Neurological Problems Musculoskeletal Headaches Arthritis GENITOURINARY Mobility/Joint Problems Genital or Oral Herpes S.T.D.'s GASTROINTESTINAL Constipation Blood in Urine Diarrhea Urinary Tract Infection Problems Urinating Blood in Stool Nausea/ Vomiting Liver Problems Prostate Problems Kidney Problems CARDIOVASCULAR Eyes Heart Problems Vision Problems Deep Vein Thrombosis/DVT ENT Blood Clots in Lungs/Legs Hearing Problems Sinus Problems High Blood Pressure RESPIRATORY PSYCHIATRIC Asthma Mood Swings Anxiety/ Depression Sleep Apnea Please list any other conditions/ illnesses not indicated above: To the best of my know this information is complete and correct. I understand that it is my responsibility to inform my doctor if there are any changes to my health Patient Signature: Date: Physician Signature: Date Reviewed: Patient Name First Outpatient Lab Order Form Last MI Mercy Lab Fax Number 641-428-7886 Birth Date Sex Collection Date Time Collected By Ordering Provider Consult Dr. Patient SSN When Tests are Complete: Send Patient Home Patient Location Patient Phone Number Send Patient to Office Page Provider on Pager# Narrative Signs/Symptoms/Diagnosis Call Results to 1. 2. Fax Results to 3. 4. Courtesy Copy to Notification to Providers and other persons legally authorized to order tests for which Medicare reimbursement will be sought Medicare will pay only for tests that meet the Medicare coverage criteria and are reasonable and necessary to treat or diagnose an individual patient. Medicare does not pay for tests for which documentation, including the patient record, does not support that the tests were reasonable and necessary. Medicare generally does not cover routine screening tests even if the physician or other authorized test arderer considers the tests appropriate for the patient. **Tests with an * behind them means that reflex testing may be performed - Please refer to the Mercy Lab Test Index Order x Tests Order x Tests Order x Tests ABSN Antibody Screen HCTX Hematocrit NA Sodium ALT ALTISGPT HPSSAB Hepatitis Anti-HBS SYPHL Syphylis IgG Antibody AST AST/SGOT HBSA Hepatitis B Surface Antigen T3F T3 Free ANASCN ANA" HPCHRN Hepatitis Chronic Profile T4F T4 Free BUN BUN HIV HIV Antibody TSTE Testosterone, Total and Free CRP C Reactive Protein HIVM HIV Medicare Screen TRIG Triglyceride HSCRP CRP Sensitive (Cardiac) IBC Iron and Iron Binding TRPI Troponin CA125 CA 125 RA Latex RA TSH TSH CBCAD CBC with Auto Difr LIPS Lipase URIC Uric Acid CBC CBC with No Diff LH LH UA Urinalysis Routine CBCD CBC with Manual Diff MG Magnesium B12F Vitamin B12 / Folate CEA CEA UMAL Microalbumin Urine B12 Vitamin B12 Cell Morphology PTHINT Parathyroid Hormone Intact VD25H Vit D 25-Hydroxy CHOL Cholesterol K Potassium Chemistry Panels HDL HDL Cholesterol PNP Prenatal Profile wHIV METB Basic Metabolic Panel DLDL LDL Direct Cholesterol PNPO Prenatal Profile no HIV ATPN Dermatology Panel CK CK PTR Protime INR HFPL Hepatic Function Panel CREAT Creatinine PSA Prostate Specific Antigen THPO Hypothyroid Panel (TSH,T4F) VCCL Creatinine Clearance 24 hr Urine PSAS PSA, Medicare Screen LIPD Lipid Panel" CRTMM Urine Total Volume needed PEL Protein Electrophoresis CMPL Comprehensive Metabolic Panel DRUG Drug Screen Random Urine Protein 24 hr Urine LYTE Electrolyte VPRT GLUC Glucose Total Volume RNPL Renal Panel GLUG Glucose 1 hr Gestational QUADM Quad Maternal Screen GHP General Health Panel (CBCAD, TSH, CMPL) GLYCO Glycohemoglobin (A1C) RETIC Reticulocyte Count Drug Levels HCGQ HCG Quant Serum ESR Sedimentation Rate HGBX Hemoglobin SMAFP Single Marker AFP Last Dose Date and Time - 1. 2. CM 3. 4. Mayo# Sianature of Orderina Provider Date Physician Order Form - Imaging Services Date: OREGON HEALTH Diagnostic Imaging Services & SCIENCE 3181 S.W. Sam Jackson Park Road, Portland OR 97239 UNIVERSITY Phone: 503-418-0990 Fax: 503-494-4621 PATIENT INFORMATION Patient will call to schedule Patient Name: Date of Birth: Patient Phone: Please call Patient ICD 9 Code Authorization #: Reason for Exam: REQUESTING PHYSICIAN INFORMATION Referring Physician: Phone: Referring Physician Signature: Results (check all that apply): E-mail report: (e-mail) CD with Images Fax report: (fax #) Special Request Phone Report: (phone #). EXAM FOCUS Brain MRI Brain MRA Neck MRI Neck MRA OMRI Cervical Spine Thoracic Lumbar w contrast Extremity (specify wol contrast Other (specify): Ow/wo contrast Vagal Nerve Stimulator. Program both generator output current and magnet output current to OMA prior to the MRI procedure. After MRI is completed, re-program device to original settings Brain Sinus Chest Abdomen Pelvis Owl contrast Cervical Spine Thoracic Lumbar wol contrast Extremity (specify w/wo contrast Other (specify) Diagnostic Screening Others (specify): Mammogram Abdomen Pelvis O OB/GYN Ultrasound Other (specify: Nuclear Medicine Bone SPECT Thyroid Liver-Spleen Head/Neck Lung Breast Lymphoma Other (specify: Barium Enema (please select): O With air contrasto Without air contrast IV. Pyelogram Upper G.I. (please select): O With small bowel series o Without small bowel series General Radiology Volding Cystourethrogram X-ray (specify): Fluoro Other (specify): Vascular Lab Peripheral Arial Exam Venous Chronic Venous Exam OPPG's Upper Extremity Transcranial Doppler Carotid Temporal Artery ABI's with waveform Lower Extremity Nielsen Cold Challenge Graft Flow Arterial Duplex Dialysis Graft Eval. Abdomen (please select) Finger Toe(s) O Renal O Mesenteric O Portal Hepatic O AAA O Renal Transplant Other (specify) Right Left Other Specify Rev OVOS Scan to PO-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 Patient Name Patient SSN Male Female Birthdate Home phone Address City State Zip Check appropriate box: B Minor Single Married Divorced Widowed Separated Patient's or parent's employer Work phone Business address City Slate Zip Spouse or parent's name Employer Work phone If patient is a student name of school/college City State Whom may we thank for referring you? Person to contact in case of emergency Phone In case of a medical emergency, if the patient is of school age 15+, it is all right to treat in my absence, X Parent or guardian signature Responsible Party Name of person responsible for this account Relationship to patient Adkiress Home phone Driver's license Birthdate Financial institution Employer Work phone Is this person currently a patient at our office! Yes No Insurance Information Name of insured Relationship to patient Birthdate Social Security Number Date employed Name of employer Work phone Address of employer State Zip Insurance company Group Union or local Ins. Co, address City State Zip How much is your deductible? How much have you used? Max. annual benefit? Do you have any additional insurance? Yes No If yes, complete the following: Name of insured Relationship to patient Birthdate Social Security Number Date employed Name of employer Work phone Address of employer City Zip Insurance company Group Union or local Ins. Co, adress City Zip How much is your deductible? How much have you used! Max. annual benefit Suite I authorize release of any information concering my for my child's health care, advice and treatment provided for the purpose of evaluating and administering claims for insurance benefits. I also hereby authorize payment of insurance benefits otherwise payable to me directly to the doctor X Signature of patient or parenti minor Date NEW PATIENT HEALTH HISTORY FORM Patient Name: Birth date:_ Date: Referring Physician: Pharmacy Name: Address: 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 drugs, prescriptions etc.) MEDICATION DOSAGE MEDICATION DOSAGE Do you take any blood thinning products such as Vitamin E, Plavix, Coumadin, or Aspirin? NO DYES YES Do you have any food, environmental, or drug allergies? | NO ALLERGY TYPE (Please explain below) REACTION Do you smoke? NO and Never have YES (Please explain below) TYPE OF SMOKING (cigarette, pipe marijuana, chew, etc.) HOW MUCH HOW LONG Do you drink alcohol? NO and Never have socially Only Daily Beer/ Wine Hard Liquor Occupation: Hand Dominance: RIGHT LEFT UNKNOWN ILLNESSES/ REASON FOR DEATH Please describe any family health issue below: FAMILY HISTORY GOOD/ NONE MOTHER FATHER SIBLING(S) OTHER HEREDITARY ILLNESS Patient Signature: Date: JJ- Date Reviewed: Physician Signature: HEALTH HISTORY FORM 2 Do you have or have you ever had any of the following: Symptoms/ illness NO YES, Explain Constitutional NO YES, Explain Symptoms/Illness Skin Fever or Chills Breast Abnormalities Weight Loss Hematologic Nipple Discharge Last Mammogram Date: Changes in Moles Hepatitis HIV/ Other Blood Diseases Lesions Bleeding Disorders Rashes Endocrine Thyroid Problems Diabetes History of Keloids Neurological Neurological Problems Musculoskeletal Headaches Arthritis GENITOURINARY Mobility/Joint Problems Genital or Oral Herpes S.T.D.'s GASTROINTESTINAL Constipation Blood in Urine Diarrhea Urinary Tract Infection Problems Urinating Blood in Stool Nausea/ Vomiting Liver Problems Prostate Problems Kidney Problems CARDIOVASCULAR Eyes Heart Problems Vision Problems Deep Vein Thrombosis/DVT ENT Blood Clots in Lungs/Legs Hearing Problems Sinus Problems High Blood Pressure RESPIRATORY PSYCHIATRIC Asthma Mood Swings Anxiety/ Depression Sleep Apnea Please list any other conditions/ illnesses not indicated above: To the best of my know this information is complete and correct. I understand that it is my responsibility to inform my doctor if there are any changes to my health Patient Signature: Date: Physician Signature: Date Reviewed: Patient Name First Outpatient Lab Order Form Last MI Mercy Lab Fax Number 641-428-7886 Birth Date Sex Collection Date Time Collected By Ordering Provider Consult Dr. Patient SSN When Tests are Complete: Send Patient Home Patient Location Patient Phone Number Send Patient to Office Page Provider on Pager# Narrative Signs/Symptoms/Diagnosis Call Results to 1. 2. Fax Results to 3. 4. Courtesy Copy to Notification to Providers and other persons legally authorized to order tests for which Medicare reimbursement will be sought Medicare will pay only for tests that meet the Medicare coverage criteria and are reasonable and necessary to treat or diagnose an individual patient. Medicare does not pay for tests for which documentation, including the patient record, does not support that the tests were reasonable and necessary. Medicare generally does not cover routine screening tests even if the physician or other authorized test arderer considers the tests appropriate for the patient. **Tests with an * behind them means that reflex testing may be performed - Please refer to the Mercy Lab Test Index Order x Tests Order x Tests Order x Tests ABSN Antibody Screen HCTX Hematocrit NA Sodium ALT ALTISGPT HPSSAB Hepatitis Anti-HBS SYPHL Syphylis IgG Antibody AST AST/SGOT HBSA Hepatitis B Surface Antigen T3F T3 Free ANASCN ANA" HPCHRN Hepatitis Chronic Profile T4F T4 Free BUN BUN HIV HIV Antibody TSTE Testosterone, Total and Free CRP C Reactive Protein HIVM HIV Medicare Screen TRIG Triglyceride HSCRP CRP Sensitive (Cardiac) IBC Iron and Iron Binding TRPI Troponin CA125 CA 125 RA Latex RA TSH TSH CBCAD CBC with Auto Difr LIPS Lipase URIC Uric Acid CBC CBC with No Diff LH LH UA Urinalysis Routine CBCD CBC with Manual Diff MG Magnesium B12F Vitamin B12 / Folate CEA CEA UMAL Microalbumin Urine B12 Vitamin B12 Cell Morphology PTHINT Parathyroid Hormone Intact VD25H Vit D 25-Hydroxy CHOL Cholesterol K Potassium Chemistry Panels HDL HDL Cholesterol PNP Prenatal Profile wHIV METB Basic Metabolic Panel DLDL LDL Direct Cholesterol PNPO Prenatal Profile no HIV ATPN Dermatology Panel CK CK PTR Protime INR HFPL Hepatic Function Panel CREAT Creatinine PSA Prostate Specific Antigen THPO Hypothyroid Panel (TSH,T4F) VCCL Creatinine Clearance 24 hr Urine PSAS PSA, Medicare Screen LIPD Lipid Panel" CRTMM Urine Total Volume needed PEL Protein Electrophoresis CMPL Comprehensive Metabolic Panel DRUG Drug Screen Random Urine Protein 24 hr Urine LYTE Electrolyte VPRT GLUC Glucose Total Volume RNPL Renal Panel GLUG Glucose 1 hr Gestational QUADM Quad Maternal Screen GHP General Health Panel (CBCAD, TSH, CMPL) GLYCO Glycohemoglobin (A1C) RETIC Reticulocyte Count Drug Levels HCGQ HCG Quant Serum ESR Sedimentation Rate HGBX Hemoglobin SMAFP Single Marker AFP Last Dose Date and Time - 1. 2. CM 3. 4. Mayo# Sianature of Orderina Provider Date Physician Order Form - Imaging Services Date: OREGON HEALTH Diagnostic Imaging Services & SCIENCE 3181 S.W. Sam Jackson Park Road, Portland OR 97239 UNIVERSITY Phone: 503-418-0990 Fax: 503-494-4621 PATIENT INFORMATION Patient will call to schedule Patient Name: Date of Birth: Patient Phone: Please call Patient ICD 9 Code Authorization #: Reason for Exam: REQUESTING PHYSICIAN INFORMATION Referring Physician: Phone: Referring Physician Signature: Results (check all that apply): E-mail report: (e-mail) CD with Images Fax report: (fax #) Special Request Phone Report: (phone #). EXAM FOCUS Brain MRI Brain MRA Neck MRI Neck MRA OMRI Cervical Spine Thoracic Lumbar w contrast Extremity (specify wol contrast Other (specify): Ow/wo contrast Vagal Nerve Stimulator. Program both generator output current and magnet output current to OMA prior to the MRI procedure. After MRI is completed, re-program device to original settings Brain Sinus Chest Abdomen Pelvis Owl contrast Cervical Spine Thoracic Lumbar wol contrast Extremity (specify w/wo contrast Other (specify) Diagnostic Screening Others (specify): Mammogram Abdomen Pelvis O OB/GYN Ultrasound Other (specify: Nuclear Medicine Bone SPECT Thyroid Liver-Spleen Head/Neck Lung Breast Lymphoma Other (specify: Barium Enema (please select): O With air contrasto Without air contrast IV. Pyelogram Upper G.I. (please select): O With small bowel series o Without small bowel series General Radiology Volding Cystourethrogram X-ray (specify): Fluoro Other (specify): Vascular Lab Peripheral Arial Exam Venous Chronic Venous Exam OPPG's Upper Extremity Transcranial Doppler Carotid Temporal Artery ABI's with waveform Lower Extremity Nielsen Cold Challenge Graft Flow Arterial Duplex Dialysis Graft Eval. Abdomen (please select) Finger Toe(s) O Renal O Mesenteric O Portal Hepatic O AAA O Renal Transplant Other (specify) Right Left Other Specify Rev OVOS Scan to PO-7070