Database Lab Project 2024

This repository contains scripts and data for the Database Lab Project 2024, focused on setting up, populating, and testing a PostgreSQL database environment.

---

Commit Guidelines

• Write commit messages in English.
• Follow the Conventional Commits specification.

---

Setup Instructions

Prerequisites

• Docker (Ensure Docker is installed and running)

Clone the Repository

git clone https://github.com/mrBymax/database-lab-project-2024
cd database-lab-project-2024

Linux/MacOS

1. Make the setup script executable:

chmod +x run.sh

2. Run the setup script:

./run.sh

Windows

1. Ensure Docker is running.
2. Run the batch script:

.\run.bat

These scripts will:

• Build database tables, triggers, and populate them with sample data.
• Run test cases and statistical scripts.

---

Database Connection Details

After successful execution of the setup scripts, the database is populated with sample data and accessible using these credentials:

Credential	Value
Host	localhost
Port	6432 (custom)
Username	db
Password	db
Database	db

⚠️ Security Notice:
Credentials are stored in the .env file. Do NOT deploy this database publicly without changing default credentials, as it poses a security risk.

---

Output and Logs

• All outputs, including SQL test results and R statistical analyses, are stored in the directory specified by the environment variable OUT_DIR (default: ./output).

• Key files in the output directory:

  • 02a-query-examples.txt: Summarizes queries executed. The summary is found at the end of the file.
  • 02b-constraint-test.txt: Summarizes constraint test results. The summary is found at the end of the file.

---

Recommended Database Exploration Tool

• Beekeeper Studio (Free, Open-Source)
  • Graphical interface for PostgreSQL database exploration.
  • Use the .env credentials to connect.

---

Project Structure Overview

├───data-sources
│       cities.txt
│       ...
│
├───DB_Lab_Report
│   │   DB_Lab_Report.pdf  # The project report
│   │   ...
│
├───output
│   │   01-populate.txt
│   │   02a-query-examples.txt  # Summary and results of queries
│   │   02b-constraint-test.txt  # Summary and results of constraint tests
│   │   03-Q1Stats.txt
│   │   04-Q2Stats.txt
│   │   05-Q3Stats.txt
│   │   06-Q4Stats.txt
│   │
│   └───plots
│           q1_married_data_pie_chart.png
│           q2_top_10_number_of_workers_per_job_title.png
│           q3_top_10_projects_by_skills.png
│           q4_employees_per_city.png
│
└───scripts
    ├───r_scripts
    │       01-populate.r
    │       02a-query-examples.r
    │       02b-constraint-test.r
    │       03-Q1Stats.r
    │       04-Q2Stats.r
    │       05-Q3Stats.r
    │       06-Q4Stats.r
    │       DESCRIPTION  # R package description
    │
    └───sql_scripts
            01-init.sql
            02-triggers.sql
            03-queries.sql
            04-test-triggers.sql