Database Management Systems - Report Example

GENERAL PRACTICE DATABASE Module By Contents Contents 2 Introduction 3 Derived and Minortables 14 These tables are a product of normalization of the main tables. They are as follows: 14 1.Cadres table: This stores all the cadres within the GP as well as the clearance level for each cadre (Physicians have a higher access level than nurses and technicians). 14 2.Staff_has_patients: This table is a result of the Many-to-Many relationship between patients table and staff table. 14 3.Logins: This targets the users of the system, and it contains the login details for staff with regards their access level. 14 4.Ailments: This table provides relevant data for the diagnosis table. 14 C. Queries 14 Queries enable a fast, easy and efficient way to obtain data from tables using specific criteria (Mcgrath 2012, 119-130) For this database one in able to query the tables to come up with useful information. The following two queries demonstrate the kind of information that can be derived from the database. 14 D. Views and Triggers 16 Views help in merging tables and in overall performance of the database, therefore, can be highly efficient when used to manage data instead of tables (Tahaghoghi & Williams 2006, 460-520). For this project, we used two views. 16 1. Patient Admission 16 2. Stafflogin 16 List of References 17 Introduction A good database should perform the desired functions, be available when needed and must have the appropriate security ( Stair & Reynolds 2010,188). All the users of the system must be satisfied. It is, therefore, important that the underlying database, which is the core data source and storage, meet all the needs. This project aims to implement a possible underlying database for a General Practice. A General Practice is a people centered environment, thus details of people is important for any form of operations to be undertaken. Patients trickle in and are admitted while doctors treat patients and prescribe medicines. Patients can also be referred to other health institutions it sent to the laboratory for medical tests. All these activities have to be recorded somewhere, and in such a way that the records reflect on these important activities. Additionally, doctors and other health workers need this data to perform even basic tasks such as appointment and referrals. Diagnosis can also be made with the aid of a repository of possible diseases, which the doctor can use to decide on the next course of action. This system will use a database known as GP for General Practice. Finding and Analysis We begin by identifying the main entities of the system. These are the the most obvious entities that will reflect on the database in its first normal. More entities can be derived from these so as to enable a normalized database when translating it to its physical schema. Technologies Used For this project, we used MySQL Workbench Community Edition for the modeling as well as the physical design. The UML diagrams and SQL code in this report have been directly extracted from MySQL Workbench CE. Connection to the database was through the WampServer, which additionally holds the final database. The tables are written in Standard Query Language (SQL). Therefore, any code in this paper will be in this language. A. Entities and Attributes The following are the main attributes for the database. 1. Patient The patient is a nonfunctional entity within this system, but it the most important data source since all operations are centered on patient data. Patients have attributes such as the Name, Telephone, Email Address, Emergency contact. Additionally, a nurse and a doctor are assigned to the patient. 2. Medical staff These are the staff within the general practice. Attributes include the names, contacts, category or cadre. They include physicians, surgeons, pharmacists, nurses, anesthesiologist, surgical technicians and nurses. They also have different access levels with which they can perform tasks in the system. This, therefore, reflects on the database as will be illustrated when we discuss the tables. 3. Prescription After seeing a physician, the patient is may need medication or other related drugs. Therefore, this becomes an entity by itself where details of the prescription such as the date, drug and prescribing doctor are the attributes. 4. Medicines: These are the drugs that are present within the General Practices pharmacy. The system is expected to perform regular updates on this list as medicines come and go. Attributes include the drug name and a description of the drug. 5. Referrals At specific times in the execution of the medical practice, the physician may decide to refer the patient to a different health center for specialized treatment. A referral should indicate the relevant patient information, a description of the symptoms or complications, information of the physician who is referring and the date of reference. 6. Rooms Patients that are admitted are expected to be allocated vacant rooms. The rooms have a room number, and an indicator of its occupancy - whether it is occupied or vacant. 7. Lab sessions This entity follows a similar philosophy to the referrals entity. The physician may decide to send the patient to the laboratory for further tests or screening. The attributes include the patient, date, the lab procedure and the technician 8. Diagnosis Physicians need a system to help them perform and record diagnostic tasks. There should be a knowledge base of a list of illnesses for this purpose. Attributes for diagnosis are the treating physician, patient, description of the diagnosis and the diagnosis date. B. Tables and Relationships The above entities are important in identifying the tables to be used in the actual database. However, more entities and tables may be arrived at during the process of normalization. It is thus crucial to perform an analysis of the entities to see if more tables will be formed. To illustrate this, we will us the Entity Relationship below to explain the tables formation. Enhanced Entity Relationship Diagram Brief explanation of the diagram The above diagram is the EER (Enhanced Entity Relationship) diagram as produced by MySQL Workbench CE, for the final model of the database. We will use this model to illustrate that the database is normalized. It includes the tables derived from the main entities as well as other tables that are a result of normalization. Additionally included are two views that we will be illustrated later in the paper. Core Table Relationships and SQL Code As can be seen in the above EER diagram, there are several relationships occurring between core tables as well as some derived tables. Here are some of the relationships between the tables There is a Many-to-Many relationship between patients and staff, thereby making it necessary to create an additional table to cater for this relationship. This means that patients can have more than one medical staff assigned to them (Say a physician and a nurse) One-to-Many relationship between patients and admissions (One patient can have several admissions since they can check out and check in later). One-to-Many relationship between patients and referral (The patient can have other referrals the next time they are treated in the hospital). One-to-Many relationship between patients and lab session (The patient can have several lab procedures) One-to-One relationships between admissions and rooms(This means that a patient can only receive one room upon admission) 1. Patients table SQL code CREATE TABLE IF NOT EXISTS `GP`.`patients` ( `patientID` INT NOT NULL AUTO_INCREMENT, `fname` VARCHAR(60) NOT NULL, `sname` VARCHAR(60) NOT NULL, `telephone` VARCHAR(45) NULL, `email` VARCHAR(60) NULL, `emergencycontact` VARCHAR(45) NULL, PRIMARY KEY (`patientID`)) ENGINE = InnoDB; The above code was used to create the patients table with the attributes: fname, sname, telephone, email and emergency contact with the relevant types. It also has the primary key patientID. 2. staff table SQL code CREATE TABLE IF NOT EXISTS `GP`.`staff` ( `staffID` INT NOT NULL AUTO_INCREMENT, `sfname` VARCHAR(60) NOT NULL, `ssname` VARCHAR(60) NOT NULL, `telephone` VARCHAR(45) NULL, `email` VARCHAR(45) NULL, `cadre` INT NOT NULL, PRIMARY KEY (`staffID`, `cadre`), INDEX `fk_staff_cadres1_idx` (`cadre` ASC), CONSTRAINT `fk_staff_cadres1` FOREIGN KEY (`cadre`) REFERENCES `GP`.`cadres` (`cadresID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; This table has the attributes: staffID(the key identifier), sfname, ssname, telephone, email and the cadre(what role a staff member has). 3. Prescriptions table SQL code CREATE TABLE IF NOT EXISTS `GP`.`prescriptions` ( `prescriptionID` INT NOT NULL AUTO_INCREMENT, `prescriptiondate` DATETIME NOT NULL, `instructions` TEXT(300) NULL, `prescribedBy` INT NOT NULL, `drug` INT NOT NULL, `patient` INT NOT NULL, PRIMARY KEY (`prescriptionID`, `prescribedBy`, `drug`, `patient`), INDEX `fk_prescriptions_staff1_idx` (`prescribedBy` ASC), INDEX `fk_prescriptions_drugs1_idx` (`drug` ASC), INDEX `fk_prescriptions_patients1_idx` (`patient` ASC), CONSTRAINT `fk_prescriptions_staff1` FOREIGN KEY (`prescribedBy`) REFERENCES `GP`.`staff` (`staffID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_prescriptions_drugs1` FOREIGN KEY (`drug`) REFERENCES `GP`.`drugs` (`drugID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_prescriptions_patients1` FOREIGN KEY (`patient`) REFERENCES `GP`.`patients` (`patientID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; The Attibutes are: prescriptionID (table key), prescriptiondate, instructions, prescribedby , drug and patient . 4. Referals table SQL code CREATE TABLE IF NOT EXISTS `GP`.`referrals` ( `referalID` INT NOT NULL AUTO_INCREMENT, `hospitalname` VARCHAR(45) NOT NULL, `referraldate` DATETIME NULL, `diagnosisID` INT NULL, `patient` INT NOT NULL, `refferingphysician` INT NOT NULL, `diagnosis` INT NOT NULL, PRIMARY KEY (`referalID`, `patient`, `refferingphysician`, `diagnosis`), INDEX `fk_referals_patients1_idx` (`patient` ASC), INDEX `fk_referals_staff1_idx` (`refferingphysician` ASC), INDEX `fk_referrals_diagnosis1_idx` (`diagnosis` ASC), CONSTRAINT `fk_referals_patients1` FOREIGN KEY (`patient`) REFERENCES `GP`.`patients` (`patientID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_referals_staff1` FOREIGN KEY (`refferingphysician`) REFERENCES `GP`.`staff` (`staffID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_referrals_diagnosis1` FOREIGN KEY (`diagnosis`) REFERENCES `GP`.`diagnosis` (`diagnosisID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; 5. Labsessions table SQL code CREATE TABLE IF NOT EXISTS `GP`.`labsessions` ( `labID` INT NOT NULL AUTO_INCREMENT, `procedureID` INT NOT NULL, `technicianID` INT NULL, `labresults` TEXT(500) NULL, `labreferencedate` DATETIME NOT NULL, `patientID` VARCHAR(45) NOT NULL, `labresultsdate` DATETIME NULL, `patient` INT NOT NULL, PRIMARY KEY (`labID`, `patient`), INDEX `fk_labsessions_patients1_idx` (`patient` ASC), CONSTRAINT `fk_labsessions_patients1` FOREIGN KEY (`patient`) REFERENCES `GP`.`patients` (`patientID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; 5. Drugs table SQL code CREATE TABLE IF NOT EXISTS `GP`.`drugs` ( `drugID` INT NOT NULL AUTO_INCREMENT, `drugname` VARCHAR(100) NOT NULL, `description` TEXT(300) NULL, PRIMARY KEY (`drugID`), UNIQUE INDEX `drugname_UNIQUE` (`drugname` ASC)) ENGINE = InnoDB; 6. Admissions SQL code CREATE TABLE IF NOT EXISTS `GP`.`admissions` ( `admissionID` INT NOT NULL AUTO_INCREMENT, `admissiondate` DATETIME NOT NULL, `signedout` DATETIME NULL, `patientID` INT NOT NULL, `roomID` INT NOT NULL, PRIMARY KEY (`admissionID`, `patientID`, `roomID`), INDEX `fk_admission_patients1_idx` (`patientID` ASC), INDEX `fk_admissions_rooms1_idx` (`roomID` ASC), CONSTRAINT `fk_admission_patients1` FOREIGN KEY (`patientID`) REFERENCES `GP`.`patients` (`patientID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_admissions_rooms1` FOREIGN KEY (`roomID`) REFERENCES `GP`.`rooms` (`roomID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; 7. Rooms Table SQL code CREATE TABLE IF NOT EXISTS `GP`.`rooms` ( `roomID` INT NOT NULL AUTO_INCREMENT, `roomnumber` INT NOT NULL, `availability` VARCHAR(6) NOT NULL, PRIMARY KEY (`roomID`, `roomnumber`), UNIQUE INDEX `roomnumber_UNIQUE` (`roomID` ASC)) ENGINE = InnoDB; 8. Diagnosis Table SQL Code CREATE TABLE IF NOT EXISTS `GP`.`diagnosis` ( `diagnosisID` INT NOT NULL AUTO_INCREMENT, `description` TEXT(500) NULL, `diagnosisdate` DATETIME NULL, `treatingphysician` INT NOT NULL, `ailment` INT NOT NULL, `patients_patientID` INT NOT NULL, PRIMARY KEY (`diagnosisID`, `treatingphysician`, `ailment`, `patients_patientID`), INDEX `fk_diagnosis_staff1_idx` (`treatingphysician` ASC), INDEX `fk_diagnosis_ailments1_idx` (`ailment` ASC), INDEX `fk_diagnosis_patients1_idx` (`patients_patientID` ASC), CONSTRAINT `fk_diagnosis_staff1` FOREIGN KEY (`treatingphysician`) REFERENCES `GP`.`staff` (`staffID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_diagnosis_ailments1` FOREIGN KEY (`ailment`) REFERENCES `GP`.`ailments` (`ailmentID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_diagnosis_patients1` FOREIGN KEY (`patients_patientID`) REFERENCES `GP`.`patients` (`patientID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; Derived and Minor tables These tables are a product of normalization of the main tables. They are as follows: 1. Cadres table: This stores all the cadres within the GP as well as the clearance level for each cadre (Physicians have a higher access level than nurses and technicians). 2. Staff_has_patients: This table is a result of the Many-to-Many relationship between patients table and staff table. 3. Logins: This targets the users of the system, and it contains the login details for staff with regards their access level. 4. Ailments: This table provides relevant data for the diagnosis table. C. Queries Queries enable a fast, easy and efficient way to obtain data from tables using specific criteria (Mcgrath 2012, 119-130) For this database one in able to query the tables to come up with useful information. The following two queries demonstrate the kind of information that can be derived from the database. This query returns the details of the patients that are in Tudor Hospital as well as those of the staff that referred them to that hospital. This query checks the number of rooms available and the number of patients with rooms. D. Views and Triggers Views help in merging tables and in overall performance of the database, therefore, can be highly efficient when used to manage data instead of tables (Tahaghoghi & Williams 2006, 460-520). For this project, we used two views. 1. Patient Admission CREATE VIEW `patientadmission` AS SELECT fname,sname,admissiondate,signedout,roomnumber FROM patients p,admissions a,rooms r WHERE p.patientID =a.patientID AND a.roomID= r.roomID 2. Stafflogin CREATE VIEW `stafflogin` AS SELECT sfname,ssname,username,cadre,password FROM staff stf ,logins log WHERE stf.staffID = log.staffID E. Security Security should be a priority when designing a database as well as during operation (Baster & Zgola 2011, 35-69). For this database, there are access levels for the users (in this case, the medical staff), such that a certain level of clearance is required in order to access certain tables. For example, while a physician can prescribe medicine, a nurse with a lower clearance level may not be able to do so. This implemented at the system level where each of the different levels may have its own interface. Login passwords may also be encrypted to prevent unauthorized access of the database tables when login details are stolen or captured (Gertz & Jajodia 2008 45 -70).  List of References Basta, A., & Zgola, M. (2011). Database security. Boston, Mass, Course Technology. Gertz, M., & Jajodia, S. (2008). Handbook of database security: applications and trends. New York, Springer. Stair, R. M., & Reynolds, G. W. (2010). Principles of information systems: a managerial approach. Boston, Mass, Course Technology, Cengage Learning.188 Mcgrath, M. (2012). Php & Mysql in easy steps. Leamington Spa, Warwickshire, U.K., In Easy Steps. Tahaghoghi, S. M. M., & Williams, H. E. (2006). Learning MySQL. Sebastopol, Calif, OReilly. http://proquest.safaribooksonline.com/0596008643. Read More