Data Science Analytics

Suppose that a data cube consists of the three dimensions time, doctor, and patient, and one measure called charge, where charge is the fee that a doctor charges a patient for a visit. The attributes of the dimensions are: time: day, month, quarter, year doctor: doctor_ID, name, phone#, address, gender, expertise (doctor_ID is unique for each doctor) patient: patient_ID, name, phone#, address, gender, birthdate, description, insurance_info (patient_ID is unique for each patient)

(1) Draw a star schema diagram for the data cube. You must identify the primary key or create a primary key for the dimension table. It has been decided that the expertise attribute of a doctor needs to be normalized into a separate table, consisting of attributes expertise_ID, description, specialty, sub_specialty, and hospital, where expertise_ID is the primary key. Also, the insurance_info attribute of a patient needs to be normalized into policy#, issuer, address, and phone#, where policy# is the primary key.

(2) Draw a snowflake schema diagram for the data cube.

(3) Starting with the base cuboid [day, doctor, patient], what specific OLAP operations should be performed in order to list the total fee collected by each doctor in 2022