ALY6030 Final Project Assignment – Pharmacy Claims

Due End of Week 6

Background:

The excel file “ALY 6030 Final Project Data Set.xlsx” contains sample pharmacy claims for made-up members of an insurance company that pays for pharmacy via a third party known as a Pharmacy Benefit Manager or PBM. The file also contains a data dictionary referring to the variables in the data along with their format for your reference.

The PBM has given you these sample records as a starting point so that you as the Developer can set up a test database and pre-program some common SQL query reports that will be expected from the reporting analysts and business users working at your company, once the full claims detail is made available in production in a few months from the PBM. Your work on the test warehouse will help ensure a smooth and successful rollout once the data warehouse goes live in production.

Part 1) Normalization

Your task is to eventually load this sample data into your test database and create a star schema.

However before you do, you notice that the raw data does not meet 3NF. You make a note to let the PBM know they need to change their formats for a relational database. You also see some of your sample members appear more than once because they filled a different drug which further complicates the format.

For now you decide to fix this yourself directly in excel.

Tasks

Part 2) Primary and Foreign Key Setup in MySQL

After you’ve converted the raw table into your 3NF fact and dimension table csv files, you are now ready to upload the data and create the star schema in MySQL as well as designate the primary and foreign keys. All of your output to the tasks below should be included in your .sql code file, except for the three questions below which you should answer directly in this word document.

Tasks

 

 

A reminder that MySQL cannot designate a character variable as a PK unless you first format it as VARCHAR (use VARCHAR(100))

 

 

 

  1. What are the primary keys you designated for each of your tables? For each PK, is it a natural key or a surrogate key?
  2. What are the foreign keys you designated for each of your tables? For each FK, which table did you reference where that FK is listed as the PK?
  3. For each FK, what did you tell MySQL to in case of deletion or update (CASCADE, SET NULL, or RESTRICT)? Why did you select the option that you did for each FK?

Here I’m not necessarily looking for a right or wrong answer to this question, i.e. if you selected SET NULL but you should have selected CASCADE…that’s not the goal; rather I want to make sure you understand the differences between these options enough to justify your selection. Again it matters less which one you choose vs. that you explain your choice to me in a way that demonstrates comprehension.

 

 

Part 3) Entity Relationship Diagram

Now that you’ve created your database, imported your data, and designated your primary and foreign keys, you’ll need to create the official ERD to be able to communicate this table structure to all business users as well as send back to the PBM so that they have for reference.

Tasks

If your ERD is not fitting the page well on your PDF, along the top menu bar, first click on Model then Diagram Properties and Size. In the box that opens up make sure that it is set to one page like this:

 

 

Part 4) Analytics and Reporting

With your database set up and ERD drawn, you want to also provide the business users with some sample queries they will likely need to analyze the production data once it is sent from the PBM.

For now you develop the queries on the sample data even though you don’t really need them since it’s not very large, however in the future this will be expanded to several thousand per month so it’s good to plan ahead for the go-live date.

Tasks

 

 

 

 

 

 

 

 

Submit one MS Word Document and one SQL program file into the Final project drop box in week 5.

Make sure your name is in both of your filenames

Also, submit all CSV files you generated in excel as part of your submission

Also, submit your ERD as a PDF export from MySQL Workbench

 

Example:

Yoni_Dvorkis_Final_Project.doc

Yoni_Dvorkis_Final_Project.sql

Yoni_Dvorkis_Final_Project_ERD.pdf

All CSV Files you imported into MySQL Workbench submit those to the drop box as well.