drop database if exists <DBNAME_MAANTTAUTOEVAL>;
create database <DBNAME_MAANTTAUTOEVAL>;
use <DBNAME_MAANTTAUTOEVAL>;



create table plan_details(plan_id varchar(6) primary key, plan_name varchar(20),plan_value integer(10),
tenure integer(10));
create table member_details(member_id varchar(6), member_name varchar(20),
address varchar(20), age integer(20), plan_id varchar(6), monthly_due_date date,corporate_name varchar(20),
primary key(member_id, plan_id));


alter table member_details add constraint fk foreign key(plan_id)
references plan_details(plan_id);

alter table member_details add premium int(10);

insert into plan_details values('PA0001','Life Insurance',50000,12);
insert into plan_details values('PA0002','Health Insurance',70000,12);
insert into plan_details values('PA0003','Medical Insurance',100000,12);
insert into plan_details values('PA0004','Vehicle Insurance',80000,12);

insert into member_details values('M01', 'xxx', 'zzz', 23, 'PA0001', '2018-02-23', 'IT',0);
insert into member_details values('M02', 'yyy', 'zvz', 22, 'PA0002', '2018-03-22', 'Automobiles',0);
insert into member_details values('M03', 'zzz', 'www', 24, 'PA0003', '2018-03-23', 'Manufacturing',0);

commit;