PROJECT DESCRIPTION

The project is about designing and implementing a database system for Concordia Hospital (CHDS). It includes the creation of E/R diagram and its corresponding relational schema such as Departments, Labs/Rooms, Tests/Operations, Admission, Doctors, Nurses, Staffs, Patients, Visits, Bills, Medical Equipment, Purchase, Cheques, and Specialties.
In addition, we have to develop a Web Based Interface capable of providing the required set of functionalities through the Internet.

Scenario
In order to help the administrators of the hospital, we have to create a database system application to support the various activities performed by the administrative medical staff.

The database will store information on doctors, nurses, employees, patients (walk-in or admitted), the dates they were admitted and/or discharged, “complete” medical history of all patients after their first admission (with strict access control), departments, their labs/rooms and the tests/operations performed in those labs/rooms, the various medical services provided (including the corresponding service fees for the tests, visits, operations, beds, etc), and day to day business of the hospital (including billings and expenses).

All Employees including doctors and nurses are paid based on their skills and work experiences on a monthly basis.

Implementation Requirement

1. Create the forms to manipulate (insert, delete, update) the entries of the tables, at least for Patient, Doctor, Employee, and Admission.


2. Create the forms to search for a specific patient based on first name, last name, phone, date admitted, date discharged, disease, or a combination thereof. It would be nice if the result page includes not only the patient's information but also extra navigation/functionality information; for example clicking on patient's name shows the update page, or clicking on the last name would sort the patient's name in either order.


3. Create the forms to report some statistical information including:


1. Calculate the average age of the patients so far admitted into the hospital.
2. Calculate the percentage of patients for each disease.
3. Calculate the average number of days to cure each disease.


4. Given a medical insurance number, list all the prescriptions with doctor's name for that patient in descending order by date.


5. Given a medical insurance number, patient name, and date generate the bill showing both summary and details of charges for the patient and for the insurance company.


6. Given a job title, list all the information about the personal with that job sorted by city, country, and start date.


7. Given a time period, report the purchase details in that period sorted by date.


8. Generate a report on the use of lab tests, showing the five most frequently conducted tests, the labs in which they are conducted, and the percentage of times each of the tests were conducted.


9. Generate a report to show the maximum, minimum, and average of the duration of the hospital stay for each type of diagnosis.


10. Generate a report to summarize the number of walk-in and admitted patients per day, per month, and per year.


11. List the top 3 disease for each type of patient (walk-in, admitted) per month and per year.


12. Prepare the monthly pay cheques for all the employees.