← Back to Portfolio

Relational Hospital Database

Overview

For this project, I was tasked with designing a relational database from scratch for a sample hospital entirely within MySQL. I was provided with the hospital’s information requirements and the relationships it needed to keep track of, including patient information, physician and nurse roles, and performed procedures. I first designed an Entity-Relationship Diagram (ERD) to define these relationships, and establish the database structure. Once the ERD was finalized and verified, I translated the design into relational tables using SQL. I then loaded sample CSV files into the database to validate the table structure and relationships. If an error occurred when loading any of the data, I troubleshot the SQL, modified the table, and recreated whatever table was causing the error. After successfully loading the data, I performed analytical queries across multiple tables to extract insights and answer questions about the hospital.

Database Design

The ERD displayed below modeled all of the relationships between entities within a sample hospital. The database was then translated into normalized relational tables with primary keys, foreign keys, and other relational constraints to maintain data integrity. This initial stage of the project was helpful in visualizing what relationships needed to be established before getting deep into the programming.

Entity-Relationship Diagram for the relational hospital database.

Data Model

As shown above, the hospital_record table is the central entity in the database. It tracks the specific patient, the performed procedure, which physician and nurse were involved, the date of the procedure, and the length of the patient’s stay. A separate table stores the medications available to patients, along with prescriptions associated with each visit and the physician who prescribed each medication. Payment information is also recorded, including the invoice associated with each record and the method(s) of payment. Patient information and insurance details are also maintained in separate tables. The database also tracks the procedures available at the hospital and the rooms each one is performed in. Each physician’s information is recorded along with their respective department, while nurse information is also logged.

SQL Analysis

Next, I performed several analytical queries against the relational hospital database covering patients, physicians, procedures, billing, and insurance. Listed below are a few examples that highlight specific operational and financial questions that would be beneficial for analyzing the hospital’s processes.

Using a window function, this query identifies the physician with the highest amount of procedures within each hospital department. Partitioning by department allows for a more fair comparison of performance based on specialty. This type of query could be useful for identifying top performers for staffing decisions and balancing workloads.

This query joins information from insurance, patients, hospital_record, and invoices to calculate the total billed revenue and average invoice amount generated by each insurance provider’s patient base. We also see the number of distinct patients per provider and the range of plan types associated with each. Analyzing these insurance metrics could be useful in contact negotiations and establishing provider partnerships.

This query joins invoice and payment records to compare each invoice’s bill against the total payments received by the patient and is filtered where the two values are not equal. Finding these mismatches is very useful for auditing. SQL can be used not just to summarize data, but also to identify discrepancies and prompt action.

Technologies

• SQL
• MySQL
• ERD

Key Takeaways

Working through this project reinforced how much analytical power SQL offers beyond basic data storage and retrieval. It is capable of writing complex queries, whether it’s through window functions or subqueries, that unlocks insightful analysis. Translating an ERD into functioning tables and queries gave me a deep appreciation for how schema design directly shapes what kind of analysis is even possible.

This type of problem-solving is what originally motivated me to become a Learning Assistant for Penn State’s introductory database course for 3 years. Supporting students through similar projects sharpened my own understanding of relational design and query logic, and reinforced how crucial organized data is for producing meaningful analysis.