Problem $3-20$ points

Topic: Data Warehouse DEsign

We would like to design a data warehouse to be used by the Enrollment Management Office at ABC University.

ABC University has multiple campuses across the nation. Each campus is identified by a campus$-$id$,$ city, and state. For each campus, we would like to keep track of the number of applications received per enrollment$-$date. An enrollment$-$date is defined by an enrollment$-$date$-$id$,$ semester $($Fall or Winter$)$ and Year. We would like to keep track of the selectivity $($percentage of applications accepted$)$ and yield $($percentage of newly accepted applicants that enrolled$)$ per enrollment$-$date. We would also like to keep track of the total number of students $($new and returning$)$ enrolled in each campus per enrollment$-$date. We would like to keep track of number of applications, selectivity, yield and number of enrolled students in the campus overall and per major. A major is defined by a major$-$id$,$ Major name, major description, and number of credits required for the major. The same major may be offered across various campuses.

The office of Enrollment Management also organizes hundreds of recruitment events across its campuses throughout the year to showcase campuses and programs to potential students and their families. For each event, we would like to keep track of the campus$-$id where it was held, event$-$date $($defined event$-$date$-$id$,$ week, month, year$),$ event, number of RSVPs$,$ number of attendees. Each event is defined by an event$-$id$,$ event$-$type $-$type $($e$.$g$.,$ campus tour, open house, project day, etc.$),$ event$-$audience $($e$.$g$.,$ transfers, high school students, counselors, middle school students, etc.$)$ and event$-$description. The same event may be organized at various campuses.

Give the data warehouse design using the fact constellation model.