Task Description

A workbench to perform bioinformatics analysis. Data should include:

  1. User information
  2. Tools (including required types of data and formats for input and output and parameters)
  3. Data (references to actual files, that will be stored elsewhere, should include data types and formats)
  4. Compatibility between tools and data types and formats
  5. Authorization rules users/data/tools
Data Model Image 
                        
                            -- Create table for User information
                            CREATE TABLE users (
                                user_id INT PRIMARY KEY AUTO_INCREMENT,
                                username VARCHAR(255) NOT NULL,
                                full_name VARCHAR(255),
                                email VARCHAR(255) NOT NULL,
                                password VARCHAR(255) NOT NULL,
                                date_of_birth DATE,
                                address VARCHAR(255),
                                phone_number VARCHAR(20)
                            );

                            -- Create table for Tools
                            CREATE TABLE tools (
                                tool_id INT PRIMARY KEY AUTO_INCREMENT,
                                tool_name VARCHAR(255) NOT NULL,
                                description TEXT,
                                version VARCHAR(50),
                                license VARCHAR(100),
                                required_data_type VARCHAR(255) NOT NULL,
                                input_format VARCHAR(255) NOT NULL,
                                output_format VARCHAR(255) NOT NULL,
                                parameters VARCHAR(255)
                            );

                            -- Create table for Data
                            CREATE TABLE data (
                                data_id INT PRIMARY KEY AUTO_INCREMENT,
                                data_name VARCHAR(255) NOT NULL,
                                description TEXT,
                                file_reference VARCHAR(255) NOT NULL,
                                data_type VARCHAR(255) NOT NULL,
                                data_format VARCHAR(255) NOT NULL,
                                storage_location VARCHAR(255),
                                upload_date DATE,
                                uploaded_by INT,
                                FOREIGN KEY (uploaded_by) REFERENCES users(user_id)
                            );

                            -- Create table for Compatibility between tools and data types and formats
                            CREATE TABLE compatibility (
                                compatibility_id INT PRIMARY KEY AUTO_INCREMENT,
                                tool_id INT,
                                data_id INT,
                                FOREIGN KEY (tool_id) REFERENCES tools(tool_id),
                                FOREIGN KEY (data_id) REFERENCES data(data_id)
                            );

                            -- Create table for Authorization rules users/data/tools
                            CREATE TABLE authorization (
                                authorization_id INT PRIMARY KEY AUTO_INCREMENT,
                                user_id INT,
                                data_id INT,
                                tool_id INT,
                                access_level ENUM('read', 'write', 'admin'),
                                FOREIGN KEY (user_id) REFERENCES users(user_id),
                                FOREIGN KEY (data_id) REFERENCES data(data_id),
                                FOREIGN KEY (tool_id) REFERENCES tools(tool_id)
                            );