ALY 6030 Data Warehousing and SQL Module 2 Assignment


Page 1 of 4

Class ALY6030 – Data Warehousing and SQL
Module 3 Assignment: Dimensional Modeling



Overview and Rationale
You are a consultant hired by a network of health care organizations to construct a list of top 10
Hospitals/Medical Centers that have SICUs (Surgical Intensive Cate Units) and ICUs (Intensive
Care Units) for the following categories of "beds" (one bed can accommodate one patient):
1. Total number of licensed beds (total beds allowed by state license)
2. Total number of census beds (total beds at the hospital)
3. Total number of staffed beds (total beds for which staffing, e.g., physicians and nurses,
exists)


ALY 6030 Data Warehousing and SQL Module 2 Assignment


Page 2 of 4

Data:
The following data is supplied to you to address the request:
 Dimension table business.csv
 Dimension table bed_type.csv
 Fact table bed_fact.csv bed_fact.csv
Task:
Your task is to complete the following steps:
1. Identify Dimensions (first three only) from the two dimension tables.
2. Identify the Facts (first three only) from the single fact table.
3. Sketch out a Star Schema (give it a good name) that includes both the data tables of
interest and the appropriate joins between the tables that can be used to answer the
questions. Use free on-line tool ERDPlus by using the following
URL: https://erdplus.com/#/standalone. Remember to take a screen shot of ERDs and
include them into your paperwork,
4. Implement the schema as a database (give it a good name) in MySQL using
MySQL Workbench.
5. Write and run SQL code for the database that that will provide answers posed in
a Story section.
6. Include interpretation and explanation of all the results for your discussions with the client
who hired you.
7. Your constructed table(s) of the results should have a layout similar to the table below
and must indicate the counts of each bed type for each for the Top 10 hospitals
per Story section (this is just a subset of the entire set of results):

NA means that hospital did not have that type of a bed,

ALY 6030 Data Warehousing and SQL Module 2 Assignment


Page 3 of 4

It is straight-forward to find the total of each different type of bed type for each
hospital and pick out the top ten hospitals for the different bed types. Note that the
top 10 hospitals for each bed type will probably not be the same set of hospitals.

Submission requirements:
 Submit paperwork using .docx file in Microsoft Word format named as
ALY6030_80528_Assignment_3_[your name] .docx
 Submit SQL script .sql file in text format names as ALY_6030_80528_Assignment_3_[your
name].sql
Paperwork file must meet the following requirements:
 Title Page must have:
o Class name
o Class number
o Class CRN number
o Assignment name
o Your full name as registered in Canvas
 Introduction section
o explain what this assignment is about
o set goals and expectations
o explain the selected dataset
o introduce tools and languages you plan to use
 Final conclusions section
o Explain if goals and expectations were met or not
o Explain cons and pros of using the tools, methods and techniques.
o Explain what would you do differently
o References (optional)

ALY 6030 Data Warehousing and SQL Module 2 Assignment


Page 4 of 4

SQL Script file must meet the following requirements:
 Only ,sql text format will be accepted
 Each line of the SQL code must have a meaningful comment
 Code should be executed on other computers without any modifications (add extra steps
in the comments if necessary)































































































学霸联盟