Base de
Datos
Trabajo Practico N° 3
Integrantes:
1. Luquet, Mariana Fernanda
2. Mamani, Adrian Javier
3. Mamani, Marcos Eduardo
4. Martinez Reina, Yahannah
DISEÑO LÓGICO Y CONSULTAS EN BASES DE DATOS
El objetivo de este trabajo práctico es que, a partir del Trabajo Práctico N° 2 donde
realizaron el Modelo Entidad-Relación (E-R) de los enunciados propuestos, elaboren
el Diseño Lógico de la base de datos, implementen su estructura, inserten datos de
prueba y, finalmente, demuestren su correcto funcionamiento mediante consultas
específicas.
1. DISEÑO LÓGICO E IMPLEMENTACIÓN
A continuación, se presenta el diseño lógico (Modelo Relacional) y los scripts DDL y
DML para la creación e inserción de datos en la base de datos MySQL.
Modelo Relacional
Se definen las siguientes tablas, atributos, claves primarias (PK) y foráneas (FK):
● Ciudad (id_ciudad [PK], nombre)
● Laboratorio (id_laboratorio [PK], nombre [UNIQUE], direccion, telefono)
● MonoDroga (id_monodroga [PK], nombre [UNIQUE])
● AccionTerapeutica (id_accion [PK], nombre [UNIQUE])
● Presentacion (id_presentacion [PK], descripcion [UNIQUE])
● Farmacia (id_farmacia [PK], nombre [UNIQUE], direccion, telefono, id_ciudad [FK],
legajo_a_cargo [FK, UNIQUE])
● Empleado (nro_legajo [PK], cuil [UNIQUE], nombre, apellido, direccion, telefono,
titulo, id_farmacia [FK])
● Farmaceutico (nro_legajo [PK, FK], nro_matricula [UNIQUE])
o Nota: Esta tabla es una especialización de Empleado. El nro_legajo es PK y
FK a la vez, referenciando a Empleado.
● Medicamento (id_medicamento [PK], nombre_comercial, id_laboratorio [FK])
● Medicamento_MonoDroga (id_medicamento [PK, FK], id_monodroga [PK, FK])
o Tabla asociativa M:N entre Medicamento y MonoDroga.
● Medicamento_Accion (id_medicamento [PK, FK], id_accion [PK, FK])
o Tabla asociativa M:N entre Medicamento y AccionTerapeutica.
● Producto (id_medicamento [PK, FK], id_presentacion [PK, FK], precio)
o Representa un medicamento en una presentación específica, con su precio.
El precio depende de esta combinación.
● Stock (id_farmacia [PK, FK], id_medicamento [PK, FK], id_presentacion [PK, FK],
cantidad)
o Almacena la cantidad de cada producto por farmacia. La PK compuesta
(id_medicamento, id_presentacion) es FK a la tabla Producto.
1
Script de Creación de BD y Tablas (DDL)
Este script MySQL crea la base de datos y todas las tablas
definidas.
-- 1. Creación de la Base de Datos
CREATE DATABASE IF NOT EXISTS `db_farmacias`;
USE `db_farmacias`;
2
-- 2. Creación de Tablas (Entidades sin FKs primero)
CREATE TABLE IF NOT EXISTS `Ciudad` (
`id_ciudad` INT AUTO_INCREMENT PRIMARY KEY,
`nombre` VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS `Laboratorio` (
`id_laboratorio` INT AUTO_INCREMENT PRIMARY KEY,
`nombre` VARCHAR(100) NOT NULL UNIQUE,
`direccion` VARCHAR(255),
`telefono` VARCHAR(50)
);
CREATE TABLE IF NOT EXISTS `MonoDroga` (
`id_monodroga` INT AUTO_INCREMENT PRIMARY KEY,
`nombre` VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS `AccionTerapeutica` (
`id_accion` INT AUTO_INCREMENT PRIMARY KEY,
`nombre` VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS `Presentacion` (
`id_presentacion` INT AUTO_INCREMENT PRIMARY KEY,
`descripcion` VARCHAR(150) NOT NULL UNIQUE
);
-- 3. Creación de Tablas (Entidades con FKs)
CREATE TABLE IF NOT EXISTS `Farmacia` (
`id_farmacia` INT AUTO_INCREMENT PRIMARY KEY,
3
`nombre` VARCHAR(100) NOT NULL UNIQUE,
`direccion` VARCHAR(255),
`telefono` VARCHAR(50),
`id_ciudad` INT NOT NULL,
`legajo_a_cargo` INT NULL UNIQUE, -- Se deja NULO para
crearla, luego se vincula
FOREIGN KEY (`id_ciudad`) REFERENCES
`Ciudad`(`id_ciudad`)
);
CREATE TABLE IF NOT EXISTS `Empleado` (
`nro_legajo` INT AUTO_INCREMENT PRIMARY KEY,
`cuil` VARCHAR(13) NOT NULL UNIQUE,
`nombre` VARCHAR(100) NOT NULL,
`apellido` VARCHAR(100) NOT NULL,
`direccion` VARCHAR(255),
`telefono` VARCHAR(50),
`titulo` VARCHAR(100),
`id_farmacia` INT NOT NULL,
FOREIGN KEY (`id_farmacia`) REFERENCES
`Farmacia`(`id_farmacia`)
);
CREATE TABLE IF NOT EXISTS `Farmaceutico` (
`nro_legajo` INT PRIMARY KEY,
`nro_matricula` VARCHAR(50) NOT NULL UNIQUE,
FOREIGN KEY (`nro_legajo`) REFERENCES
`Empleado`(`nro_legajo`)
);
-- 4. Vínculo Circular (ALTER TABLE)
-- Se añade la FK de Farmacia a Farmaceutico después de crear
ambas tablas
ALTER TABLE `Farmacia`
ADD CONSTRAINT `fk_farmacia_manager`
4
FOREIGN KEY (`legajo_a_cargo`) REFERENCES
`Farmaceutico`(`nro_legajo`)
ON DELETE SET NULL ON UPDATE CASCADE; -- Si el
farmacéutico se va, el puesto queda vacante (NULL)
-- 5. Resto de Tablas
CREATE TABLE IF NOT EXISTS `Medicamento` (
`id_medicamento` INT AUTO_INCREMENT PRIMARY KEY,
`nombre_comercial` VARCHAR(150) NOT NULL,
`id_laboratorio` INT NOT NULL,
FOREIGN KEY (`id_laboratorio`) REFERENCES
`Laboratorio`(`id_laboratorio`)
);
CREATE TABLE IF NOT EXISTS `Producto` (
`id_medicamento` INT NOT NULL,
`id_presentacion` INT NOT NULL,
`precio` DECIMAL(10, 2) NOT NULL CHECK (`precio` > 0),
PRIMARY KEY (`id_medicamento`, `id_presentacion`),
FOREIGN KEY (`id_medicamento`) REFERENCES
`Medicamento`(`id_medicamento`),
FOREIGN KEY (`id_presentacion`) REFERENCES
`Presentacion`(`id_presentacion`)
);
CREATE TABLE IF NOT EXISTS `Stock` (
`id_farmacia` INT NOT NULL,
`id_medicamento` INT NOT NULL,
`id_presentacion` INT NOT NULL,
`cantidad` INT NOT NULL DEFAULT 0 CHECK (`cantidad`
>= 0),
PRIMARY KEY (`id_farmacia`, `id_medicamento`,
`id_presentacion`),
FOREIGN KEY (`id_farmacia`) REFERENCES
`Farmacia`(`id_farmacia`),
5
FOREIGN KEY (`id_medicamento`, `id_presentacion`)
REFERENCES `Producto`(`id_medicamento`,
`id_presentacion`)
);
CREATE TABLE IF NOT EXISTS `Medicamento_MonoDroga` (
`id_medicamento` INT NOT NULL,
`id_monodroga` INT NOT NULL,
PRIMARY KEY (`id_medicamento`, `id_monodroga`),
FOREIGN KEY (`id_medicamento`) REFERENCES
`Medicamento`(`id_medicamento`),
FOREIGN KEY (`id_monodroga`) REFERENCES
`MonoDroga`(`id_monodroga`)
);
CREATE TABLE IF NOT EXISTS `Medicamento_Accion` (
`id_medicamento` INT NOT NULL,
`id_accion` INT NOT NULL,
PRIMARY KEY (`id_medicamento`, `id_accion`),
FOREIGN KEY (`id_medicamento`) REFERENCES
`Medicamento`(`id_medicamento`),
FOREIGN KEY (`id_accion`) REFERENCES
`AccionTerapeutica`(`id_accion`)
);
Script de Inserción de Registros (DML)
Se inserta un mínimo de 10 registros significativos por tabla.
USE `db_farmacias`;
-- 1. Inserción en tablas sin FK
INSERT INTO `Ciudad` (`nombre`) VALUES
('Córdoba'), ('Rosario'), ('Mendoza'), ('San Miguel de Tucumán'),
('La Plata'), ('Mar del Plata'), ('Salta'), ('Santa Fe'),
('San Juan'), ('Resistencia');
6
INSERT INTO `Laboratorio` (`nombre`, `direccion`,
`telefono`) VALUES
('Bayer', 'Av. Corrientes 300, CABA', '011-4500-1000'),
('Roemmers', 'Calle Falsa 123, CABA', '011-4500-2000'),
('Gador', 'Av. Libertador 500, CABA', '011-4500-3000'),
('Bagó', 'Ruta 8 Km 50, Pilar', '0230-450-4000'),
('Pfizer', 'Av. del Sol 200, CABA', '011-4500-5000'),
('GSK (GlaxoSmithKline)', 'San Fernando 202, BsAs', '011-4500-
6000'),
('Novartis', 'Av. Panamericana 3000, BsAs', '011-4500-7000'),
('AstraZeneca', 'Ramal Pilar Km 45, BsAs', '011-4500-8000'),
('Sanofi', 'Av. Marquez 100, BsAs', '011-4500-9000'),
('Teva', 'Calle 3 Nro 10, La Plata', '0221-450-1000');
INSERT INTO `MonoDroga` (`nombre`) VALUES
('Paracetamol'), ('Ibuprofeno'), ('Amoxicilina'), ('Loratadina'),
('Omeprazol'), ('Losartán'), ('Metformina'), ('Salbutamol'),
('Clonazepam'), ('Ácido Acetilsalicílico');
INSERT INTO `AccionTerapeutica` (`nombre`) VALUES
('Analgésico'), ('Antiinflamatorio'), ('Antibiótico'),
('Antihistamínico'),
('Antiulceroso'), ('Antihipertensivo'), ('Hipoglucemiante'),
('Broncodilatador'),
('Ansiolítico'), ('Antipirético');
INSERT INTO `Presentacion` (`descripcion`) VALUES
('Comprimidos 500mg x 10 unidades'),
('Comprimidos 600mg x 20 unidades'),
('Jarabe 100ml'),
('Gotas 20ml'),
('Ampollas x 3 unidades'),
('Cápsulas 20mg x 14 unidades'),
('Inyectable 5ml'),
('Crema 30gr'),
7
('Spray Nasal 15ml'),
('Comprimidos Masticables x 12');
-- 2. Inserción en Farmacia (con legajo_a_cargo NULL)
INSERT INTO `Farmacia` (`nombre`, `direccion`, `telefono`,
`id_ciudad`) VALUES
('Farmacia Central', 'Av. Colón 1000', '351-456001', 1),
('Farmacia del Sur', 'Av. Velez Sarsfield 2000', '351-456002', 1),
('Farmacia Norte', 'Av. Rafael Nuñez 3000', '351-456003', 1),
('Farmacia Rosario Centro', 'Peatonal Córdoba 500', '341-
456004', 2),
('Farmacia Mendoza Plaza', 'San Martín 1500', '261-456005', 3),
('Farmacia La Plata', 'Calle 7 Nro 800', '221-456006', 5),
('Farmacia del Sol', 'Belgrano 400', '381-456007', 4),
('Farmacia Güemes', 'Belgrano 800', '387-456008', 7),
('Farmacia del Puerto', 'Av. Luro 3000', '223-456009', 6),
('Farmacia 24hs', 'Av. 25 de Mayo 100', '362-456010', 10);
-- 3. Inserción de Empleados
INSERT INTO `Empleado` (`cuil`, `nombre`, `apellido`,
`direccion`, `telefono`, `titulo`, `id_farmacia`) VALUES
('20-11111111-1', 'Carlos', 'Gomez', 'San Juan 100', '351-111111',
'Farmacéutico', 1),
('20-22222222-2', 'Ana', 'Perez', 'Sucre 200', '351-222222',
'Farmacéutico', 2),
('27-33333333-3', 'Maria', 'Lopez', 'Colón 300', '351-333333',
'Auxiliar de Farmacia', 1),
('20-44444444-4', 'Juan', 'Diaz', 'Santa Fe 150', '341-444444',
'Farmacéutico', 4),
('27-55555555-5', 'Laura', 'Garcia', 'Las Heras 300', '261-555555',
'Farmacéutico', 5),
('20-66666666-6', 'Miguel', 'Rodriguez', 'Calle 8 Nro 100', '221-
666666', 'Farmacéutico', 6),
('27-77777777-7', 'Sofia', 'Martinez', '24 de Septiembre 500', '381-
777777', 'Farmacéutico', 7),
('20-88888888-8', 'Diego', 'Sanchez', 'Caseros 600', '387-
888888', 'Farmacéutico', 8),
8
('27-99999999-9', 'Lucia', 'Fernandez', 'Av. Independencia 700',
'223-999999', 'Farmacéutico', 9),
('20-10101010-0', 'Martin', 'Torres', 'Sarmiento 150', '362-
101010', 'Farmacéutico', 10),
('27-12121212-1', 'Julia', 'Romero', 'Obispo Trejo 500', '351-
121212', 'Cajero', 1),
('20-13131313-2', 'Roberto', 'Alvarez', 'General Paz 100', '351-
131313', 'Personal de Maestranza', 2);
-- 4. Inserción de Farmacéuticos (deben ser empleados
existentes)
INSERT INTO `Farmaceutico` (`nro_legajo`, `nro_matricula`)
VALUES
(1, 'MP-1111'), (2, 'MP-2222'), (4, 'MP-4444'), (5, 'MP-5555'),
(6, 'MP-6666'), (7, 'MP-7777'), (8, 'MP-8888'), (9, 'MP-9999'),
(10, 'MP-1010'), (3, 'MP-3333'); -- Empleada 3 ahora también es
farmacéutica
-- 5. Actualización de Farmacias (Asignar 'legajo_a_cargo')
UPDATE `Farmacia` SET `legajo_a_cargo` = 1 WHERE
`id_farmacia` = 1;
UPDATE `Farmacia` SET `legajo_a_cargo` = 2 WHERE
`id_farmacia` = 2;
UPDATE `Farmacia` SET `legajo_a_cargo` = 4 WHERE
`id_farmacia` = 4;
UPDATE `Farmacia` SET `legajo_a_cargo` = 5 WHERE
`id_farmacia` = 5;
UPDATE `Farmacia` SET `legajo_a_cargo` = 6 WHERE
`id_farmacia` = 6;
UPDATE `Farmacia` SET `legajo_a_cargo` = 7 WHERE
`id_farmacia` = 7;
UPDATE `Farmacia` SET `legajo_a_cargo` = 8 WHERE
`id_farmacia` = 8;
UPDATE `Farmacia` SET `legajo_a_cargo` = 9 WHERE
`id_farmacia` = 9;
UPDATE `Farmacia` SET `legajo_a_cargo` = 10 WHERE
`id_farmacia` = 10;
UPDATE `Farmacia` SET `legajo_a_cargo` = 3 WHERE
`id_farmacia` = 3;
9
-- 6. Inserción de Medicamentos
INSERT INTO `Medicamento` (`nombre_comercial`,
`id_laboratorio`) VALUES
('Aspirina', 1), ('Tafirol', 2), ('Actron 600', 1), ('Amoxidal 500', 3),
('Clonazepam Gador', 3), ('Losartán Bagó', 4), ('Omeprazol
Pfizer', 5),
('Ventolin (Salbutamol)', 6), ('Metformina GSK', 6), ('Alergix
(Loratadina)', 10);
-- 7. Inserción de Productos (Medicamento + Presentación +
Precio)
INSERT INTO `Producto` (`id_medicamento`,
`id_presentacion`, `precio`) VALUES
(1, 1, 500.00), (2, 1, 650.00), (3, 2, 900.50), (4, 1, 1200.00),
(5, 4, 800.75), (6, 6, 1500.00), (7, 6, 1100.00), (8, 3, 2000.00),
(9, 2, 750.00), (10, 3, 950.00);
-- 8. Inserción de Stock
INSERT INTO `Stock` (`id_farmacia`, `id_medicamento`,
`id_presentacion`, `cantidad`) VALUES
(1, 1, 1, 100), (1, 2, 1, 50), (1, 3, 2, 30), (2, 1, 1, 80),
(2, 4, 1, 40), (3, 5, 4, 20), (4, 6, 6, 60), (4, 7, 6, 70),
(5, 8, 3, 25), (6, 9, 2, 15), (7, 10, 3, 50);
-- 9. Inserción de Tablas Asociativas (M:N)
-- Medicamento_MonoDroga
INSERT INTO `Medicamento_MonoDroga`
(`id_medicamento`, `id_monodroga`) VALUES
(1, 10), (2, 1), (3, 2), (4, 3), (5, 9), (6, 6),
(7, 5), (8, 8), (9, 7), (10, 4);
-- Medicamento_Accion
INSERT INTO `Medicamento_Accion` (`id_medicamento`,
`id_accion`) VALUES
(1, 1), (1, 2), (1, 10), (2, 1), (2, 10), (3, 1), (3, 2),
(4, 3), (5, 9), (6, 6), (7, 5), (8, 8), (9, 7), (10, 4);
10
2. CONSULTAS Y VALIDACIÓN
A continuación, se presentan las consultas solicitadas, debidamente
comentadas.
A. Consultas de Agregación
Se presentan tres (3) consultas que utilizan funciones de agregado
(COUNT, SUM, AVG) y GROUP BY.
USE `db_farmacias`;
-- Consulta 1 (COUNT): Contar la cantidad de empleados por
cada farmacia. [cite: 14]
-- Se agrupa por el nombre de la farmacia y se cuenta el N° de
legajo de los empleados.
SELECT
[Link] AS NombreFarmacia,
COUNT(e.nro_legajo) AS CantidadEmpleados
FROM
Farmacia f
JOIN
Empleado e ON f.id_farmacia = e.id_farmacia
GROUP BY
[Link]
ORDER BY
CantidadEmpleados DESC;
-- Consulta 2 (AVG): Calcular el precio promedio de los
productos (medicamento+presentación)
-- agrupados por Laboratorio. [cite: 15]
SELECT
[Link] AS Laboratorio,
AVG([Link]) AS PrecioPromedio
11
FROM
Producto p
JOIN
Medicamento m ON p.id_medicamento = m.id_medicamento
JOIN
Laboratorio l ON m.id_laboratorio = l.id_laboratorio
GROUP BY
[Link]
ORDER BY
PrecioPromedio DESC;
-- Consulta 3 (SUM): Obtener la cantidad total de unidades en
stock (sumando todos los productos)
-- para cada farmacia en la ciudad de 'Córdoba'.
SELECT
[Link] AS NombreFarmacia,
[Link] AS Ciudad,
SUM([Link]) AS StockTotalUnidades
FROM
Stock s
JOIN
Farmacia f ON s.id_farmacia = f.id_farmacia
JOIN
Ciudad c ON f.id_ciudad = c.id_ciudad
WHERE
[Link] = 'Córdoba'
GROUP BY
[Link], [Link]
ORDER BY
StockTotalUnidades DESC;
B. Consultas de JOIN
Se presentan tres (3) consultas que demuestran el uso de JOIN,
respondiendo a las necesidades específicas del enunciado.
12
USE `db_farmacias`;
-- Consulta 1 (INNER JOIN M:N): Consultar medicamentos
compuestos por una mono droga específica (ej. 'Ibuprofeno').
-- (Requisito del enunciado)
SELECT
m.nombre_comercial AS Medicamento
FROM
Medicamento m
JOIN
Medicamento_MonoDroga mm ON m.id_medicamento =
mm.id_medicamento
JOIN
MonoDroga d ON mm.id_monodroga = d.id_monodroga
WHERE
[Link] = 'Ibuprofeno';
-- Consulta 2 (INNER JOIN simple): Consultar medicamentos
de un laboratorio específico (ej. 'Bayer'). [cite: 19]
-- (Requisito del enunciado)
SELECT
m.nombre_comercial AS Medicamento
FROM
Medicamento m
JOIN
Laboratorio l ON m.id_laboratorio = l.id_laboratorio
WHERE
[Link] = 'Bayer';
-- Consulta 3 (INNER JOIN M:N): Consultar las presentaciones
y precios de un medicamento específico (ej. 'Tafirol').
-- (Requisito del enunciado)
SELECT
m.nombre_comercial AS Medicamento,
[Link] AS Presentacion,
13
[Link] AS Precio
FROM
Producto p
JOIN
Medicamento m ON p.id_medicamento = m.id_medicamento
JOIN
Presentacion pr ON p.id_presentacion = pr.id_presentacion
WHERE
m.nombre_comercial = 'Tafirol';
-- Consulta 4 (LEFT JOIN): Mostrar TODOS los empleados,
indicando su N° de Matrícula
-- si son farmacéuticos, y NULL si no lo son. [cite: 21]
SELECT
[Link],
[Link],
[Link],
fm.nro_matricula AS MatriculaFarmaceutico
FROM
Empleado e
LEFT JOIN
Farmaceutico fm ON e.nro_legajo = fm.nro_legajo
ORDER BY
[Link];
14