Gestión Avanzada de Bases de Datos Musicales con SQL

Enviado por Chuletator online y clasificado en Informática y Telecomunicaciones

Escrito el 25 de Junio de 2025 en español con un tamaño de 19,02 KB

Preparación del Entorno

Se elimina la tabla RADIO y sus restricciones asociadas para asegurar un entorno limpio antes de la creación de nuevas estructuras.

DROP TABLE RADIO CASCADE CONSTRAINTS;

Definición del Esquema

Creación de la Tabla TEMA

Definición de la tabla TEMA con sus atributos y restricciones de integridad.

CREATE TABLE TEMA(
    codigo          VARCHAR2(20),
    titulo          VARCHAR2(120)   NOT NULL,
    duracion        NUMBER(4)   NOT NULL,
    autor           VARCHAR2(120)   NOT NULL,
    LP              NUMBER(10)  NOT NULL,
    caraLP          CHAR(1) NOT NULL,
    pistaLP         NUMBER(2)   NOT NULL,
    sencillo        NUMBER(10),
    caraSencillo    CHAR(1),
    pistaSencillo   NUMBER(2),
    -- Restricción: Un autor no puede componer dos temas con el mismo título
    CONSTRAINT CHK_TEMA_AUTOR_TITULO UNIQUE (autor, titulo),
    -- Restricción: Los campos sencillo, caraSencillo y pistaSencillo deben ser los tres nulos o los tres no nulos
    CONSTRAINT CHK_TEMA_SENCILLO_CONSISTENCIA CHECK (
        (sencillo IS NULL AND caraSencillo IS NULL AND pistaSencillo IS NULL) OR
        (sencillo IS NOT NULL AND caraSencillo IS NOT NULL AND pistaSencillo IS NOT NULL)),
    CONSTRAINT PK_TEMA PRIMARY KEY (codigo)
);

Operaciones DML

Inserción de Datos

Inserción en DISCOGRAFICA

Se insertan registros iniciales en la tabla DISCOGRAFICA.

INSERT INTO DISCOGRAFICA (nombre, telefono, direccion, pais) VALUES ('RCA','123456789','C/RCA sn','Espanya');
INSERT INTO DISCOGRAFICA (nombre, telefono, direccion, pais) VALUES ('Columbia Records','123456789','Street Columbia Records','EEUU');
INSERT INTO DISCOGRAFICA (nombre, telefono, direccion, pais) VALUES ('Capitol Records','123456789','Street Capitol Records','EEUU');
INSERT INTO DISCOGRAFICA (nombre, telefono, direccion, pais) VALUES ('Sony Music','123456789','Street Sony Music Records','EEUU');
INSERT INTO DISCOGRAFICA (nombre, telefono, direccion, pais) VALUES ('Warner Bros. Records','123456789','C/Warner sn','Espanya');
INSERT INTO DISCOGRAFICA (nombre, telefono, direccion, pais) VALUES ('Epic','123456789', 'C/Epic sn','Espanya');

Modificación del Esquema y Datos

Restricciones de País en DISCOGRAFICA

Se añade una restricción CHECK a la columna pais de la tabla DISCOGRAFICA para limitar los valores permitidos.

ALTER TABLE DISCOGRAFICA
MODIFY pais CHECK (pais IN ('Espanya', 'EEUU'));

Actualización y Manejo de Restricciones

Se intenta actualizar un registro con un valor de país no permitido, lo que provoca un error debido a la restricción. Dado que la restricción no tiene un nombre definido, es necesario buscarla para poder eliminarla y luego añadirla nuevamente con un nombre explícito.

UPDATE DISCOGRAFICA
SET pais = 'Francia'
WHERE nombre = 'RCA';

-- Búsqueda del nombre de la restricción
SELECT constraint_name
FROM user_constraints
WHERE table_name = 'DISCOGRAFICA'
AND constraint_type = 'C';

-- Eliminación de la restricción (reemplazar 'nombre_de_la_restricción' con el nombre real encontrado)
ALTER TABLE DISCOGRAFICA
DROP CONSTRAINT nombre_de_la_restricción;

-- Adición de la restricción con un nombre
ALTER TABLE DISCOGRAFICA ADD CONSTRAINT CK_DISC_PaisValido CHECK(pais IN ('Espanya', 'EEUU'));

-- Reintento de actualización de la tupla (seguirá fallando si 'Francia' no está permitido)
UPDATE DISCOGRAFICA
SET pais = 'Francia'
WHERE nombre = 'RCA';

Restricción de Unicidad en RADIO

Se añade una restricción de unicidad a la columna web de la tabla RADIO.

ALTER TABLE RADIO ADD CONSTRAINT UQ_RADIO_WEB UNIQUE(web);

Deshabilitación de Restricciones

Se desactiva la restricción de unicidad sin eliminarla, permitiendo la inserción de valores duplicados temporalmente.

ALTER TABLE RADIO DISABLE CONSTRAINT UQ_RADIO_WEB;

Actualización de Datos de Radio

Se cambia la URL web para las emisoras 'RNE1' y 'RNE3'.

UPDATE RADIO 
SET web='https://www.rtve.es'
WHERE nombre IN('RNE1', 'RNE3');

Adición de Columna con Valor por Defecto

Se consulta la tabla RADIO y se intenta añadir una columna Fecha_Fundacion como NOT NULL. Esto falla porque ya hay datos en la tabla.

SELECT * FROM RADIO;
ALTER TABLE RADIO ADD Fecha_Fundacion DATE NOT NULL;

Permitir Valores Nulos

Una alternativa es permitir que la nueva columna acepte valores nulos.

ALTER TABLE RADIO ADD Fecha_Fundacion DATE NULL;

Añadir Valor por Defecto

Otra opción es añadir la columna con un valor por defecto. SYSDATE es la fecha y hora actual del sistema.

ALTER TABLE RADIO
ADD Fecha_Fundacion DATE DEFAULT SYSDATE NOT NULL;

Modificación de Nulabilidad de Columna

Se modifica la columna telefono en la tabla RADIO para que acepte valores nulos.

ALTER TABLE RADIO
MODIFY telefono NULL;

Se comprueba que ahora se puede insertar un valor nulo en la columna telefono.

INSERT INTO RADIO (nombre, direccion, web, email,telefono ,Fecha_Fundacion) VALUES ('KISS FM', 'Direccion de RTVE', 'https://www.kissfm.es', '[email protected]', null, SYSDATE);

Se elimina el registro insertado para limpiar la tabla.

DELETE FROM RADIO 
WHERE nombre = 'KISS FM';

Consultas SQL

Consultas Básicas

Nombre de Grupos Españoles

Consulta para obtener los nombres de los grupos con nacionalidad 'Espanya'.

SELECT nombre
FROM GRUPO
WHERE pais = 'Espanya';

Emisoras de Radio con Dominio .es

Consulta el nombre y la web de las emisoras de radio cuyo dominio web es de España (contiene '.es/' o termina en '.es').

SELECT* FROM RADIO;
SELECT nombre, web
FROM RADIO
WHERE web LIKE '%.es/%' OR web LIKE '%.es';

Grupos y Copias Vendidas

Consulta el nombre de los grupos y el número total de copias vendidas, ordenados de mayor a menor. El alias AS se utiliza para renombrar la columna.

SELECT V.grupo AS nombre, SUM(V.copiasVendidas) AS total_copias_vendidas
FROM 
    VINILO V
GROUP BY 
    V.grupo
ORDER BY 
    total_copias_vendidas DESC;

Autores de Temas en Sencillos

Consulta los nombres de los autores (sin repetir) de los temas que se encuentran en sencillos.

SELECT DISTINCT autor
FROM TEMA
WHERE sencillo IS NOT NULL;

LPs con Sencillos Editados

Consulta el ISVN del LP y el número de sencillos editados para aquellos LPs que tienen sencillos.

SELECT * FROM SENCILLO;
SELECT count(*), ISVNLP
FROM SENCILLO
GROUP BY ISVNLP;

Detalle de Temas por LP

Consulta la duración, título y autor de los temas, junto con el título del LP al que pertenecen.

Sintaxis antigua:

SELECT T.duracion, T.titulo, T.autor, L.titulo
FROM TEMA T, LP L
WHERE LP = ISVN;

Con la sintaxis moderna y nombres de columna explícitos:

SELECT T.duracion, T.titulo AS tema_titulo, T.autor, L.titulo AS lp_titulo
FROM TEMA T
JOIN LP L ON T.LP = L.ISVN;

LPs con Ventas Superiores a Lanzamiento

Consulta el título y el grupo de los LPs cuyas ventas superan las copias de lanzamiento.

Sintaxis antigua:

SELECT L.titulo AS lp_titulo, V.grupo
FROM LP L, VINILO V
WHERE L.ISVN = V.ISVN
  AND V.copiasVendidas > L.copiasLanzamiento;

Sintaxis moderna:

SELECT L.titulo AS lp_titulo, V.grupo
FROM LP L
JOIN VINILO V ON L.ISVN = V.ISVN
WHERE V.copiasVendidas > L.copiasLanzamiento;

Temas No Emitidos en Radio

Consulta los títulos de los temas que nunca se han emitido en la radio.

Usando NOT IN:

SELECT T.titulo
FROM TEMA T
WHERE T.codigo NOT IN (
    SELECT E.tema
    FROM EMISION E
);

Usando LEFT JOIN:

SELECT T.titulo
FROM TEMA T
LEFT JOIN EMISION E ON T.codigo = E.tema
WHERE E.tema IS NULL;

LPs de Grupos Españoles

Consulta el ISVN y el título de los LPs que pertenecen a grupos españoles.

Sintaxis antigua:

SELECT L.ISVN, L.titulo
FROM LP L, VINILO V, GRUPO G
WHERE L.ISVN = V.ISVN
  AND V.grupo = G.nombre
  AND G.pais = 'Espanya';

Sintaxis moderna:

SELECT L.ISVN, L.titulo
FROM LP L
JOIN VINILO V ON L.ISVN = V.ISVN
JOIN GRUPO G ON V.grupo = G.nombre
WHERE G.pais = 'Espanya';

Consultas Avanzadas

Duración Total de Sencillos

Consulta el ISVN y la duración total de los sencillos cuya duración total sea superior a 500 segundos. Asegurarse de que solo se incluyan temas de sencillos.

SELECT sencillo AS ISVN, SUM(duracion) AS duracion_total
FROM TEMA
WHERE sencillo IS NOT NULL
GROUP BY sencillo
HAVING SUM(duracion) > 500;

Sencillos en Ranking de 1989

Consulta el ISVN del sencillo, el título del LP al que pertenece y el número de semanas en primer lugar del ranking durante el año 1989.

SELECT S.ISVN, L.titulo, COUNT(R.anyo) AS semanas_en_primer_lugar
FROM SENCILLO S
JOIN LP L ON S.ISVNLP = L.ISVN
JOIN RANKING R ON S.ISVN = R.primero
WHERE R.anyo = 1989
GROUP BY S.ISVN, L.titulo;

Grupos con Vinilos de Discográficas Nacionales

Consulta el nombre y la nacionalidad de los grupos que tienen al menos un vinilo producido por una discográfica de su misma nacionalidad.

SELECT G.nombre, G.pais
FROM VINILO V
JOIN GRUPO G ON V.grupo = G.nombre
JOIN DISCOGRAFICA D ON V.DISCOGRAFICA = D.nombre
WHERE G.pais = D.pais;

Grupo Más Emitido en Radio

Consulta el grupo que más veces se ha emitido en las radios y el número total de emisiones.

SELECT grupo, total_emisiones
FROM (
    SELECT G.nombre AS grupo, COUNT(*) AS total_emisiones
    FROM EMISION E
    JOIN TEMA T ON E.tema = T.codigo
    JOIN VINILO V ON T.LP = V.ISVN
    JOIN GRUPO G ON V.grupo = G.nombre
    GROUP BY G.nombre
    ORDER BY total_emisiones DESC
)
WHERE ROWNUM = 1;

Vistas de Base de Datos

A continuación, se crean varias vistas. Para cada una, se consulta su contenido y se intenta insertar una fila. Se analizan las consecuencias de las inserciones exitosas en la tabla base y las causas de los errores.

Vista de Grupos y Año de Fundación

Vista que contiene el nombre y el año de fundación de los grupos.

CREATE OR REPLACE VIEW VistaGrupos AS
SELECT nombre, anyoFundacion
FROM GRUPO;

Consulta de la vista:

SELECT * FROM VistaGrupos;

Permite insertar filas en la vista, ya que no hay restricciones CHECK ni la clave primaria impide la inserción. Además, el campo pais en la tabla GRUPO puede ser nulo.

INSERT INTO VistaGrupos (nombre, anyoFundacion)
VALUES ('Los Beatles', 1960);

Consulta de la tabla base para verificar la inserción:

SELECT * FROM GRUPO;

Se elimina la vista para futuras pruebas.

DROP VIEW VistaGrupos;

Vista de Radios y Dirección

Vista que contiene el nombre y la dirección de las radios.

CREATE OR REPLACE VIEW VistaRadios AS
SELECT nombre, direccion
FROM RADIO;

Consulta de la vista:

SELECT * FROM VistaRadios;

No permite insertar porque los campos web, email y telefono son obligatorios en la tabla RADIO y no están incluidos en la vista, lo que impide la alteración de la tabla base.

INSERT INTO VistaRadios (nombre, direccion)
VALUES ('Radio Futura', 'Calle Nueva Radio');

Vista de Vinilos por Grupo

Vista que contiene el nombre del grupo y el número de vinilos publicados por dicho grupo. El atributo que contiene el número de vinilos se llama TotalVinilos.

CREATE VIEW VistaVinilosPorGrupo AS
SELECT V.grupo, COUNT(V.ISVN) AS TotalVinilos
FROM VINILO V
GROUP BY V.grupo;

Esta vista no permite inserciones debido a la función de agregación COUNT y la cláusula GROUP BY.

INSERT INTO VistaVinilosPorGrupo (grupo, TotalVinilos)
VALUES ('Nuevo Grupo', 5);

Actualización de Datos y Modificación del Esquema

Borrado de Datos y Consecuencias

Se intenta borrar las siguientes tuplas. Se analizan las consecuencias de los borrados exitosos en otras tablas y, en caso de rechazo por Oracle, se exploran los motivos y alternativas para realizar el borrado.

Borrado de Vinilo con ISVN = 6000000001

No permite el borrado directo debido a la ausencia de la opción ON DELETE CASCADE en las restricciones de clave foránea, lo que indica que existen dependencias en otras tablas.

DELETE FROM VINILO WHERE ISVN = 6000000001;

Para poder borrarlo, se deben eliminar primero las dependencias:

-- Eliminar temas relacionados
DELETE FROM TEMA 
WHERE sencillo IN(
    SELECT ISVN
    FROM SENCILLO
    WHERE ISVNLP = 6000000001);

-- Eliminar sencillos relacionados
DELETE FROM SENCILLO 
WHERE ISVNLP = 6000000001;

-- Eliminar LP relacionado
DELETE FROM LP 
WHERE ISVN = 6000000001;

-- Finalmente, eliminar el vinilo
DELETE FROM VINILO 
WHERE ISVN = 6000000001;

Borrado de Radio RNE3

Aquí sí permite el borrado, ya que no hay dependencias que impidan la eliminación directa.

DELETE FROM RADIO WHERE nombre = 'RNE3';

Actualización de Fechas de Emisión

Se incrementa en dos minutos el valor del atributo fechaHora de las emisiones de radio del 01/04/1981. Solo actualiza las fechas del 01/04/1981 con hora 00:00:00. Para añadir 2 minutos, se convierte la duración a días (2 minutos / (60 minutos/hora * 24 horas/día)).

UPDATE EMISION
SET fechaHora = fechaHora + 2/60*24
WHERE fechaHora = TO_DATE('01-04-1981', 'DD-MM-YYYY');

Duplicación de Copias de Lanzamiento para LPs de Queen

Se duplica el número de copias de lanzamiento para los LPs del grupo Queen.

UPDATE LP
SET copiasLanzamiento = copiasLanzamiento * 2
WHERE ISVN IN (
    SELECT L.ISVN
    FROM LP L
    JOIN VINILO V ON L.ISVN = V.ISVN
    JOIN GRUPO G ON V.grupo = G.nombre
    WHERE G.nombre = 'Queen'
);
SELECT * FROM LP;

Evolución del Esquema Relacional

Se modifica el esquema relacional para considerar los siguientes supuestos:

Gestión de Miembros de Grupo

Actualmente, solo se almacena el nombre del grupo. Se añade una tabla para la información de los miembros del grupo (Identificador, nombre, fecha de nacimiento, país de nacimiento) y se asocia cada miembro al grupo al que pertenece, asumiendo que pertenece a un único grupo.

CREATE TABLE MIEMBRO (
    id              NUMBER PRIMARY KEY,
    nombre          VARCHAR2(120) NOT NULL,
    fechaNacimiento DATE,
    paisNacimiento  VARCHAR2(120),
    grupo           VARCHAR2(120) NOT NULL,
    CONSTRAINT FK_MIEMBRO_GRUPO FOREIGN KEY (grupo) REFERENCES GRUPO (nombre) ON DELETE CASCADE
);

Solución Alternativa para Composición Variable de Grupos

Para gestionar grupos con composición variable a lo largo del tiempo (miembros que se incorporan y desvinculan), se propone una tabla intermedia que registre la relación temporal entre miembros y grupos.

CREATE TABLE GRUPO_MIEMBRO (
    id              NUMBER PRIMARY KEY,
    idMiembro       NUMBER NOT NULL,
    grupo           VARCHAR2(120) NOT NULL,
    fechaInicio     DATE NOT NULL,
    fechaFin        DATE,
    CONSTRAINT FK_GRUPO_MIEMBRO_MIEMBRO FOREIGN KEY (idMiembro) REFERENCES MIEMBRO (id) ON DELETE CASCADE,
    CONSTRAINT FK_GRUPO_MIEMBRO_GRUPO FOREIGN KEY (grupo) REFERENCES GRUPO (nombre) ON DELETE CASCADE
);

Ampliación del Ranking Semanal

La solución actual solo guarda los tres primeros puestos del ranking semanal. Se modifica el esquema para incorporar cualquier número de puestos.

CREATE TABLE POSICION_RANKING (
    id              NUMBER PRIMARY KEY,
    anyo            NUMBER(4) NOT NULL,
    semana          NUMBER(2) NOT NULL,
    puesto          NUMBER(3) NOT NULL,
    sencillo        NUMBER(10),
    CONSTRAINT FK_RANKING_SENCILLO FOREIGN KEY (sencillo) REFERENCES SENCILLO (ISVN) ON DELETE CASCADE
);

Garantía de Primer Puesto en Ranking

¿La solución anterior garantiza que haya al menos un primer puesto todas las semanas? No.

Se implementa la restricción para asegurar que siempre haya un primer puesto y que este esté asociado a un sencillo:

DROP TABLE POSICION_RANKING;
CREATE TABLE POSICION_RANKING (
    id              NUMBER PRIMARY KEY,
    anyo            NUMBER(4) NOT NULL,
    semana          NUMBER(2) NOT NULL,
    puesto          NUMBER(3) NOT NULL,
    sencillo        NUMBER(10),
    CONSTRAINT FK_RANKING_SENCILLO FOREIGN KEY (sencillo) REFERENCES SENCILLO (ISVN) ON DELETE CASCADE,
    CONSTRAINT CHK_PRIMER_PUESTO CHECK (puesto = 1 AND SENCILLO IS NOT NULL)
);

Vinilos con Múltiples Grupos Participantes

El diseño actual asocia cada vinilo con un único grupo. Para permitir que existan vinilos en los que participen varios grupos, se añade una nueva columna grupo2, que puede ser nula, para permitir un segundo grupo.

ALTER TABLE VINILO ADD grupo2 VARCHAR2 (120);
ALTER TABLE VINILO ADD CONSTRAINT FK_VINILO_GRUPO2 FOREIGN KEY (grupo2) REFERENCES GRUPO (nombre);

Eliminación de Inconsistencias con Restricciones Adicionales

El diseño actual permite varias inconsistencias en los datos. Se analizan las inconsistencias eliminadas por las siguientes sentencias:

ALTER TABLE SENCILLO ADD UNIQUE (ISVN, ISVNLP);

Inconsistencia eliminada: Un sencillo debe ser único en su relación con un LP, de ahí la restricción UNIQUE. Esto asegura que una combinación específica de ISVN de sencillo y ISVN de LP solo pueda existir una vez.

ALTER TABLE TEMA ADD FOREIGN KEY (sencillo, LP) REFERENCES SENCILLO (ISVN,ISVNLP);

Inconsistencia eliminada: Esta sentencia asegura la integridad referencial, garantizando que si un tema está asociado a un sencillo y un LP, dicho sencillo debe existir en la tabla SENCILLO con el ISVNLP correspondiente. Esto previene la asociación de temas a sencillos o LPs inexistentes o inconsistentes, manteniendo la coherencia en las relaciones entre TEMA, SENCILLO y LP.

Entradas relacionadas:

Etiquetas: