Question 1: New England University maintains a data warehouse that stores information about students, courses, and instructors. Members of the university’s Board of Trustees are very much interested in students’ academic performance as well as instructors’ teaching effectiveness. For example, they may like to know:
• The average grades for different courses taught by different instructors.
• The grade distribution for a particular course in a particular semester (e.g., a newly offered MBA course).
• The average ratings of teaching effectiveness for instructors at different ranks (e.g., Lecturer, Assistant Professor, Full Professor, etc.).
• The correlation between students’ grades and their ratings for instructors’ teaching effectiveness.
Note that each undergraduate student must declare his/her major and up to two minors by the end of the junior year. A graduate student may have a major (e.g., MBA) and a minor or concentration (e.g., IT). Some courses (e.g., undergraduate GB courses) may be “co-offered” by several departments. An instructor may be affiliated with more than one department.
A) Draw the two star schemas and specify all relevant attributes.
B) Identify conformed dimensions.
C) Identify any slowly changing dimensions and the attributes whose values may change from time to time.
D) Draw a lattice hierarchy for each dimension that has more than one level.
Question 2: An automobile manufacturer needs to build a data warehouse to store and analyze data about repairs of vehicles. Among other information, the date of repair, properties of the vehicle (e.g. model), information about the specific repair case (e.g. costs, number of garage employees involved, duration of the repair), data about the garage doing the repair, and data about the customer who owns the vehicle are stored.
Typical examples of business intelligence questions are:
• What are the average total repair costs per month for garages in Boston by type of garage during the year 2011?
• Identify five vehicle types that had the lowest average part costs.
• Which factors (e.g., vehicle’s age, mileage, model; customer’s gender, age) contribute most to the repair costs?
The operational database keeps track of the repair costs (broken down by part costs, wages and total)for a specific vehicle (owned by a customer) for a specific garage on a dailybasis.
A) Draw a star schema for this data warehouse and specify all relevant attributes.
B) Identify any candidates for conformed dimensions that may be shared with other schemas (e.g., sales).
C) Identify any slowly changing dimensions and the attributes whose values may change from time to time.
D) Draw a lattice hierarchy for each dimension that has more than one level.