Skip to content

Latest commit

 

History

History
214 lines (150 loc) · 7.18 KB

README.md

File metadata and controls

214 lines (150 loc) · 7.18 KB
Raw

Queensman Espionage Agency Management Platform

Overview

QueensmanLogo

This project is a web application for managing an espionage agency, utilizing a Docker environment with multiple servers dedicated to different use cases. The platform integrates both relational (MySQL) and non-relational (MongoDB) databases, allowing seamless interaction and data migration between the two through a user-friendly web interface.


Key Aspects of the Project

Data Engineering

Focuses on data integration, ETL processes, and managing large datasets across different database systems.

  • Data Integration and ETL: The project involves migrating data between MySQL and MongoDB, which is a core data engineering task.
  • Database Management: Setting up, managing, and interacting with both relational (MySQL) and non-relational (MongoDB) databases.
  • Data Population: Automatically generating and inserting a large dataset (~25,000 entries) into the databases.

Containerization/Virtualization

Utilizes Docker Compose to orchestrate multiple containers, ensuring a scalable and reproducible environment for development, testing, and deployment.

  • Docker Environment: The use of Docker Compose to orchestrate multiple containers (web server, MySQL, MongoDB, Nginx) is a key aspect of containerization.
  • Automation and Reproducibility: Docker ensures that the environment can be easily replicated, enhancing development, testing, and deployment processes.
  • Isolation: Each service runs in its own container, which isolates dependencies and simplifies management.

Prerequisites

  • Docker
  • Docker Compose
  • Python 3.x
  • pip

Getting Started

Docker Environment

The project leverages Docker Compose to set up and manage a multi-container Docker environment. This environment includes:

  • Web Server: Handles user interactions and serves the web application.
  • MySQL Server: Manages relational data storage and provides robust querying capabilities.
  • MongoDB Server: Manages non-relational data storage, offering flexible and scalable document-based storage.
  • Nginx Server: Acts as a reverse proxy to distribute traffic across the web application.

Setting Up the Environment

To set up and start the entire Docker environment, follow these steps:

  1. Build and Start Docker Containers: This command builds all necessary Docker containers and starts the services.

    docker compose build --no-cache

  2. Starts and executes the docker environment

    docker-compose up

The Docker Compose setup will automatically:

  • Build the web application and database containers.
  • Initialize the relational database schema in MySQL.
  • Populate the MySQL database with approximately 25,000 autogenerated data entries.

Web Interface

The web application provides an intuitive interface for managing the espionage agency's data. Key functionalities include:

  • Data Management: Users can interact with the relational data stored in MySQL.
  • Data Migration: Users can migrate data from MySQL to MongoDB with a single button click within the web interface. After migration the web application can be operated like before, the user will not notice that the data is now being stored and processed on a nonrelational database.
login_screen main_screen
Login and Main Window of Webinterface

Creating the Relational Database

If you need to manually create or drop the database schema, use the following commands:

  • CREATE Table Schema: bash:

      docker exec -i mysqldb mysql -u root -p'Schikuta<3' mysql_queensmandb < relational/mysql_scripts/imse_project_mysql_create.sql

    fish shell:

    cat relational/mysql_scripts/imse_project_mysql_create.sql | docker exec -i mysqldb mysql -u root -p'Schikuta<3' mysql_queensmandb

  • DROP Table Schema:

    bash:

      docker exec -i mysqldb mysql -u root -p'Schikuta<3' mysql_queensmandb < relational/mysql_scripts/imse_project_mysql_drop.sql

    fish shell:

    cat relational/mysql_scripts/imse_project_mysql_drop.sql | docker exec -i mysqldb mysql -u root -p'Schikuta<3' mysql_queensmandb

Insert Auto-Generated Database Data with Python Program

  1. Optional create virtual environment(e.g.: venv)

  2. Import all Dependancies:

     pip install -r backend/python/requirements.txt

  3. Run the Python Program:

     python3 backend/python/Queensman_imse_Insert.py

Migrate Relational Schema from MySQL to MongoDB

  1. Install Dependancies:

     pip install -r non_relational/requirements.txt

  2. Run the Migration Program:

     python3 non_relational/mongo_insert.py

  3. Connect to MongoShell to Check Migration:

     docker exec -it mongodb mongosh

  4. (Essential) Mongo Shell Commands:

    • Show all Databases:
    show dbs
    • Access to the Project Database:
    use queensmandb
    • List all Collections:
    show collections
    • Shows first Entries of a specified Collection:
    db.<specific_collection_name>.find().pretty()

    Example:

    db.employees.find().pretty()
    • Show specific Object corresponding to an id attribute
    db.<specific_collection_name>.findOne({ <collection_id_attribute_name>: <id_value> })

    Example:

    db.employees.findOne({ employee_id: 14 })

Reports for Data Analytics

The project includes two main reports implemented to assist in managing the espionage agency. Both were implemented as SQL and NoSQL querries respectively to what type of database is currently being used to hold the agencies data.

  1. Find Most Successful Agents Based on Unique Subjects:

    • Analyzes agents' success rates with unique subjects.

  2. List of History of Specific Employees' Past Assigned Branches and Possible Transfers:

    • Provides detailed information on employees' assignment history.

For detailed implementation and comparison of MySQL and MongoDB statements, refer to the documentation.

Documentation

For more details on the project, please refer to the reports in the project documentation.

Acknowledgement

This project was an extension of a previous web project written by QuirkyCroissant, and was further developed together with LOG1CBOMB as an assignment as part of the coursework for Information Management & Systems Engineering at the University of Vienna.

License

This project is licensed under the Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License. See the LICENSE file for details.