El Camello, el León y el niño. O la evolución del perro al lobo

http://bd08001.webcindario.com/creazoo.sql

#---------------------------------------
# Javier Castañón - bd08001 - 08/05/2009
# ---------------------------------------------------
# Creación zoo - Tablas - inserción registros.- MySQL
#----------------------------------------------------

# Por defecto MySQL 5 sobre XP crea las tablas como InnoDB

# delimitador ;
delimiter ;

# Borrado de zoo si existe
DROP DATABASE IF EXISTS zoo;
#creación de zoo sino existe
CREATE DATABASE IF NOT EXISTS zoo;
#usamos la bbdd zoo
USE zoo;

#
# Tabla alimentacion
#

DROP TABLE IF EXISTS alimentacion;

CREATE TABLE alimentacion (
  Id_alimentacion INTEGER NOT NULL AUTO_INCREMENT,
  descripcion VARCHAR(150),
  PRIMARY KEY (Id_alimentacion)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

# insertamos 4 registros

INSERT INTO alimentacion (Id_alimentacion, descripcion) VALUES (1, 'vegetariana');
INSERT INTO alimentacion (Id_alimentacion, descripcion) VALUES (2, 'ovolacteovegetariana');
INSERT INTO alimentacion (Id_alimentacion, descripcion) VALUES (3, 'carnivoro');
INSERT INTO alimentacion (Id_alimentacion, descripcion) VALUES (4, 'omnivoro');

#
# tabla animales
#

DROP TABLE IF EXISTS animales;

CREATE TABLE animales (
  Id_animal INTEGER NOT NULL AUTO_INCREMENT,
  id_especie INTEGER,
  nombre_pila VARCHAR(50),
  fecha_ingreso DATE,
  fecha_salida DATE,
  nacimiento DATE,
  foto VARCHAR(200),
  id_cuidador INTEGER,
  id_medicacion INTEGER,
  id_ubicacion INTEGER,
  sexo VARCHAR(255),
  INDEX (id_cuidador),
  INDEX (id_especie),
  INDEX (id_medicacion),
  INDEX (id_ubicacion),
  PRIMARY KEY (Id_animal)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 7 REGISTROS

INSERT INTO animales (Id_animal, id_especie, nombre_pila, fecha_ingreso, fecha_salida, nacimiento, foto, id_cuidador, id_medicacion, id_ubicacion, sexo) VALUES (1, 1, 'copito', '2009-01-01 ', NULL, '2006-03-07', 'http://upload.wikimedia.org/wikipedia/commons/thumb/3/36/Gorilla_CinZoo_02036.jpg/200px-Gorilla_CinZoo_02036.jpg', 6, 6, 3, 'varon');
INSERT INTO animales (Id_animal, id_especie, nombre_pila, fecha_ingreso, fecha_salida, nacimiento, foto, id_cuidador, id_medicacion, id_ubicacion, sexo) VALUES (2, 2, 'pijamas', '2008-06-02 ', NULL, '2005-09-15', 'http://upload.wikimedia.org/wikipedia/commons/thumb/f/f2/Beautiful_Zebra_in_South_Africa.JPG/200px-Beautiful_Zebra_in_South_Africa.JPG', 6, 6, 3, 'hembra');
INSERT INTO animales (Id_animal, id_especie, nombre_pila, fecha_ingreso, fecha_salida, nacimiento, foto, id_cuidador, id_medicacion, id_ubicacion, sexo) VALUES (3, 3, 'goloso', '2009-04-15 ', NULL, '2008-06-02', 'http://upload.wikimedia.org/wikipedia/commons/thumb/c/cd/Ours_des_pyrenees_aspe_2002.jpg/230px-Ours_des_pyrenees_aspe_2002.jpg', 7, 5, 3, 'varon');
INSERT INTO animales (Id_animal, id_especie, nombre_pila, fecha_ingreso, fecha_salida, nacimiento, foto, id_cuidador, id_medicacion, id_ubicacion, sexo) VALUES (4, 4, 'raro', '2008-10-22 ', NULL, '2007-08-15', 'http://upload.wikimedia.org/wikipedia/commons/thumb/f/f2/Platypus.jpg/250px-Platypus.jpg', 7, 6, 3, 'varon');
INSERT INTO animales (Id_animal, id_especie, nombre_pila, fecha_ingreso, fecha_salida, nacimiento, foto, id_cuidador, id_medicacion, id_ubicacion, sexo) VALUES (5, 5, 'rey', '2008-01-08 ', NULL, '2003-09-08', 'http://upload.wikimedia.org/wikipedia/commons/thumb/1/10/Lion_waiting_in_Nambia.jpg/230px-Lion_waiting_in_Nambia.jpg', 6, 5, 3, 'varon');
INSERT INTO animales (Id_animal, id_especie, nombre_pila, fecha_ingreso, fecha_salida, nacimiento, foto, id_cuidador, id_medicacion, id_ubicacion, sexo) VALUES (6, 5, 'reina', '2008-11-18 ', NULL, '2005-03-09', 'http://upload.wikimedia.org/wikipedia/commons/thumb/a/a7/Lioness%2C_Olomouc.jpg/230px-Lioness%2C_Olomouc.jpg', 6, 6, 3, 'hembra');
INSERT INTO animales (Id_animal, id_especie, nombre_pila, fecha_ingreso, fecha_salida, nacimiento, foto, id_cuidador, id_medicacion, id_ubicacion, sexo) VALUES (7, 5, 'principe', '2009-05-01 ', NULL, '2009-05-01', 'http://www.fonditos.com/wallpapers/vista/03053.jpg', 7, 3, 3, 'varon');

#
# Tabla clientes
#

DROP TABLE IF EXISTS clientes;

CREATE TABLE clientes (
  Id_cliente INTEGER NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(50),
  tipo_sociedad INTEGER,
  fecha_alta DATE,
  fecha_baja DATE,
  comentario VARCHAR(150),
  PRIMARY KEY (Id_cliente)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# 4 REGISTROS clientes
#

INSERT INTO clientes (Id_cliente, nombre, tipo_sociedad, fecha_alta, fecha_baja, comentario) VALUES (1, 'green_peace', 5, '2009-04-01', '2012-12-31', 'ong ecologista');
INSERT INTO clientes (Id_cliente, nombre, tipo_sociedad, fecha_alta, fecha_baja, comentario) VALUES (2, 'caja_madrid', 2, '2009-04-02', '2012-12-31', 'ayuda a especies en extincion');
INSERT INTO clientes (Id_cliente, nombre, tipo_sociedad, fecha_alta, fecha_baja, comentario) VALUES (3, 'fulanito detal', 3, '2009-04-03', '2012-12-31', 'colaborador');
INSERT INTO clientes (Id_cliente, nombre, tipo_sociedad, fecha_alta, fecha_baja, comentario) VALUES (4, 'fulanito decual', 3, '2009-05-05', '2012-12-31', 'voluntario');

#
# Tabla empleados
#

DROP TABLE IF EXISTS empleados;

CREATE TABLE empleados (
  Id_empleado INTEGER NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(50),
  ubicacion INTEGER,
  PRIMARY KEY (Id_empleado)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 7 registros

INSERT INTO empleados (Id_empleado, nombre, ubicacion) VALUES (1, 'antonio camborio', 1);
INSERT INTO empleados (Id_empleado, nombre, ubicacion) VALUES (2, 'benito diaz', 2);
INSERT INTO empleados (Id_empleado, nombre, ubicacion) VALUES (3, 'carlos espino', 3);
INSERT INTO empleados (Id_empleado, nombre, ubicacion) VALUES (4, 'dario fernandez', 4);
INSERT INTO empleados (Id_empleado, nombre, ubicacion) VALUES (5, 'esteban garcia', 5);
INSERT INTO empleados (Id_empleado, nombre, ubicacion) VALUES (6, 'francisco hernandez', 6);
INSERT INTO empleados (Id_empleado, nombre, ubicacion) VALUES (7, 'gerardo iglesias', 6);

#
# Tabla especies
#

DROP TABLE IF EXISTS especies;

CREATE TABLE especies (
  Id_especie INTEGER NOT NULL AUTO_INCREMENT,
  nombre_comun VARCHAR(50),
  nombre_cientifico VARCHAR(50),
  id_alimentacion INTEGER,
  INDEX (id_alimentacion),
  PRIMARY KEY (Id_especie)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 5 registros

INSERT INTO especies (Id_especie, nombre_comun, nombre_cientifico, id_alimentacion) VALUES (1, 'gorila', 'primates gorilla', 1);
INSERT INTO especies (Id_especie, nombre_comun, nombre_cientifico, id_alimentacion) VALUES (2, 'cebra', 'equus zebra', 1);
INSERT INTO especies (Id_especie, nombre_comun, nombre_cientifico, id_alimentacion) VALUES (3, 'oso pardo', 'Ursus arctos', 4);
INSERT INTO especies (Id_especie, nombre_comun, nombre_cientifico, id_alimentacion) VALUES (4, 'ornitorrinco', 'Ornithorhynchus anatinus', 2);
INSERT INTO especies (Id_especie, nombre_comun, nombre_cientifico, id_alimentacion) VALUES (5, 'leon', 'panthera leo', 3);

#
# Tabla manimales
#

DROP TABLE IF EXISTS manimales;

CREATE TABLE manimales (
  Id_movimiento INTEGER NOT NULL AUTO_INCREMENT,
  id_animal INTEGER,
  fecha_movimiento DATE,
  tipo_evento INTEGER,
  descripcion VARCHAR(50),
  INDEX (id_animal),
  PRIMARY KEY (Id_movimiento)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Tabla medicacion
#

DROP TABLE IF EXISTS medicacion;

CREATE TABLE medicacion (
  Id_medicacion INTEGER NOT NULL AUTO_INCREMENT,
  descripcion VARCHAR(150),
  PRIMARY KEY (Id_medicacion)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 6 registros

INSERT INTO medicacion (Id_medicacion, descripcion) VALUES (1, 'vacunas');
INSERT INTO medicacion (Id_medicacion, descripcion) VALUES (2, 'analgesicos');
INSERT INTO medicacion (Id_medicacion, descripcion) VALUES (3, 'antipireticos');
INSERT INTO medicacion (Id_medicacion, descripcion) VALUES (4, 'antiinflamatorios');
INSERT INTO medicacion (Id_medicacion, descripcion) VALUES (5, 'antibioticos');
INSERT INTO medicacion (Id_medicacion, descripcion) VALUES (6, 'Sin_medicacion');

#
# Tabla mproductos
#

DROP TABLE IF EXISTS mproductos;

CREATE TABLE mproductos (
  Id_mproducto INTEGER NOT NULL AUTO_INCREMENT,
  id_producto INTEGER NOT NULL,
  id_proveedor INTEGER NOT NULL,
  fecha_entrada DATE,
  fecha_salida DATE,
  cantidad INTEGER,
  destino INTEGER,
  INDEX (id_producto),
  PRIMARY KEY (Id_mproducto)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Tabla productos
#

DROP TABLE IF EXISTS productos;

CREATE TABLE productos (
  Id_producto INTEGER NOT NULL AUTO_INCREMENT,
  id_proveedor INTEGER NOT NULL,
  nombre VARCHAR(50),
  stock INTEGER,
  INDEX (id_proveedor),
  PRIMARY KEY (Id_producto)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 3 registros

INSERT INTO productos (Id_producto, id_proveedor, nombre, stock) VALUES (1, 1, 'piensos', 100);
INSERT INTO productos (Id_producto, id_proveedor, nombre, stock) VALUES (2, 2, 'carne', 300);
INSERT INTO productos (Id_producto, id_proveedor, nombre, stock) VALUES (3, 3, 'kits mantenimiento', 10);

#
# Tabla proveedores
#

DROP TABLE IF EXISTS proveedores;

CREATE TABLE proveedores (
  Id_proveedor INTEGER NOT NULL AUTO_INCREMENT,
  id_cliente INTEGER DEFAULT 0,
  nombre VARCHAR(50),
  tipo_sociedad INTEGER,
  fecha_alta DATE,
  fecha_baja DATE,
  comentario VARCHAR(150),
  INDEX (id_cliente),
  PRIMARY KEY (Id_proveedor)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# 3 registros
#

INSERT INTO proveedores (Id_proveedor, id_cliente, nombre, tipo_sociedad, fecha_alta, fecha_baja, comentario) VALUES (1, 0, 'piensos la hiena', 6, '2009-05-01', NULL, 'comida para hebivoros');
INSERT INTO proveedores (Id_proveedor, id_cliente, nombre, tipo_sociedad, fecha_alta, fecha_baja, comentario) VALUES (2, 1, 'green peace', 5, '2009-05-01', NULL, 'comida para carnivoros');
INSERT INTO proveedores (Id_proveedor, id_cliente, nombre, tipo_sociedad, fecha_alta, fecha_baja, comentario) VALUES (3, 2, 'caja madrid', 2, '2009-05-01', NULL, 'productos de mantenimiento');

#
# Tabla sociedades
#

DROP TABLE IF EXISTS sociedades;

CREATE TABLE sociedades (
  Id_tipo_sociedad INTEGER NOT NULL AUTO_INCREMENT,
  descripcion VARCHAR(50),
  PRIMARY KEY (Id_tipo_sociedad)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

# registros 7

INSERT INTO sociedades (Id_tipo_sociedad, descripcion) VALUES (1, 'fundacion');
INSERT INTO sociedades (Id_tipo_sociedad, descripcion) VALUES (2, 'SA');
INSERT INTO sociedades (Id_tipo_sociedad, descripcion) VALUES (3, 'persona_fisica');
INSERT INTO sociedades (Id_tipo_sociedad, descripcion) VALUES (4, 'organismo_oficial');
INSERT INTO sociedades (Id_tipo_sociedad, descripcion) VALUES (5, 'ong');
INSERT INTO sociedades (Id_tipo_sociedad, descripcion) VALUES (6, 'SL');
INSERT INTO sociedades (Id_tipo_sociedad, descripcion) VALUES (7, 'otros');

#
# Tabla tipoeventos
#

DROP TABLE IF EXISTS tipoeventos;

CREATE TABLE tipoeventos (
  Id_tipo INTEGER NOT NULL AUTO_INCREMENT,
  descripcion VARCHAR(150),
  PRIMARY KEY (Id_tipo)
);
# ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 4 registros

INSERT INTO tipoeventos (Id_tipo, descripcion) VALUES (1, 'parto');
INSERT INTO tipoeventos (Id_tipo, descripcion) VALUES (2, 'enfermedad');
INSERT INTO tipoeventos (Id_tipo, descripcion) VALUES (3, 'traslado');
INSERT INTO tipoeventos (Id_tipo, descripcion) VALUES (4, 'otros');

#
# Tabla ubicaciones
#

DROP TABLE IF EXISTS ubicaciones;

CREATE TABLE ubicaciones (
  Id_ubicacion INTEGER NOT NULL AUTO_INCREMENT,
  descripcion VARCHAR(50),
  PRIMARY KEY (Id_ubicacion)
);
#ENGINE=InnoDB DEFAULT CHARSET=utf8;

# registros 6

INSERT INTO ubicaciones (Id_ubicacion, descripcion) VALUES (1, 'stock');
INSERT INTO ubicaciones (Id_ubicacion, descripcion) VALUES (2, 'administracion');
INSERT INTO ubicaciones (Id_ubicacion, descripcion) VALUES (3, 'animales');
INSERT INTO ubicaciones (Id_ubicacion, descripcion) VALUES (4, 'empleados');
INSERT INTO ubicaciones (Id_ubicacion, descripcion) VALUES (5, 'veterinarios');
INSERT INTO ubicaciones (Id_ubicacion, descripcion) VALUES (6, 'cuidador');

#creamos las relaciones entre las tablas o las FOREIGN KEYs
#http://dev.mysql.com/doc/refman/5.0/es/innodb-foreign-key-constraints.html

#ALTER TABLE yourtablename
#    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
#    REFERENCES tbl_name (index_col_name, ...)
#    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
#    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

#relacion entre clientes y sociedades

ALTER TABLE clientes ADD CONSTRAINT fk_cli_soci FOREIGN KEY (tipo_sociedad) REFERENCES sociedades (id_tipo_sociedad);

#relacion entre proveedores y sociedades

ALTER TABLE proveedores ADD CONSTRAINT fk_pro_soci FOREIGN KEY (tipo_sociedad) REFERENCES sociedades (id_tipo_sociedad);

#relacion entre proveedores y clientes

#ALTER TABLE proveedores ADD CONSTRAINT fk_pro_cli FOREIGN KEY (id_cliente) REFERENCES clientes (id_cliente);

#relacion entre productos y proveedores

ALTER TABLE productos ADD CONSTRAINT fk_produ_prov FOREIGN KEY (id_proveedor) REFERENCES proveedores (id_proveedor);

#relaciones entre mproductos y productos

ALTER TABLE mproductos ADD CONSTRAINT fk_mprodu_produ FOREIGN KEY (id_producto) REFERENCES productos (id_producto) ON DELETE CASCADE;
#si se borra el producto se borran sus movimientos -> ON DELETE CASCADE

ALTER TABLE mproductos ADD CONSTRAINT fk_mprodu_produ2 FOREIGN KEY (id_proveedor) REFERENCES productos (id_proveedor);

#relacion entre mproductos y ubicaciones

ALTER TABLE mproductos ADD CONSTRAINT fk_mprodu_ubi FOREIGN KEY (destino) REFERENCES ubicaciones (id_ubicacion);

#relacion entre animales y ubicaciones

ALTER TABLE animales ADD CONSTRAINT fk_ani_ubi FOREIGN KEY (id_ubicacion) REFERENCES ubicaciones (id_ubicacion);

#relacion entre animales y especies

ALTER TABLE animales ADD CONSTRAINT fk_ani_esp FOREIGN KEY (id_especie) REFERENCES especies (id_especie);

#relacion entre manimales y animales

ALTER TABLE manimales ADD CONSTRAINT fk_mani_ani FOREIGN KEY (id_animal) REFERENCES animales (id_animal) ON DELETE CASCADE;
#si se borra el animal se borran sus movimientos -> ON DELETE CASCADE

#relacion entre manimales y tipoeventos

ALTER TABLE manimales ADD CONSTRAINT fk_mani_teve FOREIGN KEY (tipo_evento) REFERENCES tipoeventos (id_tipo);

#relacion entre animales y medicacion

ALTER TABLE animales ADD CONSTRAINT fk_ani_medi FOREIGN KEY (id_medicacion) REFERENCES medicacion (id_medicacion);

#relacion entre animales y empleados

ALTER TABLE animales ADD CONSTRAINT fk_ani_emp FOREIGN KEY (id_cuidador) REFERENCES empleados (id_empleado);

#relacion entre especies y alimentacion

ALTER TABLE especies ADD CONSTRAINT fk_esp_ali FOREIGN KEY (id_alimentacion) REFERENCES alimentacion (id_alimentacion);

#relacion entre empleados y ubicaciones

ALTER TABLE empleados ADD CONSTRAINT fk_emp_ubi FOREIGN KEY (ubicacion) REFERENCES ubicaciones (id_ubicacion);

#
#		##########################################
# 		# Procedimientos almacenados y trigers   #
#		##########################################
#
# SE CREAN 2 DISPARADORES EN LOS EVENTOS BEFORE INSERT Y BEFORE UPDATE DE LA TABLA MPRODUCTOS
# PARA ASEGURAR QUE SI ES UNA ENTRADA LA CANTIDAD SEA POSITIVA Y SI ES UNA SALIDA LA CANTIDAD SEA NEGATIVA
# LUEGO SE SUMARA O RESTARA AL STOCK EN LA TABLA MAESTRA PRODUCTOS
# Tambien se crea el disparador BEFORE DELETE en mproductos para asegurar la consistencia de productos.stock

delimiter //
#######################################
# Trigger BEFORE UPDATE ON mproductos #
#######################################
DROP TRIGGER IF EXISTS tg_productos_BU_mproductos//
CREATE TRIGGER tg_productos_BU_mproductos
BEFORE UPDATE ON mproductos
FOR EACH ROW
BEGIN
 IF NEW.destino=1 THEN
 #si es una entrada debe de ser positivo
  IF NEW.cantidad<0 THEN
   SET NEW.cantidad=-1*NEW.cantidad;
  END IF;
 SET NEW.cantidad=1*NEW.cantidad;
 ELSE #si no es una entrada es una salida
 #si es una salida debe de ser negativo
  IF NEW.cantidad>0 THEN
   SET NEW.cantidad=-1*NEW.cantidad;
  END IF;
 END IF;
UPDATE productos SET stock=stock+NEW.cantidad WHERE productos.Id_producto = NEW.id_producto;
END//
delimiter ;

delimiter //
#######################################
# Trigger BEFORE INSERT ON mproductos #
#######################################
DROP TRIGGER IF EXISTS tg_productos_BI_mproductos//
CREATE TRIGGER tg_productos_BI_mproductos
BEFORE INSERT ON mproductos
FOR EACH ROW
BEGIN
 IF NEW.destino=1 THEN
 #si es una entrada debe de ser positivo
  IF NEW.cantidad<0 THEN
   SET NEW.cantidad=-1*NEW.cantidad;
  END IF;
 SET NEW.cantidad=1*NEW.cantidad;
 ELSE #si no es una entrada es una salida
 #si es una salida debe de ser negativo
  IF NEW.cantidad>0 THEN
   SET NEW.cantidad=-1*NEW.cantidad;
  END IF;
 END IF;
UPDATE productos SET stock=stock+NEW.cantidad WHERE productos.Id_producto = NEW.id_producto;
END//
delimiter ;

delimiter //
#######################################
# Trigger BEFORE DELETE ON mproductos #
#######################################
DROP TRIGGER IF EXISTS tg_productos_BD_mproductos//
CREATE TRIGGER tg_productos_BD_mproductos
BEFORE DELETE ON mproductos
FOR EACH ROW
BEGIN

UPDATE productos SET stock=stock-OLD.cantidad WHERE productos.Id_producto = OLD.id_producto;

END//
delimiter ;

#                  ##################################
#                  #           VISTAS               #
#                  ##################################

DROP VIEW IF EXISTS vanimales;

CREATE VIEW vanimales AS
select distinct em.nombre Cuidador, a.id_animal ID_ANIMAL, e.nombre_comun NOMBRE_ESPECIE,
 a.nombre_pila NOMBRE_PILA, m.descripcion Tipo_Medicacion,
 u.descripcion LUGAR, al.descripcion Tipo_Alimentacion
 from animales a, especies e, medicacion m, ubicaciones u, alimentacion al,empleados em
where a.id_especie=e.id_especie and a.id_medicacion=m.id_medicacion and
a.id_cuidador=em.id_empleado and a.id_ubicacion=u.id_ubicacion and
al.id_alimentacion=e.id_alimentacion;