Contingut

1 Disseny i implentació d'un sistema de gestió de BBDD

Disseny i implentació d'un sistema de gestió de BBDD

Guillem Solà i Boeck (host: PC05)

Introducció

Hem de crear una base de dades per a gestionar i mantenir un control dels tractaments mèdics que s'apliquen als animals d'una explotació ramadera. Ens interessa poder tenir un control de l'stock de medicaments, saber quins medicaments es necessiten per a cada tractament i tenir constància de qui ha administrat un medicament a un animal. Per a modificar i accedir a les dades ho farem a través de procediments i funcions que implementaré de manera que l'usuari final només hagi d'utilitzar aquests procediments/funcions amb diversos paràmetres per tal de tenir la informació que desitgi. També s'afegiran procediments de manera que es puguin exportar llistats a PDF o CSV per a imprimir.

Anàlisi del sistema

Definició del sistema

Necessitarem un ordinador que albergui el software gestor de la base de dades.

Sistema operatiu: - CentOS 7
Sistema gestor de la base de dades: - MariaDB
Software de desenvolupament i manteniment de la base de dades: - MySQL Workbench

Requisits del projecte

Legals

A la base de dades desarem informació referent a persones només per a tenir un control de qui hagi administrat un medicament a un animal. Tal com s'ha definit anteriorment, a la taula de persones, no hi desem informació sotmesa a la Llei de Protecció de Dades; per tant, no es tindrà en compte el compliment de la LOPD en aquest projecte.

Propietat intel·lectual i llicències

El software que s'usarà és tot open source i per tant es pot fer l'ús que se'n vulgui.

Pel que fa al sistema operatiu, està desenvolupat i distribuït sota la llicència GPL principalment, és gratuït i, per tant, no es necessita cap permís addicional per a utilitzar-lo.
Respecte al SGBD MariaDB també està distribuït sota llicència GNU/GPL. Per tant podem usar aquest software sense cap cost i, fins i tot, podríem modificar-ne el codi font si necessitéssim adaptar-lo a alguna necessitat molt específica.
Finalment, el software des del que farem el manteniment també està sotmès a la llicència GNU/GPL2 com la resta de programari necessari per a realitzar aquest projecte.

Es pot trobar més informació sobre aquesta llicència a la següent pàgina web: http://www.gnu.org/licenses/old-licenses/gpl-2.0.html

Accés

El SGBD s'assegurarà mitjançant usuaris per a cada persona que pugui tenir accés al sistema. Tots els usuaris tindran una contrasenya diferent per a autenticar-se.

Gestió de còpies de seguretat

Mètode: El sistema de backups es durà a terme mitjançant la comanda que incorpora MariaDB mysqldump. Es mantindrà una còpia en local i s'enviarà una còpia de l'arxiu resultant a un servidor extern a l'empresa per a tenir encara més seguretat de la còpia en cas de robatori d'equip informàtic, incendis, inundacions, etc. També s'implementarà un sistema de rotació de les còpies de manera que les còpies més antigues s'eliminin del sistema i es vagin mantenint les més recents.
Freqüència: S'estudiarà la freqüència amb la que cal fer les còpies per tal de trobar un equilibri entre espai consumit pels backups i aconseguir la menor pèrdua de dades possible (idealment 0) en cas de necessitat de restaurar d'emergència. S'haurà de tenir en compte que l'espai consumit al principi, mentre la BBDD sigui petita, serà tant baix que permetrà disposar de molts backups sense haver-nos de preocupar de l'espai; però a mida que vagi creixent, s'haurà d'anar arranjant el pla de còpies per a gestionar l'espai consumit ajustant la freqüència amb la que farem còpies i la rotació d'aquestes còpies.
Programació: S'utilitzarà el cron ja inclòs en el sistema operatiu per tal d'agendar els processos de còpia de seguretat, l'enviament de l'arxiu a través de scp cap a un altre servidor remot i la rotació de les còpies.

Manteniment i administració

Hi ha dues opcions possibles:

El client s'encarregarà de formar a una persona responsable del manteniment i administració del sistema. El pressupost d'aquest projecte inclou unes hores de formació i explicació del funcionament del sistema per a aquesta persona.

Contractar un pla de manteniment amb un pack d'hores mensual a la nostra empresa.

En el cas que el client esculli la segona opció, es farà un pressupost (extern a aquest projecte) adequat al tamany del sistema garantint unes hores de manteniment mensuals màximes que podrà dedicar la nostra empresa per a fer manteniment i administració del sistema.

Definició d'interfícies d'usuari

La interacció entre el sistema gestor de la base de dades i els usuaris es farà a través del programa MySQL Workbench ja que és una opció més atractiva de cara a l'usuari que no pas una interfície en mode text. Com que el sistema està orientat a usuaris no-tècnics, crec que serà la millor opció ja que una bona interfície gràfica dóna sempre més seguretat que no pas una interfície de línia d'ordres, per a aquest tipus d'usuaris. Més endavant es podria plantejar un aplicatiu web mitjançant PHP que encara permetria tenir una GUI més polida i ajustada a les necessitats dels usuaris. Els registres de les transaccions i consultes es desaran a la taula de logs que podrà ser revisada pel responsable o l'administrador del sistema per a poder diagnosticar possibles errors o dubtes que puguin sorgir al principi de la implantació del sistema.

Especificació del pla de proves

Disseny del sistema

Model conceptual

S'ha produït un error en crear la miniatura: No es pot desar la miniatura a la destinació

Model lògic de les taules

animal(idAnimal, dib, dataNaixement): PK:idAnimal

administracio(idAdministracio, idAnimal, idPersona, idTractament, data): PK:idAdministracio; FK:idAnimal ⇨ animal(idAnimal); FK:idPersona⇨persona(idPersona); FK:idTractament⇨tractament(idTractament)

persona(idPersona, tipusPersona, nom): PK:idPersona; FK:tipusPersona ⇨ tipusPersona(idTipusPersona)

tipusPersona(idTipusPersona, descripcio): PK:idTipusPersona

tractament(idTractament, descripcio): PK:idTractament

medicament(idMedicament, descripcio, stock, dosi, tempsEsperaCarn, tempsEsperaLlet): PK:idMedicament

medicaments_dels_tractaments(idMedicament,idTractament): PK:(idMedicament,idTractament); FK:idMedicament ⇨ medicament(idMedicament); FK:idTractament ⇨ tractament(idTractament)

logs(idLog, data, contingut): PK:idLog

Descripció lògica de les taules

ANIMAL
Atribut	Descripció	Tipus de dada	NULL	Observació
idAnimal	Identificador de l'animal	CHAR(4)	NO	Clau primària
dib	Document Identificador Boví	CHAR(20)	NO	Únic
dataNaixement	Data de naixement de l'animal	DATE	NO

ADMINISTRACIO
Atribut	Descripció	Tipus de dada	NULL	Observació
idAdministracio	Codi de l'administració de medicament	CHAR(4)	NO	Clau primària
idAnimal	Codi animal	CHAR(4)	NO	Clau externa
idPersona	Codi persona	CHAR(4)	NO	Clau externa
idTractament	Codi tractament	CHAR(4)	NO	Clau externa
data	Data de l'administració del medicament	DATE	NO

PERSONA
Atribut	Descripció	Tipus de dada	NULL	Observació
idPersona	Codi de persona	CHAR(4)	NO	Clau primària
tipusPersona	Codi tipus persona	CHAR(4)	NO	Clau externa
nom	Nom de la persona	VARCHAR(45)	NO

TIPUSPERSONA
Atribut	Descripció	Tipus de dada	NULL	Observació
idTipusPersona	Codi del tipus de persona	CHAR(4)	NO	Clau primària
tipusPersona	Codi tipus persona	CHAR(4)	NO	Clau externa
descripcio	Descripció tipus de persona	VARCHAR(45)	NO

TRACTAMENT
Atribut	Descripció	Tipus de dada	NULL	Observació
idTractament	Codi del tractament	CHAR(4)	NO	Clau primària
descripcio	Nom del tractament	VARCHAR(45)	NO

MEDICAMENT
Atribut	Descripció	Tipus de dada	NULL	Observació
idMedicament	Codi del medicament	CHAR(4)	NO	Clau primària
descripcio	Nom del medicament	VARCHAR(45)	NO
stock	Quantitat disponible del medicament	INT	NO
dosi	Dosi normal d'administració del medicament	FLOAT(4,2)	NO	En mm
tempsEsperaCarn	Temps d'exclusió per a consum de la carn	INT	SÍ	En dies
tempsEsperaLlet	Temps d'exclusió per a consum de la llet	INT	SÍ	En dies

MEDICAMENTS_DELS_TRACTAMENTS
Atribut	Descripció	Tipus de dada	NULL	Observació
idMedicament	Codi del medicament	CHAR(4)	NO	Clau externa	Clau primària
idTractament	Codi del tractament	CHAR(4)	NO	Clau externa	Clau primària

LOGS
Atribut	Descripció	Tipus de dada	NULL	Observació
idLog	Codi de registre	CHAR(4)	NO	Clau primària
data	Data de registre	DATETIME	NO
contingut	Contingut del registre	VARCHAR(250)	NO

Scripts de prova

Una vegada instal·lat el servidor i creada la base de dades, la importarem a la màquina final per a implantar la base de dades inicial amb unes dades per defecte, si s'escau.

Revisió de casos d'ús per subsistema

Entenem un subsistema com a un procediment per a consultar, inserir o exportar les dades. El funcionament genèric dels procediments funcionarà de la següent manera:

CALL nom_procediment(paràmetres);

D'aquesta manera, nosaltres, com a administradors del SGBD crearem els procediments per a gestionar la informació i l'usuari només haurà d'aprendre el nom dels procediments per a realitzar determinades accions i els seus paràmetres. Això ofereix un nivell d'abstracció de cara a l'usuari que facilita l'aprenentatge i el funcionament sense necessitat d'aprendre a fer consultes complicades; tot i que sempre té l'opció de fer consultes "crues" des de la interfície d'administració de la BBDD.

Requisits d'implantació

Per tal de tirar endavant la implantació en un entorn de proves, necessitarem complir els següents punts:

Màquina virtual amb el sistema operatiu CentOS 7 instal·lat ^[1]
MariaDB com a sistema gestor de la base de dades
MySQLWorkbench com a entorn gràfic d'administració de MariaDB

Notes

↑ No necessàriament amb interfície gràfica ja que serà un servidor dedicat que controlarem remotament via SSH i MySQLWorkbench per a gestió de MariaDB; de manera que podria ser un server headless

Desenvolupament

Entorn de desenvolupament

L'entorn de desenvolupament usat serà un ordinador amb Ubuntu 16.04.4.
El SGBD serà MariaDB 15.1 i com a entorn per a generar la BBDD de desenvolupament utilitzarem el mateix MySQLWorkbench.

Base de dades

Farem el disseny de la BBDD amb MySQLWorkbench

Exportarem el disseny cap a la BBDD de proves amb el mètode de forward engineer

Veurem una previsualització de l'script que generarà la nostra BBDD

Un cop acabat el procediment, tindrem el missatge que tot ha anat correctament

El contingut de l'script SQL que genera la BBDD d'aquest projecte amb les seves taules i restriccions de clau externa que hem dissenyat mitjançant el WorkBench és el següent

-- MySQL Workbench Forward Engineering

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,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema projecte
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema projecte
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `projecte` DEFAULT CHARACTER SET utf8 ;
USE `projecte` ;

-- -----------------------------------------------------
-- Table `projecte`.`animal`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `projecte`.`animal` (
  `idAnimal` CHAR(4) NOT NULL,
  `dib` CHAR(20) NOT NULL,
  `dataNaixement` DATE NOT NULL DEFAULT now(),
  PRIMARY KEY (`idAnimal`),
  UNIQUE INDEX `dib_UNIQUE` (`dib` ASC),
  UNIQUE INDEX `idAnimal_UNIQUE` (`idAnimal` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `projecte`.`medicament`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `projecte`.`medicament` (
  `idMedicament` CHAR(4) NOT NULL,
  `descripcio` VARCHAR(45) NOT NULL,
  `stock` INT NOT NULL,
  `dosi` FLOAT(4,2) NOT NULL,
  `tempsEsperaCarn` INT NULL,
  `tempsEsperaLlet` INT NULL,
  PRIMARY KEY (`idMedicament`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `projecte`.`tipusPersona`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `projecte`.`tipusPersona` (
  `idTipusPersona` CHAR(4) NOT NULL,
  `descripcio` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idTipusPersona`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `projecte`.`persona`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `projecte`.`persona` (
  `idPersona` CHAR(4) NOT NULL,
  `tipusPersona` CHAR(4) NOT NULL,
  `nom` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idPersona`),
  INDEX `fk_persona_tipusPersona1_idx` (`tipusPersona` ASC),
  CONSTRAINT `fk_persona_tipusPersona1`
    FOREIGN KEY (`tipusPersona`)
    REFERENCES `projecte`.`tipusPersona` (`idTipusPersona`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `projecte`.`tractament`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `projecte`.`tractament` (
  `idTractament` CHAR(4) NOT NULL,
  `descripcio` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idTractament`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `projecte`.`administracio`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `projecte`.`administracio` (
  `idAdministracio` CHAR(4) NOT NULL,
  `idAnimal` CHAR(4) NOT NULL,
  `idPersona` CHAR(4) NOT NULL,
  `idTractament` CHAR(4) NOT NULL,
  `data` DATE NOT NULL DEFAULT now(),
  PRIMARY KEY (`idAdministracio`),
  INDEX `fk_administracio_animal_idx` (`idAnimal` ASC),
  INDEX `fk_administracio_persona1_idx` (`idPersona` ASC),
  INDEX `fk_administracio_tractament1_idx` (`idTractament` ASC),
  CONSTRAINT `fk_administracio_animal`
    FOREIGN KEY (`idAnimal`)
    REFERENCES `projecte`.`animal` (`idAnimal`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_administracio_persona1`
    FOREIGN KEY (`idPersona`)
    REFERENCES `projecte`.`persona` (`idPersona`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_administracio_tractament1`
    FOREIGN KEY (`idTractament`)
    REFERENCES `projecte`.`tractament` (`idTractament`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `projecte`.`medicaments_dels_tractaments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `projecte`.`medicaments_dels_tractaments` (
  `idMedicament` CHAR(4) NOT NULL,
  `idTractament` CHAR(4) NOT NULL,
  PRIMARY KEY (`idMedicament`, `idTractament`),
  INDEX `fk_medicament_has_tractament_tractament1_idx` (`idTractament` ASC),
  INDEX `fk_medicament_has_tractament_medicament1_idx` (`idMedicament` ASC),
  CONSTRAINT `fk_medicament_has_tractament_medicament1`
    FOREIGN KEY (`idMedicament`)
    REFERENCES `projecte`.`medicament` (`idMedicament`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_medicament_has_tractament_tractament1`
    FOREIGN KEY (`idTractament`)
    REFERENCES `projecte`.`tractament` (`idTractament`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `projecte`.`logs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `projecte`.`logs` (
  `idLog` INT NOT NULL AUTO_INCREMENT,
  `data` DATETIME NOT NULL,
  `contingut` VARCHAR(250) NOT NULL,
  PRIMARY KEY (`idLog`))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Procediments

afegir_animal

CREATE DEFINER=`root`@`localhost` PROCEDURE `afegir_animal`(in idAnimal CHAR(4), in dib CHAR(20), in dataNaixement DATE)
BEGIN
	DECLARE EXIT HANDLER FOR 1062 SELECT CONCAT("L'animal amb el número ",idAnimal,"o el DIB ",dib,"ja està donat d'alta a la BBDD") AS 'ERROR';
    DECLARE EXIT HANDLER FOR 1048 SELECT CONCAT("Tots els camps són obligatoris: número animal, DIB i data de naixement") AS 'ERROR';
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT CONCAT("Error desconegut. Reviseu els paràmetres introduïts o contacteu amb l'administrador del sistema") AS 'ERROR';
    
    INSERT INTO animals (idAnimal,dib,dataNaixement) VALUES (idAnimal,dib,dataNaixement);
    SELECT CONCAT("L'animal ",idAnimal," amb DIB ", dib," s'ha donat d'alta correctament") AS 'CORRECTE';
END

afegir_persona

CREATE DEFINER=`root`@`localhost` PROCEDURE `afegir_persona`(in idPersona CHAR(4), in tipusPersona CHAR(4), in nom VARCHAR(45)) BEGIN DECLARE EXIT HANDLER FOR 1062 SELECT CONCAT("La persona amb el codi ",idPersona," ja està donat d'alta a la BBDD") AS 'ERROR';

   DECLARE EXIT HANDLER FOR 1048 SELECT CONCAT("Tots els camps són obligatoris: codi persona, tipus persona i nom") AS 'ERROR';
   DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT CONCAT("Error desconegut. Reviseu els paràmetres introduïts o contacteu amb l'administrador del sistema") AS 'ERROR';
   
   INSERT INTO persona (idPersona,tipusPersona,nom) VALUES (idPersona,tipusPersona,nom);
   SELECT CONCAT("La persona ",nom," amb codi ", idPersona," s'ha donat d'alta correctament") AS 'CORRECTE';

END

Exportació

Documentació

Implementació

Màquina virtual amb CentOS 7

Importació

Usuari

Configuració del MySQLWorkbench

Annexos

Instal·lació de VirtualBox

Descarregarem l'aplicació

Pas1

Per a descarregar VirtualBox haurem d'anar a la pàgina oficial de l'aplicació i fer clic a l'enllaç de la línia inferior per anar-hi. Pàgina web oficial: https://www.virtualbox.org/

Una vegada ens trobem a la pàgina de l'aplicació, podrem observar un requadre de color blau amb el missatge "Download VirtualBox". Podem veure'l ressaltat a la imatge inferior amb una fletxa de color taronja.

Pas2

En fer clic en el requadre ens portarà a una nova pàgina, on haurem de seleccionar per a quin sistema operatiu volem l'aplicació, en el nostre cas com descarregarem la versió per a Windows, ja que l'equip host del client és Windows. Farem clic al damunt del text "Windows Hosts".

Instal·lació de l'aplicació

Pas 1

Una vegada ja hàgim descarregat l'aplicació, anirem a la carpeta on l'hem descarregat i hi farem doble clic. Ens apareixerà una finestra emergent que ens donarà la benvinguda a la instal·lació. Clicarem el botó "Next >".

Pas 2

En aquesta finestra podrem canviar la ubicació on s'instal·larà l'aplicació. Deixarem l'opció per defecte; buscarem el botó "Next >" i hi farem clic.

Pas 3

Aquí se'ns permetrà seleccionar diverses opcions a l'hora de crear els accessos directes (si volem que es creïn a la barra de tasques, a l'escriptori, etc.). Deixarem les opcions per defecte i farem clic al botó "Next >"

Pas 4

S'informa que les connexions quedaran desactivades per uns instants, ja que VirtualBox estarà creant noves interfícies de xarxa virtual. Farem clic al el botó "Yes".

Pas 5

Demanarà una confirmació per a començar la instal·lació; farem clic a "Install".

Pas 6

Haurem de configurar la instal·lació com a administració fent clic al botó "Sí".

Pas 7

Esperarem mentre es realitza la instal·lació.

Pas 8

Acabarem la instal·lació fent clic a "Finish".

Crear una màquina virtual

Instal·lació de CentOS

Crear la connexió amb MySQL Workbench

Gestió de la BBDD des de MySQL Workbench

[centos-1] No necessàriament amb interfície gràfica ja que serà un servidor dedicat que controlarem remotament via SSH i MySQLWorkbench per a gestió de MariaDB; de manera que podria ser un server headless

[1]

Projecte