final project
Post on 15-Mar-2016
28 Views
Preview:
DESCRIPTION
TRANSCRIPT
Final ProjectN510 (Web-Database Concepts)
PurposeDesign and implement web based small clinic search system
Project sections1.Design and creation of a relational database with MySQL2.Design and creation of several web based forms with HTML/PHP technology to query the available database and show the end results3.A report (Word document) about phase 1 and 2 which includes the schema of the MySQL database and the HTML/PHP code
DeliverablesMySQL dump + HTML/PHP code + Report zip it all into one file and submit it on OnCourse. The final project should go live and be operational on your website before the deadline. Please provide the link in your report.
GradeThe total value of the final project is 35 points
Further InformationPlease refer to INFO510.com for further information such as deadline
RequirementsThis clinic involves patients and doctors. Doctors visit patients and prescribe medications or order lab tests. Doctors work on certain days during the week and have only one specialty. Of course, doctors can visit different patients and a patient can be assigned to multiple doctors. In each visit, a doctor may prescribe multiple medications, identify multiple signs or symptoms, and assert multiple diagnoses. Each lab test is ordered only by one doctor for one patient during a visit. The clinic wants to record the following information:
Patient Information: PID#, SSN#, First Name, Last Name, DOB, Home Tel#, Office Tel#, Cell Tel#, App#, Street#, Street Name, City Name (limited list), ZipCode
Doctor Information: DID#, First Name, Last Name, Office Tel#, Cell Tel#, Office#, Specialty (limited list), Workdays (limited list)
Visit Information: VID#, Date and Time, Patient, Doctor, Prescriptions (limited list), Signs or Symptoms (limited list), Diagnoses (limited list), Lab Test (complete blood count RBC, …)
Lab Information: LID#, Date, RBC, WBC, PLT, Hgb, HCT, MCV, MCH, RDW
Cities are limited to: Indianapolis, Carmel, Zionsville, Geist, Lawrence and Noblesville
Specialties are limited to: Dermatology, Ophthalmology, Orthopedics, Pediatrics, Geriatrics, ENT, Cardiology, Internist, Psychiatry
Workdays are limited to: Monday, Tuesday, Wednesday, Thursday, Friday
Prescriptions are limited to: Paxil, Lexapro, Hydrocodone, Xanax, Tramadol, Vicodin, Lyrica, Oxycodone, Lisinopril, Cymbalta, Lipitor, Percocet, Zoloft
Signs and Symptoms are limited to: Cachexia, Weight gain, Dry mouth, Fatigue, Muscle weakness, Pyrexia, Jaundice, Pain, Bruising, Epistaxis, Tremor, Convulsions, Muscle cramps, Tinnitus, Dizziness/Vertigo, Syncope, Hypothermia, Hyperthermia, Discharge, Bleeding, Swelling, Deformity, Sweats, Chills
Diagnoses are limited to: Acne, ADHD, Alcohol Abuse, Allergies, Alzheimer, Anxiety, Arthritis, Asthma, Back Pain, Bipolar Disorder, Breast Cancer, Cervical Cancer, Cholesterol, Chronic Fatigue Syndrome, Cold, Flu, Colon Cancer, COPD, Dental Care, Depression, Diabetes (Type 1), Diabetes (Type 2), Diarrhea, Epilepsy, Erectile Dysfunction, Fertility, Fibromyalgia, Genital Herpes, GERD, Headache, Heart Disease, Heartburn, High Blood Pressure, Insomnia, Irritable Bowel Syndrome, Incontinence, Knee Pain, Menopause, Migraine, Multiple Sclerosis, Obesity, Osteoarthritis, Osteoporosis, Parkinson, Postpartum Depression, Pregnancy and Childbirth, Prostate Cancer, Psoriasis, Restless Legs Syndrome, Rheumatoid Arthritis, Sexual Health, Skin Cancer, Sleep Apnea, Sleep Disorders, Stroke, Ulcer, Yeast Infection
DatabaseYou should decide how many tables are necessary for the project in order to follow the Normalization (1st, 2nd and 3rd) rules.
Number of columns in each table depends on the normalization process – sometimes you may increase the number of columns and sometimes you reduce them based on your needs. Do NOT limit the columns to the variables on the ‘Requirements’ slide; however, you can use them as a starting point.
You need to fill the tables with fake data. Please create at least 50 patients, 5 doctors, 100 lab tests and 200 visits. This may take a while. You should NOT share your fake data with others as it will share the database structure as well!
Show the Entity Relationship Diagram (ERD) and normalization process in your report. Create the tables in your MySQL database on the server. When you are finished with entering the data in it and testing it, dump your database in a .sql file and include it in your final zip file submission.
Creating proper SQL commands to link your PHP files with MySQL and returning proper results is also part of your database grading.
Web PagesCreate a splash page which will link you to four main pages: Patient Query, Doctor Query, Visit Query and Lab Query. As bonus points, you can also create more pages that will let you enter and edit Patient, Doctor, Visit and Lab results.
These links will open separate pages (forms) where you can query each of the given categories (Patient, Doctor, Visit and Lab) based on detailed information relevant to them.
Each of these forms will open another webpage (PHP) that will show the results to the user. Results are generated dynamically based on the user input. You need to create a connection to the database to retrieve the information.
As bonus points, you can generate an XML containing the query results in addition to showing the results in plain HTML on the screen. You can also make the entire interaction in a secure connection protected by a user/pass assigned to each user. Of course, patients will be only capable of browsing their own information including their doctors; and doctors will be able to see all of their patients.
Finally, the structure of all of the web pages should be based on Server Side Includes using templates. This means, that you should include the header and footer of your webpage from a consistent HTML or PHP file to have the same look and feel propagated throughout your website.
GradingThe following table shows the grades associated with each component of the project:
Category Item Point BonusDatabase Entity Relationship Diagram 1Database Database Normalization 1Database Creating Tables in MySQL (proper variable type) 2Database Entering Fake Data 1Database Correct SQL Commands in PHP 5Web Page Entry Page (Splash Page) 1Web Page Proper Use of CSS 3Web Page HTML Forms (Patient, Doctor, Visit and Lab) 4Web Page HTML Templates and PHP Server Side Includes 2Web Page PHP Pages/Results (Patient, Doctor, Visit and Lab) 12Web Page Cross Linking PHP Pages 2Bonus Generating XML as Results 2Bonus Login Process and Authentication (requires database
addition)5
Bonus Creating Entry Forms + PHP Pages (Pat, Doc, Vis and Lab)
8
Report Formatted Final Report 3
Sample Clinic
Copyright @ Sample Clinic 2010
Homepage
Welcome to Sample Clinic
Please choose from the following list of actions:
Search a: Patient Doctor Lab Visit
Enter a new: Patient Doctor Lab Visit
Name the forms: form_patient.php, form_doctor.php, form_lab.php and form_visit.php and the result pages :result_patient.php, result_doctor.php, resutl_lap.php and result_visit.php
Sample Clinic
Copyright @ Sample Clinic 2010
Patient Search - Form
Please complete the following search form:
ID (P_id)
First Name
Last Name
Date of Birth
Tel (Home)
Tel (Cell)
Tel (Work)
Address
City
Zip Code
Joh
317
Indianapolis
SearchClear
This field is generated automatically from the database
Break down this field into multiple fields as necessary (e.g. Apt#, …)
form_patient.php
Sample Clinic
Copyright @ Sample Clinic 2010
Patient Search - Results
The system found the following patient(s):
ID (P_id) 36
First Name John
Last Name Simpson
Date of Birth
1987/09/18
Tel (Home) 317-789-0099
Tel (Cell)
Tel (Work)
Address Apt 311, 5678 Spiderbay Ct
City Indianapolis
Zip Code 46204
Doctor #1 Andy Robertson (more…)
Doctor #2 Peter Green (more…)
Visits #1 Date: 2009-10-09 / Doctor: Andy Robertson / Diagnoses: Asthma, Knee Pain / Sign and Symptom: Pain, Bruising / (more…)
Lab Test #1 Date: 2009-11-01 / RBC: 8.9 / WBC: 11.9 / PLT: 317,890 / Hgb: 12.9 / …. (more…)
You can link the name of the doctors to the detailed information about them on the result_doctor.php page (bonus point)
You can link the visit to the detailed information about it on the result_visit.php page (bonus point)
Please complete this list…
result_patient.php
This page only shows one patient – your results may include more than one patient! Make sure you show all of them…
Sample Clinic
Copyright @ Sample Clinic 2010
Doctor Search - Form
Please complete the following search form:
ID (D_id)
First Name
Last Name
Tel (Home)
Tel (Cell)
Tel (Work)
Specialty
Work Days Monday Tuesday Wednesday Thursday Friday Saturday Sunday
Joh
317
Dermatology
SearchClear
This field does NOT need to be generated from the database
This field is generated automatically from the database
form_doctor.php
This field is made of checkboxes as doctors can work on multiple days
Please add any other field that is necessary such as office #
Sample Clinic
Copyright @ Sample Clinic 2010
Doctor Search - Results
The system found the following doctor(s):
ID (D_id) 4
First Name Peter
Last Name Green
Tel (Home) 317-999-4656
Tel (Cell) 317-789-0009
Tel (Work) 317-911-9111
Specialty Dermatology
Office # 4ER
Workdays Monday, Tuesday, Friday, Sunday
Patient #1 John Simpson (more…)
Patient #2 David Black (more…)
Visits #1 Date: 2009-10-09 / Patient: David Black (more…)
Visits #2 Date: 2009-10-10 / Patient: Jack Wilson (more…)
You can link the name of the patient to the detailed information about them on the result_patient.php page (bonus point)
result_doctor.php
This page only shows one doctor– your results may include more than one doctor! Make sure you show all of them…
Pay attention that this doctor’s working days include Monday and Friday but it is not limited to this list
Sample Clinic
Copyright @ Sample Clinic 2010
Visit Search - Form
Please complete the following search form:
ID (V_id)
Patient First Name
Patient Last Name
Doctor First Name
Doctor Last Name
Sign or Symptom
Prescription
Diagnosis
Joh
Paxil
SearchClear
These fields are generated automatically from the database
No need to create checkboxes for these items
If you make a multiple selection dropdown box you will receive bonus points
form_visit.php
Please add any other field that is necessary such as office #
Chill
Sample Clinic
Copyright @ Sample Clinic 2010
Visit Search - Results
The system found the following visits(s):
ID (V_id) 84
Date 2009-10-09
Patient John Simpson (more…)
Doctor Peter Green (more…)
Diagnoses Asthma, Acne, Heart Failure
Sign & Symp.
Chill, Hypothermia, Pain, Bruising
Prescriptions
Paxil
Lab Test Date: 2009-11-01 / RBC: 9.9 / WBC: 12.9 / PLT: 65000 / Hgb: 9.2 / …. (more…)
You can link the name of the patient or doctor to the detailed information about them on the result_patient.php or result_doctor.php page (bonus point)
result_visit.php
This page only shows one visit– your results may include more than one visit! Make sure you show all of them…
Pay attention that this list includes Chill along with other signs and symptoms. This inclusions may also happen in diagnoses and prescriptions…
Sample Clinic
Copyright @ Sample Clinic 2010
Lab Search - Form
Please complete the following search form:
ID (L_id)
Patient First Name
Patient Last Name
Doctor First Name
Doctor Last Name
RBC >= <=
WBC >= <=
PLT >= <=
Hgb >= <=
HCT >= <=
MCV >= <=SearchClear
This way the user can search for values that are less or more than a certain value.
In your SQL command you can simply check for values that are between two values.
form_lab.php
Please add any other field that is necessary such as office #
515
12
Please complete this list
Sample Clinic
Copyright @ Sample Clinic 2010
Lab Search - Results
The system found the following lab result(s):
ID (L_id) 153
Date 2009-10-11
Patient John Simpson (more…)
Doctor Peter Green (more…)
RBC 4.3
WBC 11.3
PLT 145,899
Hgb 14.3
HCT …
MCV …
You can link the name of the patient or doctor to the detailed information about them on the result_patient.php or result_doctor.php page (bonus point)
You can even add a link to the actual visit that this lab test was ordered
result_lab.php
This page only shows one lab result– your results may include more than one lab test! Make sure you show all of them…
Please complete this list
Of course there might be more results…
top related