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.