README

REATE DATABASE IF NOT EXISTS centre_vaccination CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE centre_vaccination;

DROP VIEW IF EXISTS centres; DROP TABLE IF EXISTS rappels; DROP TABLE IF EXISTS vaccinations; DROP TABLE IF EXISTS campagnes; DROP TABLE IF EXISTS vaccins; DROP TABLE IF EXISTS patients; DROP TABLE IF EXISTS personnel; DROP TABLE IF EXISTS centres; DROP TABLE IF EXISTS centre_vaccination;

CREATE TABLE centre_vaccination ( id_centre INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(100) NOT NULL, adresse VARCHAR(150) NOT NULL, telephone VARCHAR(30) NOT NULL );

CREATE TABLE personnel ( id_personnel INT AUTO_INCREMENT PRIMARY KEY, id_centre INT NOT NULL, nom VARCHAR(80) NOT NULL, prenom VARCHAR(80) NOT NULL, fonction VARCHAR(80) NOT NULL, telephone VARCHAR(30), FOREIGN KEY (id_centre) REFERENCES centre_vaccination(id_centre) );

CREATE TABLE patients ( id_patient INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(80) NOT NULL, prenom VARCHAR(80) NOT NULL, date_naissance DATE NOT NULL, sexe ENUM('M', 'F') NOT NULL, telephone VARCHAR(30) NOT NULL, adresse VARCHAR(150) );

CREATE TABLE vaccins ( id_vaccin INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(100) NOT NULL, maladie_cible VARCHAR(100) NOT NULL, nombre_doses INT NOT NULL, delai_rappel_jours INT NOT NULL, stock INT NOT NULL DEFAULT 0 );

CREATE TABLE campagnes ( id_campagne INT AUTO_INCREMENT PRIMARY KEY, id_centre INT NOT NULL, nom VARCHAR(120) NOT NULL, date_debut DATE NOT NULL, date_fin DATE NOT NULL, objectif TEXT, statut ENUM('preparee', 'en_cours', 'terminee') NOT NULL DEFAULT 'preparee', FOREIGN KEY (id_centre) REFERENCES centre_vaccination(id_centre) );

CREATE TABLE vaccinations ( id_vaccination INT AUTO_INCREMENT PRIMARY KEY, id_patient INT NOT NULL, id_vaccin INT NOT NULL, id_campagne INT, date_vaccination DATE NOT NULL, dose_numero INT NOT NULL, observation TEXT, FOREIGN KEY (id_patient) REFERENCES patients(id_patient), FOREIGN KEY (id_vaccin) REFERENCES vaccins(id_vaccin), FOREIGN KEY (id_campagne) REFERENCES campagnes(id_campagne) );

CREATE TABLE rappels ( id_rappel INT AUTO_INCREMENT PRIMARY KEY, id_patient INT NOT NULL, id_vaccin INT NOT NULL, date_rappel DATE NOT NULL, statut ENUM('a_faire', 'envoye', 'fait') NOT NULL DEFAULT 'a_faire', FOREIGN KEY (id_patient) REFERENCES patients(id_patient), FOREIGN KEY (id_vaccin) REFERENCES vaccins(id_vaccin) );

& "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u root -p --default-character-set=utf8mb4 < schema_corrige.sqlINSERT INTO centre_vaccination (nom, adresse, telephone) VALUES ('Centre Principal', 'Avenue de la Sante', '0550000000');

INSERT INTO vaccins (nom, maladie_cible, nombre_doses, delai_rappel_jours, stock) VALUES ('BCG', 'Tuberculose', 1, 0, 100), ('DTP', 'Diphterie Tetanos Polio', 3, 30, 150), ('Grippe', 'Grippe saisonniere', 1, 365, 80), ('COVID-19', 'COVID-19', 2, 21, 200);

-- INSERT INTO patients (nom, prenom, date_naissance, sexe, telephone, adresse) -- Remplir les patients depuis l'application -- VALUES -- ('Nom', 'Prenom', 'AAAA-MM-JJ', 'M/F', 'XXXXXXXXXX', 'Adresse');

INSERT INTO campagnes (id_centre, nom, date_debut, date_fin, objectif, statut) VALUES (1, 'Campagne Grippe 2026', '2026-10-01', '2026-12-31', 'Vacciner les personnes fragiles et le personnel de sante.', 'preparee');