Question

In: Operations Management

You are to construct a star schema for Simplified Automobile Insurance Company. The relevant dimensions, dimension attributes, and dimension sizes are as follows:

Modern Database Management, 13th Edition

Jeff Hoffer

9-39. You are to construct a star schema for Simplified Automobile Insurance Company. The relevant dimensions, dimension attributes, and dimension sizes are as follows:

InsuredParty

Attributes: InsuredPartyID and Name. There is an average of two insured parties for each policy and covered item.

CoverageItem

Attributes: CoverageKey and Description. There is an average of 10 covered items per policy.

Agent

Attributes: AgentID and AgentName. There is one agent for each policy and covered item.

Policy

Attributes: PolicyID and Type. The company has approximately 1 million policies at the present time.

Period

Attributes: DateKey and FiscalPeriod.

Facts to be recorded for each combination of these dimensions are PolicyPremium, Deductible, and NumberOfTransactions.

  1. Design a star schema for this problem. Follow the format shown in the lecture notes in diagrammatically representing the star schema. Use LucidChart to create the star schema.

  2. Estimate the number of rows in the fact table, using the assumptions stated previously.

  3. Estimate the total size of the fact table (in bytes), assuming an average of 10 bytes per field.

Solutions

Expert Solution

A star schema for Simplified Automobile Insurance Company


Related Solutions

Which of the following best describes a dimension table in a star schema: Most attributes will...
Which of the following best describes a dimension table in a star schema: Most attributes will likely be text Most attributes will likely be numeric The primary key should be a single integer surrogate key Both a. and c. Which of the following best describes a fact table in a star schema: Most attributes will likely be text Most attributes will likely be numeric The primary key should be a single integer surrogate key Both a. and c. Which of...
Using the Internet, obtain the income statement for the insurance company where you insure your automobile...
Using the Internet, obtain the income statement for the insurance company where you insure your automobile (casualty insurance company) and perform a vertical analysis of this company. My company of insurance is Progressive.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT