Add MySQL schema for Biological Database integration project

kavyamguru · web-flow · commit 8a5f95e155c1 · 2025-10-29T12:10:15.000Z
Adds BD_assignment.sql — the SQL schema defining database structure for the 
Biological Database Integration Project.

This script creates the `bioinfo_db` database and associated tables for:
- gene_annotations: Ensembl-based gene details and identifiers
- kegg_data: KEGG pathway enrichment outputs
- mirna_data: miRNA mapping information
- summary: combined relational view joining gene, pathway, and miRNA data

The schema supports data integration from Ensembl, UniProt, KEGG, and miRBase, 
enabling structured storage and querying of biological datasets retrieved via R scripts.
diff --git a/BD_assignment.sql b/BD_assignment.sql
@@ -0,0 +1,63 @@
+-- #########################################################
+-- Project: Biological Databases – MySQL Schema
+-- Student ID: B269797
+-- Description:
+-- Define schema and relationships for integrated gene,
+-- protein, KEGG pathway, and miRNA datasets.
+-- #########################################################
+
+CREATE DATABASE IF NOT EXISTS bioinfo_db;
+USE bioinfo_db;
+
+-- ---- Gene Annotation Table ----
+CREATE TABLE IF NOT EXISTS gene_annotations (
+    ensembl_gene_id VARCHAR(30) PRIMARY KEY,
+    external_gene_name VARCHAR(50),
+    description TEXT,
+    gene_biotype VARCHAR(50),
+    chromosome_name VARCHAR(10),
+    start_position INT,
+    end_position INT,
+    strand TINYINT,
+    uniprotswissprot VARCHAR(20),
+    entrezgene_id VARCHAR(20)
+);
+
+-- ---- KEGG Pathway Table ----
+CREATE TABLE IF NOT EXISTS kegg_data (
+    id INT AUTO_INCREMENT PRIMARY KEY,
+    pathway_id VARCHAR(20),
+    Description VARCHAR(255),
+    pvalue FLOAT,
+    qvalue FLOAT,
+    geneID VARCHAR(255)
+);
+
+-- ---- miRNA Table ----
+CREATE TABLE IF NOT EXISTS mirna_data (
+    id INT AUTO_INCREMENT PRIMARY KEY,
+    ensembl_gene_id VARCHAR(30),
+    external_gene_name VARCHAR(50),
+    mirbase_id VARCHAR(30),
+    mirbase_accession VARCHAR(30)
+);
+
+-- ---- Summary Integration Table ----
+CREATE TABLE IF NOT EXISTS summary AS
+SELECT 
+    g.ensembl_gene_id,
+    g.external_gene_name,
+    g.description,
+    g.gene_biotype,
+    k.Description AS pathway,
+    k.pvalue,
+    m.mirbase_id,
+    m.mirbase_accession
+FROM gene_annotations g
+LEFT JOIN kegg_data k ON g.entrezgene_id = k.geneID
+LEFT JOIN mirna_data m ON g.ensembl_gene_id = m.ensembl_gene_id;
+
+-- #########################################################
+-- End of SQL Schema
+-- #########################################################
+
