Your consulting firm has just been hired at No Mercy Nor Grace (NMNG) Hospital to provide some recommendations for improving the struggling organization. Over the past 10 years, the hospital’s operations have steadily entered a state of disarray and the organization is bleeding money through inefficiencies and law suits have arisen as a result. Your team has been hired to study the hospital’s existing data in hopes that you can present some feasible solutions that will alleviate some of the organization’s current burdens.
It is the hospital’s strict policy that you, as an outside firm, are not allowed to speak or come in contact with any clinician or patient. That being said, interviews are out of the question and you are forced to liberally interpret the data you have.
You are merely given a data-dump Preview(see attachment) the documentView in a new window from the hospital’s archaic mainframe system. The system is populated primarily by human typing, so the potential for human input error is extremely high in the provided data dump. Unfortunately, this mainframe contains no business logic to prevent human error.
As a consultant, you have been asked to provide detailed recommendations for the problems below. Your job as an informatics consultant is to organize the data and make some logical conclusions and present them to the Board of Trustees. The Board is primarily interested in well rooted conclusions backed up by sound data.
By the way, you are competing against other firms as well; the team with the best recommendations will get the contract to implement the proposed solutions. Use logic and interpretation to create and frame your explanations.
Nursing Morale: Overtime is being paid at a ridiculous rate due to inefficiency. Nursing morale is suffering and patient satisfaction scores are at an all time low as a result of the unhappy nurses. What is causing this?
Fraudulent Billing: It has been suspected for a long time that the insurance companies are charging random, often exorbitant, fees for billing. Executives think this is happening, but have no idea why. The legal team wants some kind of proof.
Medical Mistakes: A $6M law-suit is currently underway with a patient receiving medications she was not supposed to. Unfortunately, the hospital cannot release the case’s information to you since you are not their employee and to be honest, they don’t really know what happened. How could this have happened? How can it be prevented?
Physician Salaries/Payments: Since this hospital is collectively owned and operated by a group of physicians, all physicians receive the same salary. Some physicians are claiming that they should be paid according to the number of patients they see, while others are perfectly content with the system. Some world-renowned physicians are threatening to walk out, because they are claiming to be overworked. No one knows why these physicians are upset. HR does not understand what’s going on and without meaningful data they are in a state of panic as to how to proceed.
The hospital is looking to re-organize their database in to a relational manner, but do not know how to break it out or group the relationships. What would the new tables look like? How would they be joined together? Unique ID columns will more than likely need to be created.
You are requested to prepare an executive report and a presentation; both to be delivered to the board of trustees of the hospital. Your report and presentation should include the following:
The hospital wants you to first look at their existing database and get things organized. As you can see, they are currently storing everything into a single table. This table needs to be broken down into multiple tables and relationships among them. You may choose to add additional columns in your new tables; that is up to you. Based on the results of your evaluation, you are asked to create the following:
An Entity Relationship Diagram that includes cardinalities and primary and foreign keys.
The data definition (DDL) scripts that are used to create the newly formed tables using CREATE and INSERT SQL statements. Your statements should include CONSTRAINT declarations.
In the process of splitting up the original table, you should encounter some issues in regards to the keys (ID columns). Include a brief discussion of what the issues are and how you resolve them?
A general blurb (4-5 sentences) about each table and why it was designed in the way your team chose. What is the benefit of the way you designed it? Why is it more ‘maintainable’ then they way in which it was previously organized? How easy is it to add additional information to it? How does the data ‘flow’ to the other tables?
Now that your data is organized, it is time to solve some problems. In looking at the four problems that are described above, provide a thorough analysis as to why each problem is occurring. Each problem should consist of charts/graphs, a summary defending your analysis, a summary explaining your recommendation, and the SQL that you used to extract the data in performing the analysis.
You have gotten this far and have assumingly solved the hospital’s immediate headaches. Now that the data is organized and maintainable, the hospital’s administration wants to readily access reports so that they can prevent future problems from occurring and stay on top of things. What reports would you suggest to be created? These could be similar to what you used in your analysis in 4 or some additional ones. Provide an explanation for how each report you suggest would benefit the hospital and create database views for each report. Include your CREATE VIEW SQL statements.
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.