Caso práctico Video Patito

La cadena de Video-Patito ha decidido, para mejorar su servicio, emplear una base de datos para almacenar la información referente a las películas que ofrece en alquiler.

Se propone el siguiente DER el cual puede modificar a sus necesidades

Esta información es la siguiente:

La BD deberá responder a consultas del tipo:

Consulta : Títulos de las películas que existen en el videoclub

SELECT nombrePelicula FROM pelicula;

Consulta 2: Títulos de las películas en las que participa Robert De Niro.

SELECT A.nombreActor Actor,
	P.nombrePelicula Pelicula
  FROM pelicula P,
	  actua D,
	  actor A
  WHERE A.NombreActor = 'Robert de Niro'
  AND  A.idActor = D.idActor
  AND	D.idPelicula = P.idPelicula;

Consulta 3: Número de ejemplares por película en el videoClub

SELECT P.nombrePelicula Film, A.Ejemplares
  FROM pelicula P,  (
       SELECT idPelicula,
              COUNT(idPelicula) AS Ejemplares
       FROM ejemplar
       GROUP BY idPelicula) A
       WHERE P.idPelicula = A.idPelicula
       ORDER BY Film;

Consulta 4: ¿Quiénes son los directores de nacionalidad X que tienen películas en el videoclub?

SELECT  P.nombrePelicula "Nombre Pelicula",
	     P.year           Año,
	     R.nombreRol      Rol,
        A.Actor
  FROM	 pelicula P, rol R, actua D,
        SELECT  A.idActor, A.nombreActor Actor,
                N.nombreNacionalidad
        FROM actor A, nacionalidad N
        WHERE N.idNacionalidad = A.idNacionalidad
              AND  N.nombreNacionalidad = 'Mexicana') A
  WHERE (D.idRol = R.idRol)
        AND	 (D.idPelicula = P.idPelicula)
        AND  (R.nombreRol  = 'Director')
        AND (A.idActor    = D.idActor);

Consulta 5: Nombre de las películas del genero X.

SELECT P.nombrePelicula, G.nombreGenero
  FROM   pelicula P, genero G
  WHERE P.idGenero = G.idGenero
        AND nombreGenero = 'Drama';

Consulta 6: Películas dirigidas por director

  SELECT  P.nombrePelicula 'Nombre Pelicula',
         P.year          Año,
         R.nombreRol     Rol,
         A.nombreActor   Actor
  FROM	 pelicula P, rol R, actua D, actor A
  WHERE  (D.idRol = R.idRol)
         AND  (R.nombreRol = 'Director')
         AND	 (D.idPelicula = P.idPelicula)
         AND  (A.idActor = D.idActor);

Consulta 7: Películas donde X participa como director y protagonista.

  SELECT P.nombrePelicula, Part.nombreActor
  FROM pelicula P, (SELECT  A.nombreActor,
                           COUNT(R.nombreRol) AS nRol,
                           Ac.idPelicula
                   FROM   rol R, actua Ac, actor A
                   WHERE  A.idActor = Ac.idActor
                          AND  Ac.idRol = R.idRol
                   GROUP BY A.nombreActor,
                         Ac.idPelicula
                  ) Part
  WHERE (P.idPelicula = Part.idPelicula)
         AND (Part.nRol > 1)

Consulta 8: ¿Cuáles son las películas que tiene alquiladas el socio X?

SELECT S.nombreSocio     AS Nombre,
       R.fechaRenta      AS Rentada,
       P.nombrePelicuala AS Film
  FROM pelicula P, ejemplar E, Renta R, socio S
  WHERE R.fechaEntrega IS NULL
        AND S.IFE = R.IFE
        AND R.idEjemplar = E.idEjemplar
        AND E.idPelicula = P.idPelicula
        AND S.nombreSocio = 'Noemi Pedroza Ortiz';

Consulta 9: Películas con más de 3 días sin entregar.

SELECT  S.nombreSocio    AS nombre,
        R.fechaRenta     AS rentada,
        P.nombrePelicula AS film
FROM    pelicula P, ejemplar E, renta R, socio S
WHERE   S.IFE = R.IFE
  AND   R.idEjemplar =  E.idEjemplar
  AND   E.idPelicula    =  P.idPelicula
  AND   R.fechaEntrega IS NULL
  AND   SYSDATE > (R.fechaRenta + 3);

Se desea mantener la información de los socios aunque estos se den de baja, para lo que se crea una tabla SOCIO_BAJA, que contiene los datos de socio y la fecha de baja y que se actualizará cada vez que se borre un socio.

CREATE TABLE socioBaja (
  IFE        CHAR(13) NOT NULL, 
  nombreSocio CHAR(40) NOT NULL, 
  direccion   CHAR(50) NOT NULL, 
  telefono    CHAR(13) NOT NULL,
  fecha_Baja  DATE; 
  CONSTRAINT PK_SocioBaja PRIMARY KEY (IFE)
);
CREATE OR REPLACE TRIGGER disSocio
  AFTER DELETE ON socio
  FOR EACH ROW
BEGIN
  INSERT INTO socioBaja
    VALUES (:OLD.IFE, :OLD.nombreSocio,
             :OLD.direccion,
             :OLD.telefono, SYSDATE);
END;


Anterior
Valid XHTML
home

Siguiente