Hospital Data Warehouse Design and Implementation: Assignment 2

This assignment details the design and implementation of a data warehouse for a hospital system, building upon a conceptual design from a previous assignment. The improvements made in the conceptual design are highlighted, along with the rationale for analytical subjects, specifically focusing on prescription analysis. A star schema is designed to support this analysis, with justifications and sample data provided. Furthermore, the assignment explores data aggregation techniques to reduce query complexity. The report includes the Oracle DDL statements for creating the database tables and outlines a strategy for loading data into the warehouse, including the use of daily data updates and the implementation of constraints. The appendix provides the Oracle SQL code and sample data populated into the tables. The assignment focuses on creating a data warehouse to analyze the prescriptions issued by the doctors in the hospital and provide insights on the most treated diseases, workload of each hospital, and the drugs that are utilized most. This information will help the hospital establish effective prescriptions for certain diagnosis and also in planning for the amount of stock for drug types.