Agency Database

An academic MySQL relational-database project modeling an event-agency scenario through personnel, clients, jobs, expenses, department salary records, and personnel audit logs.

Project Status

Academic database-design project presented to demonstrate:

• relational data modeling;
• SQL schema creation;
• primary-key and foreign-key constraints;
• multi-table relationships;
• stored procedures and row-level triggers;
• read-only demonstration queries; and
• ER diagram design.

The repository documents the original coursework scope. It is not presented as a client system or a production database.

Project Overview

The ajans database connects personnel with departments and cities, records clients and agency jobs, links each job to one personnel record and one expense record, categorizes expenses through receipt types, and stores department-level salary records.

Three AFTER triggers copy personnel snapshots into separate audit tables when personnel rows are inserted, updated, or deleted. Ten stored procedures provide filtered lookups and simple aggregate queries. None of the routines performs automatic payroll calculation.

Verified Scope

• MySQL-specific SQL dump originally exported from MySQL 8.0.31 with phpMyAdmin 5.2.0
• 11 tables and 11 primary keys
• 7 foreign-key constraints with supporting indexes
• 10 read-only stored procedures
• 3 AFTER triggers on the personel table
• 85 explicit synthetic seed rows
• Transaction wrapper and foreign-key checks for repeatable imports
• Editable Draw.io source and a recruiter-readable PNG export
• 6 read-only demo queries

Data Model

Table	Purpose
departmanlar	Department reference records.
sehirler	City reference records for personnel.
personel	Personnel details linked to a department and city.
musteriler	Synthetic client contact and company records.
isler	Jobs linked directly to one client, one personnel record, and one expense record.
fisler	Receipt or expense-category reference records.
giderler	Expense amount and date records linked to receipt categories.
maaslar	Dated salary-amount records linked to departments.
yeni_personeller	Personnel snapshots written after inserts.
guncel_personeller	Personnel snapshots written after updates.
eski_personeller	Personnel snapshots written after deletes.

Relationship Highlights

• personel.departman_id references departmanlar.departman_id.
• personel.sehir_id references sehirler.sehir_id.
• maaslar.departman_id references departmanlar.departman_id.
• giderler.fis_id references fisler.fis_id.
• isler references one expense, one client, and one personnel record through gider_id, musteri_id, and personel_id.
• The three personnel audit tables receive copied values through triggers; they do not have foreign-key constraints back to personel.

The schema does not include a junction table for many-to-many personnel assignments or a separate contract-history model.

ER Diagram

Open the editable Draw.io source.

Solid green relationships in the diagram are enforced foreign keys. Dashed amber relationships show trigger writes and are explicitly not foreign keys.

Stored Procedures

All ten stored procedures execute SELECT statements; they do not modify data.

Name	Purpose
departman_personel	Returns personnel rows for a department.
dep_per_is	Returns jobs, personnel names, and client companies for a department.
Is_gider	Returns the expense linked to a job.
musterinin_isleri	Returns jobs linked to a client.
personelSehir	Returns the city linked to a personnel record.
personel_is	Returns the personnel ID linked to a job.
ToplamIs	Counts job rows.
ToplamMaas	Sums recorded salary amounts.
ToplamPersonel	Counts personnel rows.
YasAralığındakiPersoneller	Returns personnel within an age range.

Triggers

Name	Event	Purpose
personel_ekleme	AFTER INSERT on personel	Copies the new personnel values to yeni_personeller.
personel_güncelleme	AFTER UPDATE on personel	Copies the updated personnel values to guncel_personeller.
personel_silinme	AFTER DELETE on personel	Copies the deleted personnel values to eski_personeller.

Example Queries

database/demo-queries.sql contains six read-only examples covering:

• personnel with department and city;
• jobs with personnel and client details;
• job expense and receipt-category details;
• salary records summarized by department;
• client activity; and
• personnel activity.

The stored procedures can also be inspected with calls such as:

CALL ToplamPersonel();
CALL musterinin_isleri(1);
CALL YasAralığındakiPersoneller(20, 30);

Repository Structure

Agency-Db/
├── .gitattributes
├── .gitignore
├── README.md
├── database/
│   ├── agency-database.sql
│   └── demo-queries.sql
└── docs/
    └── diagrams/
        ├── agency-er-diagram.drawio
        └── agency-er-diagram.png

Run Locally

Requirements:

• MySQL 8.x command-line client and server
• A MySQL account allowed to create the ajans database

git clone https://github.com/UAJOP/Agency-Db.git
cd Agency-Db
mysql -u root -p < database/agency-database.sql
mysql -u root -p ajans < database/demo-queries.sql

The main script creates and selects the ajans database, defines the tables, loads the synthetic sample data, adds the foreign keys, creates the stored procedures and triggers, and restores foreign-key checks at the end.

Sample Data and Privacy

The repository contains test data only. Person names, client companies, phone values, email addresses, and venue names use explicit synthetic placeholders. No credentials or production connection settings are required by the SQL scripts.

Known Limitations

• The schema reflects the original academic coursework scope.
• There is no application, API, authentication layer, or user interface.
• Each job stores one personnel reference; there is no many-to-many assignment table.
• Salary values are stored as department-linked records; payroll is not calculated automatically.
• The database is not a production accounting or financial system.
• Import execution was not verified in this repository-polish environment because no MySQL, MariaDB, or Docker runtime was available; the scripts received a static structure and reference audit.

Portfolio

• Agency Database project page
• Portfolio
• GitHub
• LinkedIn
• Email