Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Q1. Insurance Database We are asked to design a database management system for all information related to JCUInsurance, a life and vehicle insurance company
Q1. Insurance Database We are asked to design a database management system for all information related to JCUInsurance, a life and vehicle insurance company at John Carroll. The first step is to organize the information given about insurance. We have collected the following data: Every insurance company has its own policies. A Policy has policy_number, term_price, coverage and date. There are two types of policies: vehicle_policy, life_policy. For life_policy, we have its ID, value, minimum age and maximum age. For vehicle_policy, we have its ID, vehicle VIN number, type (new/old), and the driver's driving history, Every insurance policy covers one or more vehicles/life and premium payments associated with it. Each payment has its id, due date, and payment amount. Vehicle supported by JCU-Insurance are only cars or motorcycles. Every vehicle has its VIN number, plate, registered state, color, year and model. For motorcycles, we should keep track of its weight, and type (standard, cruiser, sport bike, sport touring, dual sport, scooters, etc.) For cars, we need to have car type (sedan, coupe, etc.), transmission type (automatic, manual), and size (sub- compact, compact, midsize, large). For each customer, we have his/her SSN, driving id, name, telephone and address. 6 When a vehicle accident happens, a report should be prepared including report number, date, location, and damage cost. Accidents are also associated with a customer; so, driving license should be mentioned in the report. Please answer the following questions: Q1.1 Draw an ER diagram for this database. Make sure to indicate primary keys, cardinality constraints, weak entities (if any), and participation constraints. List any assumptions you make in the process. Hint: You may need an ISA hierarchy somewhere. Q1.2 Translate the ER diagram in Q4.1 into relational database tables (i.e. give the SQL statements). Make sure that the translation captures key constraints (primary keys and foreign keys if applicable) and participation constraints in the ER diagram. Identify constraints, if any, that you are not able to capture.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
The information provided seems to be part of a larger assignment or test concerning the design of a database for an insurance company The question req...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started