-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Jeu 23 Février 2012 à 15:33
-- Version du serveur: 5.1.44
-- Version de PHP: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


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

--
-- Base de données: `dm`
--

-- --------------------------------------------------------

--
-- Structure de la table `client`
--

CREATE TABLE IF NOT EXISTS `client` (
  `nosiret` int(11) NOT NULL AUTO_INCREMENT,
  `nomclient` varchar(50) DEFAULT NULL,
  `adresseclient` varchar(50) DEFAULT NULL,
  `telephoneclient` int(11) DEFAULT NULL,
  `notype` int(11) DEFAULT NULL,
  `nofiliale` int(11) DEFAULT NULL,
  `nosecudefaut` bigint(15) DEFAULT NULL,
  PRIMARY KEY (`nosiret`),
  KEY `FK_client_notype` (`notype`),
  KEY `FK_client_nofiliale` (`nofiliale`),
  KEY `nosecudefaut` (`nosecudefaut`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=123456790 ;

--
-- Contenu de la table `client`
--

INSERT INTO `client` (`nosiret`, `nomclient`, `adresseclient`, `telephoneclient`, `notype`, `nofiliale`, `nosecudefaut`) VALUES
(123456781, 'BNP Paribas', 'Rue de paris Marseille', 123, 2, NULL, 123456789123458),
(123456782, 'EDF', 'avenue Jean jaures Paris', 123, 3, NULL, 123456789123459),
(123456783, 'Marie de paris', 'Place de l''H?¥tel de Ville PARIS 13', 123, 1, NULL, 123456789123461),
(123456785, 'France T?©l?©com Paris', 'rue de papin villepinte', 123, 5, 1, 123456789123461),
(123456786, 'France T?©l?©com Lyon', 'rue du temple Lyon', 123, 5, 1, 123456789123467),
(123456789, 'Total', 'Rue de france PARIS', 123, 4, NULL, 123456789123459);

-- --------------------------------------------------------

--
-- Structure de la table `employes`
--

CREATE TABLE IF NOT EXISTS `employes` (
  `nosecurite` bigint(15) NOT NULL,
  `nomconsultant` varchar(50) DEFAULT NULL,
  `prenomconsultant` varchar(50) DEFAULT NULL,
  `telephoneconsultant` int(11) DEFAULT NULL,
  `emailconsultant` varchar(50) DEFAULT NULL,
  `salairebrut` int(11) DEFAULT NULL,
  `nobureau` int(11) DEFAULT NULL,
  `noservice` int(11) DEFAULT NULL,
  `statut` varchar(50) NOT NULL,
  PRIMARY KEY (`nosecurite`),
  KEY `FK_consultant_noservice` (`noservice`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Contenu de la table `employes`
--

INSERT INTO `employes` (`nosecurite`, `nomconsultant`, `prenomconsultant`, `telephoneconsultant`, `emailconsultant`, `salairebrut`, `nobureau`, `noservice`, `statut`) VALUES
(123456789123456, 'Jean', 'Paul', 123, 'jean@dm.fr', 2000, 1, 1, 'oui'),
(123456789123457, 'Bousquet', 'Celine', 123, 'celine@dm.fr', 2200, 11, 1, 'oui'),
(123456789123458, 'Martin', 'Petit', 123, 'petit@dm.fr', 1500, 3, 2, 'oui'),
(123456789123459, 'Robert', 'Camille', 123, 'camille@dm.fr', 1800, 4, 2, 'oui'),
(123456789123461, 'Dubois', 'Guillaume', 123, 'guillame@dm.fr', 2100, 5, 2, 'non'),
(123456789123462, 'Roux', 'guy', 123, 'guy@dm.fr', 2100, 1, 3, 'oui'),
(123456789123463, 'Vincent', 'Paul', 123, 'paul@dm.fr', 2000, 1, 4, 'oui'),
(123456789123464, 'Muller', 'Marlene', 123, 'marlene@dm.fr', 2200, 11, 4, 'oui'),
(123456789123465, 'Simon', 'Petit', 123, 'petit1@dm.fr', 1500, 3, 4, 'non'),
(123456789123466, 'Robert', 'Lucas', 123, 'lucas@dm.fr', 1800, 4, 5, 'oui'),
(123456789123467, 'Denis', 'Guillaume', 123, 'guillame1@dm.fr', 2100, 5, 5, 'oui'),
(123456789123468, 'Dubois', 'guy', 123, 'guy1@dm.fr', 2100, 1, 4, 'oui'),
(123456789123469, 'Jean', 'David', 123, 'david@dm.fr', 2000, 1, 7, 'oui'),
(123456789123470, 'Bousquet', 'Francois', 123, 'francois@dm.fr', 2200, 11, 8, 'oui'),
(123456789123471, 'Martin', 'Nicolas', 123, 'nicolas@dm.fr', 1500, 3, 8, 'oui'),
(123456789123472, 'Lefevre', 'Andre', 123, 'andre@dm.fr', 1800, 4, 7, ''),
(123456789123473, 'Dubois', 'Lopez', 123, 'Lopez@dm.fr', 2100, 5, 8, 'oui'),
(123456789123474, 'Roux', 'Blanc', 123, 'blanc@dm.fr', 2100, 1, 3, 'oui');

-- --------------------------------------------------------

--
-- Structure de la table `filiale`
--

CREATE TABLE IF NOT EXISTS `filiale` (
  `nofiliale` int(11) NOT NULL AUTO_INCREMENT,
  `nomfiliale` varchar(50) DEFAULT NULL,
  `adressefiliale` varchar(50) DEFAULT NULL,
  `telephonefiliale` int(11) DEFAULT NULL,
  PRIMARY KEY (`nofiliale`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Contenu de la table `filiale`
--

INSERT INTO `filiale` (`nofiliale`, `nomfiliale`, `adressefiliale`, `telephonefiliale`) VALUES
(1, 'France T?©l?©com', 'rue du peuplier Paris', 123);

-- --------------------------------------------------------

--
-- Structure de la table `historiqueemployes`
--

CREATE TABLE IF NOT EXISTS `historiqueemployes` (
  `nohistorique` int(11) NOT NULL AUTO_INCREMENT,
  `datedentree` date DEFAULT NULL,
  `datechangment` date DEFAULT NULL,
  `noservice` int(11) NOT NULL,
  `nosecurite` bigint(15) NOT NULL,
  PRIMARY KEY (`nohistorique`),
  KEY `FK_historiqueconsultant_noservice` (`noservice`),
  KEY `FK_historiqueconsultant_nosecurite` (`nosecurite`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Contenu de la table `historiqueemployes`
--


-- --------------------------------------------------------

--
-- Structure de la table `projet`
--

CREATE TABLE IF NOT EXISTS `projet` (
  `noprojet` int(11) NOT NULL AUTO_INCREMENT,
  `nomprojet` varchar(50) DEFAULT NULL,
  `datedebut` date DEFAULT NULL,
  `datefin` date DEFAULT NULL,
  `budgetprevisonnel` varchar(50) DEFAULT NULL,
  `nosecurite` bigint(15) NOT NULL,
  `nosiret` int(11) NOT NULL,
  `parent` int(11) DEFAULT NULL,
  PRIMARY KEY (`noprojet`),
  KEY `nosecurite` (`nosecurite`),
  KEY `nosiret` (`nosiret`),
  KEY `parent` (`parent`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Contenu de la table `projet`
--

INSERT INTO `projet` (`noprojet`, `nomprojet`, `datedebut`, `datefin`, `budgetprevisonnel`, `nosecurite`, `nosiret`, `parent`) VALUES
(1, 'Projet France T?©l?©com 1', '2012-01-02', '2012-02-25', '30000', 123456789123461, 123456785, NULL),
(2, 'Projet France T?©l?©com 2', '2012-01-11', '2012-02-16', '12000', 123456789123464, 123456785, 1);

-- --------------------------------------------------------

--
-- Structure de la table `service`
--

CREATE TABLE IF NOT EXISTS `service` (
  `noservice` int(11) NOT NULL AUTO_INCREMENT,
  `nomservice` varchar(50) DEFAULT NULL,
  `villeservice` enum('paris','lyon','lille') DEFAULT NULL,
  `datecreation` date DEFAULT NULL,
  `nosecurite` bigint(15) DEFAULT NULL,
  PRIMARY KEY (`noservice`),
  KEY `FK_service_nosecurite` (`nosecurite`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Contenu de la table `service`
--

INSERT INTO `service` (`noservice`, `nomservice`, `villeservice`, `datecreation`, `nosecurite`) VALUES
(1, 'commerciale', 'paris', '2001-01-01', 123456789123456),
(2, 'technique', 'paris', '2001-01-01', 123456789123461),
(3, 'administration', 'paris', '2001-01-01', 123456789123462),
(4, 'commerciale', 'lyon', '2001-01-01', 123456789123464),
(5, 'technique', 'lyon', '2001-01-01', 123456789123467),
(7, 'commerciale', 'lille', '2001-01-01', 123456789123469),
(8, 'technique', 'lille', '2001-01-01', 123456789123471);

-- --------------------------------------------------------

--
-- Structure de la table `tache`
--

CREATE TABLE IF NOT EXISTS `tache` (
  `notache` int(11) NOT NULL AUTO_INCREMENT,
  `nomtache` varchar(50) DEFAULT NULL,
  `datedebutt` date DEFAULT NULL,
  `datefint` date DEFAULT NULL,
  `prixheure` int(11) DEFAULT NULL,
  `nosecurite` bigint(15) NOT NULL,
  `nosiret` int(11) NOT NULL,
  `idprojet` int(11) DEFAULT NULL,
  PRIMARY KEY (`notache`),
  KEY `nosecurite` (`nosecurite`),
  KEY `nosiret` (`nosiret`),
  KEY `idprojet` (`idprojet`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Contenu de la table `tache`
--

INSERT INTO `tache` (`notache`, `nomtache`, `datedebutt`, `datefint`, `prixheure`, `nosecurite`, `nosiret`, `idprojet`) VALUES
(1, 'T?¢che 1 France t?©l?©com', '2012-02-08', '2012-02-11', 15, 123456789123458, 123456785, 1),
(2, 'T?¢che 1 France t?©l?©com', '2012-01-11', '2012-02-01', 20, 123456789123461, 123456785, 1);

-- --------------------------------------------------------

--
-- Structure de la table `typedactivite`
--

CREATE TABLE IF NOT EXISTS `typedactivite` (
  `notype` int(11) NOT NULL AUTO_INCREMENT,
  `nomtype` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`notype`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Contenu de la table `typedactivite`
--

INSERT INTO `typedactivite` (`notype`, `nomtype`) VALUES
(1, 'marie'),
(2, 'banque'),
(3, 'energie'),
(4, 'p?©trole'),
(5, 't?©l?©com');

--
-- Contraintes pour les tables exportées
--

--
-- Contraintes pour la table `client`
--
ALTER TABLE `client`
  ADD CONSTRAINT `client_ibfk_1` FOREIGN KEY (`nosecudefaut`) REFERENCES `employes` (`nosecurite`),
  ADD CONSTRAINT `FK_client_nofiliale` FOREIGN KEY (`nofiliale`) REFERENCES `filiale` (`nofiliale`),
  ADD CONSTRAINT `FK_client_notype` FOREIGN KEY (`notype`) REFERENCES `typedactivite` (`notype`);

--
-- Contraintes pour la table `employes`
--
ALTER TABLE `employes`
  ADD CONSTRAINT `FK_consultant_noservice` FOREIGN KEY (`noservice`) REFERENCES `service` (`noservice`);

--
-- Contraintes pour la table `historiqueemployes`
--
ALTER TABLE `historiqueemployes`
  ADD CONSTRAINT `FK_historiqueconsultant_nosecurite` FOREIGN KEY (`nosecurite`) REFERENCES `employes` (`nosecurite`),
  ADD CONSTRAINT `FK_historiqueconsultant_noservice` FOREIGN KEY (`noservice`) REFERENCES `service` (`noservice`);

--
-- Contraintes pour la table `projet`
--
ALTER TABLE `projet`
  ADD CONSTRAINT `projet_ibfk_3` FOREIGN KEY (`parent`) REFERENCES `projet` (`noprojet`),
  ADD CONSTRAINT `projet_ibfk_1` FOREIGN KEY (`nosecurite`) REFERENCES `employes` (`nosecurite`),
  ADD CONSTRAINT `projet_ibfk_2` FOREIGN KEY (`nosiret`) REFERENCES `client` (`nosiret`);

--
-- Contraintes pour la table `service`
--
ALTER TABLE `service`
  ADD CONSTRAINT `FK_service_nosecurite` FOREIGN KEY (`nosecurite`) REFERENCES `employes` (`nosecurite`);

--
-- Contraintes pour la table `tache`
--
ALTER TABLE `tache`
  ADD CONSTRAINT `tache_ibfk_3` FOREIGN KEY (`idprojet`) REFERENCES `projet` (`noprojet`),
  ADD CONSTRAINT `tache_ibfk_1` FOREIGN KEY (`nosecurite`) REFERENCES `employes` (`nosecurite`),
  ADD CONSTRAINT `tache_ibfk_2` FOREIGN KEY (`nosiret`) REFERENCES `client` (`nosiret`);