Transcript Final Project

Final Project
N510 (Web-Database Concepts)
Purpose
Design and implement web based small clinic search system
Project sections
1. Design and creation of a relational database with MySQL
2. Design and creation of several web based forms with HTML/PHP
technology to query the available database and show the end results
3. A report (Word document) about phase 1 and 2 which includes the schema
of the MySQL database and the HTML/PHP code
Deliverables
MySQL 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.
Grade
The total value of the final project is 35 points
Further Information
Please refer to INFO510.com for further information such as deadline
Requirements
This 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
Database
You 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 Pages
Create 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.
Grading
The following table shows the grades associated with each component of the project:
Category
Item
Point
Database
Entity Relationship Diagram
1
Database
Database Normalization
1
Database
Creating Tables in MySQL
Database
Entering Fake Data
1
Database
Correct SQL Commands in PHP
5
Web Page
Entry Page (Splash Page)
1
Web Page
Proper Use of CSS
3
Web Page
HTML Forms (Patient, Doctor, Visit and Lab)
4
Web Page
HTML Templates and PHP Server Side Includes
2
Web Page
PHP Pages/Results (Patient, Doctor, Visit and Lab)
12
Web Page
Cross Linking PHP Pages
2
Bonus
Generating XML as Results
2
Bonus
Login Process and Authentication (requires database addition)
5
Bonus
Creating Entry Forms + PHP Pages (Pat, Doc, Vis and Lab)
8
Report
Formatted Final Report
(proper variable type)
Bonus
2
3
Sample Clinic
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
Copyright @ Sample Clinic 2010
form_patient.php
Sample Clinic
Patient Search - Form
Please complete the following search form:
ID (P_id)
First Name
Joh
Last Name
Date of Birth
Tel (Home)
317
Tel (Cell)
Break down this
field into multiple
fields as necessary
(e.g. Apt#, …)
Tel (Work)
Address
City
Indianapolis
This field is
generated
automatically
from the database
Zip Code
Clear
Search
Copyright @ Sample Clinic 2010
result_patient.php
Sample Clinic
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
This page only shows one patient – your results may include
more than one patient! Make sure you show all of them…
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 / …. Please complete this list… (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)
Copyright @ Sample Clinic 2010
form_doctor.php
Sample Clinic
Doctor Search - Form
Please complete the following search form:
ID (D_id)
First Name
Please add any other field that is necessary such as office #
Joh
Last Name
Tel (Home)
Tel (Cell)
This field is
generated
automatically
from the database
317
Tel (Work)
Specialty
Work Days
Dermatology
 Monday
This field does NOT
need to be
generated from
the database
Tuesday
Wednesday
Thursday
 Friday
Saturday
Sunday
Clear
This field is made
of checkboxes as
doctors can work
on multiple days
Search
Copyright @ Sample Clinic 2010
result_doctor.php
Sample Clinic
Doctor Search - Results
The system found the following doctor(s):
This page only shows one doctor– your results may include
more than one doctor! Make sure you show all of them…
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…)
Pay attention that this doctor’s working days include Monday
and Friday but it is not limited to this list
You can link the name of the patient to the detailed information
about them on the result_patient.php page (bonus point)
Copyright @ Sample Clinic 2010
form_visit.php
Sample Clinic
Visit Search - Form
Please complete the following search form:
ID (V_id)
Patient First Name
Please add any other field that is necessary such as office #
Joh
Patient Last Name
Doctor First Name
These fields are generated
automatically from the
database
Doctor Last Name
Sign or Symptom
Chill
Prescription
Paxil
No need to create checkboxes
for these items
Diagnosis
Clear
Search
If you make a multiple
selection dropdown box you
will receive bonus points
Copyright @ Sample Clinic 2010
result_visit.php
Sample Clinic
Visit Search - Results
The system found the following visits(s):
This page only shows one visit– your results may include more
than one visit! Make sure you show all of them…
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 / ….
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)
Pay attention that this list includes Chill along with other signs
and symptoms. This inclusions may also happen in diagnoses and
prescriptions…
Copyright @ Sample Clinic 2010
(more…)
form_lab.php
Sample Clinic
Lab Search - Form
Please complete the following search form:
ID (L_id)
Please add any other field that is necessary such as office #
Patient First Name
Patient Last Name
Doctor First Name
Doctor Last Name
RBC
>=
<=
5
WBC
>=
<=
15
PLT
>=
<=
Hgb
>=
HCT
>=
<=
MCV
>=
<=
12
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.
<=
Please complete this list
Clear
Search
Copyright @ Sample Clinic 2010
result_lab.php
Sample Clinic
Lab Search - Results
This page only shows one lab result– your results may include
more than one lab test! Make sure you show all of them…
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
Please complete this list
Of course there might be more results…
Copyright @ Sample Clinic 2010