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;