Task Manager (MySQL)

Task Manager is a beginner-friendly SQL project designed to manage tasks, users, and categories using MySQL only.

It demonstrates the use of relational database design, stored procedures, and views — without relying on any external programming language.

Database Structure

The project includes three main tables:

• users → Stores user accounts.
• categories → Defines task categories.
• tasks → Contains all user tasks, linked to both users and categories.

Relationships

• Each task belongs to one user.
• Each task may optionally belong to a category.
• Deleting a user automatically deletes their tasks (DELETE CASCADE).
• Deleting a category sets the related tasks’ category to NULL (ON DELETE SET NULL).

Features

1. Stored Procedures

This project includes several stored procedures to perform CRUD operations directly inside MySQL:

• add_task() → title, description, limit_date, priority, id_user, id_category → Adds a new task
• edit_task() → id_task, new_title, new_description, new_priority → Updates an existing task
• delete_task() → id_task → Deletes a task
• update_task_state() → id_task, new_state → Changes a task’s state (pending/complete)

2. Views

Views were created to simplify common queries:

• v_pending_tasks → Shows all pending tasks
• v_high_priority_tasks → Shows only high-priority tasks
• v_tasks_by_user → Lists tasks grouped by user
• v_overdue_tasks → Shows pending tasks whose due date has passed

Example Usage

After importing the schema and procedures:

CALL add_task('Buy groceries', 'Milk, Bread, Eggs', '2025-11-05', 'medium', 1, 2);
CALL update_task_state(1, 'complete');
CALL delete_task(3);
SELECT * FROM v_pending_tasks;

Project Structure

Task-Manager-MySQL/

│

├── task_manager_schema.sql # Contains the database schema (tables + relationships)

│

├── procedures.sql # All stored procedures

│

├── views.sql # All view definitions

│

├── data.sql # Dummy data for testing

│

└── README.md

How to Use

1. Open MySQL Workbench (or any MySQL client).
2. Run the schema script to create the database and tables.
3. Execute the stored procedures and views scripts.
4. (Optional) Run the sample data script to insert test data.
5. Use the provided stored procedures and views to interact with your data.

Contributing

Pull requests are welcome! If you'd like to improve the project (e.g., add triggers, stored functions, or optimize queries), open an issue first to discuss your ideas.

License

MIT