テーブルの正規化、リレーション、結合

sqlite> .schema
CREATE TABLE sample (
	"N_id" VARCHAR NOT NULL, 
	"Alternative_Sample_name" VARCHAR(64), 
	phenotype VARCHAR, 
	clinical_info VARCHAR, 
	PRIMARY KEY ("N_id")
);
CREATE INDEX "ix_sample_Alternative_Sample_name" ON sample ("Alternative_Sample_name");
CREATE TABLE experiment (
	"Sample_number" VARCHAR(64) NOT NULL, 
	"N_number" INTEGER, 
	"Seq_type" VARCHAR(64), 
	"Bam_file" VARCHAR, 
	"Bam_for_UCSC" VARCHAR, 
	vcf_file VARCHAR, 
	"RNA_bam_file" VARCHAR, 
	prot_exp_file VARCHAR, 
	PRIMARY KEY ("Sample_number"), 
	FOREIGN KEY("N_number") REFERENCES sample ("N_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE exome (
	"SampleName_phenotype" VARCHAR, 
	chr VARCHAR, 
	start VARCHAR, 
	end VARCHAR, 
	ref VARCHAR, 
	alt VARCHAR, 
	variant_id INTEGER NOT NULL, 
	variant_count INTEGER, 
	"A_number" INTEGER, 
	PRIMARY KEY (variant_id), 
	FOREIGN KEY("A_number") REFERENCES experiment ("Sample_number") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "ix_exome_SampleName_phenotype" ON exome ("SampleName_phenotype");

まずはこんな感じでテーブルを定義してみる。