Exercice Modelisation

L'idée de cet exercice est d'arriver a modeliser un logiciel de gestion de condition général d'assurance vie. Une assurance vie est un document ayant les caractéristiques suivantes:

composé de titre/chapitre/sous-chapitre

composé de paragraphes qui sont des textes plus ou moins long

L'idée de ce logiciel est double:

permettre un partage entre les documents des titre/chapitres/sous chapitres/paragraphes.

permettre de faire des mises a jour groupé de documents.

En effet le contexte est qu'un paragraphe de contrat est un objet complexe a créer, et il semble une bonne idée que tant qu'a l'avoir crée il puisse être partagé avec plusieurs contrats.

 CREATE DATABASE IF NOT EXISTS `legitheque` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `legitheque`; -- MySQL dump 10.13 Distrib 5.5.16, for Win32 (x86) -- -- Host: localhost Database: legitheque -- ------------------------------------------------------ -- Server version 5.5.24

/*!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 utf8 */; /*!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 `node` --

DROP TABLE IF EXISTS `node`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `node` (

 `ID` bigint(20) NOT NULL AUTO_INCREMENT,

 `IDCONTRAT` bigint(20) NOT NULL,

 `ID_Chapitre` bigint(20) DEFAULT NULL,

 `ID_Paragraphe` bigint(20) DEFAULT NULL,

 PRIMARY KEY (`ID`),

 KEY `idx_contrat` (`IDCONTRAT`) USING HASH,

 KEY `idx_para` (`ID_Paragraphe`) USING HASH,

 KEY `idx_chap` (`ID_Chapitre`) USING HASH,

 KEY `fk_contrat` (`IDCONTRAT`),

 KEY `fk_chapitre` (`ID_Chapitre`),

 KEY `fk_para` (`ID_Paragraphe`),

 CONSTRAINT `fk_para` FOREIGN KEY (`ID_Paragraphe`) REFERENCES `paragraphe` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,

 CONSTRAINT `fk_chapitre` FOREIGN KEY (`ID_Chapitre`) REFERENCES `chapitre` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,

 CONSTRAINT `fk_contrat` FOREIGN KEY (`IDCONTRAT`) REFERENCES `contrat` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */;

-- -- Table structure for table `paragraphe` --

DROP TABLE IF EXISTS `paragraphe`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `paragraphe` (

 `ID` bigint(20) NOT NULL AUTO_INCREMENT,

 `VALUE` longtext NOT NULL,

 PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */;

-- -- Table structure for table `chapitre` --

DROP TABLE IF EXISTS `chapitre`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `chapitre` (

 `ID` bigint(20) NOT NULL AUTO_INCREMENT,

 `VALUE` varchar(50) NOT NULL,

 PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */;

-- -- Table structure for table `contrat` --

DROP TABLE IF EXISTS `contrat`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `contrat` (

 `ID` bigint(20) NOT NULL AUTO_INCREMENT,

 `NAME` varchar(50) NOT NULL,

 PRIMARY KEY (`ID`),

 UNIQUE KEY `NAME_UNIQUE` (`NAME`)

) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */;

-- -- Table structure for table `parentchildnode` --

DROP TABLE IF EXISTS `parentchildnode`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `parentchildnode` (

 `ID_parent` bigint(20) NOT NULL,

 `ID_child` bigint(20) NOT NULL,

 `order_node` int(11) NOT NULL DEFAULT '0',

 PRIMARY KEY (`ID_parent`,`ID_child`),

 KEY `idx_order` (`order_node`) USING BTREE,

 KEY `idx_node` (`ID_parent`) USING HASH,

 KEY `fk_parent` (`ID_parent`),

 KEY `fk_childe` (`ID_child`),

 CONSTRAINT `fk_childe` FOREIGN KEY (`ID_child`) REFERENCES `node` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,

 CONSTRAINT `fk_parent` FOREIGN KEY (`ID_parent`) REFERENCES `node` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 2013-09-16 22:12:22