Imagine that you are charged with building a knowledge-based system for a car insurance company. Car insurance companies use quite complex grids (we could say rule sets, or knowledge bases) to determine the offers for insurance contracts. Such grids take several things in account, such as customer (that is, the supposed driver) age, gender and living place, as well as car details. The insurance company probably keeps their customer data in a complex database and will know details about cars and the cities where they can place contract offers.

Design and implement a database based on the following requirements:

(1) The company keeps track of all customers. Each customer is uniquely identified by a customer ID (Cust_ID), and is further described by name, birth-date, gender, and city of residence.

(2) Cities are uniquely identified by the city name, and further described by population.

(3) Each customer may have one or more cars. Each car is uniquely identified by a vehicle identification number (VIN), and is further described by its make, model, displacement, power, and luxury (indicating whether the car is a luxury car).

(4) A customer may have zero or more contracts with the company. Each contract covers one car for one customer. A contract is uniquely identified by a contract number, and is further described by contract_date, contract_class, max_refund, base_price, and final_price.

PART I: Design a conceptual schema using ER diagram

Make sure that you capture all the requirements and use the ER notation mentioned in the Lecture 5 slides. For each entity, indicate the primary key and all the attributes. For any relationship, indicate the cardinality constraints and participation constraints.

Show ER diagram here

PART II: Using SQL statements to map the above ER diagram into a relational schema

Pay special attention to the way primary key constraints and foreign key constraints are expressed in the statements.

Show the SQL statements

PART III: Implement this database in SQLite

(3-1) Download SQLite Manager

SQLite Manager is an add-on utility of Firefox browser. To install SQLite Manager, visit https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ and download and install the plug-in. You will probably need to restart Firefox.

To use SQLite Manager, start Firefox, and go to Tools menu to find SQLite Manager.

When you are in SQLite Manager, you can view the basic interface. Mouse over the icons to see their functions.

Show a screen shot of the tables:

Hint: you can see the content of a table by clicking on the table name on the left panel and then click Structure tab

Cars:

Customers:

Contracts:

(3-2) Write SQL statements to populate the database with records

Please use the SQL INSERT INTO statements to populate your database tables.

Here is some of the information you can use to populate your database:

There are four customers. Information about each customer is as follow:

Cust_ID	Name	BDate	Gender	City
10005	Mario Rossi	1965-03-28	M	rome
10006	Antonio Verdi	1975-06-12	M	florence
10007	Giovanna Bianchi	1987-10-03	F	milan
10008	Fabio Bruni	1952-04-30	M	siena

There are seven cars. Information about these cars are:

VIN	Make	Model	Displacement	Power	Luxury
meA160	mercedes	A160 CDI	1.7	55	N
meE200	mercedes	E200 CDI	2.15	100	Y
opCR17D	opel	Corsa 1.7 CDTI	1.7	75	N
opVE18	opel	Vectra 1.8	1.8	90	N
fi500b14	fiat	Fiat 500 1.4	1.4	60	N
fdMO18	ford	Mondeo 2.0	2	107	N
fdMO25	ford	Mondeo 2.5	2.5	162	Y

We also know the cars that the customers own:

Name	Make	Model
Antonio Verdi	mercedes	A160 CDI
Giovanna Bianchi	mercedes	E200 CDI
Mario Rossi	opel	Corsa 1.7 CDTI
Fabio Bruni	opel	Vectra 1.8
Mario Rossi	fiat	Fiat 500 1.4
Giovanna Bianchi	ford	Mondeo 2.0
Fabio Bruni	ford	Mondeo 2.5

Existing contracts are:

Name	VIN	C_Date	C_Class	Max_Refund	Base_Price	Final_Price
Giovanna Bianchi	meE200	2006-07-15	1	6000000
Mario Rossi	opCR17D	2003-01-20	10	2500000
Mario Rossi	fi500b14	2008-01-01	14	2000000
Fabio Bruni	opVE18	2009-01-25	3	4000000

Show your SQL (INSERT INTO) statements here:

Show the screen shots of the tables with the contents shown:

Hint: you can see the content of a table by clicking on the table name on the left panel and then click Browse & Search tab

Cars table:

Customers table:

Contracts table: