-- MySQL dump 10.18  Distrib 10.3.27-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: bdMissionGSB02
-- ------------------------------------------------------
-- Server version	10.3.27-MariaDB-0+deb10u1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `famille`
--

DROP TABLE IF EXISTS `famille`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `famille` (
  `famCode` varchar(3) NOT NULL,
  `famLibelle` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`famCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `famille`
--

LOCK TABLES `famille` WRITE;
/*!40000 ALTER TABLE `famille` DISABLE KEYS */;
/*!40000 ALTER TABLE `famille` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `medicament`
--

DROP TABLE IF EXISTS `medicament`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `medicament` (
  `medDepotLegal` varchar(10) NOT NULL,
  `numMedic` varchar(3) DEFAULT NULL,
  `medNomCommercial` varchar(25) DEFAULT NULL,
  `famMedic` varchar(3) DEFAULT NULL,
  `medComposition` varchar(100) DEFAULT NULL,
  `medEffets` varchar(250) DEFAULT NULL,
  `medContreIndic` varchar(250) DEFAULT NULL,
  `medPrixEchantillon` char(5) DEFAULT NULL,
  PRIMARY KEY (`medDepotLegal`),
  KEY `famMedic` (`famMedic`),
  CONSTRAINT `medicament_ibfk_1` FOREIGN KEY (`famMedic`) REFERENCES `famille` (`famCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `medicament`
--

LOCK TABLES `medicament` WRITE;
/*!40000 ALTER TABLE `medicament` DISABLE KEYS */;
/*!40000 ALTER TABLE `medicament` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `observation`
--

DROP TABLE IF EXISTS `observation`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `observation` (
  `numPraticien` varchar(3) NOT NULL,
  `numMedi` varchar(3) NOT NULL,
  `commentairePraticien` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`numPraticien`,`numMedi`),
  CONSTRAINT `observation_ibfk_1` FOREIGN KEY (`numPraticien`) REFERENCES `praticien` (`praNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `observation`
--

LOCK TABLES `observation` WRITE;
/*!40000 ALTER TABLE `observation` DISABLE KEYS */;
/*!40000 ALTER TABLE `observation` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `praticien`
--

DROP TABLE IF EXISTS `praticien`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `praticien` (
  `praNum` varchar(3) NOT NULL,
  `praNom` varchar(25) DEFAULT NULL,
  `praPrenom` varchar(15) DEFAULT NULL,
  `metierPraticien` varchar(12) DEFAULT NULL,
  `praAdresse` varchar(30) DEFAULT NULL,
  `praVille` varchar(20) DEFAULT NULL,
  `praCP` varchar(5) DEFAULT NULL,
  `praCoeffNotoriete` char(7) DEFAULT NULL,
  `typcode` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`praNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `praticien`
--

LOCK TABLES `praticien` WRITE;
/*!40000 ALTER TABLE `praticien` DISABLE KEYS */;
INSERT INTO `praticien` VALUES ('1','Notini','Alain',NULL,'114 r Authie','LA ROCHE SUR YON','85000','290.03','MH'),('10','Lerat','Bernard',NULL,'31 r St Jean','LILLE','59000','257.79','PO'),('11','Marçais-Lefebvre','Bertrand',NULL,'86Bis r Basse','STRASBOURG','67000','450.96','MH'),('12','Boscher','Bruno',NULL,'94 r Falaise','TROYES','10000','356.14','MV'),('13','Morel','Catherine',NULL,'21 r Chateaubriand','PARIS','75000','379.57','PS'),('14','Guivarch','Chantal',NULL,'4 av Gén Laperrine','ORLEANS','45000','114.56','PH'),('15','Bessin-Grosdoit','Christophe',NULL,'92 r Falaise','NICE','6000','222.06','PO'),('16','Rossa','Claire',NULL,'14 av Thiès','NICE','6000','529.78','MH'),('17','Cauchy','Denis',NULL,'5 av Ste Thérèse','NARBONNE','11000','458.82','MV'),('18','Gaffé','Dominique',NULL,'9 av 1ère Armée Française','RENNES','35000','213.4','PS'),('19','Guenon','Dominique',NULL,'98 bd Mar Lyautey','NANTES','44000','175.89','PH'),('2','bbb','bbb','',NULL,NULL,NULL,NULL,NULL),('20','Prévot','Dominique',NULL,'29 r Lucien Nelle','LIMOGES','87000','151.36','PO'),('21','Houchard','Eliane',NULL,'9 r Demolombe','ANGERS','49100','436.96','MH'),('22','Desmons','Elisabeth',NULL,'51 r Bernières','QUIMPER','29000','281.17','MV'),('23','Flament','Elisabeth',NULL,'11 r Pasteur','RENNES','35000','315.6','PS'),('24','Goussard','Emmanuel',NULL,'9 r Demolombe','BLOIS','41000','40.72','PH'),('25','Desprez','Eric',NULL,'9 r Vaucelles','BORDEAUX','33000','406.85','PO'),('26','Coste','Evelyne',NULL,'29 r Lucien Nelle','TULLE','19000','441.87','MH'),('27','Lefebvre','Frédéric',NULL,'2 pl Wurzburg','VERDUN','55000','573.63','MV'),('28','Lemée','Frédéric',NULL,'29 av 6 Juin','VANNES','56000','326.4','PS'),('29','Martin','Frédéric',NULL,'Bât A 90 r Bayeux','VESOUL','70000','506.06','PH'),('3','Delahaye','André',NULL,'36 av 6 Juin','BESANCON','25000','185.79','PS'),('30','Marie','Frédérique',NULL,'172 r Caponière','VESOUL','70000','313.31','PO'),('31','Rosenstech','Geneviève',NULL,'27 r Auvergne','PARIS','75000','366.82','MH'),('32','Pontavice','Ghislaine',NULL,'8 r Gaillon','POITIERS','86000','265.58','MV'),('33','Leveneur-Mosquet','Guillaume',NULL,'47 av Robert Schuman','PAU','64000','184.97','PS'),('34','Blanchais','Guy',NULL,'30 r Authie','SEDAN','8000','502.48','PH'),('35','Leveneur','Hugues',NULL,'7 pl St Gilles','ARRAS','62000','7.39','PO'),('36','Mosquet','Isabelle',NULL,'22 r Jules Verne','ROUEN','76000','77.1','MH'),('37','Giraudon','Jean-Christophe',NULL,'1 r Albert de Mun','VIENNE','38100','92.62','MV'),('38','Marie','Jean-Claude',NULL,'26 r Hérouville','LYON','69000','120.1','PS'),('39','Maury','Jean-François',NULL,'5 r Pierre Girard','CHALON SUR SAONE','71000','13.73','PH'),('4','Leroux','André',NULL,'47 av Robert Schuman','BEAUVAIS','60000','172.04','PH'),('40','Dennel','Jean-Louis',NULL,'7 pl St Gilles','CHARTRES','28000','550.69','PO'),('41','Ain','Jean-Pierre',NULL,'4 résid Olympia','LAON','2000','5.59','MH'),('42','Chemery','Jean-Pierre',NULL,'51 pl Ancienne Boucherie','CAEN','14000','396.58','MV'),('43','Comoz','Jean-Pierre',NULL,'35 r Auguste Lechesne','BOURGES','18000','340.35','PS'),('44','Desfaudais','Jean-Pierre',NULL,'7 pl St Gilles','BREST','29000','71.76','PH'),('45','Phan','JérÃ´me',NULL,'9 r Clos Caillet','NIORT','79000','451.61','PO'),('46','Riou','Line',NULL,'43 bd Gén Vanier','MARNE LA VALLEE','77000','193.25','MH'),('47','Chubilleau','Louis',NULL,'46 r Eglise','SAINTES','17000','202.07','MV'),('48','Lebrun','Lucette',NULL,'178 r Auge','NANCY','54000','410.41','PS'),('49','Goessens','Marc',NULL,'6 av 6 Juin','DOLE','39000','548.57','PH'),('5','Desmoulins','Anne',NULL,'31 r St Jean','NIMES','30000','94.75','PO'),('50','Laforge','Marc',NULL,'5 résid Prairie','SAINT LO','50000','265.05','PO'),('51','Millereau','Marc',NULL,'36 av 6 Juin','LA FERTE BERNARD','72000','430.42','MH'),('52','Dauverne','Marie-Christine',NULL,'69 av Charlemagne','DIJON','21000','281.05','MV'),('53','Vittorio','Myriam',NULL,'3 pl Champlain','BOISSY SAINT LEGER','94000','356.23','PS'),('54','Lapasset','Nhieu',NULL,'31 av 6 Juin','CHAUMONT','52000','107','PH'),('55','Plantet-Besnier','Nicole',NULL,'10 av 1ère Armée Française','CHATELLEREAULT','86000','369.94','PO'),('56','Chubilleau','Pascal',NULL,'3 r Hastings','AURRILLAC','15000','290.75','MH'),('57','Robert','Pascal',NULL,'31 r St Jean','BOBIGNY','93000','162.41','MV'),('58','Jean','Pascale',NULL,'114 r Authie','SAUMUR','49100','375.52','PS'),('59','Chanteloube','Patrice',NULL,'14 av Thiès','MARSEILLE','13000','478.01','PH'),('6','Mouel','Anne',NULL,'27 r Auvergne','AMIENS','80000','45.2','MH'),('60','Lecuirot','Patrice',NULL,'résid St Pères 55 r Pigacière','NANCY','54000','239.66','PO'),('61','Gandon','Patrick',NULL,'47 av Robert Schuman','TOURS','37000','599.06','MH'),('62','Mirouf','Patrick',NULL,'22 r Puits Picard','ANNECY','74000','458.42','MV'),('63','Boireaux','Philippe',NULL,'14 av Thiès','CHALON EN CHAMPAGNE','10000','454.48','PS'),('64','Cendrier','Philippe',NULL,'7 pl St Gilles','RODEZ','12000','164.16','PH'),('65','Duhamel','Philippe',NULL,'114 r Authie','MONTPELLIER','34000','98.62','PO'),('66','Grigy','Philippe',NULL,'15 r Mélingue','CLISSON','44000','285.1','MH'),('67','Linard','Philippe',NULL,'1 r Albert de Mun','ALBI','81000','486.3','MV'),('68','Lozier','Philippe',NULL,'8 r Gaillon','TOULOUSE','31000','48.4','PS'),('69','Dechâtre','Pierre',NULL,'63 av Thiès','MONTLUCON','23000','253.75','PH'),('7','Desgranges-Lentz','Antoine',NULL,'1 r Albert de Mun','MORLAIX','29000','20.07','MV'),('70','Goessens','Pierre',NULL,'22 r Jean Romain','MONT DE MARSAN','40000','426.19','PO'),('71','Leménager','Pierre',NULL,'39 av 6 Juin','METZ','57000','118.7','MH'),('72','Née','Pierre',NULL,'39 av 6 Juin','MONTAUBAN','82000','72.54','MV'),('73','Guyot','Pierre-Laurent',NULL,'43 bd Gén Vanier','MENDE','48000','352.31','PS'),('74','Chauchard','Roger',NULL,'9 r Vaucelles','MARSEILLE','13000','552.19','PH'),('75','Mabire','Roland',NULL,'11 r Boutiques','STRASBOURG','67000','422.39','PO'),('76','Leroy','Soazig',NULL,'45 r Boutiques','ALENCON','61000','570.67','MH'),('77','Guyot','Stéphane',NULL,'26 r Hérouville','FIGEAC','46000','28.85','MV'),('78','Delposen','Sylvain',NULL,'39 av 6 Juin','DREUX','27000','292.01','PS'),('79','Rault','Sylvie',NULL,'15 bd Richemond','SOISSON','2000','526.6','PH'),('8','Marcouiller','Arnaud',NULL,'31 r St Jean','MULHOUSE','68000','396.52','PS'),('80','Renouf','Sylvie',NULL,'98 bd Mar Lyautey','EPINAL','88000','425.24','PO'),('81','Alliet-Grach','Thierry',NULL,'14 av Thiès','PRIVAS','7000','451.31','MH'),('82','Bayard','Thierry',NULL,'92 r Falaise','SAINT ETIENNE','42000','271.71','MV'),('83','Gauchet','Thierry',NULL,'7 r Desmoueux','GRENOBLE','38100','406.1','PS'),('84','Bobichon','Tristan',NULL,'219 r Caponière','FOIX','9000','218.36','PH'),('85','Duchemin-Laniel','Véronique',NULL,'130 r St Jean','LIBOURNE','33000','265.61','PO'),('86','Laurent','Younès',NULL,'34 r Demolombe','MAYENNE','53000','496.1','MH'),('9','Dupuy','Benoit',NULL,'9 r Demolombe','MONTPELLIER','34000','395.66','PH');
/*!40000 ALTER TABLE `praticien` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `rapportVisite`
--

DROP TABLE IF EXISTS `rapportVisite`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `rapportVisite` (
  `rapNum` char(5) NOT NULL,
  `rapDate` date NOT NULL,
  `rapBilan` varchar(500) NOT NULL,
  `rapMotif` varchar(30) NOT NULL,
  `idVisiteur` int(10) unsigned NOT NULL,
  `numPra` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`rapNum`),
  KEY `fk_idVisiteur` (`idVisiteur`),
  KEY `fk_numPra` (`numPra`),
  CONSTRAINT `fk_idVisiteur` FOREIGN KEY (`idVisiteur`) REFERENCES `utilisateur` (`id`),
  CONSTRAINT `fk_numPra` FOREIGN KEY (`numPra`) REFERENCES `praticien` (`praNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `rapportVisite`
--

LOCK TABLES `rapportVisite` WRITE;
/*!40000 ALTER TABLE `rapportVisite` DISABLE KEYS */;
/*!40000 ALTER TABLE `rapportVisite` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `utilisateur`
--

DROP TABLE IF EXISTS `utilisateur`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `utilisateur` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `roleUtilisateur` char(11) NOT NULL CHECK (`roleUtilisateur` in ('visiteur','responsable')),
  `nom` varchar(32) NOT NULL,
  `prenom` varchar(32) NOT NULL,
  `mail` varchar(32) NOT NULL,
  `login` varchar(20) NOT NULL,
  `mdp` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `utilisateur`
--

LOCK TABLES `utilisateur` WRITE;
/*!40000 ALTER TABLE `utilisateur` DISABLE KEYS */;
INSERT INTO `utilisateur` VALUES (2,'visiteur','aaa','aaa','aaa@gmail.com','aaa','hD2t4cBmjbpgM'),(4,'responsable','bbb','bbb','bbb@bbb.com','bbb','X7z92atO3sqxE');
/*!40000 ALTER TABLE `utilisateur` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-05-28 10:51:21
