Difference between revisions of "Creating a database"

Revision as of 01:05, 7 December 2020

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;

Difference between revisions of "Creating a database"

Revision as of 01:05, 7 December 2020

Navigation menu

Personal tools

Namespaces

Variants

Views

More

Search

Navigation

Tools

@@ Line 7: / Line 7: @@
 This is designed using MySQL database.
-drop database insurance;
+drop database insurance;<br />
-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));
+create database insurance;<br />
+use insurance;<br />
+<p>
+create table members (<br />
+member_id int not null primary key auto_increment,<br />
+first_name varchar(25),<br />
+last_name varchar(25),<br />
+street varchar(255),<br />
+city varchar(50),<br />
+state varchar(2),<br />
+zip varchar(5),<br />
+phone_number varchar(10)<br />
+);<br />
+</p>
+<p>
+insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mitch','McConnel','1237 Main Ave','Lousville','KY','23784','7478339090');<br />
+insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Nancy','Pelosi','8944 Second Ave','San Fransisco','CA','90045','3235845978');<br />
+insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Marco','Rubio','944400 Mad People Street','Miami','FL','78484','5455325998');<br />
+insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Amy','Klobuchar','1237 Main Ave','Lousville','KY','23784','7478339090');<br />
+insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Lindsay','Graham','8944 Second Ave','Filthy','SC','90045','3235845978');<br />
+insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Elizabeth','Warren','944400 Mad People Street','Foxborough','MA','78484','5455325998');<br />
+insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Tim','Scott','1237 Main Ave','Lousville','SC','23784','7478339090');<br />
+insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Lisa','Murkowski','8944 Second Ave','Augusta','ME','90045','3235845978');<br />
+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');<br />
+insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Chuck','Schumer','1237 Main Ave','New York','NY','23784','7478339090');<br />
+insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Bernard','Sanders','8944 Second Ave','Montpelier','VT','90045','3235845978');<br />
+insert into members (first_name, last_name, street, city, state, zip, phone_number) values ('Mark','Kelley','944400 Mad People Street','Phoenix','AZ','78484','5455325998');<br />
+</p>
+<p>
+create table offering (<br />
+offering_id int not null primary key auto_increment,<br />
+deductible float,<br />
+premium float);<br />
+</p>
+<p>
+insert into offering (deductible, premium) values (500.00, 1200.00);<br />
+insert into offering (deductible, premium) values (1000.00, 800.00);<br />
+insert into offering (deductible, premium) values (100.00, 1500.00);<br />
+</p>
+<p>
+create table policy (<br />
+policy_id int not null primary key auto_increment,<br />
+policy_number varchar(20),<br />
+member_id int not null,<br />
+offering_id int not null,<br />
+start_date date,<br />
+end_date date,<br />
+constraint po_me_fk foreign key (member_id) references members (member_id));<br />
+</p>
+<p>
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y78',3, 2, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y79',1, 1, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y80',2, 1, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y81',12, 2, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y82',11, 1, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y83',9, 1, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y84',10, 2, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y85',7, 1, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y86',6, 1, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y87',4, 2, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y88',5, 1, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
 insert into policy (policy_number, member_id, offering_id, start_date, end_date) values ('A45Y89',8, 1, '2020-01-01',
-'2020-12-31');
+'2020-12-31');<br />
+</p>
-create table car_make (
+<p>
-car_make_id int not null primary key auto_increment,
+create table car_make (<br />
-car_make varchar(25));
+car_make_id int not null primary key auto_increment,<br />
+car_make varchar(25));<br />
-insert into car_make (car_make) values ('Toyota');
+</p>
-insert into car_make (car_make) values ('Nissan');
+<p>
-insert into car_make (car_make) values ('Ford');
+insert into car_make (car_make) values ('Toyota');<br />
-insert into car_make (car_make) values ('Mercedes');
+insert into car_make (car_make) values ('Nissan');<br />
-insert into car_make (car_make) values ('Land Rover');
+insert into car_make (car_make) values ('Ford');<br />
+insert into car_make (car_make) values ('Mercedes');<br />
-create table make_model (
+insert into car_make (car_make) values ('Land Rover');<br />
-make_model_id int not null primary key auto_increment,
+</p>
-car_model varchar(25),
+<p>
-car_make_id int,
+create table make_model (<br />
-constraint cm_mm_fk foreign key (car_make_id) references car_make (car_make_id));
+make_model_id int not null primary key auto_increment,<br />
+car_model varchar(25),<br />
-insert into make_model (car_make_id, car_model) values (1, 'Tacoma');
+car_make_id int,<br />
-insert into make_model (car_make_id, car_model) values (1, 'Venza');
+constraint cm_mm_fk foreign key (car_make_id) references car_make (car_make_id));<br />
-insert into make_model (car_make_id, car_model) values (1, 'Corolla');
+</p>
-insert into make_model (car_make_id, car_model) values (1, 'Sienna');
+<p>
-insert into make_model (car_make_id, car_model) values (1, 'Camry');
+insert into make_model (car_make_id, car_model) values (1, 'Tacoma');<br />
-insert into make_model (car_make_id, car_model) values (1, 'Highlander');
+insert into make_model (car_make_id, car_model) values (1, 'Venza');<br />
-insert into make_model (car_make_id, car_model) values (2, 'Sentra');
+insert into make_model (car_make_id, car_model) values (1, 'Corolla');<br />
-insert into make_model (car_make_id, car_model) values (2, 'Altima');
+insert into make_model (car_make_id, car_model) values (1, 'Sienna');<br />
-insert into make_model (car_make_id, car_model) values (2, 'Pathfinder');
+insert into make_model (car_make_id, car_model) values (1, 'Camry');<br />
-insert into make_model (car_make_id, car_model) values (3, 'Focus');
+insert into make_model (car_make_id, car_model) values (1, 'Highlander');<br />
-insert into make_model (car_make_id, car_model) values (3, 'F-150');
+insert into make_model (car_make_id, car_model) values (2, 'Sentra');<br />
-insert into make_model (car_make_id, car_model) values (3, 'Escape');
+insert into make_model (car_make_id, car_model) values (2, 'Altima');<br />
-insert into make_model (car_make_id, car_model) values (4, 'C-Class');
+insert into make_model (car_make_id, car_model) values (2, 'Pathfinder');<br />
-insert into make_model (car_make_id, car_model) values (4, 'E-Class');
+insert into make_model (car_make_id, car_model) values (3, 'Focus');<br />
-insert into make_model (car_make_id, car_model) values (4, 'S-Class');
+insert into make_model (car_make_id, car_model) values (3, 'F-150');<br />
-insert into make_model (car_make_id, car_model) values (5, 'Defender');
+insert into make_model (car_make_id, car_model) values (3, 'Escape');<br />
-insert into make_model (car_make_id, car_model) values (5, 'Range Rover');
+insert into make_model (car_make_id, car_model) values (4, 'C-Class');<br />
-insert into make_model (car_make_id, car_model) values (5, 'Discovery');
+insert into make_model (car_make_id, car_model) values (4, 'E-Class');<br />
+insert into make_model (car_make_id, car_model) values (4, 'S-Class');<br />
-create table automobile (
+insert into make_model (car_make_id, car_model) values (5, 'Defender');<br />
-automobile_id int not null primary key auto_increment,
+insert into make_model (car_make_id, car_model) values (5, 'Range Rover');<br />
-vin varchar(255),
+insert into make_model (car_make_id, car_model) values (5, 'Discovery');<br />
-make_model_id int,
+</p>
-year_of_manufacture varchar(4),
+<p>
-policy_id int,
+create table automobile (<br />
-constraint au_po_fk foreign key (policy_id) references policy (policy_id),
+automobile_id int not null primary key auto_increment,<br />
-constraint au_mm_fk foreign key (make_model_id) references make_model (make_model_id));
+vin varchar(255),<br />
+make_model_id int,<br />
-insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('23456789W',1,'2020', 2);
+year_of_manufacture varchar(4),<br />
+policy_id int,<br />
-insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('123456789X',5,'2020', 3);
+constraint au_po_fk foreign key (policy_id) references policy (policy_id),<br />
-insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('123456789Y', 7,'2018',1);
+constraint au_mm_fk foreign key (make_model_id) references make_model (make_model_id));<br />
-insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('123456789Z',1,'2010', 10);
+</p>
-insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('523456789W',3,'2012',3);
+<p>
-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 ('23456789W',1,'2020', 2);<br />
-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 ('123456789X',5,'2020', 3);<br />
-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 ('123456789Y', 7,'2018',1);<br />
-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 ('123456789Z',1,'2010', 10);<br />
-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 ('523456789W',3,'2012',3);<br />
-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 ('623456789W',7, '2019',4);<br />
-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 ('723456789W',13,'2020',6);<br />
-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 ('121456789W',1,'2020',8);<br />
-insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('773456789W',16,'2020',12);
+insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('122456789X',2,'2020',10);<br />
+insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('124456789Y', 3,'2018',9);<br />
-create table claim_event (
+insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('124456789Z',1,'2010',4);<br />
-claim_event_id int not null primary key auto_increment,
+insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('525456789W',3,'2012',5);<br />
-event_type varchar(255));
+insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('66456789W',5,'2019',11);<br />
-insert into claim_event (event_type) values ('Hit');
+insert into automobile (vin, make_model_id, year_of_manufacture, policy_id) values ('773456789W',16,'2020',12);<br />
-insert into claim_event (event_type) values ('Roll');
+</p>
-insert into claim_event (event_type) values ('Drown');
+<p>
-insert into claim_event (event_type) values ('Fire');
+create table claim_event (<br />
-insert into claim_event (event_type) values ('Theft');
+claim_event_id int not null primary key auto_increment,<br />
-insert into claim_event (event_type) values ('Puncture');
+event_type varchar(255));<br />
-insert into claim_event (event_type) values ('Vandalism');
+insert into claim_event (event_type) values ('Hit');<br />
+insert into claim_event (event_type) values ('Roll');<br />
-create table claims (
+insert into claim_event (event_type) values ('Drown');<br />
-claim_id int not null primary key auto_increment,
+insert into claim_event (event_type) values ('Fire');<br />
-claim_number varchar(25),
+insert into claim_event (event_type) values ('Theft');<br />
-automobile_id int,
+insert into claim_event (event_type) values ('Puncture');<br />
-claim_event_id int,
+insert into claim_event (event_type) values ('Vandalism');<br />
-claim_date date,
+</p>
-constraint cl_au_fk foreign key (automobile_id) references automobile (automobile_id),
+<p>
-constraint cl_ev_fk foreign key (claim_event_id) references claim_event (claim_event_id));
+create table claims (<br />
+claim_id int not null primary key auto_increment,<br />
-insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457822',11,2,'2020-04-01');
+claim_number varchar(25),<br />
-insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457722',10,1,'2020-04-05');
+automobile_id int,<br />
-insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457823',4,2,'2020-04-01');
+claim_event_id int,<br />
-insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457824',2,3,'2020-09-09');
+claim_date date,<br />
-insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457825',5,2,'2020-09-01');
+constraint cl_au_fk foreign key (automobile_id) references automobile (automobile_id),<br />
-insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457826',9,4,'2020-10-05');
+constraint cl_ev_fk foreign key (claim_event_id) references claim_event (claim_event_id));<br />
-insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457722',8,1,'2020-04-05');
+</p>
+<p>
-select claims.claim_number, claim_event.event_type,
+insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457822',11,2,'2020-04-01');<br />
-policy.policy_number,
+insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457722',10,1,'2020-04-05');<br />
-policy.start_date,
+insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457823',4,2,'2020-04-01');<br />
-policy.end_date,
+insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457824',2,3,'2020-09-09');<br />
-claims.claim_date,
+insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457825',5,2,'2020-09-01');<br />
-automobile.vin,
+insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457826',9,4,'2020-10-05');<br />
-car_make.car_make,
+insert into claims (claim_number, automobile_id, claim_event_id, claim_date) values ('AS457722',8,1,'2020-04-05');<br />
-make_model.car_model,
+</p>
-members.first_name,
+<p>
-members.last_name,
+select claims.claim_number, claim_event.event_type,<br />
+policy.policy_number,<br />
+policy.start_date,<br />
+policy.end_date,<br />
+claims.claim_date,<br />
+automobile.vin, <br />
+car_make.car_make,<br />
+make_model.car_model,<br />
+members.first_name,<br />
+members.last_name,<br />
 automobile.automobile_id
-from claims
+from claims <br />
-join claim_event on claims.claim_event_id = claim_event.claim_event_id
+join claim_event on claims.claim_event_id = claim_event.claim_event_id<br />
-join automobile on claims.automobile_id = automobile.automobile_id
+join automobile on claims.automobile_id = automobile.automobile_id<br />
-join make_model on automobile.make_model_id = make_model.make_model_id
+join make_model on automobile.make_model_id = make_model.make_model_id<br />
-join car_make on make_model.car_make_id = car_make.car_make_id
+join car_make on make_model.car_make_id = car_make.car_make_id<br />
-join policy on automobile.policy_id = policy.policy_id
+join policy on automobile.policy_id = policy.policy_id<br />
-join members on policy.member_id = members.member_id;
+join members on policy.member_id = members.member_id;<br />
+</p>