Soluciones del test de Álgebra Relacional

En realidad, el operador división del álgebra relacional es una combinación de

diferencias, producto cartesiano y proyecciones.

La concatenación natural del álgebra relacional

siempre se puede expresar como una secuencia de producto cartesiano, selección y proyección 

El álgebra relacional

está basado en la teoría de conjuntos

Para que dos relaciones sean compatibles en álgebra relacional

deben tener el mismo grado 

La selección en álgebra relacional

obtiene un conjunto de tuplas en el que todas cumplen una condición establecida 

Las relaciones derivadas en álgebra relacional

no tienen nombre ni alias 

En álgebra relacional, el producto cartesiano entre dos relaciones necesita

no necesita nada, se puede hacer siempre que queramos 

El nombre de las columnas del resultado de realizar una unión en álgebra relacional

son los mismos que los de la primera relación. 

En álgebra relacional, suponiendo que las columnas con el mismo nombre tienen los mismos dominios en cualquier tabla, y siendo T(a,b,c) y S(d,b,c,e), si efectúo T[a,b,c] DIVIDIDO S[b,c], el resultado es

nada, esta expresión no es correcta

Una consulta en álgebra relacional que sea parecida a "dame TODOS los vendedores de la provincia de Alicante"

no necesita el operador DIVISIÓN

En álgebra relacional, suponiendo que las columnas con el mismo nombre tienen los mismos dominios en cualquier tabla, y siendo T(a,b,c) y S(d,b,c,e), si efectúo T[a,b,c] DIVIDIDO (S[b,c]), el resultado es

una relación con una columna, R[a]. 

Uno de los indicadores de la importancia del álgebra dentro del modelo relacional es

            su uso en ciertos optimizadores de consultas. 

Una de las diferencias entre el álgebra relacional y SQL o los cálculos relacionales es que

el AR es procedimental, establece una secuencia de operaciones, mientras que los otros son declarativos

Una relación derivada se define en el álgebra relacional como

             la relación, tabla, resultado de operar en AR

Al efectuar una proyección de álgebra relacional en una tabla por una columna que NO sea clave candidata

dado que el resultado también es una relación, no es posible obtener duplicados de filas

Al efectuar un producto cartesiano de álgebra relacional de una tabla por si misma

por definición, nunca produce duplicados de tuplas

Si R1, R2 y R3 son 3 relaciones compatibles

puede que R3 sea el resultado de efectuar R1concatenadoR2, y en este caso sería igual que haber efectuado R1intersecciónR2

El operador concatenación natural del álgebra relacional, para poderse ejecutar apropiadamente

necesita atributos con el mismo dominio y con el mismo nombre 

Para evitar redundancias en una base de datos relacional

se aplica el proceso de normalización hasta obtener relaciones en 3FN (en la mayoría de los casos)

La forma normal de Boyce-Codd en una relación R

            se cumple cuando R está en tercera forma normal y tiene varias claves candidatas no solapadas

Un defecto de normalización en una base de datos relacional puede provocar anomalías

al modificar la información de una tabla, ya que un cambio simple de un dato podría afectar a varias tuplas. 

La dependencia funcional es

una relación entre atributos de una tabla de tal forma que un valor de uno determina unívocamente el valor del otro 

Una relación en tercera forma normal con 3 atributos puede tener, como máximo

tres claves candidatas 

La normalización

se utiliza actualmente más como un criterio de calidad en el diseño 

La primera forma normal garantiza que

los dominios contienen valores atómicos 

Una tabla en el modelo relacional

sólo puede estar en tercera forma normal si lo está en primera y en segunda 

La forma normal de Boice-Codd

se debe comprobar en tablas con dos o más claves candidatas 

La segunda forma normal persigue que

no haya dependencias funcionales incompletas 

La tercera forma normal busca que

no haya dependencias funcionales transitivas 

La forma nomal Boyce-Codd intenta eliminar

aquellos determinantes que no son clave candidata 

Cuando decimos que un atributo es "primo" es porque

pertenece a una clave candidata 

Si A determina funcionalmente a B, B a C, y C a A, siendo estos los únicos atributos de la relación a normalizar, ¿cuántas claves candidatas tiene la tabla?

            Tres.

Si en una tabla no hay dependencias funcionales entre sus atributos

esa tabla solo tiene una clave candidata 

Durante el proceso de normalización de una tabla

es imposible que se generen, en la misma tabla, dos claves ajenas solapadas en atributos 

Soluciones T10

T10.001- ¿Hay alguna fila en la tabla marca?

select exists (select * from marca)

T10.002- Email y nombre de los usuarios que no han pedido ninguna cámara.
select u.email, u.nomb

from usuario u

where not exists (select 1 from camara c,linped l, pedido p

where p.numPedido=l.numPedido and c.cod=l.articulo and u.email=p.usuario);

T10.003- Email y nombre de los usuarios que, habiendo realizado algún pedido, no han pedido ninguna cámara.
select u.email, u.nombre
from usuario u
where exists (select 1 from pedido p where u.email=p.usuario) and
not exists (select 1 from camara c,linped l, pedido p
where p.numPedido=l.numPedido and c.cod=l.articulo and u.email=p.usuario);

T10.004- Código y nombre del artículo que ha sido incluído en todos los pedidos.
select a.cod, a.nombre
from articulo a
where not exists (select 1 from pedido p where
not exists (select 1 from linped l where
l.numpedido=p.numpedido and l.articulo=a.cod));

T10.005- Código y nombre de los artículos que han sido solicitados en todos los pedidos del usuario acm@colegas.com.
select a.cod, a.nombre
from articulo a
were not exists (select 1 from pedido p where p.usuario='acm@colegas.com'

and not exists ( select 1 from linped l where p.numPedido=l.numPedido

and l.articulo=a.cod));

T10.006- ¿Hay alguna fila en la tabla marca? Si la respuesta es positiva, que muestre la palabra "sí".
select 'sí' respuesta
from dual
where exists (select 1 from marca);

T10.007- ¿Hay alguna fila en la tabla memoria? Si la respuesta es negativa, que muestre la palabra "no".
select 'no' respuesta
from dual
 where not exists (select 1 from memoria);

T10.008- Pedidos que incluyen cámaras y televisiones.
select * from pedido p
where exists (select 1 from linped l where l.numpedido=p.numpedido
and articulo in (select cod from tv))
and exists (select 1 from linped l where l.numpedido=p.numpedido
and articulo in (select cod from camara));

T10.009- Pedidos que incluyen cámaras y objetivos.
select * from pedido p
where exists (select 1 from linped l where l.numpedido=p.numpedido
and articulo in (select cod from camara))
and exists (select 1 from linped l where l.numpedido=p.numpedido
and articulo in (select cod from objetivo));

Soluciones T09

T09.001- ¿Cuántos artículos de cada marca hay?

select marca, count(*)
from articulo
group by marca;

T09.002- ¿Cuáles son las marcas que tienen menos de 150 artículos?

select marca, count(*)
from articulo
group by marca
having count(*)<150;

T09.003- ¿Cuáles son las marcas que tienen menos de 150 artículos (eliminar las marcas que sean null)?

select marca, count(*)
from articulo
where marca is not null
group by marca
having count(*)<150;

T09.004- Número de cámaras que tienen sensor CMOS

select count(*)
from camara
where sensor like 'CMOS%';

T09.005- Dni, nombre, apellidos y email de los usuarios que han realizado más de un pedido.

select dni, nombre, apellidos, email
from usuario u, pedido p
where u.email = p.usuario
group by dni,nombre,apellidos,email
having count(*)>1;

T09.006- Pedidos (número de pedido y usuario) de importe mayor a 4000 euros.

select p.numpedido,p.usuario  from pedido p, linped l  where p.numpedido=l.numpedido

group by p.numpedido,p.usuario

having sum(cantidad*precio)>4000; 

T09.007- Pedidos (número de pedido y usuario) con más de 10 artículos, mostrando esta cantidad.

select p.numPedido, p.usuario, sum(cantidad) from pedido p, linped l

                where P.numPedido=L.numPedido

group by p.numPedido,p.usuario

having sum(cantidad)>10;

T09.008- Pedidos (número de pedido y usuario) que contengan más de cuatro artículos distintos.

select p.numPedido,p.usuario,

count(distinct articulo)  from pedido p, linped l

 where p.numPedido=l.numPedido

group by p.numPedido,p.usuario

having count(distinct articulo)>4;

 T09.009- ¿Hay dos provincias que se llamen igual (con nombre repetido)?

select nombre,count(*)

from provincia

group by nombrehaving count(*)>1;

T09.010- ¿Hay algún pueblo con nombre repetido?

select pueblo,count(*)

from localidad group by pueblo

having count(*)>1; 

T09.011- Obtener el código y nombre de las provincias que tengan más de 100 pueblos.

select P.codp,P.nombre,count(*)

from provincia P, localidad L WHERE P.codp=L.provincia

group by P.codp,P.nombre

having count(*)>100;

T09.012- Ha habido un error en Tiendaonline y se han colado varios artículos sin stock en la cesta. Averigua el código de esos artículos y las veces que aparecen en la cesta. 

select c.articulo, count(c.articulo) from cesta c, stock s

where c.articulo=s.articulo              

and disponible=0  group by c.articulo;

T09.013- Clientes que hayan adquirido (pedido) más de 2 tv

select p.usuario, sum(cantidad)

from pedido p, linped l,articulo a, tv t

where p.numpedido=l.numpedido

and l.articulo=a.cod           

and a.cod=t.cod group by p.usuario having sum(cantidad)>2;

T09.014- ¿Cuántas veces se ha pedido cada artículo?. Si hubiesen artículos que no se han incluido en pedido alguno también se mostrarán. Mostrar el código y nombre del artículo junto con las veces que ha sido incluido en un pedido (solo si ha sido incluido, no se trata de la "cantidad").

select cod, nombre, count(numpedido)

from articulo a left join linped l on (a.cod=l.articulo)

group by cod, nombre;

T09.015- Código y nombre de las provincias que tienen más de 50 usuarios (provincia del usuario no de la dirección de envío).

select p.codp, p.nombre from provincia p, usuario u

where p.codp=u.provincia

group by p.codp, p.nombre

having count(email)>50;

T09.016- Cantidad de artículos con stock 0

select count(*)

from stock where disponible=0;

T09.017- Cantidad de artículos que no son ni memoria, ni tv, ni objetivo, ni cámara ni pack.

select  count(*)

from articulo

where cod not in (select cod from camara) 

and cod not in (select cod from tv)                

and cod not in (select cod from memoria)   

and cod not in (select cod from objetivo)    

and cod not in (select cod from pack);

T09.018- Número de artículos pedidos por provincia (provincia del usuario no de la dirección de envío). Mostrar el código de la provincia, su nombre y la cantidad de veces que se ha pedido el artículo; si la provincia no tiene asociada esta cantidad, mostrar "0" en esa columna.

select pr.codp, pr.nombre,

IFNULL(sum(cantidad),0)

from provincia pr               

left join usuario u on (pr.codp=u.provincia)                 left join pedido p on (p.usuario=u.email)     

left join linped l on (p.numpedido=l.numpedido)group by pr.codp,pr.nombre;

Soluciones T08

T08.001- Obtener el precio total por línea para el pedido 1, en la salida aparecerá los campos numlinea, articulo y el campo calculado total. 

                select linea, articulo, (precio * cantidad)
                from linped
                where numPedido = 1;

T08.002- Obtener la cantidad de provincias distintas de las que tenemos conocimiento de algún usuario.

                select count(distinct provincia)
                from usuario;

T08.003- Cantidad de usuarios de nuestra BD.

                select count(*)
                from usuario;

T08.004- Número de articulos con precio de venta mayor de 200 euros.

                select count(*)
                from articulo
                where pvp > 200;

T08.005- Total en euros de la cesta del usuario "bmm@agwab.com".

select sum(ar.pvp)
 from cesta ce, articu

 where (ce.usuario = 'bmm@agwab.com')
and (ce.articulo = ar.cod);

T08.006- Tamaño máximo de pantalla para las televisiones.

                select MAX(pantalla)
                from tv;

T08.007- Media de precios de venta al público distintos de los articulos, redondeada a dos decimales.

                select round(avg(distinct pvp),2)
                from articulo;

T08.008- Nombre y precio de los articulos con el mínimo stock disponible.

select ar.nombre,ar.pvp
 from articulo ar, stock sto
 where (ar.cod = sto.articulo)
 and disponible = (select min(disponible) from stock);

T08.009- Número de pedido, fecha y nombre y apellidos del usuario de las lineas de pedido cuyo total en euros es el más alto.

                select p.numPedido,fecha,nombre,apellidos
                from pedido p,linped l,usuario u
                where p.usuario=email
                and p.numPedido=l.numPedido
                and (cantidad*precio)=(select max(cantidad*precio) from linped);

T08.010- Máximo, mínimo y media de precio de venta de los artículos.

                select MAX(pvp) max_Pvp, MIN(pvp) min_Pvp, AVG(pvp) media_Pvp
                from articulo; 

T08.011- Código, nombre, pvp y fecha de incorporación del artículo a la cesta más reciente.

                select cod, nombre, pvp
                from cesta, articulo
                where articulo=cod and fecha=(select MAX(fecha) from cesta);

T08.012- Cantidad de artículos que están descatalogados.

                select count(*) Num_Art_Desc
                from stock
                where entrega='Descatalogado'

T08.013- Precio máximo del artículo en stock que será entregado próximamente.

                select max(pvp)
                from articulo, stock
                where cod=articulo
                and entrega='Próximamente';

T08.014- Nombre, código y disponible en stock para todos los artículos cuyo código acabe en 3, siendo ese disponible el mínimo de toda la tabla.

select nombre, cod, disponible MinDisp
                from stock, articulo
                where articulo=cod and cod like '%3'
                and disponible=(select MIN(disponible) from stock);

T08.015- Precio máximo, mínimo y medio en los pedidos que incluyen el artículo “Bravia KDL-32EX402”

                select MAX(precio) Max_Prec, MIN(precio) Min_Prec, AVG(precio) Prec_Med

                from linped l, articulo a

                where a.cod=l.articulo and a.nombre='Bravia KDL-32EX402';

T08.016- Cantidad total que se ha pedido de los artículos cuyo nombre empieza por "UE22".

                select SUM(cantidad) cant_Tot
                from linped, articulo
                where articulo=cod and nombre like 'UE22%';

T08.017- Precio medio de los artículos incluidos en la línea de pedido número 4, redondeado a 3 decimales.

select round(avg(precio),3) PrecioMedio
from linped
where linea=4;

T08.018- Número de pedido, nombre, teléfono y email de usuario del pedido (o los pedidos) que contiene líneas de pedido cuyo precio sea igual al precio más alto de entre todas las segundas líneas de todos los pedidos.

select l.numPedido, nombre, telefono, email
from usuario u, pedido p, linped l
where l.numPedido=p.numPedido
                and p.usuario=u.email
                and precio=(select MAX(precio) from linped where linea=2);

T08.019- Diferencia entre el precio máximo y el precio mínimo del pedido número 30.

select (MAX(precio)-MIN(precio)) Diff
from linped
where numPedido=30;

T08.020- Código, nombre, precio de venta del artículo que más hay en stock.

select cod,nombre,pvp
from articulo,stock
where cod=articulo and disponible = (select MAX(disponible) from stock);

T08.021- Fecha de nacimiento del usuario más viejo.

                select MIN(nacido) from usuario;