Unit 17: Data Base Design concepts

Unit 17: Data Base Design concepts


Case Study Background
New Hope General Hospital is a new established hospital located at Fairview, Quezon City, Philippines. Its full operation started January 30, 2013.The hospital was first conceived on the idea of young doctors from UST, Philippine General Hospital, FYM General Hospital, Philippine Heart Center, and St. Luke Medical Center led by Dr. Lucas Pag-asa Celestial. The location of the hospital is situated near Manggahan, Quezon City where it wishes to serve the poorest community within the area. It envisions the following:

* Provide low-cost health care with high quality of service; * Promote prevention program rather than cure;
* Sustain continuous health outreach program within the community; * Adopt alternative medicine to give wider healing options for patients.

The management of the hospital decided to maintain its transaction on a database to monitor the daily operation of the institution. Patient may request a billing statement for the cost of the hospitalization or consultation. The generated Statement of Account will serve as a basis of how much to pay with regards to stay in the hospital.

The hospital accommodates not just IN PATIENT but OUT PATIENT as well. Patient may visit doctors’ office for continuous consultation.

Building up a database system would give the hospital better idea on how to monitor patient consultation history that will serve as a synopsis on how treatment was done for every encounter with a physician.

The institution wishes to monitor also the payment made by each patient. This will serve as a reminder to patients who must be aware of the ongoing cost of hospitalization.The hospital has a policy that patient has to pay down payment of 5,000 if its bill reaches already 10,000 or more to continue with the treatment. Payment history will list payment against current billing statement cost. The same report may generate list of patients as a proof for the helping foundations (such as PAGCOR, Belmonte Memorial Foundation, Caritas Manila, FYM Foundation, etc.) of whom it may help through financial support.

The database system will serve its usual reports generation that may help the hospital in tracking patient and doctors data. Data analysis in the future would be also possible as historical data build up that may help management in giving sound decision with regards on the operation of the hospital and providing better health care to community it wishes to serve.

General Guidelines in Designing New Hope Hospital Database
1. Normalize Sample Report 1, 2, 3, and 4.

2. Run date or date of report generation (e.g. as of 2013-02-05) is system date (the date it is run) and not a date that should be included in normalization or data to be maintained in the database.

3. Patient name in Sample Report 1 is a result of concatenation of last name, first name, and the first letter of the middle name.

4. Have your normalized tables and ERD checked before you proceed on the actual implementation of the database by your professor.

5. Designate appropriate primary keys as stated in your ERD. Use the same entity/attribute name in the ERD as you name it on your table/column. These names must be aligned with each other.

6. No other tables should exist other than what is listed in the ERD. You can have unlimited number of VIEWS to establish relationships connections that you may need in report generation.

7. Validate fields during entries. On GENDER, M and F are only accepted; Payment Mode should have only 4 possible values: CASH, CHECK, Medicard, PhilHealth.

8. Utilize DELETE RESTRICT from parent table to related tables. This means that you cannot delete rows on base/parent table that has info existing on other tables.

9. Sample Reports must be generated with the same set of output (not more or less) with the use of iReport open-source third party report writer.

10. Use VIEWS to simplify exportation of data from database system to report writer.

11. It is suggested that there would be a form (java application) where the user of the system will click a certain button to drive iReport and display the generated pdf of the sample report.

12. Sample report 5 and 6 can be easily generated as a result of normalized tables from Sample Report 1, 2, 3, and 4. Encode other data on designed base tables that are NOT shown on the sample report 1,2, 3, 4 but visible on 5. STATEMENT OF ACCOUNT

SAMPLE REPORT ONE

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

Statement of Account
Patient Number: 1234Bill ACN: IN098
Patient Name: ROBLES, Carlos L.Billing Date: 2013-01-12

ServiceCode| Service Name| Unit| Unit Cost| Quantity| Total| M010| Lorazepam| ea| 100.00 | 7| 700.00 | S004| Radiology| svc| 3,000.00 | 1| 3,000.00 | S007| Neoro surgery| svc| 25,000.00 | 1| 25,000.00 | | | | | | |

| | | Total| | 28,700.00 |

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

Statement of Account
Patient Number: 1235Bill ACN: OUT045
Patient Name: GARCIA, Aubrey L.Billing Date: 2013-01-22

ServiceCode| Service Name| Unit| Unit Cost| Quantity| Total| C001| Consultation - General Medicine| Svc| 500.00 | 1| 500.00 | M001| Doxepin| Ea| 150.00 | 4| 600.00 | | | | | | |

| | | Total| | 1,100.00 |

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

Statement of Account
Patient Number: 7431Bill ACN: IN201
Patient Name: MONTEMAYOR, Tina W.Billing Date: 2013-02-19

Service Code| Service Name| Unit| Unit Cost| Quantity| Total| C001| Consultation - General Medicine| svc| 500.00 | 1| 500.00 | M003| Xanax| pc| 2,500.00 | 4| 10,000.00 |

M010| Lorazepam| ea| 100.00 | 3| 300.00 | S004| Radiology| svc| 3,000.00 | 1| 3,000.00 | | | | | | |
| | | Total| | 13,800.00 |

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

Statement of Account
Patient Number: 1234Bill ACN: OUT155
Patient Name: ROBLES, Carlos L.Billing Date: 2013-02-05

Service Code| Service Name| Unit| Unit Cost| Quantity| Total| C002| Consultation – Psychiatry| Svc| 1,000.00 | 1| 1,000.00 | M0010| Zolops| Pc| 56.00 | 100| 5,600.00 | | | | | | |

| | | Total| | 6,600.00 |

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

Statement of Account
Patient Number: 9183Bill ACN: IN298
Patient Name: CASTRODES, Richard A.Billing Date: 2013-02-22

ServiceCode| Service Name| Unit| Unit Cost| Quantity| Total| M001| Doxepin| ea| 150.00 | 4| 600.00 | M010| Lorazepam| ea| 100.00 | 4| 400.00 |
S007| Neoro surgery| svc| 25,000.00 | 1| 25,000.00 | | | | | | |
| | | Total| | 26,000.00 |

CONSULTATION HISTORY
SAMPLE REPORT TWO

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

Consultation History
Patient Number: 1234RunDate: 2013-02-20
Patient Name: ROBLES, Carlos L.

Physician Code| Physican Name| Consultation Date| Symptoms| Diagnosis| D100| Edgardo Abejar| 1/12/2013| Nausea, Sleeplessness| Migraine| D200| LemuelManalo| 2/5/2013| Hand Tremor| Nervous Breakdown|

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

Consultation History
Patient Number: 1235RunDate: 2013-02-21
Patient Name: GARCIA, Aubrey L.

Physician Code| Physican Name| Consultation Date| Symptoms| Diagnosis| D009| Shaula Prado| 1/15/2013| Vomitting| Pregnant|
D200| LemuelManalo| 2/5/2013| Flu, nausea| UTI|

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

Consultation History
Patient Number: 8001RunDate: 2013-02-21
Patient Name: QUIOGUE, Baltazar C.

Physician Code| Physican Name| Consultation Date| Symptoms| Diagnosis| D001| Mark Martinez| 12/19/2012| Blood on urine| UTI|
D009| Shaula Prado| 1/10/2013| Sleepless ness| Stress| D200| LemuelManalo| 1/17/2013| Loss of Apetite| Prostate Cancer| D300| Rowena Garcia| 2/2/2013| Loss of Weight| Prostate Cancer|

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

Consultation History
Patient Number: 9564RunDate: 2013-01-23
Patient Name: SALCEDO, Oliver V.

Physician Code| Physican Name| Consultation Date| Symptoms| Diagnosis| D101| NeloTumbokon| 2/1/2013| Loose Bowel| Diarrhea|
D200| LemuelManalo| 2/17/2013| Itchy Rashes| Chicken Fox| D101| NeloTumbokon| 2/20/2013| Swelling of neck| Goiter|

PHYSICIAN LIST BY DEPARTMENT
SAMPLE REPORT THREE

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

PHYSICIAN LIST BY DEPARTMENT
As of 2013-03-15

Department Code: GENDepartment: General Medicine

Physician Code| Physician Name| Specialization| |
D001| Mark Martinez| General Medicine| |
D020| Karen Chua| General Medicine| |
D030| Albert Leroy| General Medicine| |
D060| Anne Lacuesta| General Medicine| |
D401| Sheila Fuentes| General Medicine| |
Total Doctors : 5| | | |
Department Code: INTDepartment: Internal Medicine

Physician Code| Physician Name| Specialization| |
D009| Shaula Prado| Obe gynecology| |
D300| Rowena Garcia| Oncology| |
Total Doctors : 2| | | |

Department Code: PEDDepartment: Pediatrics

Physician Code| Physician Name| Specialization| |
D101| NeloTumbokon| Genetics| |
Total Doctors: 1| | | |

Department Code: PSYDepartment: Psychiatry

Physician Code| Physician Name| Specialization| |
D100| Edgardo Abejar| Geriatric Psychiatry| |
D200| LemuelManalo| Child Psychiatry| |
Total Doctors: 2| | | |

PATIENT PAYMENT HISTORY
SAMPLE REPORT FOUR

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

PATIENT PAYMENT HISTORY
As of 2013-03-15

| | | | | |
Bill A.C.N.| IN098| | | | |
Patient Num| 1234| | | | |
Patient Name| | | | | |
Last| ROBLES| First| CARLOS| Middle| LEGARDA|
| | | | | |
Payment Date| Amount| Payment Mode| Cheque Number| | | 1/10/2013| 5,000.00 | CASH| | | |
1/13/2013| 23,700.00 | CASH| | | |
| | | | | |
Total| 28,700.00 | | | | |
Current Bill| 28,700.00 | | | | |
Balance| 0.00 | | | | |
*************************************************************************| | | | | | |
| | | | | |
Bill A.C.N.| OUT045| | | | |
Patient Num| 1235| | | | |
Patient Name| | | | | |
Last| GARCIA| First| AUBREY| Middle| LOPEZ|
| | | | | |
Payment Date| Amount| Payment Mode| Cheque Number| | | 1/22/2013| 500.00 | CASH| | | |
| | | | | |
Total| 500.00 | | | | |
Current Bill| 1,100.00 | | | | |
Balance| 600.00 | | | | |
*************************************************************************| | | | | | |
| | | | | |
Bill A.C.N.| IN201| | | | |
Patient Num| 7431| | | | |
Patient Name| | | | | |
Last| MONTEMAYOR| First| TINA| Middle| WALTER|
| | | | | |
Payment Date| Amount| Payment Mode| Cheque Number| | | 2/18/2013| 5,000.00 | CASH| | | |
2/19/2013| 2,000.00 | CHECK| BPI 20478910| | | | | | | | |
Total| 7,000.00 | | | | |
Current Bill| 13,800.00 | | | | |
Balance| 6,800.00 | | | | |
*************************************************************************| | | | | | |
| | | | | |
| | | | | |
Bill A.C.N.| OUT155| | | | |
Patient Num| 1234| | | | |
Patient Name| | | | | |
Last| ROBLES| First| CARLOS| Middle| LEGARDA|
| | | | | |
Payment Date| Amount| Payment Mode| Cheque Number| | | 2/4/2013| 5,000.00 | PHILHEALTH| PHL 345901A| | | | | | | | |
Total| 5,000.00 | | | | |
Current Bill| 6,600.00 | | | | |
Balance| 1,600.00 | | | | |
*************************************************************************|

| | | | | |
| | | | | |
| | | | | |
Bill A.C.N.| IN298| | | | |
Patient Num| 9183| | | | |
Patient Name| | | | | |
Last| CASTRODES| First| RICHARD| Middle| AMOYO|
| | | | | |
Payment Date| Amount| Payment Mode| Cheque Number| | | 2/21/2013| 5,000.00 | CASH| | | |
| | | | | |
Total| 5,000.00 | | | | |
Current Bill| 50,200.00 | | | | |
Balance| 45,200.00 | | | | |

PATIENT LIST
SAMPLE REPORT FIVE

New Hope General Hospital
7 Commonwealth Avenue, Quezon City

PATIENT LIST
As of 2013-03-15

Patient No.| Last Name| First Name| Middle Name| Birthdate| Gender| Adress| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| 8001| QUIOGUE| BALTAZAR| CRUZ| 10/24/1997| M| Cainta, Rizal| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| 9564| SALCEDO| OLIVER| VALDERAMA| 5/24/2007| M| TandangSora, QC|

STATEMENT OF ACCOUNT
SAMPLE REPORT ONE NORMALIZATION

SAMPLE REPORT 1: STATEMENT OF ACCOUNT
DATA
Patient_no| Lastname| Firstname| Middlename| Birthdate| Gender| Address| BillACN| Billdate| Servcode| Servname| Unit| Unitcost| Quantity| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| IN098| 1/12/2013| M010| Lorazepam| ea| 100| 7| | | | | | | | | | S004| Radiology| svc| 3,000.00| 1| | | | | | | | | | S007| Neoro surgery| svc| 25,000.00| 1| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| OUT045| 1/22/2013| C001| Consultation - General Medicine| Svc| 500| 1| | | | | | | | | | M001| Doxepin| Ea| 150| 4|

7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| IN201| 2/19/2013| C001| Consultation - General Medicine| svc| 500| 1| | | | | | | | | | M003| Xanax| pc| 2,500.00| 4| | | | | | | | | | M010| Lorazepam| ea| 100| 3| | | | | | | | | | S004| Radiology| svc| 3,000.00| 1| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| OUT155| 2/5/2013| C002| Consultation – Psychiatry| Svc| 1,000.00| 1| | | | | | | | | | M0010| Zolops| Pc| 56| 100| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan| IN298| 2/22/2012| M001| Doxepin| ea| 150| 4| | | | | | | | | | M010| Lorazepam| ea| 100| 4| | | | | | | | | | S007| Neoro surgery| svc| 25,000.00| 1|

REMOVE REPEATING GROUPS
Patient_no| Lastname| Firstname| Middlename| Birthdate| Gender| Address| BillACN| Billdate| Servcode| Servname| Unit| Unitcost| Quantity| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| IN098| 1/12/2013| M010| Lorazepam| ea| 100| 7| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| IN098| 1/12/2013| S004| Radiology| svc| 3,000.00| 1| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| IN098| 1/12/2013| S007| Neoro surgery| svc| 25,000.00| 1| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| OUT045| 1/22/2013| C001| Consultation - General Medicine| Svc| 500| 1| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| OUT045| 1/22/2013| M001| Doxepin| Ea| 150| 4| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| IN201| 2/19/2013| C001| Consultation - General Medicine| svc| 500| 1| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| IN201| 2/19/2013| M003| Xanax| pc| 2,500.00| 4| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| IN201| 2/19/2013| M010| Lorazepam| ea| 100| 3| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| IN201| 2/19/2013| S004| Radiology| svc| 3,000.00| 1| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| OUT155| 2/5/2013| C002| Consultation – Psychiatry| Svc| 1,000.00| 1| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| OUT155| 2/5/2013| M0010| Zolops| Pc| 56| 100| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan| IN298| 2/22/2012| M001| Doxepin| ea| 150| 4| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan| IN298| 2/22/2012| M010| Lorazepam| ea| 100| 4| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan| IN298| 2/22/2012| S007| Neoro surgery| svc| 25,000.00| 1| Define Functional Dependency|

| |
| Patientno-->Lastname, Firstname, Middlename, Birthdate, Gender, Address| | |
| BillACN--> Billdate, Patientno|
| Servcode--> Servname|
| |
| Servcode, BillACN--> Quantity|
| |
| Servcode--> Servname, Unit, Unitcost|
| |
Establish the relation with primary key|
| |
| SR_1(Patientno, Lastname, Firstname, Middlename, Birthdate, Gender, Address, BillACN, Billdate, Servcode, Servname, Unit, Unitcost, Quantity)|

FIRST NORMAL FORM
SR_1| | | | | | | | | | | | | 1NF|
Patient_no| Lastname| Firstname| Middlename| Birthdate| Gender| Address| BillACN| Billdate| Servcode| Servname| Unit| Unitcost| Quantity| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| IN098| 1/12/2013| M010| Lorazepam| ea| 100| 7| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| IN098| 1/12/2013| S004| Radiology| svc| 3,000.00| 1| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| IN098| 1/12/2013| S007| Neoro surgery| svc| 25,000.00| 1| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| OUT045| 1/22/2013| C001| Consultation - General Medicine| Svc| 500| 1| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| OUT045| 1/22/2013| M001| Doxepin| Ea| 150| 4| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| IN201| 2/19/2013| C001| Consultation - General Medicine| svc| 500| 1| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| IN201| 2/19/2013| M003| Xanax| pc| 2,500.00| 4| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| IN201| 2/19/2013| M010| Lorazepam| ea| 100| 3| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| IN201| 2/19/2013| S004| Radiology| svc| 3,000.00| 1| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| OUT155| 2/5/2013| C002| Consultation – Psychiatry| Svc| 1,000.00| 1| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| OUT155| 2/5/2013| M0010| Zolops| Pc| 56| 100| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan| IN298| 2/22/2012| M001| Doxepin| ea| 150| 4| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan| IN298| 2/22/2012| M010| Lorazepam| ea| 100| 4| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan| IN298| 2/22/2012| S007| Neoro surgery| svc| 25,000.00| 1|

Remove partial functional dependency|
| | |
| Split the relation into new relations as defined in functional dependency| | | |
| | PATIENT(Patientno, Lastname, Firstname, Middlename, Birthdate, Gender, Address)| | | BILLHEADER(BillACN, Patientno, Billdate)|
| | BILLDETAIL(BillACN, Servcode, Quantity)|
| | SERVICE(Servcode, Servname, Unit, Unitcost)|
| | |

SECOND NORMAL FORM
PATIENT| | | | | | 2NF|
Patientno| Lastname| Firstname| Middlename| Birthdate| Gender| Address| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan|

BILLHEADER| | 2NF|
BillACN| Patientno| Billdate|
IN098| 1234| 1/12/2013|
OUT045| 1235| 1/22/2013|
IN201| 7431| 2/19/2013|
OUT155| 1234| 2/5/2013|
IN298| 9183| 2/22/2012|

BILLDETAIL| | 2NF|
BillACN| Servcode| Quantity|
IN098| M010| 7|
IN098| S004| 1|
IN098| S007| 1|
OUT045| C001| 1|
OUT045| M001| 4|
IN201| C001| 1|
IN201| M003| 4|
IN201| M010| 3|
IN201| S004| 1|
OUT155| C002| 1|
OUT155| M0010| 100|
IN298| M001| 4|
IN298| M010| 4|
IN298| S007| 1|

SERVICE| | | 2NF|
Servcode| Servname| Unit| Unitcost|
M010| Lorazepam| ea| 100|
S004| Radiology| svc| 3,000.00|
S007| Neoro surgery| svc| 25,000.00|
C001| Consultation - General Medicine| Svc| 500|
M001| Doxepin| Ea| 150|
M003| Xanax| pc| 2,500.00|
C002| Consultation – Psychiatry| Svc| 1,000.00|
M0010| Zolops| Pc| 56|

ERD

CONSULTATION HISTORY
SAMPLE REPORT TWO NORMALIZATION

SAMPLE REPORT 2: CONSULTATION HISTORY
DATA
Patientno| Lastname| Firstname| Middlename| Birthdate| Gender| Address| Phycode| Phyname| Condate| Symptoms| Diagnosis| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| D100| Edgardo Abejar| 1/12/2013| Nausea, Sleeplessness| Migraine| | | | | | | | D200| LemuelManalo| 2/5/2013| Hand Tremor| Nervous Breakdown| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| D009| Shaula Prado| 1/15/2013| Vomitting| Pregnant| | | | | | | | D200| LemuelManalo| 2/5/2013| Flu, nausea| UTI| 8001| QUIOGUE| BALTAZAR| CRUZ| 10/24/1997| M| Cainta, Rizal| D001| Mark Martinez| 12/19/2012| Blood on urine| UTI| | | | | | | | D009| Shaula Prado| 1/10/2013| Sleepless ness| Stress| | | | | | | | D200| LemuelManalo| 1/17/2013| Loss of Apetite| Prostate Cancer| | | | | | | | D300| Rowena Garcia| 2/2/2013| Loss of Weight| Prostate Cancer| 9564| SALCEDO| OLIVER| VALDERAMA| 5/24/2007| M| TandangSora, QC| D101| NeloTumbokon| 2/1/2013| Loose Bowel| Diarrhea| | | | | | | | D200| LemuelManalo| 2/17/2013| Itchy Rashes| Chicken Fox| | | | | | | | D101| NeloTumbokon| 2/20/2013| Swelling of neck| Goiter|

REMOVE REPEATING GROUPS
Patientno| Lastname| Firstname| Middlename| Birthdate| Gender| Address| Phycode| Phyname| Condate| Symptoms| Diagnosis| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| D100| Edgardo Abejar| 1/12/2013| Nausea, Sleeplessness| Migraine| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| D200| LemuelManalo| 2/5/2013| Hand Tremor| Nervous Breakdown| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| D009| Shaula Prado| 1/15/2013| Vomitting| Pregnant| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| D200| LemuelManalo| 2/5/2013| Flu, nausea| UTI| 8001| QUIOGUE| BALTAZAR| CRUZ| 10/24/1997| M| Cainta, Rizal| D001| Mark Martinez| 12/19/2012| Blood on urine| UTI| 8001| QUIOGUE| BALTAZAR| CRUZ| 10/24/1997| M| Cainta, Rizal| D009| Shaula Prado| 1/10/2013| Sleepless ness| Stress| 8001| QUIOGUE| BALTAZAR| CRUZ| 10/24/1997| M| Cainta, Rizal| D200| LemuelManalo| 1/17/2013| Loss of Apetite| Prostate Cancer| 8001| QUIOGUE| BALTAZAR| CRUZ| 10/24/1997| M| Cainta, Rizal| D300| Rowena Garcia| 2/2/2013| Loss of Weight| Prostate Cancer| 9564| SALCEDO| OLIVER| VALDERAMA| 5/24/2007| M| TandangSora, QC| D101| NeloTumbokon| 2/1/2013| Loose Bowel| Diarrhea| 9564| SALCEDO| OLIVER| VALDERAMA| 5/24/2007| M| TandangSora, QC| D200| LemuelManalo| 2/17/2013| Itchy Rashes| Chicken Fox| 9564| SALCEDO| OLIVER| VALDERAMA| 5/24/2007| M| TandangSora, QC| D101| NeloTumbokon| 2/20/2013| Swelling of neck| Goiter|

Define Functional Dependency|
| |
| Patientno-->Lastname, Firstname, Middlename, Birthdate, Gender, Address| | |
| Patientno, Condate, Phycode--> Phyname, Symptoms, Diagnosis| | |
| Phycode--> Phyname|
| |
| |
Establish the relation with primary key|
| |
| SR_2(Patientno Lastname, Firstname, Middlename, Birthdate, Gender, Address, Phycode, Phyname, Condate, Symptoms, Diagnosis)|

FIRST NORMAL FORM
SR_2| | | | | | | | | | | 1NF|
Patientno| Lastname| Firstname| Middlename| Birthdate| Gender| Address| Phycode| Phyname| Condate| Symptoms| Diagnosis| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| D100| Edgardo Abejar| 1/12/2013| Nausea, Sleeplessness| Migraine| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| D200| LemuelManalo| 2/5/2013| Hand Tremor| Nervous Breakdown| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| D009| Shaula Prado| 1/15/2013| Vomitting| Pregnant| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| D200| LemuelManalo| 2/5/2013| Flu, nausea| UTI| 8001| QUIOGUE| BALTAZAR| CRUZ| 10/24/1997| M| Cainta, Rizal| D001| Mark Martinez| 12/19/2012| Blood on urine| UTI| 8001| QUIOGUE| BALTAZAR| CRUZ| 10/24/1997| M| Cainta, Rizal| D009| Shaula Prado| 1/10/2013| Sleepless ness| Stress| 8001| QUIOGUE| BALTAZAR| CRUZ| 10/24/1997| M| Cainta, Rizal| D200| LemuelManalo| 1/17/2013| Loss of Apetite| Prostate Cancer| 8001| QUIOGUE| BALTAZAR| CRUZ| 10/24/1997| M| Cainta, Rizal| D300| Rowena Garcia| 2/2/2013| Loss of Weight| Prostate Cancer| 9564| SALCEDO| OLIVER| VALDERAMA| 5/24/2007| M| TandangSora, QC| D101| NeloTumbokon| 2/1/2013| Loose Bowel| Diarrhea| 9564| SALCEDO| OLIVER| VALDERAMA| 5/24/2007| M| TandangSora, QC| D200| LemuelManalo| 2/17/2013| Itchy Rashes| Chicken Fox| 9564| SALCEDO| OLIVER| VALDERAMA| 5/24/2007| M| TandangSora, QC| D101| NeloTumbokon| 2/20/2013| Swelling of neck| Goiter|

Remove partial functional dependency|
| | |
| Split the relation into new relations as defined in functional dependency| | | |
| | PATIENT(Patientno, Lastname, Firstname, Middlename, Birthdate, Gender, Address)| | | CONSULTATION(Patientno, Condate, Phycode, Phyname, Symptoms, Diagnosis)| | | PHYSICIAN(Phycode, Phyname)|

SECOND NORMAL FORM
PATIENT| | | | | | 2NF|
Patientno| Lastname| Firstname| Middlename| Birthdate| Gender| Address| 1234| ROBLES | CARLOS | LEGARDA| 12/11/1976| M| Manngahan, QC| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| 8001| QUIOGUE| BALTAZAR| CRUZ| 10/24/1997| M| Cainta, Rizal| 9564| SALCEDO| OLIVER| VALDERAMA| 5/24/2007| M| TandangSora, QC|

CONSULTATION| | | | 2NF|
Patientno| Phycode| Condate| Symptoms| Diagnosis|
1234| D100| 1/12/2013| Nausea, Sleeplessness| Migraine| 1234| D200| 2/5/2013| Hand Tremor| Nervous Breakdown|
1235| D009| 1/15/2013| Vomitting| Pregnant|
1235| D200| 2/5/2013| Flu, nausea| UTI|
8001| D001| 12/19/2012| Blood on urine| UTI|
8001| D009| 1/10/2013| Sleepless ness| Stress|
8001| D200| 1/17/2013| Loss of Apetite| Prostate Cancer| 8001| D300| 2/2/2013| Loss of Weight| Prostate Cancer| 9564| D101| 2/1/2013| Loose Bowel| Diarrhea|
9564| D200| 2/17/2013| Itchy Rashes| Chicken Fox|
9564| D101| 2/20/2013| Swelling of neck| Goiter|

PHYSICIAN| 2NF|
Phycode| Phyname|
D100| Edgardo Abejar|
D200| LemuelManalo|
D001| Mark Martinez|
D009| Shaula Prado|
D300| Rowena Garcia|
D101| NeloTumbokon|

ERD

PHYSICIAN LIST BY DEPARTMENT
SAMPLE REPORT THREE NORMALIZATION

SAMPLE REPORT 3: PHYSICIAN LIST BY DEPARTMENT
DATA
Deptcode| Department| Phycode| Phyname| Specialization| GEN| General Medicine| D001| Mark Martinez| General Medicine| | | D020| Karen Chua| General Medicine|
| | D030| Albert Leroy| General Medicine|
| | D060| Anne Lacuesta| General Medicine|
| | D401| Sheila Fuentes| General Medicine|
INT| Internal Medicine| D009| Shaula Prado| Obe gynecology| | | D300| Rowena Garcia| Oncology|
PED| Pediatrics| D101| NeloTumbokon| Genetics|
PSY| Psychiatry| D100| Edgardo Abejar| Geriatric Psychiatry| | | D200| LemuelManalo| Child Psychiatry|

REMOVE REPEATING GROUPS
Deptcode| Department| Phycode| Phyname| Specialization| GEN| Genral Medicine| D001| Mark Martinez| General Medicine| GEN| Genral Medicine| D020| Karen Chua| General Medicine| GEN| Genral Medicine| D030| Albert Leroy| General Medicine| GEN| Genral Medicine| D060| Anne Lacuesta| General Medicine| GEN| Genral Medicine| D401| Sheila Fuentes| General Medicine| INT| Internal Medicine| D009| Shaula Prado| Obe gynecology| INT| Internal Medicine| D300| Rowena Garcia| Oncology| PED| Pediatrics| D101| NeloTumbokon| Genetics|

PSY| Psychiatry| D100| Edgardo Abejar| Geriatric Psychiatry| PSY| Psychiatry| D200| LemuelManalo| Child Psychiatry|

Define Functional Dependency|
| |
| Phycode--> Phyname, Deptcode, Department, Specialization| | |
| Deptcode--> Department|
| |
| |
Establish the relation with primary key|
| |
| SR_3(Deptcode, Department, Phycode, Phyname, Specialization)| FIRST NORMAL FORM
SR_3| | | | 1NF|
Deptcode| Department| Phycode| Phyname| Specialization| GEN| Genral Medicine| D001| Mark Martinez| General Medicine| GEN| Genral Medicine| D020| Karen Chua| General Medicine| GEN| Genral Medicine| D030| Albert Leroy| General Medicine| GEN| Genral Medicine| D060| Anne Lacuesta| General Medicine| GEN| Genral Medicine| D401| Sheila Fuentes| General Medicine| INT| Internal Medicine| D009| Shaula Prado| Obe gynecology| INT| Internal Medicine| D300| Rowena Garcia| Oncology| PED| Pediatrics| D101| NeloTumbokon| Genetics|

PSY| Psychiatry| D100| Edgardo Abejar| Geriatric Psychiatry| PSY| Psychiatry| D200| LemuelManalo| Child Psychiatry| Remove transitive dependency|
| | |
| Split the relation into new relations as defined in functional dependency| | | |
| | PHYSICIAN(Phycode, Phyname, Deptcode, Specialization)| | |
| | DEPARTMENT(Deptcode, Department)|
THIRD NORMAL FORM

| |
PHYSICIAN| | | 3NF|
Phycode| Phyname| Deptcode| Specialization|
D001| Mark Martinez| GEN| General Medicine|
D020| Karen Chua| GEN| General Medicine|
D030| Albert Leroy| GEN| General Medicine|
D060| Anne Lacuesta| GEN| General Medicine|
D401| Sheila Fuentes| GEN| General Medicine|
D009| Shaula Prado| INT| Obe gynecology|
D300| Rowena Garcia| INT| Oncology|
D101| NeloTumbokon| PED| Genetics|
D100| Edgardo Abejar| PSY| Geriatric Psychiatry|
D200| LemuelManalo| PSY| Child Psychiatry|
DEPARTMENT| 3NF|
Department Code| Department|
GEN| Gene\ral Medicine|
INT| Internal Medicine|
PED| Pediatrics|
PSY| Psychiatry|

ERD

PATIENT PAYMENT HISTORY
SAMPLE REPORT FOUR NORMALIZATION

SAMPLE REPORT 4: PATIENT PAYMENT HISTORY
DATA
BillACN| Patientno| Lastname| Firstname| Middlename| Birthdate| Gender| Address| Paydate| Amount| Paymode| Chequeno| IN098| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| 1/10/2013| 5,000.00| CASH|  | | | | | | | | | 1/13/2013| 23,700.00| CASH|  | OUT045| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| 1/22/2013| 500| CASH|  | IN201| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| 2/18/2013| 5,000.00| CASH|  | | | | | | | | | 2/19/2013| 2,000.00| CHECK| BPI 20478910| OUT155| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| 2/4/2013| 5,000.00| PHILHEALTH| PHL 345901A| IN298| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan| 2/21/2013| 5,000.00| CASH|  |

REMOVE REPEATING GROUPS
BillACN| Patientno| Lastname| Firstname| Middlename| Birthdate| Gender| Address| Paydate| Amount| Paymode| Chequeno| IN098| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| 1/10/2013| 5,000.00| CASH| NULL| IN098| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| 1/13/2013| 23,700.00| CASH| NULL| OUT045| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| 1/22/2013| 500| CASH| NULL| IN201| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| 2/18/2013| 5,000.00| CASH| NULL| IN201| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| 2/19/2013| 2,000.00| CHECK| BPI 20478910| OUT155| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| 2/4/2013| 5,000.00| PHILHEALTH| PHL 345901A| IN298| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan| 2/21/2013| 5,000.00| CASH| NULL|

Define Functional Dependency|
| |
| Patientno-->Lastname, Firstname, Middlename, Birthdate, Gender, Address| | BillACN-->Patientno|
| BillACN, Patientno, Paydate --> Amount, Paymode, Chequeno| | |
Establish the relation with primary key|
| |
| SR_4(BillACN, Patientno, Lastname, Firstname, Middlename, Birthdate, Gender, Address, Paydate, Amount, Paymode, Chequeno)| FIRST NORMAL FORM
SR_4| | | | 1NF|
BillACN| Patientno| Lastname| Firstname| Middlename| Birthdate| Gender| Address| Paydate| Amount| Paymode| Chequeno| IN098| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| 1/10/2013| 5,000.00| CASH| NULL| IN098| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| 1/13/2013| 23,700.00| CASH| NULL| OUT045| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| 1/22/2013| 500| CASH| NULL| IN201| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| 2/18/2013| 5,000.00| CASH| NULL| IN201| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| 2/19/2013| 2,000.00| CHECK| BPI 20478910| OUT155| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| 2/4/2013| 5,000.00| PHILHEALTH| PHL 345901A| IN298| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan| 2/21/2013| 5,000.00| CASH| NULL|

Remove partial functional dependency|
| | |
| Split the relation into new relations as defined in functional dependency| | | |
| | PATIENT(Patientno, Lastname, Firstname, Middlename, Birthdate, Gender, Address)| | | BILLHEADER(BillACN, Patientno)|
| | PAYMENT(BillACN, Patientno, Paydate, Amount, Payment Mode, Chequeno)|

SECOND NORMAL FORM
PATIENT| | | | | | 2NF|
Patientno| Lastname| Firstname| Middlename| Birthdate| Gender| Address| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| 1235| GARCIA| AUBREY| LOPEZ| 3/25/1985| F| Central Ave, QC| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| 7431| MONTEMAYOR| TINA| WALTER| 7/21/1960| F| Fairview QC| 1234| ROBLES| CARLOS| LEGARDA| 12/11/1976| M| Manngahan, QC| 9183| CASTRODES| RICHARD| AMOYO| 11/2/1954| M| San del Monte, Bulacan|

PAYMENT| | | | | 2NF|
BillACN| Patientno| Paydate| Amount| Paymode| Chequeno| IN098| 1234| 1/10/2013| 5,000.00| CASH| NULL|
IN098| 1234| 1/13/2013| 23,700.00| CASH| NULL|
OUT045| 1235| 1/22/2013| 500| CASH| NULL|
IN201| 7431| 2/18/2013| 5,000.00| CASH| NULL|
IN201| 7431| 2/19/2013| 2,000.00| CHECK| BPI 20478910| OUT155| 1234| 2/4/2013| 5,000.00| PHILHEALTH| PHL 345901A| IN298| 9183| 2/21/2013| 5,000.00| CASH| NULL|

BILL_HEADER| 2NF|
BillACN| Patientno|
IN098| 1234|
IN201| 7431|
OUT155| 1234|
IN298| 9183|
OUT045| 1235|

ERD

ENTITY RELATIONAL DIAGRAM
NEW HOPE HOSPITAL DATABASE: OVER-ALL

TABLE STRUCTURES

PATIENT TABLE STRUCTURE:

DEPARTMENT TABLE STRUCTURE:

SERVICE TABLE STRUCTURE:

PHYSICIAN TABLE STRUCTURE:

BILLHEADER TABLE STRUCTURE:

BILLDETAIL TABLE STRUCTURE:

CONSULTATION TABLE STRUCTURE:

PAYMENT TABLE STRUCTURE:

STATEMENT OF ACCOUNT
GENERATED SAMPLE REPORT ONE

CONSULTATION HISTORY
GENERATED SAMPLE REPORT TWO

-------------------------------------------------

-------------------------------------------------

PHYSICIAN LIST BY DEPARTMENT
GENERATED SAMPLE REPORT THREE

PATIENT PAYMENT HISTORY
GENERATED SAMPLE REPORT FOUR

PATIENT LIST
GENERATED SAMPLE REPORT FIVE 

Comments