Actividad No 2.
Actividad No 2. Fecha de entrega:
Nombre y apellidos:
Presentación y Objetivos
La actividad consta de varias preguntas, la primera de ellas propone convertir algunas consultas en SQL,
mientras que otra consiste en la puesta en práctica de ciertos conocimientos adquiridos.
Ejercicio 1
a. Según las relaciones detalladas en la parte posterior se detallan a continuación, indicar las instrucciones
SQL (para PostgreSQL) para la creación de las tablas y proponed los tipos de cada atributo según sus
posibles valores. Añadid restricciones adicionales, si lo crees conveniente.
INDICACIONES: No utilicéis herramientas de generación automática de código. No declaréis las restricciones
a posteriori (no utilicéis ALTER TABLE para añadir restricciones después de la creación de las tablas). Dad
nombre a todas las restricciones.
1
Actividad No 2.
Speaker (speaker_code, speaker_name, email, birth_date)
{birth_date} tiene formato YYYY-MM-DD (por ejemplo: '1985-02-28').
Restricciones mínimas obligatorias a considerar:
- speaker_name no puede ser nul.
- email debe tener un formato mínimo.
- birth_date no puede ser superior a la fecha del sistema.
Paper (paper_code, paper_name, topic)
Restricciones mínimas obligatorias a considerar:
- paper_name debe contener más de 10 caracteres.
- topic no puede ser nul, contiene datos tipo carácter y debe tener tamaño de 40.
2
City (city_code, city_name, country)
Restricciones mínimas obligatorias a considerar:
- city_code es tipo carácter fijo y debe tener un tamaño de 10 caracteres.
- city_name no puede ser nul.
Speech (speech_year, location, city_code, web)
{city_code} es clave foránea de City
Introduce (speaker_code, paper_code, speech_year, opening_date_hour, duration)
{speaker_code} es clave foránea de Speaker
{paper_code} es clave foránea de Paper
{speech_year} es clave foránea de Speech
Restricciones mínimas obligatorias a considerar:
- speech_year debe estar en el rango 2000-2015
- duration es un campo tipo numérico y contendrá únicamente valores entre 1 y 120
- opening_date_hour no será superior a la fecha del sistema
Reference (paper_code1, paper_code2)
{paper_code1} es clave foránea de Paper
{paper_code2} es clave foránea de Paper
Restricciones mínimas obligatorias a considerar:
- Un Paper no se puede referenciar a sí mismo.
b. Indicad el orden en que se han tenido que crear las tablas, según las relaciones que existen entre ellas.
Respuesta:
El orden para crear las tablas va de la siguiente manera: en primer lugar se tuvo que crear la tabla Speaker,
después la tabla Paper, posteriormente City, luego la tabla Speech que tiene relación con la tabla City, después
la tabla Introduce que guarda relación con las tablas Speaker y Paper; finalmente se creó la tabla Referencia.
c. Ejecutad las inserciones de datos que se adjuntan seguidamente, corrigiendo los posibles errores que
pudieran haber. En caso de existir errores en los datos que relacionan distintas tablas, considerad como
válido el primero. Los posibles datos incorrectos que no tengan relevancia para las consultas se pueden
corregir libremente.
Se insertan datos específicos para demostrar que cada consulta retorna un resultado correcto.
INSERT INTO Speaker VALUES (1, 'Jaume Armengol', 'jarmengol@[Link]', '08/01/1969'), (2, 'Ramon Fornelles',
'ramon_for@[Link]', '15/03/1976'), (3, 'Ingrid Prados', 'ingrid_p@[Link]', '20/08/1981'), (4, 'Josep Maria
Frenades', 'jmfrenades@[Link]', '06/05/1979') (5, 'Manel Jimenez', 'mjimenez@[Link]', '16/09/1976'), (6,
'Maria Jesus Alabart', 'maalabart@[Link]', '16/07/1980');
INSERT INTO Paper VALUES (1, 'NoSQL Databases', 'DataBases'), (2, 'New lithium battery', 'Technology'), (3, 'Grid
Computing and DataBases', 'DataBases'), (4, 'Approximation to MongoDB', 'DataBases'), (5, 'Solar Cell Tech',
'Technology'), (6, 'The new fuel; H2O2', 'Technology'), (7, 'Open Source SGBD', 'DataBases');
INSERT INTO City VALUES (1, 'Barcelona', 'Spain');
INSERT INTO City VALUES (2, 'Hospitalet del Llobregat', 'Spain');
INSERT INTO City VALUES (3, 'Bogota', 'Colombia');
INSERT INTO City VALUES (4, 'Madrid', 'Spain');
INSERT INTO City VALUES (5, 'London', 'United Kingdom');
INSERT INTO Speech VALUES (2012, 'Palau de la Música', 1, '[Link]');
INSERT INTO Speech VALUES (2011, 'Fira Bcn', 1, '[Link]');
INSERT INTO Speech VALUES (2010, 'Drum L'Os', 3, '[Link]');
INSERT INTO Introduce VALUES (1, 1, 2010, '05/02/2010 05:30', 60);
INSERT INTO Introduce VALUES (5, 7, 2010, '05/02/2010 07:00', 60);
INSERT INTO Introduce VALUES (4, 2, 2011, '30/02/2011 10:15', 45);
INSERT INTO Introduce VALUES (3, 3, 2012, '18/02/2012 12:30', 90);
INSERT INTO Introduce VALUES (2, 4, 2012, '18/02/2012 09:30', 120);
INSERT INTO Reference VALUES (3,1), (4,1), (7, 1), (6,5), (6,2);
d. Indicad las instrucciones SQL que resuelven las consultas siguientes y mostrad para cada una
de ellas una captura de pantalla con los datos resultantes:
INDICACIONES: Al realizar las consultas no se debe utilizar ni el NOT IN, ni el DISCTINCT, MINUS o similares.
Se deben utilizar siempre combinaciones.
d1) Obtened el código y el nombre de los artículos con tema 'DataBases' presentados en la conferencia del
año 2012, que posteriormente nunca han sido referenciados.
d2) Obtened el nombre y el tema de los artículos de menor duración.
d3) Obtened el nombre y email de las personas que han presentado algún artículo el año 2011 y el año 212
sobre el tema 'DataBases'.
d4) Obtened el nombre de los artículos presentados antes del año 2010 y que han sido referenciados como
artículos del tema 'DataBases'
d5) Obtened el nombre de todos los presentadores que consta que no han presentado ningún artículo.
Formato de entrega
Se deberán entregar dos documentos; uno en formato editable (PDF, ODT, DOC, RTF o SXW).
