Building a Hospital Database and Data Warehouse in SQLServer

Project Objective

The purpose of this project was to explore the complexities of recording a patient event for a hospital database. The project does not attempt to build a whole hospital database. This would require more time and a much larger team then myself and two other classmates at Utah Valley University.

Creating the Database

Above is the Electronic Relational Database (ERD) we created for our project. After deciding what type of data we wanted to record, our first step as a team was making the ERD. It displays the relationships between the tables and shows how we kept referential integrity.

Once the ERD was created we created the tables script and ran it in SQL Server Management Studio. We then went to Mockaroo to generate random data to fill in the database and then ran the script. Below are two links. The first is the script to create the database, the second is to the script that we created to fill the database.

• Tables Script Link

• Fill Tables Script Link

Creating the Data Warehouse

Above is the Star Schema Electronic Diagram we created for our Data Warehouse. The Data Warehouse was chosen to be a star schema because our objective was to record the patient event. The main benefit of using a star schema is that there is single large fact table to store primary measured data. The dimension tables surrounding it were used to store important supporting information.

One important point to emphasize is that a database and data warehouse should not be stored in the same database. If I were to be brought onto your team, I would keep them separated. However, for the purpose of this project and the constraints of the class I kept them in the same database.

• Data Warehouse Script Link

Stored Procedures, Functions, and Triggers

The Triggers that we created for this project mostly deal with updating the Data Warehouse. That way when a change is made to the database it will reflect in the non-fact table dimensions. For filling the Facts table a procedure was used to do incremental updates (adding new rows of data).

Procedures were used for a lot of different reasons. One example from this database is we nested a while loop within the stored procedure to create a list of dates From January 1, 2020, to January 1, 2021. We included elements such as the raw date, day of the week, month of the year, quarter, and year number as part of it. Since it was in the data warehouse it also include the getdate() built in function so that the dwDateDim table had a record of when it was done.

Functions weren't used as often in this project but one example of it being used was to change admit date to the appropriate date id. This was used in the procedure to fill the facts table.

• Procedures Script Link