Duluth Mutual Policy Management SystemBackground: Duluth Mutual Life is an insurance company that sells personal automobile insurance. Duluth Mutual Life manages its personal automobile insurance data on a mainframe computer. Problem Statement: Duluth Mutual Life's policy management system has outgrown its mainframe computer. The system working about three times per week, causing downtime for the users. Duluth Mutual Life wants to replace the mainframe computer with a new system that uses relational database technology. The budget for this project is set and cannot be increased. The design of the system should solve the business requirements stated here, and should not include any extra data elements or features. Business Analyst: The policy is our main piece of data. Each policy can cover multiple vehicles. For example, a policyholder might own two vehicles. Both of these vehicles would be covered by one policy. A policy can also cover multiple drivers. For example, a husband and wife might own one car. Both the husband and wife would be covered by one policy. To take this even further, a family of three might share two cars. The family of three and the two cars would be covered by one policy. The system will need to validate that only one active policy exists for each driver and vehicle. In other words, a vehicle cannot be insured under more then one active policy, and a driver cannot be insured under more than one active policy. We also need to track the effective date and the termination date for each policy, as well as the current status of the policy. When the user has entered all of the data, the user should have to direct the system to calculate the premium. Once all of the data is entered and the premium is calculated, the user should direct the system to generate a confirmation letter to the policyholder. The confirmation letter should include all of the information that we have in file. Business Manger Number One: Duluth Mutual Life charges the customer a premium for the insurance policy. The policy premium is calculated based on the number of drivers per vehicle, and on specific characteristics for each driver. The driver characteristics that are used to calculate the premium include the number of driving violations, the number of years licensed, and the age of the driver. We will need to keep track of the specific violations that a driver has committed. The insurance agent enters the violation data for each driver when the policy is set up. A specific violation counts against a driver until three years after the violation date. The motor vehicle registration office sends Duluth Mutual Life a master file of all the possible violation codes, along with a description of each violation. The number of insured vehicles affects the policy premium. The vehicle characteristics that affect the policy premium include the vehicle's value, year, make, and model. The vehicle manufacturer assign a unique identification number to each vehicle. The identification number is common called the VIN or Vehicle Identification Number. The VIN is different from the vehicle's license number. Business Manager Number Two: Drivers can be excluded from driving a specific vehicle. Suppose a wife, husband, and 16-year-old son own a station wagon, a truck, and an expensive sports car, but the son has two speeding violations. The policyholder might want to exclude the son from driving the sports car. The policyholder would be decreasing the number of drivers on one car, and excluding a high-risk driver from driving an expensive car. All drivers must be explicitly associated with cars that they can drive and cars that they are excluded from driving. |