After hearing about the data mart prototypes, Dr. Jefferson, chief of surgery, expresses an interest in a surgery data mart. Some of the reports he wishes to receive include the number of surgeries, by surgeon; the number of inpatient and outpatient surgeries per week and per month; the number of canceled surgeries, by reason, surgeon, and month; surgeries per week, operating room (MVCH has a suite of six operating rooms), and surgeon; mortality rates by surgeon; average surgery time, by type of surgery;

average surgery time, by OR; and number of negative patient reactions to blood transfusions by surgeon and by patient gender and age. Dr. Jefferson also wishes to analyze surgeries in terms of the duration of anesthesia, total time in the operating room, and amount of time in the operating room before the start and after the end of the surgery. He wants to be able to slice and dice the surgery data by diagnosis, patient age, gender, insurance company, acuity code (patient acuity at MVCH is rated on a five-point scale, with 1 reflecting the highest acuity and 5 the lowest acuity), operating room, and surgeon.
Given this information, you have the following tasks:

a. Identify the dimensions and facts for this data mart.

b. Create the star schema.

c. Use SQL to create three queries that satisfy some of Dr. Jefferson’s information requirements.

d. Develop a business case for this data mart.