MariaDB Research Platform with Semantic Search

A comprehensive academic research platform built on MariaDB with semantic search capabilities using Ollama embeddings.

🚀 Quick Start

Prerequisites

• MariaDB 12+ (with built-in vector support)
• Python 3.8+
• Ollama with mxbai-embed-large model
• Virtual environment (recommended)

Setup Commands

# Clone and navigate to project
cd /home/ec/Work/clg/dbms/proect

# Set up virtual environment
python3 -m venv venv
source venv/bin/activate
pip install mariadb

# Create database schema
mariadb -h 127.0.0.1 -P 3306 -u root -ppassword dbms_projekt < schema

# Populate with complete dataset
mariadb -h 127.0.0.1 -P 3306 -u root -ppassword dbms_projekt < populate_complete.sql

# Generate and add semantic embeddings
source venv/bin/activate && python3 generate_embeddings.py
mariadb -h 127.0.0.1 -P 3306 -u root -ppassword dbms_projekt < update_embeddings.sql

# Ensure Ollama is running
ollama run mxbai-embed-large

📊 Database Structure

Entity Tables (with semantic embeddings)

• Domain (6) - Research domains
• User (6) - Researchers with profiles
• Search_Queries (5) - Search queries with embeddings
• Job_Posting (5) - Job listings with embeddings
• Research_Paper (5) - Academic papers with embeddings

Reference Tables

• Institution (10) - Academic institutions
• Venue (10) - Conferences and journals

Relationship Tables

• IS_AFFILIATED_WITH - User-institution affiliations
• Has_Interest_In - User-domain interests
• Authorship - Paper-author relationships
• Published_In - Paper-venue publications
• Job_Belongs_To - Job-domain classifications
• Searches - User query history
• Jobs_Returned - Query-job results
• Paper_Belongs_To - Paper-domain classifications
• Research_Paper_Returned - Query-paper results
• Subscribes_To - Institution-venue subscriptions

🔍 Semantic Search

The platform uses 1024-dimensional embeddings generated by Ollama's mxbai-embed-large model for semantic similarity search.

Sample Queries

-- Find papers similar to a topic
SELECT p.title, p.abstract,
       COSINE_DISTANCE(p.semantic_embedding, 
           VEC_FromText('[query_vector]')) as similarity
FROM Research_Paper p
ORDER BY similarity
LIMIT 10;

-- Find jobs matching user interests
SELECT j.title, j.description
FROM Job_Posting j
WHERE j.domain_id IN (
    SELECT domain_id FROM Has_Interest_In 
    WHERE user_id = 1
);

-- Recommend papers based on search history
SELECT p.title
FROM Research_Paper p
JOIN Research_Paper_Returned rpr ON p.paper_id = rpr.paper_id
JOIN Searches s ON rpr.query_id = s.query_id
WHERE s.user_id = 1
GROUP BY p.paper_id
ORDER BY COUNT(*) DESC;

🛠 Development

Using Makefile

make help        # Show all targets
make setup       # Set up environment and database
make embeddings   # Generate embeddings
make populate     # Populate database
make status       # Show table counts
make clean        # Clean generated files

File Structure

├── schema                    # Database schema with vector support
├── populate_complete.sql      # Complete dataset population
├── generate_embeddings.py      # Embedding generation script
├── update_embeddings.sql       # SQL for embedding updates
├── AGENTS.md                # Guidelines for coding agents
├── DATABASE_OVERVIEW.md       # Database documentation
├── Makefile                  # Build automation
└── README.md                # This file

📈 Features

• ✅ Semantic Search - Vector-based similarity search
• ✅ Academic Focus - Research papers, venues, institutions
• ✅ Job Matching - AI/ML job opportunities
• ✅ User Profiles - Researcher profiles and interests
• ✅ Recommendations - Content and job recommendations
• ✅ Real Embeddings - Generated by state-of-the-art models
• ✅ Foreign Key Integrity - Complete relational structure

🔧 Configuration

Database Connection

• Host: 127.0.0.1
• Port: 3306
• Database: dbms_projekt
• User: root
• Password: password

Ollama Model

• Model: mxbai-embed-large
• Dimensions: 1024
• Purpose: Text embeddings for semantic search

📚 Documentation

• AGENTS.md - Development guidelines for agents
• DATABASE_OVERVIEW.md - Detailed database documentation

🧪 Testing

No formal unit tests. Verify functionality by:

1. Running embedding generation: source venv/bin/activate && python3 generate_embeddings.py
2. Executing SQL updates: mariadb -h 127.0.0.1 -P 3306 -u root -ppassword dbms_projekt < update_embeddings.sql
3. Checking data integrity: make status

🤖 Dependencies

• MariaDB 12+ (built-in vector support)
• Python 3.8+
• Ollama
• mxbai-embed-large model
• mariadb Python connector

📄 License

This project is for educational and research purposes.

---

Status: Complete - All tables populated with semantic embeddings