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.