drop table clerk cascade;
drop table shop cascade;
drop table area cascade;

create table area (
	code integer primary key,
	name text not null
);
insert into area values(1, '関東');
insert into area values(2, '関西');
insert into area values(3, '九州');

create table shop (
	a_code integer references area(code),
	code integer,
	name text not null,
	primary key(a_code, code)
);
insert into shop values(1, 1, '東京店');
insert into shop values(1, 2, '横浜店');
insert into shop values(1, 3, '大宮店');
insert into shop values(1, 4, '千葉店');
insert into shop values(2, 1, '大阪店');
insert into shop values(2, 2, '京都店');
insert into shop values(2, 3, '神戸店');
insert into shop values(2, 4, '奈良店');
insert into shop values(3, 1, '博多店');
insert into shop values(3, 2, '大分店');
insert into shop values(3, 3, '長崎店');
insert into shop values(3, 4, '熊本店');

create table clerk (
	a_code integer,
	s_code integer,
	code integer,
	name text not null,
	sales integer not null check (sales > 0),
	primary key(a_code, s_code, code),
	foreign key(a_code, s_code) references shop(a_code, code)
);
\copy clerk from clerk.dat csv