UNIVERSIDAD DE EXTREMADURA

Centro Universitario de Mérida

Base de Datos
Examen final de Julio de 2014
4/7/2014

PRÁCTICA

EJERCICIO VIVEROS

Se desea diseñar una Base de Datos para gestionar los empleados y productos a la venta en una cadena de viveros
dedicados a la venta de diversos productos relacionados con la jardinería. Los supuestos que hay que recoger en la Base de
Datos son los siguientes:
La cadena de viveros dispone de varios viveros en la provincia de Badajoz identificados por un código de tienda y de los
que se almacenará un nombre, una dirección, un código postal, varios teléfonos, un código de ciudad donde está y un nombre
de ciudad. Cada vivero dispone de un responsable que será uno de los empleados que trabaja en el vivero (es necesario
almacenar durante qué periodos de tiempo ha sido responsable cada empleado, es decir, una fecha de inicio, que entrará a
formar parte de la clave, y un tiempo en días).
Los productos de cada vivero tienen asignado un código de producto y nos interesa guardar el nombre del producto, el
precio de compra y el precio de venta. También se desea almacenar el stock y la cantidad mínima que tiene cada uno de los
productos en cada uno de los viveros. Se debe tener en cuenta que cada producto debe ser solamente de uno de estos tres
tipos: las plantas de las que se quiere guardar su nombre científico y una breve descripción de los cuidados que requiere; los
accesorios de jardinería de los que se quiere guardar el código de color y el nombre de color; y por último, los artículos de
decoración. Estos productos se distribuyen en zonas, cada una de ellas identificada por un código, un nombre y existen tres
tipos de zonas: zona exterior de regadío, zona interior climatizada y zona de caja.
Los empleados están asignados a una determinada zona en cada vivero, de tal forma que pueden existir varios empleados
en una misma zona. Además, a lo largo del tiempo, los empleados pueden moverse de tanto de zona como de vivero según
las necesidades, por ellos se desea guardar un histórico de asignaciones con la fecha, que formara parte de la clave, el tiempo
en días que ha estado asignado y la función que realizó. De los empleados se quiere guardar su DNI como identificador, su
nombre, los teléfonos de contacto que tiene, un código de idioma que habla, el nombre del idioma que habla, un código de
país de origen y un nombre de país de origen.
En cuanto al proceso de venta que realizan los empleados en los viveros de los productos a los clientes, sólo se almacenan
los pedidos que realizan estos clientes (código de cliente, nombre, dni, dirección, teléfono y fecha de alta). Es decir, los
clientes realizan pedidos en un determinado vivero de distintos productos que sirve un empleado concreto, cuando esto se
produce, se desea almacenar, la fecha del pedido, que formará parte de la clave, el precio de venta del cada producto, las
unidades pedidas de cada producto, el descuento de cada producto, y el precio del porte del pedido completo en el caso de
que se haya contratado. Hay que tener en cuenta que cada pedido lo gestiona un solo empleado en cada vivero.
De cada pedido producido completo es pagado por un único cliente que puede ser el mismo al que se le vendió u otro que
también debe estar dado de alta en nuestra base de datos. Cuando se produce este pago se desea almacenar un número de
recibo, que será la clave primaria, el importe total pagado, la fecha de pago y la forma que ha sido pagado.
Con estos requerimientos se desea diseñar un sistema de información debiendo aportar como documentación mínima del
resultado:
a) El modelo Entidad-Relación completo.
b) Reducción al modelo Relacional correspondiente obteniendo el diagrama relacional.
c) Normalización de datos sobre el diagrama relacional.
Tiempo 2 horas

Profesor: Juan Ángel Contreras Vas Página 1 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014
Ejercicio 2.- Conocidas las siguientes tablas de la base de datos relacional obtenida tras el diseño:

VIVEROS
Que almacena en cada fila los datos referidos a los distintos viveros que tiene la empresa.
Clave primaria: VIV_id_vivero

CIUDADES
Que almacena en cada fila los datos referidos a las distintas ciudades donde están ubicados los viveros.
Clave primaria: CIU_id_ciudad

VIVTEL
Que almacena en cada fila los datos de cada uno de los teléfonos de cada vivero.
Clave primaria: VIT_id_vivero + VIT_telefono
Claves ajenas: VIT_id_vivero (VIVEROS.VIV_id_vivero)

PAISES
Que almacena en cada fila los datos referidos a los distintos países a los que pueden pertenecer los empleados.
Clave primaria: PAI_id_pais

IDIOMAS
Que almacena en cada fila los datos referidos a los distintos idiomas que pueden hablar los empleados.
Clave primaria: IDI_id_idioma

EMPLEADOS
Que almacena en cada fila los datos referidos a los distintos empleados que trabajan en los viveros.
Clave primaria: EMP_dni
Claves ajenas: EMP_id_pais (PAISES.PAI_id_pais)
EMP_id_idioma (IDIOMAS.IDI_id_idioma)

EMPTEL
Que almacena en cada fila los datos de cada uno de los teléfonos de cada empleado.
Clave primaria: EMT_dni + EMT_telefono
Claves ajenas: EMT_dni (EMPLEADOS.EMP_dni)

COLORES
Que almacena en cada fila los datos referidos a los distintos colores que pueden tener los accesorios de jardinería.
Clave primaria: COL_id_color

RESPONSABLES
Que almacena en cada fila los datos referidos a los empleados que han sido responsables de viveros junto la fecha de
comienzo y el tiempo que ha estado de responsable.
Clave primaria: RES_id_vivero + RES_fecha_inicio
Claves ajenas: RES_id_vivero (VIVEROS.VIV_id_vivero)
RES_dni (EMPLEADOS.EMP_dni)

ZONAS
Que almacena en cada fila las zonas en las que están distribuidas los viveros. El atributo ZON_tipo tiene los valores REG
para las zonas de regadío, CLI para la zona climatizada o CAJ para la zona de caja.
Clave primaria: ZON_id_zona

PRODUCTOS
Que almacena en cada fila los datos correspondientes a los distintos productos que se venden en los viveros. El atributo
PRO_tipo tiene los valores PLA para las plantas, JAR para los artículos de jardinería o DEC para los artículos de
decoración.
Clave primaria: PRO_id_producto
Claves ajenas: PRO_id_zona (ZONAS.ZON_id_zona)
PRO_id_color (COLORES.COL_id_color)

Profesor: Juan Ángel Contreras Vas Página 2 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014

EXISTENCIAS
Que almacena en cada fila los datos correspondientes al stock, es decir, la cantidad de producto que hay en cada uno de los
viveros.
Clave primaria: EXI_id_vivero + EXI_id_producto
Claves ajenas: EXI_id_vivero (VIVEROS.VIV_id_vivero)
EXI_id_producto (PRODUCTOS.PRO_id_producto)

ASIGNACIONES
Que almacena en cada fila los datos históricos de cada uno de los empleados que han estado trabajando en cada zona de cada
vivero con el tiempo que ha estado en ellas y la función que ha realizado.
Clave primaria: ASI_id_vivero + ASI_id_zona + ASI_dni + ASI_fecha
Claves ajenas: ASI_id_vivero (VIVEROS.VIV_id_vivero)
ASI_id_zona (ZONAS.ZON_id_zona)
ASI_dni (EMPLEADOS.EMP_dni)

CLIENTES
Que almacena en cada fila los datos de cada uno de los clientes que han realizado pedidos en cualquier vivero o ha pagado
alguno de los pedidos realizados.
Clave primaria: CLI_id_cliente

PEDIDOS_CAB
Que almacena en cada fila los datos generales de cada uno de los pedidos realizado por los clientes.
Clave primaria: PEC_id_vivero + PEC_id_cliente + PEC_dni + PEC_fecha_pedido
Claves ajenas: PEC_id_vivero (VIVEROS.VIV_id_vivero)
PEC_id_cliente (CLIENTES.CLI_id_cliente)
PEC_dni (EMPLEADOS.EMP_dni)

PEDIDOS_LIN
Que almacena en cada fila los datos particulares de los productos de cada pedido realizado por los clientes.
Clave primaria: PEL_id_vivero + PEL_id_cliente + PEL_dni + PEL_fecha_pedido + PEL_id_producto
Claves ajenas: PEL_id_vivero + PEL_id_cliente + PEL_dni + PEL_fecha_pedido (PEDIDOS_CAB.PEC_id_vivero +
PEC_id_cliente + PEC_dni + PEC_fecha_pedido)
PEL_id_producto (PRODUCTOS.PRO_id_producto)

PAGOS
Que almacena en cada fila los datos de los pagos que realizan los clientes de cada uno de los pedidos.
Clave primaria: PAG_num_recibo
Claves ajenas: PAG_id_vivero + PAG_id_cliente_pe + PAG_dni + PAG_fecha_pedido
(PEDIDOS_CAB.PEC_id_vivero + PEC_id_cliente + PEC_dni + PEC_fecha_pedido)
PAG_id_cliente_pa (CLIENTES.CLI_id_cliente)

PAGOS_PRO
Que almacena en cada fila el detalle de los productos que componen el pago.
Clave primaria: PAP_num_recibo + PAP_id_producto
Claves ajenas: PAP_num_recibo (PAGOS.PAG_num_recibo)
PAP_id_producto (PRODUCTOS.PRO_id_producto)

Profesor: Juan Ángel Contreras Vas Página 3 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014
A la vista del esquema de base de datos relacional siguiente:

Profesor: Juan Ángel Contreras Vas Página 4 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014

Obtener mediante el uso de sentencias SQL, con una EXPLICACIÓN previa pormenorizada y
OBLIGATORIA de los pasos a seguir que no sean obvios, los siguientes procesos de gestión:

1. Se desea incrementar el precio de venta en un 5% de todos los productos de tipo “PLANTAS” que
han tenido pedidos en el año anterior al año en curso.
Puntos 3

2. Se desea sacar todos los productos de tipo “ACCESORIOS DE JARDINERIA”, que han sido
pedidos en el año anterior al año en curso, en el vivero de “ARANCHA” de la ciudad de
“SEVILLA” y que hayan sido servidos por los empleados que hablan el idioma “INGLES”.
Ordenados de mayor a menor por precio y de menor a mayor por nombre de producto con el
siguiente formato de salida:

Nombre Producto Precio Pedido Cantidad Pedida Descuento Pedido

Puntos 3

3. Se desea sacar aquellos productos que son “ACCESORIOS DE JARDINERIA” que tienen color
“ROJO”, que han sido pedidos más de 20 veces en los dos últimos años (anteriores al año actual),
ordenados descendentemente por el número de veces que han sido pedidos y con el siguiente
formato de salida:

Nombre Zona Nombre Producto Año Número de veces Pedidos

Puntos 4

Tiempo 1 hora

Profesor: Juan Ángel Contreras Vas Página 5 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014

DIAGRAMA ENTIDAD/ATRIBUTO Y RELACION/ATRIBUTO

Profesor: Juan Ángel Contreras Vas Página 6 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014

DIAGRAMA ENTIDAD/RELACION

Profesor: Juan Ángel Contreras Vas Página 7 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014

ESQUEMA RELACIONAL PREVIO

Profesor: Juan Ángel Contreras Vas Página 8 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014

DEPENDENCIAS FUNCIONALES

Profesor: Juan Ángel Contreras Vas Página 9 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014

Profesor: Juan Ángel Contreras Vas Página 10 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014

Profesor: Juan Ángel Contreras Vas Página 11 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014

ESQUEMA RELACIONAL DEFINITIVO

Profesor: Juan Ángel Contreras Vas Página 12 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)
Examen de Julio de 2014 Base de Datos 4/7/2014
SENTENCIAS SQL

SENTENCIA 1

-- 1.- Se desea incrementar el precio de venta en un 5% de todos los productos de tipo “PLANTAS” que han tenido pedidos
-- en el año anterior al año en curso.
--
update productos set pro_precio_ven = pro_precio_ven * 1.05
from pedidos_lin
where pel_id_producto = pro_id_producto and
pro_tipo like 'PLA' and
year(pel_fecha_pedido) = year(getdate())-1

SENTENCIA 2

-- 2. Se desea sacar todos los productos de tipo “ACCESORIOS DE JARDINERIA”, que han sido pedidos en el año
-- anterior al año en curso, en el vivero de “ARANCHA” de la ciudad de “SEVILLA” y que hayan sido servidos por los
-- empleados que hablan el idioma “INGLES”. Ordenados de mayor a menor por precio y de menor a mayor por nombre de
-- producto con el siguiente formato de salida:
--
-- Nombre Producto Precio Pedido Cantidad Pedida Descuento Pedido
--
select distinct pro_nombre as 'Nombre Producto', pel_precio_pro as 'Precio Pedido',pel_cantidad as 'Cantidad Pedida',
pel_descuento as 'Descuento pedido'
from pedidos_lin inner join productos on pel_id_producto = pro_id_producto and
pro_tipo like 'JAR'
inner join pedidos_cab on pel_id_vivero = pec_id_vivero and pel_id_cliente = pec_id_cliente and
pel_dni = pec_dni and pel_fecha_pedido = pec_fecha_pedido
inner join viveros on pec_id_vivero = viv_id_vivero and viv_nombre like 'ARANCHA'
inner join ciudades on viv_id_ciudad = ciu_id_ciudad and ciu_nombre_ciudad like 'SEVILLA'
inner join empleados on pec_dni = emp_dni
inner join idiomas on emp_id_idioma = idi_id_idioma and idi_nombre_idioma like 'INGLES'
where year(pel_fecha_pedido)= year(getdate())-1
order by 2 desc,1 asc

SENTENCIA 3

-- 3. Se desea sacar aquellos productos que son “ACCESORIOS DE JARDINERIA” que tienen color “ROJO”, que han
-- sido pedidos más de 20 veces en los dos últimos años (anteriores al año actual), ordenados descendentemente por el
-- número de veces que han sido pedidos y con el siguiente formato de salida:
--
-- Nombre Zona Nombre Producto Año Número de veces Pedidos
--
select zon_nombre as 'Nombre Zona', pro_nombre as 'Nombre Producto', year(pel_fecha_pedido) as 'Año', count(*) as
'Número de veces Pedidos'
from pedidos_lin inner join productos on pel_id_producto = pro_id_producto and pro_tipo like 'JAR'
inner join colores on pro_id_color = col_id_color and col_nombre_color like 'ROJO'
inner join zonas on pro_id_zona = zon_id_zona
where year(pel_fecha_pedido) between year(getdate())-2 and year(getdate())-1
group by zon_nombre,pro_nombre,year(pel_fecha_pedido)
having count(*) > 20
order by 4 desc

Profesor: Juan Ángel Contreras Vas Página 13 de 13

Área: Lenguajes y Sistemas Informáticos
Departamento: Ingeniería de Sistemas Informáticos y Telemáticos (Centro Universitario de Mérida)