Skip to main content

Exercice Data WareHouse

Le but de cette exercice est de construire un data warehouse pour une micro société d'informatique. Cette société se compose ainsi:

  • Les personnes ont un nom,prenom et nom d'usage
  • Les personnes ont aussi une adresse ainsi que des contacts a prevenir en cas d'urgence.
  • Principalement, ils utilisent du matériel informatique qui peuvent être des PC.

Le schéma est le suivant :

 

Solution

 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';


DROP SCHEMA IF EXISTS orsysformation ;

CREATE SCHEMA IF NOT EXISTS orsysformation DEFAULT CHARACTER SET latin1 ;

USE orsysformation ;


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

-- Table orsysformation.ville

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

DROP TABLE IF EXISTS orsysformation.ville ;


CREATE TABLE IF NOT EXISTS orsysformation.ville (

 departement VARCHAR(3) NOT NULL ,
 INSEE VARCHAR(5) NOT NULL DEFAULT  ,
 commune VARCHAR(100) NOT NULL ,
 idville INT(11) NOT NULL AUTO_INCREMENT ,
 PRIMARY KEY (idville) ,
 INDEX IDX_DEPARTEMENT (departement ASC) )
ENGINE = InnoDB

AUTO_INCREMENT = 201

DEFAULT CHARACTER SET = latin1;



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

-- Table orsysformation.adresse

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

DROP TABLE IF EXISTS orsysformation.adresse ;


CREATE TABLE IF NOT EXISTS orsysformation.adresse (

 idadresse INT(11) NOT NULL AUTO_INCREMENT ,
 rue VARCHAR(50) NOT NULL COMMENT 'le nom de la rue avec le numéro' ,
 ville INT(11) NOT NULL DEFAULT '1' COMMENT 'Reference la table ville' ,
 PRIMARY KEY (idadresse) ,
 INDEX in_ville (ville ASC) ,
 CONSTRAINT fk_ville
   FOREIGN KEY (ville )
   REFERENCES orsysformation.ville (idville ))
ENGINE = InnoDB

AUTO_INCREMENT = 201

DEFAULT CHARACTER SET = latin1

COMMENT = 'Contient les informations sur les adresses des contact et du' /* comment truncated */;



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

-- Table orsysformation.application

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

DROP TABLE IF EXISTS orsysformation.application ;


CREATE TABLE IF NOT EXISTS orsysformation.application (

 idapplication INT(11) NOT NULL AUTO_INCREMENT ,
 name_application VARCHAR(20) NOT NULL ,
 type_application ENUM('bureautique','developpeur','financiere','autre') NOT NULL ,
 PRIMARY KEY (idapplication) )
ENGINE = InnoDB

AUTO_INCREMENT = 201

DEFAULT CHARACTER SET = latin1;



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

-- Table orsysformation.contacturgent

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

DROP TABLE IF EXISTS orsysformation.contacturgent ;


CREATE TABLE IF NOT EXISTS orsysformation.contacturgent (

 idContactUrgent INT(11) NOT NULL AUTO_INCREMENT ,
 nom VARCHAR(25) NOT NULL DEFAULT 'INCONNU' ,
 idadresse INT(11) NULL DEFAULT NULL ,
 telephone VARCHAR(10) NULL DEFAULT NULL ,
 relation ENUM('famille','autre') NULL DEFAULT NULL ,
 PRIMARY KEY (idContactUrgent) ,
 INDEX in_adresse (idadresse ASC) ,
 CONSTRAINT fk_adresse
   FOREIGN KEY (idadresse )
   REFERENCES orsysformation.adresse (idadresse )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB

AUTO_INCREMENT = 201

DEFAULT CHARACTER SET = latin1;



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

-- Table orsysformation.materiel_informatique

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

DROP TABLE IF EXISTS orsysformation.materiel_informatique ;


CREATE TABLE IF NOT EXISTS orsysformation.materiel_informatique (

 idmateriel INT(11) NOT NULL AUTO_INCREMENT ,
 typemateriel ENUM('PC','Laptop','Portable') NULL DEFAULT NULL ,
 daterevision DATE NULL DEFAULT NULL ,
 PRIMARY KEY (idmateriel) )
ENGINE = InnoDB

AUTO_INCREMENT = 201

DEFAULT CHARACTER SET = latin1;



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

-- Table orsysformation.materiel_pc

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

DROP TABLE IF EXISTS orsysformation.materiel_pc ;


CREATE TABLE IF NOT EXISTS orsysformation.materiel_pc (

 idmateriel INT(11) NOT NULL ,
 memoire INT(11) NOT NULL DEFAULT '0' ,
 disque INT(11) NOT NULL DEFAULT '0' ,
 processeur ENUM('pentium','core ix','autre') NOT NULL DEFAULT 'autre' ,
 ip VARCHAR(20) NULL DEFAULT NULL ,
 PRIMARY KEY (idmateriel) ,
 CONSTRAINT fk_materiel
   FOREIGN KEY (idmateriel )
   REFERENCES orsysformation.materiel_informatique (idmateriel ))
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1;



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

-- Table orsysformation.personne

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

DROP TABLE IF EXISTS orsysformation.personne ;


CREATE TABLE IF NOT EXISTS orsysformation.personne (

 idPersonne INT(11) NOT NULL AUTO_INCREMENT ,
 nom VARCHAR(25) NOT NULL ,
 prenom VARCHAR(25) NOT NULL ,
 nom_usage VARCHAR(25) NOT NULL ,
 PRIMARY KEY (idPersonne) ,
 INDEX fk_personnerh (idPersonne ASC) )
ENGINE = InnoDB

AUTO_INCREMENT = 201

DEFAULT CHARACTER SET = latin1;



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

-- Table orsysformation.personnerh

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

DROP TABLE IF EXISTS orsysformation.personnerh ;


CREATE TABLE IF NOT EXISTS orsysformation.personnerh (

 idPersonne INT(11) NOT NULL ,
 date_naissance DATE NULL DEFAULT NULL ,
 adresse_personnel INT(11) NULL DEFAULT NULL ,
 bureau VARCHAR(25) NULL DEFAULT NULL ,
 telephone VARCHAR(14) NOT NULL DEFAULT '0033600000000' ,
 telephone_mobile VARCHAR(14) NULL DEFAULT NULL ,
 sexe ENUM('M','F','XX') NULL DEFAULT 'XX' ,
 PRIMARY KEY (idPersonne) ,
 INDEX in_contacturgent (idPersonne ASC) ,
 INDEX in_personne (idPersonne ASC) ,
 INDEX in_adresse (adresse_personnel ASC) ,
 CONSTRAINT personnerh_ibfk_1
   FOREIGN KEY (idPersonne )
   REFERENCES orsysformation.personne (idPersonne )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT personnerh_ibfk_2
   FOREIGN KEY (adresse_personnel )
   REFERENCES orsysformation.adresse (idadresse )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1;



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

-- Table orsysformation.rel_materiel_personne

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

DROP TABLE IF EXISTS orsysformation.rel_materiel_personne ;


CREATE TABLE IF NOT EXISTS orsysformation.rel_materiel_personne (

 idpersonne INT(11) NOT NULL ,
 idmateriel INT(11) NOT NULL ,
 PRIMARY KEY (idmateriel) ,
 INDEX fk_materielinformatique (idmateriel ASC) ,
 INDEX in_personne (idpersonne ASC) ,
 CONSTRAINT fk_materielinformatique
   FOREIGN KEY (idmateriel )
   REFERENCES orsysformation.materiel_informatique (idmateriel )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT fk_personne
   FOREIGN KEY (idpersonne )
   REFERENCES orsysformation.personne (idPersonne )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1;



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

-- Table orsysformation.rel_personne_application

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

DROP TABLE IF EXISTS orsysformation.rel_personne_application ;


CREATE TABLE IF NOT EXISTS orsysformation.rel_personne_application (

 idpersonne INT(11) NOT NULL ,
 idapplication INT(11) NOT NULL ,
 login VARCHAR(20) NULL DEFAULT NULL ,
 password VARCHAR(20) NULL DEFAULT NULL ,
 PRIMARY KEY (idpersonne, idapplication) ,
 INDEX fk_rel_personne_application_personnerh1 (idpersonne ASC) ,
 INDEX in_applicatif (idapplication ASC) ,
 INDEX log_pass (login ASC, password ASC) ,
 CONSTRAINT fk_applicatif
   FOREIGN KEY (idapplication )
   REFERENCES orsysformation.application (idapplication )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT fk_rel_personne_application_personnerh1
   FOREIGN KEY (idpersonne )
   REFERENCES orsysformation.personne (idPersonne )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1;



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

-- Table orsysformation.relpersonnerhcontacturgent

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

DROP TABLE IF EXISTS orsysformation.relpersonnerhcontacturgent ;


CREATE TABLE IF NOT EXISTS orsysformation.relpersonnerhcontacturgent (

 idPersonneRH INT(11) NOT NULL ,
 idContactUrgent INT(11) NOT NULL ,
 PRIMARY KEY (idContactUrgent) ,
 INDEX in_personne (idPersonneRH ASC) ,
 CONSTRAINT fk_contacturgent
   FOREIGN KEY (idContactUrgent )
   REFERENCES orsysformation.contacturgent (idContactUrgent ),
 CONSTRAINT relpersonnerhcontacturgent_ibfk_1
   FOREIGN KEY (idPersonneRH )
   REFERENCES orsysformation.personnerh (idPersonne )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1;



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

-- Placeholder table for view orsysformation.parc_applicatif

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

CREATE TABLE IF NOT EXISTS orsysformation.parc_applicatif (nom INT, name_application INT, type_application INT, login INT, password INT);


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

-- Placeholder table for view orsysformation.vue_demenageur

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

CREATE TABLE IF NOT EXISTS orsysformation.vue_demenageur (idpersonne INT, nom INT, prenom INT, nom_usage INT, bureau INT, telephone INT);


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

-- View orsysformation.parc_applicatif

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

DROP VIEW IF EXISTS orsysformation.parc_applicatif ;

DROP TABLE IF EXISTS orsysformation.parc_applicatif;

USE orsysformation;

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW orsysformation.parc_applicatif AS select orsysformation.personne.nom AS nom,orsysformation.application.name_application AS name_application,orsysformation.application.type_application AS type_application,orsysformation.rel_personne_application.login AS login,orsysformation.rel_personne_application.password AS password from ((orsysformation.personne join orsysformation.rel_personne_application on((orsysformation.personne.idPersonne = orsysformation.rel_personne_application.idpersonne))) join orsysformation.application on((orsysformation.rel_personne_application.idapplication = orsysformation.application.idapplication)));


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

-- View orsysformation.vue_demenageur

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

DROP VIEW IF EXISTS orsysformation.vue_demenageur ;

DROP TABLE IF EXISTS orsysformation.vue_demenageur;

USE orsysformation;

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW orsysformation.vue_demenageur AS select orsysformation.personne.idPersonne AS idpersonne,orsysformation.personne.nom AS nom,orsysformation.personne.prenom AS prenom,orsysformation.personne.nom_usage AS nom_usage,orsysformation.personnerh.bureau AS bureau,orsysformation.personnerh.telephone AS telephone from (orsysformation.personne left join orsysformation.personnerh on((orsysformation.personne.idPersonne = orsysformation.personnerh.idPersonne)));

USE orsysformation;


DELIMITER $$


USE orsysformation$$

DROP TRIGGER IF EXISTS orsysformation.insert_personne $$

USE orsysformation$$

CREATE

DEFINER=root@localhost

TRIGGER orsysformation.insert_personne

AFTER INSERT ON orsysformation.personne

FOR EACH ROW

BEGIN

DECLARE mycontactId INTEGER;

insert into personnerh(idPersonne) values (NEW.idPersonne);

insert into contacturgent values ();

select MAX(contacturgent.idContactUrgent) from contacturgent into mycontactId;

insert into relpersonnerhcontacturgent values(NEW.idPersonne,mycontactId);

END$$



DELIMITER ;



SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Back to top