Q1. Insurance Database We are asked to design a database management system for all information related...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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. 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.
Expert Answer:
Answer rating: 100% (QA)
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... View the full answer
Related Book For
Smith and Roberson Business Law
ISBN: 978-0538473637
15th Edition
Authors: Richard A. Mann, Barry S. Roberts
Posted Date:
Students also viewed these programming questions
-
The Houston Corporation manufactures filing cabinets in two operations: machining and finishing. It provides the following information: (Click the icon to view the department information.) Each...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
An analog filter is defined by its transfer function: 100n H(s) = %3D s+ 100n What type of filter H(s) is? A. Low-pass B. High-pass C. Band-pass D. Band-stop
-
Pretzelmania, Inc., issues 6%, 10-year bonds with a face amount of $60,000 for $60,000 on January 1, 2012. The market interest rate for bonds of similar risk and maturity is 6%. Interest is paid...
-
The slender metal rods are welded together in the configuration shown. Each 6-in. segment weighs 0.30 lb. Compute the moment of inertia of the assembly about the y-axis. 6" 6" 6" 6" 6"
-
Loris Catering, Inc., purchased $400 of supplies on account. The journal entry to record this transaction is denoted by which of the following? DATE ACCOUNTS POST REF. DR. CR. a. Inventory 400...
-
John Sleaze and Mary Scum run Earnings Management, Inc., a consulting business. They have the following items in their product line: 1. A database that lists types of depreciable assets and the...
-
AbankexpectsthattheMexicanpesowilldepreciateagainstUSdollar fromitisspotrateof$0.43to$0.42in60 days.The followinginterbanklendingand borrowingrates: Currency Lending Rate Borrowing Rate U.S Dollar...
-
The Ploughman family owns and operates a 640-acre farm that has been in the family for several generations. The Ploughmans always have had to work hard to make a decent living from the farm and have...
-
Suppose you purchase a zero coupon bond with face value $1,000, maturing in 25 years, for $180. What is the implicit interest, in dollars, in the first year of the bond's life? A) $2.86 B) $ 9.84 C)...
-
The affluent consumer segment can be defined in many ways, but most consider those with over $100,000 in household income and $250,000 or more in assets to be mass affluent. Affluent individuals tend...
-
A cessna 172 aircraft is cruising at 110 knots TAS at 5,500 feet. What is the equivalent airspeed? Solve the problem in the British system of units.
-
Do you think that it would be more difficult to lead a non-profit group of employees versus a traditional organization? Why and why not? What type of leadership styles, skills, and qualities are...
-
Write a function that takes an array Arr of size N and returns another array which, first disregards the numbers greater than a certain threshold (also a passed parameter), and then stores the...
-
Bardot has taxable income of $58,000 in 2023. Bardot is filing as a head of household taxpayer. What is the amount of Bardot's Federal income tax liability, using the Tax Rate Schedule?
-
2. A wave on a string has the formula y = 0.030sin(0.55x - 62.8t + /3). What is the wavelength, frequency, period, phase constant, and speed of the wave. The string has a linear density = 0.020...
-
An 8.0 kg crate is pulled 5.0 m up a 30 incline by a rope angled 18 above the incline. The tension in the rope is 120 N, and the crates coefficient of kinetic friction on the incline is 0.25. a. How...
-
On February 2, Phillips induced Miller to purchase from her fifty shares of stock in the XYZ Corporation for $10,000, representing that the actual book value of each share was $200. A certificate for...
-
This is a stocklist case arising under 220(b) of our [Delaware] General Corporation Law. The issue is whether a shareholder states a proper purpose for inspection under our statute in seeking to...
-
MPL Leasing Corporation is a California corporation that provides financing plans to dealers of Saxon Business Products. MPL invited Jay Johnson, a Saxon dealer in Alabama, to attend a sales seminar...
-
Which of the following is not advice for closing projects or phases? A. You dont need to celebrate completing a project, especially if it did not go well. B. You should capture lessons learned as...
-
What are the six different versions of project management software offered by Microsoft as of April 2016? Briefly describe how they differ and who uses them.
-
A __________ is a procedure that has been shown by research and experience to produce optimal results and that is established or proposed as a standard suitable for widespread adoption. A. benchmark...
Study smarter with the SolutionInn App