Complete problem of the Mannino text. . You can use a design tool. Discuss why you chose one design over the other? Provide responses to the questions in the problems. Question 3. apply the flatten transformation for a policy item fact table. The auto insurance database uses a two-level representation of policy transactions. The data warehouse schema should contain a single fact table 1-M relationships from dimension tables to the fact table. Ignore the Insured Party entity for this problem. Question 8. Design a star or snowflake schema to extend the schema from Problem 3. Your schema should use resolutions to summariz ability problems that you identified in earlier problems.   Question 9.  For each dimension table in the schema for Problem 8, list the independent and hierarchical dimensions. Analyze each hierarchical dimension for summary problems. Propose alternative representations to resolve any summariz ability problems.

 

Transcribed Image Text:

FIGURE 13.P1 ERD for Auto Insurance Poli- cies and Claims Item Itemid ItemName ItemMinCoverage ItemMaxCoverage ItemDesc InsuredAuto IAVIN IALIcPlateNo IAState IAMake IAModel IAYear FIF I Usesitem 1 1 - I 1 1 1 InsuresAuto Contains Policyltem LineNo PILimits PIPremium PIComments Autolnvolved Insured Party IPND IPDrivLicNo IPCity IPState IPZip IPPhone IPDOB IPRiskCategory IPSex Insures Party Policy PolNo PolBegDate HH PolEndDate PollssueDate PolPremium PolGroupRating Made Against Claim ClaimNo HIF Agent AgentNo AgentName Writes-OH AgentPhone ClaimSubDate POMadeByl ClaimDecDate ClaimAmount ClaimEstimate Agent Region Agent Dept Claimant ClmtNo Clmt SSN ClmtName Clmt City Clmt State ClmtZip Driverinvolved FIGURE 13.P1 ERD for Auto Insurance Poli- cies and Claims Item Itemid ItemName ItemMinCoverage ItemMaxCoverage ItemDesc InsuredAuto IAVIN IALIcPlateNo IAState IAMake IAModel IAYear FIF I Usesitem 1 1 - I 1 1 1 InsuresAuto Contains Policyltem LineNo PILimits PIPremium PIComments Autolnvolved Insured Party IPND IPDrivLicNo IPCity IPState IPZip IPPhone IPDOB IPRiskCategory IPSex Insures Party Policy PolNo PolBegDate HH PolEndDate PollssueDate PolPremium PolGroupRating Made Against Claim ClaimNo HIF Agent AgentNo AgentName Writes-OH AgentPhone ClaimSubDate POMadeByl ClaimDecDate ClaimAmount ClaimEstimate Agent Region Agent Dept Claimant ClmtNo Clmt SSN ClmtName Clmt City Clmt State ClmtZip Driverinvolved