[DATABASE] Create a database

[madelahay]

Création de la base de données :

Tables :

• Projets
• Utilisateurs (relation avec projets)
• Equipe
• Achats
• Documents

Projets :

• codeProjet
• Nom
• Description
• Objectifs
• Deadline (date)
• Budget (float)
• Membres (relation vers employés)
• Achats
• Documents

-> relation membres, correspondant à une association entre Projet et Employés car un employé peut avoir plusieurs projets. -> Achats relation avec la table achats -> documents relation avec la table documents

Employés :

• id
• Equipe (0..1)
• Nom
• Prénom
• Compétences
• Status (senior ou junior)

Utilisateurs :

• id
• Nom d'utilisateur
• Mot de passe
• Relation projets

Achats :

• id
• Titre
• Description
• Prix
• Numéro de facture

-> relation entre projet et utilisateurs car un utilisateur peut avoir accès à plusieurs projets en gestion

Documents :

• id
• Titre
• Description
• contenu (lien)

[madelahay]

[DATABASE SCHEME]

Projects Table

project_id: SERIAL PRIMARY KEY name: VARCHAR(255) NOT NULL description: TEXT objectives: TEXT deadline: DATE budget: FLOAT Employees Table

employee_id: SERIAL PRIMARY KEY team_id: INT REFERENCES Teams(team_id) -- Assuming you have a Teams table. first_name: VARCHAR(255) NOT NULL last_name: VARCHAR(255) NOT NULL skills: TEXT status: VARCHAR(50) CHECK (status IN ('senior', 'junior')) Users Table

user_id: SERIAL PRIMARY KEY username: VARCHAR(255) UNIQUE NOT NULL password: TEXT NOT NULL -- Store hashed passwords, never plain text. Teams Table

team_id: SERIAL PRIMARY KEY name: VARCHAR(255) NOT NULL Purchases Table

purchase_id: SERIAL PRIMARY KEY project_id: INT REFERENCES Projects(project_id) title: VARCHAR(255) NOT NULL description: TEXT price: DECIMAL(10, 2) NOT NULL invoice_number: VARCHAR(255) Documents Table

document_id: SERIAL PRIMARY KEY project_id: INT REFERENCES Projects(project_id) title: VARCHAR(255) NOT NULL description: TEXT link: TEXT NOT NULL -- Can store a URL or a file path. ProjectMembers (Junction Table for Many-to-Many Relationship between Projects and Employees)

project_id: INT REFERENCES Projects(project_id) employee_id: INT REFERENCES Employees(employee_id) Composite PRIMARY KEY (project_id, employee_id) ProjectUsers (Junction Table for Many-to-Many Relationship between Projects and Users)

project_id: INT REFERENCES Projects(project_id) user_id: INT REFERENCES Users(user_id) Composite PRIMARY KEY (project_id, user_id)

[madelahay]

[POSSIBLE SCRIPT]

-- Create Projects Table CREATE TABLE Projects ( project_id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, description TEXT, objectives TEXT, deadline DATE, budget FLOAT );

-- Create Teams Table CREATE TABLE Teams ( team_id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL );

-- Create Employees Table CREATE TABLE Employees ( employee_id SERIAL PRIMARY KEY, team_id INT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, skills TEXT, status VARCHAR(50) CHECK (status IN ('senior', 'junior')), FOREIGN KEY (team_id) REFERENCES Teams(team_id) );

-- Create Users Table CREATE TABLE Users ( user_id SERIAL PRIMARY KEY, username VARCHAR(255) UNIQUE NOT NULL, password TEXT NOT NULL -- Ensure this is a hashed password );

-- Create Purchases Table CREATE TABLE Purchases ( purchase_id SERIAL PRIMARY KEY, project_id INT, title VARCHAR(255) NOT NULL, description TEXT, price DECIMAL(10, 2) NOT NULL, invoice_number VARCHAR(255), FOREIGN KEY (project_id) REFERENCES Projects(project_id) ON DELETE CASCADE );

-- Create Documents Table CREATE TABLE Documents ( document_id SERIAL PRIMARY KEY, project_id INT, title VARCHAR(255) NOT NULL, description TEXT, link TEXT NOT NULL, -- This should be a URL or a path to the document FOREIGN KEY (project_id) REFERENCES Projects(project_id) ON DELETE CASCADE );

-- Create ProjectMembers Junction Table for Many-to-Many relationship between Projects and Employees CREATE TABLE ProjectMembers ( project_id INT, employee_id INT, PRIMARY KEY (project_id, employee_id), FOREIGN KEY (project_id) REFERENCES Projects(project_id) ON DELETE CASCADE, FOREIGN KEY (employee_id) REFERENCES Employees(employee_id) ON DELETE CASCADE );

-- Create ProjectUsers Junction Table for Many-to-Many relationship between Projects and Users CREATE TABLE ProjectUsers ( project_id INT, user_id INT, PRIMARY KEY (project_id, user_id), FOREIGN KEY (project_id) REFERENCES Projects(project_id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE );