COMP 266 Lecture Notes - Lecture 10: Fact Table, Star Schema
1 Jul 2018
School
Department
Course
Professor
Question: Consider the following dimensions,
dimension attri...
Consider the following dimensions, dimension attributes, and dimension sizes for Farm Coop-
Insurance.
- Member(member_ID, Name, Address).
On average, there are two members for each policy and item covered by the policy.
- InsuredItem(Item_ID, Description, Coverage_Type).
There is an average of ten covered items per policy.
- CoopOffice(Office_ID, Address, Manager_name).
Each policy is registered and managed by only one office of the cooperative.
- Policy(Policy_ID, Type).
The company has approximately one million policies at the present time. Approximately five
percent of these policies experience some change each month.
- Period(Date_Key, Fiscal_Period).
The length of the fiscal period is one month. The decision system reports are supposed to be
based on five years of data.
- Claim(Claim_ID, Claim_Description, Claim_Type).
The facts to be recorded for each combination of these dimensions are: Policy_Premium,
Deductible, and Monthly_Claim_Total.
a. Design a star schema for this problem.
b. Using the assumptions stated above, estimate the number of rows in the fact table.
c. Estimate the total size of the fact table (in bytes), assuming an average of 5 bytes per field.
PLEASE ANSWER THIS QUESTION; I'M PREPARING FOR MY FINAL EXAM! :(
Expert Answer
find more resources at oneclass.com
find more resources at oneclass.com
