Creating a database

This is an example of creating a test database based on fictitious data. The database is based on an automobile insurance company. It assumes that an insurance table has the following main tables:

Members Claims Policy

This is designed using MySQL database.

/*Drop database if it exists*/

drop database insurance;

/*Create and use insurance database*/

create database insurance;
use insurance;

/*Create and populate members table*/

create table members (
member_id int not null primary key auto_increment,
first_name varchar(25),
last_name varchar(25),
street varchar(255),
city varchar(50),
state varchar(2),
zip varchar(5),
phone_number varchar(10)
);

insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mitch','McConnel','1237 Main Ave','Lousville','KY','23784','7478339090');
insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Nancy','Pelosi','8944 Second Ave','San Fransisco','CA','90045','3235845978');
insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Marco','Rubio','944400 Mad People Street','Miami','FL','78484','5455325998');
insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Amy','Klobuchar','1237 Main Ave','Lousville','KY','23784','7478339090');
insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Lindsay','Graham','8944 Second Ave','Filthy','SC','90045','3235845978');
insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Elizabeth','Warren','944400 Mad People Street','Foxborough','MA','78484','5455325998');
insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Tim','Scott','1237 Main Ave','Lousville','SC','23784','7478339090');
insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Lisa','Murkowski','8944 Second Ave','Augusta','ME','90045','3235845978');
insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mitt','Romney','944400 Mad People Street','Sal Lake City','UT','78484','5455325998');
insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Chuck','Schumer','1237 Main Ave','New York','NY','23784','7478339090');
insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Bernard','Sanders','8944 Second Ave','Montpelier','VT','90045','3235845978');
insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mark','Kelley','944400 Mad People Street','Phoenix','AZ','78484','5455325998');

/*Create and populate offering table*/

create table offering (
offering_id int not null primary key auto_increment,
deductible float,
premium float);

insert into offering (deductible, premium) values (500.00, 1200.00);
insert into offering (deductible, premium) values (1000.00, 800.00);
insert into offering (deductible, premium) values (100.00, 1500.00);

/*Create and populate policy table*/

create table policy (
policy_id int not null primary key auto_increment,
policy_number varchar(20),
member_id int not null,
offering_id int not null,
start_date date,
end_date date,
constraint po_me_fk foreign key (member_id) references members (member_id));

insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y78',3, 2, '2020-01-01', '2020-12-31');
insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y79',1, 1, '2020-01-01', '2020-12-31');
insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y80',2, 1, '2020-01-01', '2020-12-31');
insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y81',12, 2, '2020-01-01', '2020-12-31');
insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y82',11, 1, '2020-01-01', '2020-12-31');
insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y83',9, 1, '2020-01-01', '2020-12-31');
insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y84',10, 2, '2020-01-01', '2020-12-31');
insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y85',7, 1, '2020-01-01', '2020-12-31');
insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y86',6, 1, '2020-01-01', '2020-12-31');
insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y87',4, 2, '2020-01-01', '2020-12-31');
insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y88',5, 1, '2020-01-01', '2020-12-31');
insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y89',8, 1, '2020-01-01', '2020-12-31');

/*Create and populate car make table*/

create table car_make (
car_make_id int not null primary key auto_increment,
car_make varchar(25));

insert into car_make (car_make) values ('Toyota');
insert into car_make (car_make) values ('Nissan');
insert into car_make (car_make) values ('Ford');
insert into car_make (car_make) values ('Mercedes');
insert into car_make (car_make) values ('Land Rover');

/*Create and populate car model table*/

create table make_model (
make_model_id int not null primary key auto_increment,
car_model varchar(25),
car_make_id int,
constraint cm_mm_fk foreign key (car_make_id) references car_make (car_make_id));

insert into make_model (car_make_id, car_model) values (1, 'Tacoma');
insert into make_model (car_make_id, car_model) values (1, 'Venza');
insert into make_model (car_make_id, car_model) values (1, 'Corolla');
insert into make_model (car_make_id, car_model) values (1, 'Sienna');
insert into make_model (car_make_id, car_model) values (1, 'Camry');
insert into make_model (car_make_id, car_model) values (1, 'Highlander');
insert into make_model (car_make_id, car_model) values (2, 'Sentra');
insert into make_model (car_make_id, car_model) values (2, 'Altima');
insert into make_model (car_make_id, car_model) values (2, 'Pathfinder');
insert into make_model (car_make_id, car_model) values (3, 'Focus');
insert into make_model (car_make_id, car_model) values (3, 'F-150');
insert into make_model (car_make_id, car_model) values (3, 'Escape');
insert into make_model (car_make_id, car_model) values (4, 'C-Class');
insert into make_model (car_make_id, car_model) values (4, 'E-Class');
insert into make_model (car_make_id, car_model) values (4, 'S-Class');
insert into make_model (car_make_id, car_model) values (5, 'Defender');
insert into make_model (car_make_id, car_model) values (5, 'Range Rover');
insert into make_model (car_make_id, car_model) values (5, 'Discovery');

/*Create and populate automobile table*/

create table automobile (
automobile_id int not null primary key auto_increment,
vin varchar(255),
make_model_id int,
year_of_manufacture varchar(4),
policy_id int,
constraint au_po_fk foreign key (policy_id) references policy (policy_id),
constraint au_mm_fk foreign key (make_model_id) references make_model (make_model_id));

insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('23456789W',1,'2020', 2);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('123456789X',5,'2020', 3);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('123456789Y', 7,'2018',1);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('123456789Z',1,'2010', 10);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('523456789W',3,'2012',3);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('623456789W',7, '2019',4);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('723456789W',13,'2020',6);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('121456789W',1,'2020',8);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('122456789X',2,'2020',10);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('124456789Y', 3,'2018',9);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('124456789Z',1,'2010',4);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('525456789W',3,'2012',5);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('66456789W',5,'2019',11);
insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('773456789W',16,'2020',12);

/*Create and populate claim event table*/

create table claim_event (
claim_event_id int not null primary key auto_increment,
event_type varchar(255));
insert into claim_event (event_type) values ('Hit');
insert into claim_event (event_type) values ('Roll');
insert into claim_event (event_type) values ('Drown');
insert into claim_event (event_type) values ('Fire');
insert into claim_event (event_type) values ('Theft');
insert into claim_event (event_type) values ('Puncture');
insert into claim_event (event_type) values ('Vandalism');

/*Create and populate claims table*/

create table claims (
claim_id int not null primary key auto_increment,
claim_number varchar(25),
automobile_id int,
claim_event_id int,
claim_date date,
constraint cl_au_fk foreign key (automobile_id) references automobile (automobile_id),
constraint cl_ev_fk foreign key (claim_event_id) references claim_event (claim_event_id));

insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457822',11,2,'2020-04-01');
insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457722',10,1,'2020-04-05');
insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457823',4,2,'2020-04-01');
insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457824',2,3,'2020-09-09');
insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457825',5,2,'2020-09-01');
insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457826',9,4,'2020-10-05');
insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457722',8,1,'2020-04-05');

/*Test query*/

select claims.claim_number, claim_event.event_type,
policy.policy_number,
policy.start_date,
policy.end_date,
claims.claim_date,
automobile.vin,
car_make.car_make,
make_model.car_model,
members.first_name,
members.last_name,
automobile.automobile_id from claims
join claim_event on claims.claim_event_id = claim_event.claim_event_id
join automobile on claims.automobile_id = automobile.automobile_id
join make_model on automobile.make_model_id = make_model.make_model_id
join car_make on make_model.car_make_id = car_make.car_make_id
join policy on automobile.policy_id = policy.policy_id
join members on policy.member_id = members.member_id;

drop database insurance;

create database insurance;
use insurance;

create table members ( member_id int not null primary key auto_increment, first_name varchar(25), last_name varchar(25), street varchar(255), city varchar(50), state varchar(2), zip varchar(5), phone_number varchar(14) );

insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Lamar','Alexander','455 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-4944'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Tammy','Baldwin','709 Hart Senate Office Building ','Washington','DC','20510','(202) 224-5653'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('John','Barrasso','307 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-6441'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Michael','Bennet','261 Russell Senate Office Building ','Washington','DC','20510','(202) 224-5852'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Marsha','Blackburn','357 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-3344'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Richard','Blumenthal','706 Hart Senate Office Building ','Washington','DC','20510','(202) 224-2823'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Roy','Blunt','260 Russell Senate Office Building ','Washington','DC','20510','(202) 224-5721'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Cory','Booker','717 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3224'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('John','Boozman','141 Hart Senate Office Building ','Washington','DC','20510','(202) 224-4843'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mike','Braun','374 Russell Senate Office Building ','Washington','DC','20510','(202) 224-4814'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Sherrod','Brown','503 Hart Senate Office Building ','Washington','DC','20510','(202) 224-2315'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Richard','Burr','217 Russell Senate Office Building ','Washington','DC','20510','(202) 224-3154'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Maria','Cantwell','511 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3441'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Shelley Moore','Capito','172 Russell Senate Office Building ','Washington','DC','20510','(202) 224-6472'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Benjamin','Cardin','509 Hart Senate Office Building ','Washington','DC','20510','(202) 224-4524'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Thomas','Carper','513 Hart Senate Office Building ','Washington','DC','20510','(202) 224-2441'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Robert','Casey','393 Russell Senate Office Building ','Washington','DC','20510','(202) 224-6324'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Bill','Cassidy','520 Hart Senate Office Building ','Washington','DC','20510','(202) 224-5824'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Susan','Collins','413 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-2523'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Christopher','Coons','218 Russell Senate Office Building ','Washington','DC','20510','(202) 224-5042'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('John','Cornyn','517 Hart Senate Office Building ','Washington','DC','20510','(202) 224-2934'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Catherine','Cortez Masto','516 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3542'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Tom','Cotton','326 Russell Senate Office Building ','Washington','DC','20510','(202) 224-2353'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Kevin','Cramer','400 Russell Senate Office Building ','Washington','DC','20510','(202) 224-2043'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mike','Crapo','239 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-6142'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Ted','Cruz','127A Russell Senate Office Building ','Washington','DC','20510','(202) 224-5922'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Steve','Daines','320 Hart Senate Office Building ','Washington','DC','20510','(202) 224-2651'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Tammy','Duckworth','524 Hart Senate Office Building ','Washington','DC','20510','(202) 224-2854'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Richard','Durbin','711 Hart Senate Office Building ','Washington','DC','20510','(202) 224-2152'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Michael','Enzi','379A Russell Senate Office Building ','Washington','DC','20510','(202) 224-3424'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Joni','Ernst','730 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3254'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Dianne','Feinstein','331 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3841'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Deb','Fischer','454 Russell Senate Office Building ','Washington','DC','20510','(202) 224-6551'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Cory','Gardner','354 Russell Senate Office Building ','Washington','DC','20510','(202) 224-5941'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Kirsten','Gillibrand','478 Russell Senate Office Building ','Washington','DC','20510','(202) 224-4451'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Lindsey','Graham','290 Russell Senate Office Building ','Washington','DC','20510','(202) 224-5972'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Chuck','Grassley','135 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3744'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Kamala','Harris','112 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3553'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Margaret Wood','Hassan','324 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3324'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Josh','Hawley','212 Russell Senate Office Building ','Washington','DC','20510','(202) 224-6154'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Martin','Heinrich','303 Hart Senate Office Building ','Washington','DC','20510','(202) 224-5521'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mazie','Hirono','713 Hart Senate Office Building ','Washington','DC','20510','(202) 224-6361'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('John','Hoeven','338 Russell Senate Office Building ','Washington','DC','20510','(202) 224-2551'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Cindy','Hyde-Smith','702 Hart Senate Office Building ','Washington','DC','20510','(202) 224-5054'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('James','Inhofe','205 Russell Senate Office Building ','Washington','DC','20510','(202) 224-4721'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Ron','Johnson','328 Hart Senate Office Building ','Washington','DC','20510','(202) 224-5323'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Doug','Jones','330 Hart Senate Office Building ','Washington','DC','20510','(202) 224-4124'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Tim','Kaine','231 Russell Senate Office Building ','Washington','DC','20510','(202) 224-4024'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mark','Kelly','B40B Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-2235'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('John','Kennedy','416 Russell Senate Office Building ','Washington','DC','20510','(202) 224-4623'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Angus','King','133 Hart Senate Office Building ','Washington','DC','20510','(202) 224-5344'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Amy','Klobuchar','425 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-3244'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('James','Lankford','316 Hart Senate Office Building ','Washington','DC','20510','(202) 224-5754'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Patrick','Leahy','437 Russell Senate Office Building ','Washington','DC','20510','(202) 224-4242'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mike','Lee','361A Russell Senate Office Building ','Washington','DC','20510','(202) 224-5444'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Kelly','Loeffler','131 Russell Senate Office Building ','Washington','DC','20510','(202) 224-3643'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Joe','Manchin','306 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3954'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Edward','Markey','255 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-2742'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mitch','McConnell','317 Russell Senate Office Building ','Washington','DC','20510','(202) 224-2541'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Robert','Menendez','528 Hart Senate Office Building ','Washington','DC','20510','(202) 224-4744'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Jeff','Merkley','313 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3753'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Jerry','Moran','521 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-6521'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Lisa','Murkowski','522 Hart Senate Office Building ','Washington','DC','20510','(202) 224-6665'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Christopher','Murphy','136 Hart Senate Office Building ','Washington','DC','20510','(202) 224-4041'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Patty','Murray','154 Russell Senate Office Building ','Washington','DC','20510','(202) 224-2621'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Rand','Paul','167 Russell Senate Office Building ','Washington','DC','20510','(202) 224-4343'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('David','Perdue','455 Russell Senate Office Building ','Washington','DC','20510','(202) 224-3521'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Gary','Peters','724 Hart Senate Office Building ','Washington','DC','20510','(202) 224-6221'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Rob ','Portman','448 Russell Senate Office Building ','Washington','DC','20510','(202) 224-3353'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Jack','Reed','728 Hart Senate Office Building ','Washington','DC','20510','(202) 224-4642'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('James','Risch','483 Russell Senate Office Building ','Washington','DC','20510','(202) 224-2752'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Pat ','Roberts','109 Hart Senate Office Building ','Washington','DC','20510','(202) 224-4774'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mitt','Romney','124 Russell Senate Office Building ','Washington','DC','20510','(202) 224-5251'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Jacky','Rosen','144 Russell Senate Office Building ','Washington','DC','20510','(202) 224-6244'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mike','Rounds','502 Hart Senate Office Building ','Washington','DC','20510','(202) 224-5842'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Marco','Rubio','284 Russell Senate Office Building ','Washington','DC','20510','(202) 224-3041'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Bernard','Sanders','332 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-5141'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Ben ','Sasse','107 Russell Senate Office Building ','Washington','DC','20510','(202) 224-4224'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Brian','Schatz','722 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3934'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Charles','Schumer','322 Hart Senate Office Building ','Washington','DC','20510','(202) 224-6542'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Rick','Scott','716 Hart Senate Office Building ','Washington','DC','20510','(202) 224-5274'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Tim','Scott','104 Hart Senate Office Building ','Washington','DC','20510','(202) 224-6121'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Jeanne','Shaheen','506 Hart Senate Office Building ','Washington','DC','20510','(202) 224-2841'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Richard','Shelby','304 Russell Senate Office Building ','Washington','DC','20510','(202) 224-5744'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Krysten','Sinema','317 Hart Senate Office Building ','Washington','DC','20510','(202) 224-4521'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Tina','Smith','720 Hart Senate Office Building ','Washington','DC','20510','(202) 224-5641'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Debbie','Stabenow','731 Hart Senate Office Building ','Washington','DC','20510','(202) 224-4822'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Dan','Sullivan','302 Hart Senate Office Building ','Washington','DC','20510','(202) 224-3004'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Joni','Tester','311 Hart Senate Office Building ','Washington','DC','20510','(202) 224-2644'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('John','Thune','511 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-2321'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Thom','Tillis','113 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-6342'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Patrick','Toomey','248 Russell Senate Office Building ','Washington','DC','20510','(202) 224-4254'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Tom','Udall','531 Hart Senate Office Building ','Washington','DC','20510','(202) 224-6621'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Chris ','Van Hollen','110 Hart Senate Office Building ','Washington','DC','20510','(202) 224-4654'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mark','Warner','703 Hart Senate Office Building ','Washington','DC','20510','(202) 224-2023'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Elizabeth','Warren','309 Hart Senate Office Building ','Washington','DC','20510','(202) 224-4543'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Sheldon','Whitehouse','530 Hart Senate Office Building ','Washington','DC','20510','(202) 224-2921'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Roger','Wicker','555 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-6253'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Ron','Wyden','221 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-5244'); insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Todd','Young','185 Dirksen Senate Office Building ','Washington','DC','20510','(202) 224-5623');

create table offering ( offering_id int not null primary key auto_increment, deductible float, premium float);

insert into offering (deductible, premium) values (500.00, 1200.00); insert into offering (deductible, premium) values (1000.00, 800.00); insert into offering (deductible, premium) values (100.00, 1500.00);

create table policy ( policy_id int not null primary key auto_increment, policy_number varchar(20), member_id int not null, offering_id int not null, start_date date, end_date date, constraint po_me_fk foreign key (member_id) references members (member_id));

Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y78',11,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y79xx',12,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y80xx',13,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y81xx',14,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y82xx',15,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y83xx',16,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y84xx',17,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y85xx',18,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y86xx',19,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y87xx',20,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y88xx',21,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y89xx',1,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y90xx',2,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y91xx',3,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y92xx',4,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y93xx',5,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y94xx',6,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y95xx',7,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y96xx',8,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y97xx',9,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y98xx',10,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y99xx',22,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y100xx',23,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y101xx',24,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y102xx',25,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y103xx',26,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y104xx',27,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y105xx',28,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y106xx',29,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y107xx',30,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y108xx',31,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y109xx',32,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y110xx',51,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y111xx',52,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y112xx',53,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y113xx',54,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y114xx',55,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y115xx',56,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y116xx',57,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y117xx',58,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y118xx',59,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y119xx',60,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y120xx',61,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y121xx',62,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y122xx',63,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y123xx',64,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y124xx',65,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y125xx',66,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y126xx',67,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y127xx',68,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y128xx',69,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y129xx',70,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y130xx',71,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y131xx',72,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y132xx',73,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y133xx',74,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y134xx',75,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y135xx',76,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y136xx',77,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y137xx',33,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y138xx',34,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y139xx',35,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y140xx',36,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y141xx',37,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y142xx',38,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y143xx',39,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y144xx',40,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y145xx',41,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y146xx',42,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y147xx',43,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y148xx',44,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y149xx',45,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y150xx',46,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y151xx',47,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y152xx',48,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y153xx',49,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y154xx',50,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y155xx',78,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y156xx',79,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y157xx',80,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y158xx',81,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y159xx',82,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y160xx',83,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y161xx',84,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y162xx',85,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y163xx',86,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y164xx',87,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y165xx',88,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y166xx',89,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y167xx',90,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y168xx',91,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y169xx',92,2,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y170xx',93,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y171xx',94,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y172xx',95,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y173xx',96,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y174xx',97,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y175xx',98,3,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y176xx',99,1,'2020-01-01','2020-12-31'); Insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y177xx',100,1,'2020-01-01','2020-12-31');

create table car_make ( car_make_id int not null primary key auto_increment, car_make varchar(25));

insert into car_make (car_make) values ('Toyota'); insert into car_make (car_make) values ('Nissan'); insert into car_make (car_make) values ('Ford'); insert into car_make (car_make) values ('Mercedes'); insert into car_make (car_make) values ('Land Rover');

create table make_model ( make_model_id int not null primary key auto_increment, car_model varchar(25), car_make_id int, constraint cm_mm_fk foreign key (car_make_id) references car_make (car_make_id));

insert into make_model (car_make_id, car_model) values (1, 'Tacoma'); insert into make_model (car_make_id, car_model) values (1, 'Venza'); insert into make_model (car_make_id, car_model) values (1, 'Corolla'); insert into make_model (car_make_id, car_model) values (1, 'Sienna'); insert into make_model (car_make_id, car_model) values (1, 'Camry'); insert into make_model (car_make_id, car_model) values (1, 'Highlander'); insert into make_model (car_make_id, car_model) values (2, 'Sentra'); insert into make_model (car_make_id, car_model) values (2, 'Altima'); insert into make_model (car_make_id, car_model) values (2, 'Pathfinder'); insert into make_model (car_make_id, car_model) values (3, 'Focus'); insert into make_model (car_make_id, car_model) values (3, 'F-150'); insert into make_model (car_make_id, car_model) values (3, 'Escape'); insert into make_model (car_make_id, car_model) values (4, 'C-Class'); insert into make_model (car_make_id, car_model) values (4, 'E-Class'); insert into make_model (car_make_id, car_model) values (4, 'S-Class'); insert into make_model (car_make_id, car_model) values (5, 'Defender'); insert into make_model (car_make_id, car_model) values (5, 'Range Rover'); insert into make_model (car_make_id, car_model) values (5, 'Discovery');

create table automobile ( automobile_id int not null primary key auto_increment, vin varchar(255), make_model_id int, year_of_manufacture varchar(4), policy_id int, constraint au_po_fk foreign key (policy_id) references policy (policy_id), constraint au_mm_fk foreign key (make_model_id) references make_model (make_model_id));

insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737489',1,'2018',46); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737490',2,'2018',47); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737491',3,'2018',48); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737492',4,'2018',49); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737493',5,'2018',50); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737494',6,'2018',51); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737495',7,'2018',52); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737496',8,'2018',53); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737497',9,'2018',54); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737498',10,'2018',55); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737499',11,'2018',56); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737500',12,'2018',57); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737501',13,'2018',58); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737502',14,'2018',59); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737503',15,'2018',60); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737504',16,'2018',61); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737505',17,'2018',62); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737506',18,'2018',63); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737507',1,'2020',100); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737508',2,'2020',99); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737509',3,'2020',98); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737510',4,'2020',97); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737511',5,'2020',96); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737512',6,'2020',95); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737513',7,'2020',94); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737514',8,'2020',93); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737515',9,'2020',92); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737516',10,'2020',91); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737517',11,'2020',90); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737518',12,'2020',89); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737519',13,'2020',88); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737520',14,'2020',87); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737521',15,'2019',86); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737522',16,'2019',85); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737523',17,'2019',64); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737524',18,'2019',65); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737525',1,'2019',66); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737526',2,'2019',67); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737527',3,'2019',68); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737528',4,'2019',69); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737529',5,'2019',70); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737530',6,'2019',71); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737531',7,'2019',72); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737532',8,'2019',73); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737533',9,'2019',74); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737534',10,'2019',75); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737535',11,'2019',76); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737536',12,'2019',87); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737537',13,'2019',86); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737538',14,'2021',85); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737539',15,'2021',84); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737540',16,'2021',83); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737541',17,'2021',82); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737542',18,'2021',81); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737543',1,'2021',80); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737544',2,'2021',79); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737545',3,'2021',78); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737546',4,'2021',77); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737547',5,'2021',76); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737548',6,'2021',75); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737549',7,'2021',74); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737550',8,'2021',73); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737551',9,'2021',72); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737552',10,'2021',71); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737553',11,'2021',70); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737554',12,'2021',45); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737555',13,'2021',44); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737556',14,'2021',43); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737557',15,'2021',42); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737558',16,'2021',41); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737559',17,'2021',40); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737560',18,'2017',39); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737561',1,'2017',38); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737562',2,'2017',37); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737563',3,'2017',36); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737564',4,'2017',35); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737565',5,'2017',34); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737566',6,'2017',33); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737567',7,'2017',32); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737568',8,'2017',31); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737569',9,'2017',30); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737570',10,'2017',29); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737571',11,'2017',28); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737572',12,'2017',27); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737573',13,'2017',26); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737574',14,'2017',25); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737575',15,'2017',1); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737576',16,'2017',2); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737577',17,'2017',3); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737578',18,'2019',4); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737579',1,'2019',5); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737580',2,'2019',6); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737581',3,'2019',7); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737582',4,'2019',8); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737583',5,'2019',9); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737584',6,'2019',10); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737585',7,'2019',11); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737586',8,'2019',12); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737587',9,'2019',13); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737588',10,'2019',14); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737589',11,'2019',15); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737590',12,'2019',16); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737591',13,'2019',17); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737592',14,'2019',18); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737593',15,'2020',19); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737594',16,'2020',20); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737595',17,'2020',21); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737596',18,'2020',22); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737597',11,'2020',23); insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('QA2737598',12,'2020',24);

create table claim_event ( claim_event_id int not null primary key auto_increment, event_type varchar(255)); insert into claim_event (event_type) values ('Hit'); insert into claim_event (event_type) values ('Roll'); insert into claim_event (event_type) values ('Drown'); insert into claim_event (event_type) values ('Fire'); insert into claim_event (event_type) values ('Theft'); insert into claim_event (event_type) values ('Puncture'); insert into claim_event (event_type) values ('Vandalism');

create table claims ( claim_id int not null primary key auto_increment, claim_number varchar(25), automobile_id int, claim_event_id int, claim_date date, constraint cl_au_fk foreign key (automobile_id) references automobile (automobile_id), constraint cl_ev_fk foreign key (claim_event_id) references claim_event (claim_event_id));

insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457822',11,2,'2020-04-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457722',10,4,'2020-04-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457823',4,3,'2020-04-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457824',2,5,'2020-09-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457825',5,6,'2020-09-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457826',9,3,'2020-10-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457722',8,1,'2020-04-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457833',92,5,'2020-09-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457834',95,6,'2020-09-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457835',99,3,'2020-10-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457836',72,5,'2020-10-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457837',75,6,'2020-10-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457838',79,3,'2020-10-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457839',62,5,'2020-10-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457840',65,6,'2020-10-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457841',69,3,'2020-10-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457842',62,5,'2020-10-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457843',65,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457844',79,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457845',72,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457846',75,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457847',79,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457848',102,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457849',45,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457850',49,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457851',42,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457852',35,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457853',49,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457854',52,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457855',55,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457856',59,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457857',52,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457858',65,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457859',69,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457860',62,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457861',65,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457862',79,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457863',72,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457864',75,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457865',79,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457866',82,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457867',85,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457868',89,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457869',82,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457870',95,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457871',99,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457872',92,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457873',95,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457874',109,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457875',22,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457876',25,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457877',29,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457878',22,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457879',25,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457880',39,3,'2020-11-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457881',32,5,'2020-11-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457882',35,6,'2020-11-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457883',39,3,'2020-12-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457884',32,5,'2020-12-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457885',45,6,'2020-12-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457886',49,3,'2020-12-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457887',42,5,'2020-12-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457888',45,6,'2020-12-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457889',49,3,'2020-12-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457890',52,5,'2020-12-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457891',55,6,'2020-12-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457892',59,3,'2020-12-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457893',25,5,'2020-12-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457894',55,6,'2020-12-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457895',59,3,'2020-12-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457896',62,5,'2020-12-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457897',65,6,'2020-12-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457899',69,3,'2020-12-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457900',62,5,'2020-12-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457901',65,6,'2020-12-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457902',79,3,'2020-12-05'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457903',72,5,'2020-12-09'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457904',75,6,'2020-12-01'); insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457904',79,3,'2020-12-05');

select claims.claim_number, claim_event.event_type, policy.policy_number, policy.start_date, policy.end_date, claims.claim_date, automobile.vin, car_make.car_make, make_model.car_model, members.first_name, members.last_name from claims join claim_event on claims.claim_event_id = claim_event.claim_event_id join automobile on claims.automobile_id = automobile.automobile_id join make_model on automobile.make_model_id = make_model.make_model_id join car_make on make_model.car_make_id = car_make.car_make_id join policy on automobile.policy_id = policy.policy_id join members on policy.member_id = members.member_id;