Fundamentals Of Database Systems In Royal Bahrain Hospital - Research Paper Example

PATIENT RECORDS MANAGEMENT SYSTEM FOR ROYAL BAHRAIN HOSPITAL. Number: Lecturer: Introduction. The main purpose of this project is to analyse the business operations of the Royal Bahrain Hospital and identify the challenges they face in data management. This will involve determination of their data needs and requirements and subsequently developing a database through which the staff and patients in the hospital can effectively manage and maintain their data (Ramakrishnan, Gehrke, & Gehrke, 2003). In any particular database and particularly in this setting, data integrity, confidentiality and authenticity are the major principles that must be adhered to at all times. This can only be achieved through proper collection, processing, storage, access and manipulation of data in the database (Elmasri, 2008). In view of ensuring the success of this project the team will: i) Ensure that there is research. ii) Proper identification of the problems inherent in the organization. iii) Proper planning and design to ensure that the final database system will provide the perfect solution to the identified problems. iv) Regular meeting s and contact to harmonize findings and plan the development of the database. Description of current business operation. Royal Bahrain Hospital is a hospital in Bahrain that provides quality premium healthcare at an economic cost. The facility provides health care services to the general public that is accessible, economic, boundless, comprehensive and appropriate. They have a list of specialised professionals who have the capacity to provide these health care services to their patients (Royal Bahrain Hospital, 2014). Currently, the facility has a semi-automated system that manages all the records and information in the hospital. The system keeps records of all the patients and the doctors together with the prescriptions and all the data required to complete treatment operation in the hospital. The patient works into the hospital and is registered and examined before they are given a patient number. A patient file is created where the details of the patient will be stored together with the examination results, doctor’s prescription, comments and treatment. This file is kept at the registry where it can be accessed anytime the patient needs to see the doctor. This mode of data management and processing possess a great challenge to the hospital staff, patients and hospital management in general. Essentially, patient records must be stored accurately and accessed and modified by doctors when needed. It is the availability and access to the information that will determine the effectiveness of the treatment given to the patient. The current system used by the hospital has many pitfalls including; Huge amount of paper trails and increased storage requirements. Incomplete information. Incidences of loss of patient records. Duplication of data or data redundancy. Difficulty and time wastage in accessing the patient records. Lack of confidentiality of patient records. Integrity issues regarding the accuracy of patient records. Given the challenges faced by the organization regarding patient records and general management of information and processes, it is advisable to develop a computer based database that will be used for storage and management of data in the hospital. In order for us to design and develop an effective database for the Royal Bahrain Hospital, we have to consider a set of data requirements including; Patient information; patient Id, names, address, date of birth among others. Staff information; doctor records and schedules including appointments. Available health plans. Prescriptions. Inventory of drugs in the system. Database strategy. Database platform. There are several database management systems that are used to develop and manage database in business settings. These systems vary depending on the nature of data requirements and the scope of the data to be managed (Rosina & Mohammad, 2009). Royal Bahrain Hospital is a medium sized enterprise therefore; the proposed database will be developed in Ms Access. The database will have all the tables and queries that are needed to address the data management challenges experienced in the hospital. The database will contain the required entities, attributes and relationships. The database will contain various tables namely; Patients, Doctors, Health Plans, Drugs and prescriptions. Business Rules: A database must always have entities, attributes, relationships and constraints that must be defined using business rules (Halle & Ronald, 2001). Business rules essentially validate the data stored in the database. Without business rules these records are simply records with no meaning. Therefore, the proposed database for Royal Bahrain Hospital has business rules that will help all the employees and all users of the database to focus on implementing accuracy and integrity in provision and use of the data. Business rules will be important in creating accurate and reliable data and records that can be used for effective decision making in the hospital. Given their great significance, we intend to make the business rules for the database simple and easy to understand. Additionally, we will consider keeping the rules broad to allow similar interpretation from all users of the data in the hospital environment. Here are some of the business rules for the proposed database: i) A patient can only be in one health Plan. (1:M) ii) A patient May have more than one Prescription.(1:M) iii) A doctor may issue more than one prescription.(1:M) iv) One prescription may have many drugs(1:M). v) One prescriprioin may have several refills.(1:M) Data Dictionary. Table Attributes Contents Type Format Range PKey or FKey Required FK ref Table Patient PatientID Patient Identity Number Number XXX PKey Y First Name First Name Text XXXX N Last Name Last Name Text XXXX N Phone Phone Contacts Text (xxx)xxx-xxxx N Date of Birth Date of birth Date Dd/mm/yy N Gender Patients Gender Text X N Health Plan Health plan Id text HXXXX Fkey N Health Plan Doctors Doctors ID Doctors Id Text XX Pkey Y First Name First Name Text XXXX N Last Name Last Name Text XXXX N Phone Phone Number Text (XXX)XXX-XXXX N Health Plans Plan Id Health Plan No Text HXXXX Pkey Y Name Health Plan Name Text XXXX N Drugs UPN Drug ID Text XXXX Pkey Y Name Drug Name Text XXXX N Generic Drug type Yes/No X N Description Description Text XXXX N Unit Units Text XXX N Dosage Dosage Text XXX N Dosage Form Dosage form Text XXXX N Cost Cost of drug Currency XXXX N Price/Dosage Price//dosage Currency XXXX N Prescription Prescription ID Prescription Id Number XX Pkey Y UPN Drug Number Text XXX FKey N Quantity Quantity Number XX N Unit Unit Text XXXX N Date Prescription Date Date DD/MM/YY N Exp Date Date of Expiry Date DD/MM/YY N Refills Authorised Refills Authorised Number XX N Auto Refill? Auto refill Yes/No X N Refills used Refills used Number X N Instructions instructions Text XXXX N Patient Id Patient Id Text XXXX FKey N Patients Doctor Doctors Id Text XXXX Fkey N Doctors Refills Prescription Id Prescription number Number XX PKey Y Refill Date Date of Refill Date DD/MM/YY N Entity Relationship Diagram. TABLES: The following section has details for the different tables in the database: DOCTORS TABLE: DOCTORS (DOCTOR_ID, DOCTOR_FNAME, DOCTOR_LNAME,DOCTOR_PHONE); Contains details of the doctors in the hospital including their telephone numbers. The Doctors Id is the Primary Key that uniquely identifies the doctors. DRUGS: DRUGS (DRUG_UPN,DRUG_NAME, DRUG_TYPE(GENERIC), DRUG_DESCRIPTION, DRUG_UNIT, DRUG_DOSAGE, DRUG_DOSAGEFORM, DRUG_COST, DRUG_PRICE/DOSE) HEALTH_PLANS. HPLANS (HPLAN_ID, HPLAN_NAME) PATIENTS: PATIENTS (PATIENT ID, PATIENT_FNAME, PATIENT_LNAME, PATIENT_PHONE, PATIENT_DOB, PATIENT_GENDER, PATIENT_ HPLAN) PX: Contain a List of prescriptions: PX (PX_NO, PX_UPN, PX_QUANTITY, PX_UNIT, PX_DATE, PX_EXPIRY_DATE, PX_REFILLSAUTH, PX_AUTOREFILL, PX_REFILLSUSED, PX_INSTRUCTIONS, PX_PATIENTSID, PX_DOCTORID) REFILLS: Shows prescriptions to be refilled. REFILLS (REFILL_PXNO, REFILL_REFILLDATE) QUERIES. TREATED PATIENTS: This query extracts the list of patients that have been treated and have prescriptions with the auto fill of drugs authorised. This query helps the hospital to determine how to stock drugs according to the patients with prescriptions to be auto refilled. The query is sorted by Expiry date in descending order. PATIENT_HEALTH PLAN. The query shows which patients are in which health Plan by extracting the details from the patient and Health Plan tables. The hospital also needs to know which patients are in which health plan for billing purposes. The query selects patients and their health plans: BALANCE_REFILLS. This query shows remaining refills for each patient whose prescription is authorised to be refilled. The balance is determined by subtracting used refills from the authorised number of refills. VALUE_PRESCRIBED DRUGS: Calculates the total value of drugs that have been prescribed to patients this helps in determining returns and drug inventory. AUTOREFILL_PRESCRIPTIONS: This query selects the list of patients for which their prescriptions have been authorised for auto refill. This helps the physician and the hospital to determine which patients have been authorised to refill their prescriptions. DOCTORS_PRESCRIPTIONS Shows the prescriptions to each patient from every doctor. The records are sorted in ascending order according to the DOCTORS_ID; References. Elmasri, R. (2008). Fundamentals of database systems. Pearson Education India. http://www.cs.gmu.edu/~aobaidi/fall_05/.../ENCh10.ppt Halle, B. V., & Ronald, G. (2001). Business rules applied: building better systems using the business rules approach. . John Wiley & Sons, Inc.. http://dl.acm.org/citation.cfm?id=559532 Ramakrishnan, R., Gehrke, J., & Gehrke, J. (2003). Database management systems . New York: McGraw-Hill. http://dl.acm.org/citation.cfm?id=560733 Rosina, S. K., & Mohammad, S. (2009). Design of a Hospital-Based Database System. International Journal on Computer Science and Engineering, 2616-2621 http://www.enggjournals.com/ijcse/doc/IJCSE10-02-08-050.pdf Royal Bahrain Hospital. (2014). Royal Bahrain Hospital. Retrieved November 29, 2014, from Royalbahrainhospital: http://www.royalbahrainhospital.com/ Silbershatz, H., & Sudarshan. (2005). Database System Concepts,. McGraw Hill http://www.cse.iitb.ac.in/~sudarsha/db-book/slide-dir/ Read More