BDD_CA - Task Management Database System

Author: Carl ATTARA

📋 Project Overview

This project implements a comprehensive task management database system designed to handle users, tasks, projects, and scoring mechanisms. The database supports task assignment, periodicity, categories, and progress tracking through a well-structured relational model.

🗂️ Project Structure

BDD_CA/
├── main.sql                 # Main script that orchestrates all other SQL files
├── SQL_Tables.sql          # Database schema creation (tables, constraints, views)
├── test.sql               # Sample data insertion for testing
├── requetes.sql          # SQL queries (Part 2 requirements)
├── Modele_relationnel.html # Detailed relational model documentation
└── README.md             # This documentation

🏗️ Database Architecture

Core Entities

1. Utilisateur (User)

   • Manages user accounts with authentication
   • Tracks user scores and personal information
   • Links to scoring programs

2. Tâche (Task)

   • Base task entity with inheritance pattern
   • Two specialized tables: Tache_en_cours (ongoing) and Tache_fini (completed)
   • Unified view through Taches view

3. Liste_tâche (Task List)

   • Organizes tasks into categorized lists
   • Owned by specific users

4. Périodicité (Periodicity)

   • Handles recurring task schedules
   • Configurable start/end dates and intervals

5. Score_catégorie_tâche (Task Category Scoring)

   • Scoring system for different task categories
   • Tracks completion status and points

Key Relationships

• Many-to-Many Relationships:

  • Users ↔ Tasks (through Est_assigne)
  • Tasks ↔ Task Lists (through Tache_appartenant_a_liste)
  • Tasks ↔ Tasks (through Depend_de for dependencies)
  • Users ↔ Projects (through Travaille)

• One-to-Many Relationships:

  • Users → Task Lists
  • Users → Tasks (creator)
  • Periodicity → Tasks
  • Categories → Tasks

🚀 Quick Start

1. Database Setup

Run the main script to set up the complete database:

@@./main.sql

This will execute in sequence:

1. SQL_Tables.sql - Creates all tables and constraints
2. test.sql - Inserts sample data
3. requetes.sql - Executes required queries

2. Manual Setup (Alternative)

If you prefer step-by-step execution:

-- 1. Create database structure
@@./SQL_Tables.sql

-- 2. Insert test data
@@./test.sql

-- 3. Run queries
@@./requetes.sql

📊 Database Schema Details

Main Tables

Utilisateur

- ref_utilisateur (PK, INT, AUTO_INCREMENT)
- login (UNIQUE, VARCHAR(10))
- mot_de_passe (VARCHAR(255))
- score (INT)
- nom, prenom (VARCHAR(255))
- adresse (VARCHAR(511))
- pays (VARCHAR(100))
- date_de_naissance (DATE)
- date_d_inscription (DATE)
- nom_programme (VARCHAR(255), FK to Comporte)

Task System (Inheritance Pattern)

Tache (Base)
├── Tache_en_cours (Ongoing tasks)
└── Tache_fini (Completed tasks)

Both inherit:

• Task details (title, description, priority, URL)
• Scheduling (due date, periodicity)
• Categorization and user assignment
• Status tracking

Liste_tâche

- ref_liste (PK, INT)
- nom_categorie (VARCHAR(255))
- ref_utilisateur (FK to Utilisateur)

Advanced Features

1. Task Dependencies: Tasks can depend on other tasks completion
2. Periodic Tasks: Configurable recurring tasks with intervals
3. Scoring System: Category-based point system for task completion
4. Project Management: Users can work on multiple projects
5. Task Lists: Organize tasks in custom categorized lists

🧪 Sample Data

The test.sql file includes sample data:

• User: Theo Mabouk (login: tmabouk86)
• Project: "Cisaux"
• Task: "Papier" (ongoing task in "Bureau" category)
• Periodicity: 2-day interval recurring task
• Scoring: "Important" category worth 100 points

Additional user: Jean Dupon (login: jdupon86) with "France 4" scoring program.

📝 Key Features

1. Flexible Task Management

• Create, assign, and track tasks
• Support for task dependencies
• Priority and deadline management
• URL attachments for task resources

2. User & Project Management

• Multi-user support with authentication
• Project-based collaboration
• Personal task lists and categories

3. Smart Scheduling

• Periodic task automation
• Flexible date/time handling
• Configurable recurrence patterns

4. Scoring & Gamification

• Category-based scoring system
• User score tracking
• Completion rewards

5. Data Integrity

• Comprehensive foreign key constraints
• Business rule enforcement
• Clean separation of concerns

🔍 Database Views

Taches View

Unified view combining ongoing and completed tasks:

CREATE VIEW Taches AS
  SELECT * FROM Tache_fini
  UNION
  SELECT * FROM Tache_en_cours;

🛠️ Technical Implementation

Database Design Patterns Used:

1. Table Inheritance (Tache → Tache_en_cours/Tache_fini)
2. Many-to-Many Relationships with junction tables
3. Self-referencing relationships (task dependencies)
4. Lookup tables for categories and scoring
5. Audit trails with creation/modification dates

Constraints & Data Integrity:

• Primary key constraints on all entities
• Foreign key relationships with proper cascading
• Unique constraints (user login)
• Check constraints for data validation
• NOT NULL constraints for required fields

📚 Documentation

The Modele_relationnel.html file contains detailed documentation of:

• Complete relational model
• Field descriptions and constraints
• Migration notes from conceptual to logical model
• Relationship explanations

🔧 Usage Examples

Create a new user:

INSERT INTO Utilisateur(login, mot_de_passe, nom, prenom, ...)
VALUES ('newuser', 'password123', 'Doe', 'John', ...);

Assign a task:

INSERT INTO Est_assigne(ref_utilisateur, ref_tache)
VALUES (user_id, task_id);

Query all user tasks:

SELECT t.*, u.nom, u.prenom 
FROM Taches t
JOIN Est_assigne ea ON t.ref_tache = ea.ref_tache
JOIN Utilisateur u ON ea.ref_utilisateur = u.ref_utilisateur
WHERE u.ref_utilisateur = ?;

📋 Requirements Fulfilled

This database system addresses typical task management requirements:

• ✅ User authentication and management
• ✅ Task creation, assignment, and tracking
• ✅ Project-based organization
• ✅ Recurring task scheduling
• ✅ Category-based organization
• ✅ Scoring and gamification
• ✅ Task dependencies
• ✅ Progress tracking
• ✅ Data integrity and consistency