Business Management Database

Overview

This project implements a relational database for a company to manage employee information and departments. It includes employee details (e.g., AMKA/SSN, name, age, salary, phone), department attributes (name, budget), work allocations with time percentages, managerial hierarchies, and constraints like minimum age for managers (30+) and minimum salary (€30,000). The database enforces integrity rules, executes complex queries, and uses triggers to automatically adjust salaries and budgets during updates. Developed for the "Databases" course at the University of Piraeus, it covers schema design, data population, SQL queries, and advanced features like triggers.

Technologies

• Database: PostgreSQL
• Languages: SQL
• Tools: pgAdmin (for ERD generation), Data generators (e.g., Mockaroo or Faker for realistic data)

Features

• Relational schema with tables for employees, departments, work assignments, and managers.
• Integrity constraints: Primary/foreign keys, CHECK for age/salary/percentages, and custom triggers.
• Data loading with realistic entries using generators.
• Advanced SQL queries for reporting (e.g., employee-department overlaps, org charts, workforce calculations).
• Triggers: Automatic salary increases for managers and budget adjustments to maintain hierarchies and totals.
• Documentation of unimplementable constraints (if any) and full SQL scripts.

Installation

1. Clone the repository:

   git clone https://github.com/OzzYBcc/business-management-db.git
   

2. Navigate to the project directory:

   cd business-management-db
   

3. Set up PostgreSQL on your local machine (e.g., via pgAdmin or command line).
4. Run the SQL script to create tables and load data:

   psql -U your_username -d your_database -f database.sql
   

   (Replace your_username and your_database with your PostgreSQL credentials.)

Usage

• Schema Creation: Use the provided CREATE TABLE statements to build the database.
• Data Loading: Insert sample data via INSERT statements or CSV imports (generated with tools like Mockaroo).
• Example Query (from Question 2a - Employees in Two Specific Departments):

  SELECT e.name, e.age
  FROM employees e
  JOIN works_in w1 ON e.amka = w1.employee_amka
  JOIN works_in w2 ON e.amka = w2.employee_amka
  WHERE w1.department_name = 'Development' AND w2.department_name = 'Sales'
    AND w1.employee_amka = w2.employee_amka;

• Trigger Example (Salary Adjustment):

  CREATE OR REPLACE FUNCTION adjust_manager_salary() RETURNS TRIGGER AS $$
  BEGIN
      -- Logic to check and increase manager salary if needed
      -- Also update department budget
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  CREATE TRIGGER salary_update_trigger
  AFTER UPDATE OF salary ON employees
  FOR EACH ROW EXECUTE FUNCTION adjust_manager_salary();

• Run queries via pgAdmin or psql to generate reports like org charts or workforce analysis.

Contributing

Contributions are welcome! To add improvements (e.g., more queries or optimizations):

1. Fork the repository.
2. Create a new branch (git checkout -b feature-branch).
3. Commit your changes (git commit -m 'Add new query').
4. Push to the branch (git push origin feature-branch).
5. Open a pull request.

License

MIT License