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 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(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 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',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 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 ('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 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,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');

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;