HI 601-QL Databases and Data Modeling Case Study Your consulting firm has just been hired at No Mercy Nor Grace (NMNG) Hospital to provide some

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.

THE PROBLEMS

1. 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? 
2. Billing Issues: It has been suspected for a long time that the insurance companies are taking advantage of the hospital's bad record keeping practices and denying payment for some procedures or underpaying the hospital. Executives think this is happening, but have no idea why. They want to be able to find out and they want recommendations how to solve it.
3. 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?
4. 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 and they are in a state of panic as to how to proceed.

THE ASSIGNMENT

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:

1. 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:
2. An Entity Relationship Diagram that includes cardinalities and primary and foreign keys.
3. The data definition (DDL) scripts that are used to create the newly formed tables using CREATE and data manipulation (DML) scripts that are used to populate the new tables using INSERT SQL statements. Your statements should include CONSTRAINT declarations.
4. 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? 
5. 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 the 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?
6. 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.
7. 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. 

Format

It is up to the you to organize the report and the presentation, however all elements indicated in the items above should be included in the final report. You may choose to include technical documents as appendices or include in the main body of your presentation/report. Just make sure to remember your target audience (board of trustees). All of this analysis you have done becomes valuable if it is presented in a manner that will captivate their attention. Your summary is expected to be professional and data visualization is key to capturing the executive's attention. On the other hand, they would like technical details to be included in your report and presentation, in case they feel the need to get your claims verified. The primary purpose of the presentation is to convince the board that you are the right team for the job. The secondary purpose of the presentation is to get feedback from the instructor.

The submissions should include the presentation in ppt, pptx, or pdf format; all SQL scripts in sql format (SQL files must include appropriate comments and be appropriately referenced by file name in presentation/report); all other submissions (including the final report) should be in pdf format.