414_数据库规范化设计¶

-- ========== 生信数据库规范化设计示例 ==========

-- ========== 反例：未规范化的表（违反1NF/2NF/3NF）==========
-- ❌ 坏设计：一张大表包含所有信息（数据冗余严重）
CREATE TABLE bad_design (
    sample_id      VARCHAR(20),
    gene_id        VARCHAR(20),
    expression     FLOAT,
    -- 以下冗余：样本信息每行都重复存储
    patient_name   VARCHAR(100),   -- 同一患者多个样本时重复
    patient_age    INT,
    hospital_name  VARCHAR(100),   -- 同一医院多个患者时重复
    hospital_city  VARCHAR(50),    -- 传递依赖：hospital_city依赖hospital_name
    -- 以下违反1NF：一列存多个值
    go_terms       TEXT,           -- "GO:0001;GO:0002;GO:0003" 不可分
    co_expressed_genes TEXT        -- "BRCA1,TP53,KRAS" 不可分
);

-- 问题：
-- 1. 更新患者年龄需要修改所有包含该患者的行
-- 2. go_terms无法单独查询某个GO term
-- 3. hospital_city依赖hospital_name而非主键（传递依赖）

-- ========== 正确设计：规范化到3NF ==========

-- 1. 患者表（独立实体）
CREATE TABLE patients (
    patient_id   SERIAL PRIMARY KEY,     -- 代理主键（自增）
    patient_name VARCHAR(100) NOT NULL,  -- 患者姓名
    patient_age  INT CHECK (patient_age BETWEEN 0 AND 150),
    hospital_id  INT REFERENCES hospitals(hospital_id)  -- 外键
);

-- 2. 医院表（解决传递依赖：city依赖hospital，不依赖patient）
CREATE TABLE hospitals (
    hospital_id   SERIAL PRIMARY KEY,
    hospital_name VARCHAR(100) NOT NULL UNIQUE,
    hospital_city VARCHAR(50) NOT NULL
);

-- 3. 样本表（样本属于患者）
CREATE TABLE samples (
    sample_id       VARCHAR(20) PRIMARY KEY,
    patient_id      INT NOT NULL REFERENCES patients(patient_id),
    collection_date DATE NOT NULL,
    tissue_type     VARCHAR(50),
    status          VARCHAR(20) DEFAULT 'pending'
);

-- 4. 基因表（基因独立存储）
CREATE TABLE genes (
    gene_id    VARCHAR(20) PRIMARY KEY,  -- 如 ENSG00000012048
    gene_name  VARCHAR(50),              -- BRCA1
    chromosome VARCHAR(5),
    strand     CHAR(1) CHECK (strand IN ('+', '-'))
);

-- 5. 表达量表（多对多：样本-基因）
CREATE TABLE gene_expression (
    expr_id    SERIAL PRIMARY KEY,
    gene_id    VARCHAR(20) REFERENCES genes(gene_id),
    sample_id  VARCHAR(20) REFERENCES samples(sample_id),
    expression FLOAT NOT NULL CHECK (expression >= 0),
    condition  VARCHAR(50),
    UNIQUE (gene_id, sample_id, condition)  -- 同一基因同一样本同一条件只有一条记录
);

-- 6. GO注释表（解决1NF违反：多值属性拆分为多行）
CREATE TABLE go_annotations (
    annotation_id  SERIAL PRIMARY KEY,
    gene_id        VARCHAR(20) REFERENCES genes(gene_id),
    go_id          VARCHAR(15) NOT NULL,         -- GO:0001234
    evidence_code  CHAR(3),                       -- IEA, EXP, IDA等
    UNIQUE (gene_id, go_id)  -- 同一基因同一GO term只记录一次
);

-- 7. GO词条表
CREATE TABLE go_terms (
    go_id     VARCHAR(15) PRIMARY KEY,
    go_name   TEXT NOT NULL,
    namespace VARCHAR(30)  -- biological_process/molecular_function/cellular_component
);

-- ========== 规范化后的查询示例 ==========
-- 查询某患者所有样本中BRCA1的表达量（联表查询）
SELECT 
    p.patient_name,
    s.sample_id,
    s.tissue_type,
    e.expression,
    e.condition
FROM patients p
JOIN samples s ON p.patient_id = s.patient_id       -- 患者→样本
JOIN gene_expression e ON s.sample_id = e.sample_id  -- 样本→表达
JOIN genes g ON e.gene_id = g.gene_id               -- 基因过滤
WHERE p.patient_name = '张三'
  AND g.gene_name = 'BRCA1';

-- ========== 查询某GO term下所有注释基因的表达量 ==========
SELECT 
    gt.go_name,
    g.gene_name,
    AVG(e.expression) AS avg_expression
FROM go_terms gt
JOIN go_annotations ga ON gt.go_id = ga.go_id       -- GO词条→注释
JOIN genes g ON ga.gene_id = g.gene_id              -- 注释→基因
JOIN gene_expression e ON g.gene_id = e.gene_id    -- 基因→表达
WHERE gt.go_name = 'autophagy'
GROUP BY gt.go_name, g.gene_name
ORDER BY avg_expression DESC;

-- ========== 反范式化：创建报表视图（适度冗余，提升查询速度）==========
-- 分析用宽表（物化视图或普通视图）
CREATE MATERIALIZED VIEW analysis_wide AS  -- 物化视图：实际存储数据
SELECT 
    e.gene_id,
    g.gene_name,
    g.chromosome,
    e.sample_id,
    s.tissue_type,
    p.patient_age,
    h.hospital_city,
    e.expression,
    e.condition
FROM gene_expression e
JOIN genes g ON e.gene_id = g.gene_id
JOIN samples s ON e.sample_id = s.sample_id
JOIN patients p ON s.patient_id = p.patient_id
JOIN hospitals h ON p.hospital_id = h.hospital_id;

-- 分析时直接查宽表（无需多表JOIN，速度快）
SELECT gene_name, AVG(expression) FROM analysis_wide
WHERE condition = 'tumor' GROUP BY gene_name;