DATABASE SYSTEMS
You are to construct a star schema for Simplified Automobile Insurance Company. The relevant dimensions, dimension attributes, and dimension sizes are as follow:
- Insured Party. Attributes: Insured_Party_ID, Name. There is an average of two insured parties for each policy and covered item.
- Coverage Item. Attributes: Coverage_Key, Description. There is an average of ten covered items per policy.
- Agent. Attributes: Agent_ID, Agent_Name. There is one agent for each policy and covered item.
- Policy. Attributes: Policy_ID, Type. The company has approximately one million policies at the present time.
- Period. Attributes: Date_Key, Fiscal_Period.
Facts to be recorded for each combination of these dimensions are Policy_Premium, Deductible, and Number_of_Transactions.
1. Design a star schema for this problem with database modeling tool (Xcase).
2. Estimate the number of rows in the fact table, using the assumptions stated above.
3. Estimate the total size of the fact table (in bytes), assuming an average of 5 bytes per field.