EJERCICIOS DE PRÁCTICA
ACTIVIDAD N°2
DANIS DANIEL DORIA GUERRA
YESICA HERNANDEZ AVILA
BASE DE DATOS II
UNICARTAGENA
FACULTAD DE INGENIERIA
ING DE SOFTWARE V SEMESTRE
LORICA – CORDOBA
2019
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 cres conveniente.
CREATE DATABASE "ACTIVIDAD2"
WITH
OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'Spanish_Colombia.1252'
LC_CTYPE = 'Spanish_Colombia.1252'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;
create table Speaker (
speaker_code int,
speaker_name varchar(30) not null,
email varchar(100),
primary key (speaker_code),
birth_date date
);
create table Paper (
paper_code int,
paper_name varchar(30),
topic varchar(40) not null,
primary key (paper_code)
);
create table City (
city_code int,
city_name varchar(20) not null,
country varchar(20),
primary key (city_code)
);
create table Speech (
speech_year int,
location varchar(20),
city_code int,
web varchar(50),
primary key (speech_year),
foreign key (city_code) references City (city_code)
);
create table Introduce (
speaker_code int,
paper_code int ,
speech_year int,
opening_date_hour datetime,
duration int,
foreign key (speaker_code) references speaker (speaker_code),
foreign key (paper_code) references Paper (paper_code),
foreign key (speech_year) references Speech (speech_year)
);
create table Referencia (
paper_code1 int,
paper_code2 int,
foreign key (paper_code1) references Paper (paper_code),
foreign key (paper_code2) references Paper (paper_code)
);
b. Indicad el orden en que se han tenido que crear las tablas, según las relaciones que
existen entre ellas. R/
Para crear las tablas primero se tuvo que crear la tabla Speaker, después Paper,
después City, después Speech que está relacionada con City, después Introduce que
está relacionada con speaker y Paper y por último 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', '[email protected]', '1969-
01-08'),
(2, 'Ramon Fornelles', '[email protected]', '1976-03-15'),
(3, 'Ingrid Prados', '[email protected]', '1981-08-20'),
(4, 'Josep Maria Frenades', '[email protected]','1979-05-06'),
(5, 'Manel Jimenez', '[email protected]','1976-09-16'),
(6, 'Maria Jesus Alabart', '[email protected]', '1980-07-16');
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, 'www.openDB.com');
INSERT INTO Speech VALUES (2011, 'Fira Bcn', 1, 'www.solarcell.com');
INSERT INTO Speech VALUES (2010, 'Drum LOs', 3, 'www.hidroxidfuel.com');
INSERT INTO Introduce VALUES (1, 1, 2010, '2010-02-05 05:30', 60);
INSERT INTO Introduce VALUES (5, 7, 2010, '2010-02-05 07:00', 60);
INSERT INTO Introduce VALUES (4, 2, 2011, '2011-02-30 10:15', 45);
INSERT INTO Introduce VALUES (3, 3, 2012, '2012-02-18 12:30', 90);
INSERT INTO Introduce VALUES (2, 4, 2012, '2012-02-18 09:30', 120);
INSERT INTO Referencia 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.
-- 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.
select paper.paper_code, paper_name from paper
join introduce on paper.paper_code=introduce.paper_code
join speech on speech.speech_year=introduce.speech_year
join referencia on paper.paper_code=referencia.paper_code1 is null
where topic='databases' and speech.speech_year=2012 ;
-- Obtened el nombre y el tema de los artículos de menor duración.
SELECT paper_name, topic, min(duration)
FROM Paper join introduce
where paper.paper_code=introduce.paper_code;
-- 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'.
select speaker_name, email from speaker join introduce on
speaker.speaker_code=introduce.speaker_code join paper on
paper.paper_code=introduce.paper_code where speech_year BETWEEN 2011 and
2012 and topic='DataBases';
-- Obtened el nombre de los artículos presentados antes del año 2010 y que han sido
referenciados como artículos del tema 'DataBases'
select paper_name,topic from paper join introduce on
paper.paper_code=introduce.paper_code where speech_year < 2010 and
topic='DataBases';
-- Obtened el nombre de todos los presentadores que consta que no han presentado
ningún artículo.
SELECT speaker_name
FROM speaker
LEFT JOIN introduce
ON speaker.speaker_code=introduce.speaker_code
WHERE speaker.speaker_code=introduce.speaker_code IS NULL
