drop table assign cascade;
drop table teacher cascade;
drop table educand cascade;
drop table subject cascade;

create table subject (
	code integer primary key,
	name text not null
);
insert into subject values(1, '数学');
insert into subject values(2, '物理');
insert into subject values(3, '化学');

create table educand (
	code integer primary key,
	name text not null
);
insert into educand values(1, '村越カズミ');
insert into educand values(2, '久保田大介');
insert into educand values(3, '大塚紗弥香');
insert into educand values(4, '川村ヒロキ');
insert into educand values(5, '伊久美哲平');
insert into educand values(6, '内田麻菜美');
insert into educand values(7, '山田ナオト');
insert into educand values(8, '岡田美恵子');
insert into educand values(9, '広瀬健太郎');

create table teacher (
	code integer primary key,
	name text not null,
	s_code integer references subject(code),
	unique (code, s_code)
);
insert into teacher values(1, '小平邦彦', 1);
insert into teacher values(2, '湯川秀樹', 2);
insert into teacher values(3, '福井謙一', 3);
insert into teacher values(4, '白川英樹', 3);
insert into teacher values(5, '小柴昌俊', 2);
insert into teacher values(6, '広中平祐', 1);

create table assign (
	e_code integer references educand(code),
	s_code integer,
	t_code integer,
	primary key (e_code, s_code),
	foreign key (s_code, t_code) references teacher(s_code, code)
);
insert into assign values(1, 1, 1);
insert into assign values(1, 3, 4);
insert into assign values(2, 1, 6);
insert into assign values(2, 2, 2);
insert into assign values(2, 3, 3);
insert into assign values(3, 1, 1);
insert into assign values(4, 1, 6);
insert into assign values(4, 2, 2);
insert into assign values(4, 3, 4);
insert into assign values(5, 1, 1);
insert into assign values(5, 2, 5);
insert into assign values(6, 1, 6);
insert into assign values(6, 2, 2);
insert into assign values(7, 1, 6);
insert into assign values(7, 2, 5);
insert into assign values(8, 1, 1);
insert into assign values(8, 2, 2);
insert into assign values(8, 3, 4);
insert into assign values(9, 1, 6);
insert into assign values(9, 2, 5);
insert into assign values(9, 3, 3);