Master Internacional en Software

Libre - Bases de Datos

PEC 2

Fecha de entrega:

08/04/2015

PROPUESTA SOLUCIN

Presentacin y Objetivos
La Prueba de Evaluacin Continuada II (PEC2) es la segunda de las cuatro PECs de las que
consta la asignatura. Dicha PEC tendr un peso del 20% de la nota final y los conceptos que se
trabajan en ella son los correspondientes al mdulo 'El lenguaje SQL'.
La PEC consta de dos preguntas, la primera de ellas sigue el mismo caso de lgebra relacional
de la PEC 1 y propone convertir algunas consultas en SQL, mientras que la segunda consiste en
la puesta en prctica de ciertos conocimientos adquiridos.
La valoracin de cada pregunta en el global de la PEC se incluye en la cabecera de cada
ejercicio. Puesto que la asignatura no tiene examen, es necesario realizar todas las PECs para
poder superar la asignatura.

Ejercicio 1 (55%)
a. Segn las relaciones detalladas en la PEC1 y las restricciones que se detallan a continuacin,
indicad las instrucciones SQL (para PostgreSQL) para la creacin de las tablas Commercial,
Demand, DemandCode, Zone y Flat, proponiendo los tipos de datos para cada atributo segn
sus posibles valores. Aadid las restricciones indicadas y las adicionales que aseguren un
mnimo de calidad de los datos (como mnimo se debe asegurar que las fechas estn
comprendidas entre unos mrgenes lgicos).
INDICACIONES: No utilicis herramientas de generacin automtica de cdigo. No declaris las restricciones
a posteriori (no utilicis ALTER TABLE para aadir restricciones despus de la creacin de las tablas). Dad
nombre a todas las restricciones.

NOTA: en el caso de desear crear las tablas en un esquema concreto, se debern utilizar las siguientes
instrucciones
CREATE SCHEMA p15;
SET search_path TO p15, "$user", public;

Commercial {commercialCode, name, surname, NSS, CC, phone, incorporationDate}

(NSS) es clave alternativa

CREATE TABLE Commercial (

commercialCode CHAR(10),
name VARCHAR(20),
surname VARCHAR(40),
NSS CHAR(16),
CC CHAR(16),
phone VARCHAR(12),
incorporationDate DATE,
CONSTRAINT pk_CommercialCode PRIMARY KEY (commercialCode),
CONSTRAINT nn_name CHECK (name IS NOT NULL),
CONSTRAINT nn_surname CHECK (surname IS NOT NULL),
CONSTRAINT nn_NSS CHECK (NSS IS NOT NULL),
CONSTRAINT nn_CC CHECK (CC IS NOT NULL),

Master Internacional en Software

Libre - Bases de Datos
CONSTRAINT nn_phone CHECK (phone IS NOT NULL),
CONSTRAINT nn_incorporationDate CHECK (incorporationDate IS NOT NULL),
CONSTRAINT ch_incorporationDate CHECK (incorporationDate > '01/01/2000'
incorporationDate < '31/12/2020'),
CONSTRAINT ak_NSS UNIQUE (NSS)
);

Demand
{demandCode,
commercialCode,
demandNumRooms}
(commercialCode) es clave fornea a Commercial

demandDate,

AND

demandNumMeters,

CREATE TABLE Demand (

demandCode CHAR(10),
commercialCode CHAR(10),
demandDate DATE,
demandNumMeters DECIMAL(5,2),
demandNumRooms DECIMAL(2,0),
CONSTRAINT pk_Demand PRIMARY KEY (demandCode),
CONSTRAINT nn_commercialCode CHECK (commercialCode IS NOT NULL),
CONSTRAINT nn_demandDate CHECK (demandDate IS NOT NULL),
CONSTRAINT ch_demandDate CHECK (demandDate > '01/01/2000' AND demandDate <
'31/12/2020'),
CONSTRAINT nn_demandNumMeters CHECK (demandNumMeters IS NOT NULL and
demandNumMeters > 15),
CONSTRAINT nn_demandNumRooms CHECK (demandNumRooms IS NOT NULL and
demandNumRooms > 0),
CONSTRAINT fk_commercialCode FOREIGN KEY (commercialCode)
REFERENCES Commercial(commercialCode)
);

Zone {zoneCode, name, ubication, city, includedIn}

(includedIn) es clave fornea a Zone
CREATE TABLE Zone (
zoneCode CHAR(10),
name VARCHAR(40),
ubication VARCHAR(40),
city VARCHAR(40),
includedIn CHAR(10),
CONSTRAINT pk_Zone PRIMARY KEY (zoneCode),
CONSTRAINT nn_name CHECK (name IS NOT NULL),
CONSTRAINT nn_ubication CHECK (ubication IS NOT NULL),
CONSTRAINT nn_city CHECK (city IS NOT NULL),
CONSTRAINT fk_includedIn FOREIGN KEY (includedIn)
REFERENCES Zone (zoneCode)
);

DeamandZone {demandCode, zoneCode}

(demandCode) es clave fornea a Demand
(zoneCode) es clave fornea a Zone

Master Internacional en Software

Libre - Bases de Datos

CREATE TABLE DemandZone (

demandCode CHAR(10),
zoneCode CHAR(10),
CONSTRAINT pk_demandCodeZoneCode PRIMARY KEY (demandCode, zoneCode),
CONSTRAINT nn_demandCode CHECK (demandCode IS NOT NULL),
CONSTRAINT nn_zoneCode CHECK (zoneCode IS NOT NULL),
CONSTRAINT fk_demandCode FOREIGN KEY (demandCode)
REFERENCES Demand (demandCode),
CONSTRAINT fk_zoneCode FOREIGN KEY (zoneCode)
REFERENCES Zone (zoneCode)
);

Flat {flatCode, address, zone, metersNum, roomsNum, dateFrom}

(address) es clave alternativa
(zone) es clave fornea a Zone
CREATE TABLE Flat (
flatCode CHAR(10),
address VARCHAR(40),
zone CHAR(10),
metersNum DECIMAL(5,2),
roomsNum DECIMAL(2,0),
dateFrom DATE,
CONSTRAINT pk_Flat PRIMARY KEY (flatCode),
CONSTRAINT nn_address CHECK (address IS NOT NULL),
CONSTRAINT nn_zone CHECK (zone IS NOT NULL),
CONSTRAINT nn_metersNum CHECK (metersNum IS NOT NULL AND metersNum > 34),
CONSTRAINT nn_roomsNum CHECK (roomsNum IS NOT NULL AND roomsNum > 0),
CONSTRAINT nn_dateFrom CHECK (dateFrom IS NOT NULL),
CONSTRAINT ch_dateFrom CHECK (dateFrom > '01/01/2000' AND dateFrom < '31/12/2020'),
CONSTRAINT ak_address UNIQUE (address),
CONSTRAINT fk_zone FOREIGN KEY (zone)
REFERENCES Zone (zoneCode)
);

b. Indicad el orden en que se han tenido que crear las tablas, segn las relaciones que existen
entre ellas.
Commercial
Zone
Flat
Demand
DemandZone

c. Ejecutad las inserciones de datos que muestran seguidamente, corrigiendo los errores que
pueda haber. En caso de existir errores en datos que relacionan distintas tablas, considerad
como vlido el primero. Pueden corregirse libremente datos incorrectos que no tengan relevancia
para las consultas.

Master Internacional en Software

Libre - Bases de Datos
-- Table Commercial
INSERT INTO Commercial VALUES ('ZI2C34J1', 'Juan', 'Gonzales Villar', '1230985673459673',
'1004234398992371', '+34934877623', '12/12/2014');
INSERT INTO Commercial VALUES ('ZI2C34J2', 'Juan', 'Gonzales Villar', '4230985673459673',
'1004234398992371', '+34934877623', '12/01/2015');
INSERT INTO Commercial VALUES ('ZI3C35J1', 'Jose', 'Martin Rojas', '9027649876395462',
'1004338562849810', '+34932134862', '10/01/2015');
INSERT INTO Commercial VALUES ('ZI3C36J0', 'Ramon',
'Ballester Marin', '2286594765829212',
'1013886257123863', '+34934346132', '16/01/2015');
INSERT INTO Commercial VALUES ('ZI5C34J1', 'Sabrina',
'Ornito Blanc', '9685465258436402',
'1004948776581621', '+34644993741', '18/12/2014');
INSERT INTO Commercial VALUES ('ZI5C34J2', 'Jordi', 'Frapoulos Matavacas', '5619524974545888',
'1130948739934587', '+34977256018', '17/12/2014');
INSERT INTO Commercial VALUES ('ZI5C34J3', 'Arantxa',
'Parmin Jimenez', '9874618467656103',
'1004238651045723', '+34933768790', '01/02/2015');
INSERT INTO Commercial VALUES ('ZI5C34J4', 'Joaquin',
'Santander
Casanova',
'6674683000801901', '1036123421344688', '+34661836532', '30/01/2015');
INSERT INTO Commercial VALUES ('ZI1C31J0', 'Aranzazu', 'Alvareda Gamez', '1929287465966879',
'1093769123723723', '+34644236922', '04/01/2015');
INSERT INTO Commercial VALUES ('ZI0C30J1', 'Marta',
'Gasol Noguera', '4368243571546379',
'1006090774763517', '+34939832421', '24/12/2014');
INSERT INTO Commercial VALUES ('ZI0C33J2', 'Sebastian', 'PelAman Tasor', '5326748658585963',
'1004064737643621', '+34973996733', '18/01/2015');
-- Table Zone
INSERT INTO Zone VALUES ('015', 'Madrid City', 'Madrid', 'Madrid', null);
INSERT INTO Zone VALUES ('020', 'Madrid Sur', 'Madrid', 'Madrid', '015');
INSERT INTO Zone VALUES ('021', 'Puente de Vallecas', 'Madrid', 'Madrid', '015');
INSERT INTO Zone VALUES ('300', 'Mataro', 'Mataro', 'Mataro', null);
INSERT INTO Zone VALUES ('303', 'Llantia', 'La Llantia', 'Mataro', '300');
INSERT INTO Zone VALUES ('290', 'Barcelona', 'Barcelona', 'Barcelona', null);
INSERT INTO Zone VALUES ('291', 'Horta', 'Horta de Sant Joan', 'Barcelona', '290');
INSERT INTO Zone VALUES ('350', 'Zaragoza', 'Zaragoza', 'Zaragoza', null);
INSERT INTO Zone VALUES ('353', 'Pilar', 'Zaragoza Pilar', 'Zaragoza', '350');
INSERT INTO Zone VALUES ('023', 'Moratalaz', 'Madrid Moratalaz', 'Madrid', '015');
INSERT INTO Zone VALUES ('024', 'San Blas-Canillejas', 'Madrid San Blas-Canillejas', 'Madrid', '015');
INSERT INTO Zone VALUES ('610', 'Valencia', 'Valencia', 'Valencia', null);

-- Table Flat
INSERT INTO Flat VALUES ('150315003', 'Calle Villalpurria, 34, 6o, 2a', '020', 53, 2, '15/03/2015');
INSERT INTO Flat VALUES ('150315004', 'Calle Santa Marta, 1', '021', 69, 3, '15/03/2015');
INSERT INTO Flat VALUES ('150312001', 'Avda. Cervantes, 51, 1o, 1a', '290', 112, 4, '12/03/2015');
INSERT INTO Flat VALUES ('150315005', 'Pasaje Cobos, 12', '353', 78, 3, '15/03/2015');
INSERT INTO Flat VALUES ('150311001', 'Calle Ramon Berenguer, 43, 5o, 2a', '290', 121, 5,
'11/03/2015');
INSERT INTO Flat VALUES ('150311002', 'Calle Urquinaona, 269, 3o, 1a', '290', 109, 4, '11/03/2015');
INSERT INTO Flat VALUES ('150315028', 'Calle Romanosa, 15, 3o, 3a', '350', 53, 2, '15/03/2015');
INSERT INTO Flat VALUES ('150315029', 'Calle Concierto Aranjuez, 7, 1a', '353', 46, 1, '15/03/2015');
INSERT INTO Flat VALUES ('150315033', 'Ronda Sur, PK. 8', '290', 73, 3, '15/03/2015');
-- Table Demand

Master Internacional en Software

Libre - Bases de Datos
INSERT INTO Demand VALUES ('2015000048', 'ZI2C34J1', '28/01/2015', 35, 1);
INSERT INTO Demand VALUES ('2015000049', 'ZI2C34J1', '28/01/2015', 100, 4);
INSERT INTO Demand VALUES ('2015000050', 'ZI2C34J1', '29/01/2015', 50, 2);
INSERT INTO Demand VALUES ('2015000051', 'ZI2C34J1', '29/01/2015', 75, 3);
INSERT INTO Demand VALUES ('2015000102', 'ZI3C35J1', '28/01/2015', 35, 1);
INSERT INTO Demand VALUES ('2015000103', 'ZI3C35J1', '28/01/2015', 100, 5);
INSERT INTO Demand VALUES ('2015000104', 'ZI3C35J1', '28/01/2015', 65, 3);
INSERT INTO Demand VALUES ('2015000105', 'ZI3C35J1', '28/01/2015', 45, 1);
INSERT INTO Demand VALUES ('2015000106', 'ZI3C35J1', '28/01/2015', 50, 2);
INSERT INTO Demand VALUES ('2015000114', 'ZI5C34J4', '30/01/2015', 70, 3);
INSERT INTO Demand VALUES ('2015000115', 'ZI5C34J4', '30/01/2015', 55, 1);
INSERT INTO Demand VALUES ('2015000116', 'ZI5C34J4', '30/01/2015', 55, 2);
INSERT INTO Demand VALUES ('2015000123', 'ZI0C33J2', '31/01/2015', 150, 6);
INSERT INTO Demand VALUES ('2015000124', 'ZI0C33J2', '31/01/2015', 115, 4);
INSERT INTO Demand VALUES ('2015000126', 'ZI0C33J2', '31/01/2015', 110, 4);
-- Table DemandZone
INSERT INTO DemandZone VALUES ('2015000048','021');
INSERT INTO DemandZone VALUES ('2015000048','020');
INSERT INTO DemandZone VALUES ('2015000048','024');
INSERT INTO DemandZone VALUES ('2015000049','291');
INSERT INTO DemandZone VALUES ('2015000049','290');
INSERT INTO DemandZone VALUES ('2015000050','023');
INSERT INTO DemandZone VALUES ('2015000051','353');
INSERT INTO DemandZone VALUES ('2015000102','290');
INSERT INTO DemandZone VALUES ('2015000102','291');
INSERT INTO DemandZone VALUES ('2015000103','300');
INSERT INTO DemandZone VALUES ('2015000104','353');
INSERT INTO DemandZone VALUES ('2015000104','350');
INSERT INTO DemandZone VALUES ('2015000105','023');
INSERT INTO DemandZone VALUES ('2015000106','024');
INSERT INTO DemandZone VALUES ('2015000114','290');
INSERT INTO DemandZone VALUES ('2015000115','021');
INSERT INTO DemandZone VALUES ('2015000115','020');
INSERT INTO DemandZone VALUES ('2015000116','291');
INSERT INTO DemandZone VALUES ('2015000123','015');
INSERT INTO DemandZone VALUES ('2015000124','024');
INSERT INTO DemandZone VALUES ('2015000126','353');
INSERT INTO DemandZone VALUES ('2015000126','350');

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:
d1. - Obtened el name y el surname de todos los comerciales que tengan alguna solicitud
(Demand) asignada.
SELECT name, surname
FROM Commercial JOIN Demand ON Commercial.commercialCode = Demand.commercialCode
GROUP BY name, surname;

Master Internacional en Software

Libre - Bases de Datos

d2. - Obtened el cdigo identificador y la fecha de incorporacin a la empresa de los

comerciales que no tengan ninguna solicitud asignada. No utilicis la clusula NOT IN para
resolver esta consulta, utilizad junciones (JOINs entre tablas).
SELECT Commercial.commercialCode, Commercial.incorporationDate
FROM Commercial LEFT JOIN Demand ON Commercial.commercialCode = Demand.commercialCode
WHERE demandCode IS NULL;

d3. Obtened el cdigo de zona, el nombre y la ciudad donde estn, de las zona/zonas que
tienen ms solicitudes.
SELECT DemandZone.zoneCode, name, city
FROM DemandZone, Zone
WHERE DemandZone.zoneCode = Zone.zoneCode
GROUP BY DemandZone.zoneCode, name, city
HAVING COUNT(*) = (
SELECT COUNT(*)
FROM DemandZone
GROUP BY zoneCode
ORDER BY 1 DESC
LIMIT 1);

Master Internacional en Software

Libre - Bases de Datos
d4. Obtened para cada solicitud registrada, el cdigo de la solicitud, el cdigo de la zona, el
nombre de la zona, su ubicacin y el nombre de la zona de la que depende, si es el caso.
SELECT DemandZone.demandCode,
DemandZone.zoneCode,
Zneighborhood.name,
Zneighborhood.ubication, Zcity.name
FROM DemandZone JOIN Zone AS Zneighborhood
ON Zneighborhood.zoneCode = DemandZone.zoneCode
LEFT JOIN Zone AS Zcity
ON Zneighborhood.includedIn = Zcity.zoneCode;

d5. - Obtened para cada ubicacin que est incluida en otra zona y que no tenga en oferta
ningn piso, el nombre, la ubicacin y la ciudad.
SELECT name, ubication, city
FROM Zone LEFT JOIN Flat ON Flat.Zone = Zone.ZoneCode
WHERE flatCode IS NULL AND
includedIn IS NOT NULL;

Master Internacional en Software

Libre - Bases de Datos

Ejercicio 2 (45%)
a. Siguiendo el documento 'Ejemplo carga de datos', cread la tabla 'loadIbexIntradia (en
PostgreSQL) con los nombres de los campos que aparecen en la cabecera del fichero
'ibexIntradia.txt' y cargad los datos.
Adjuntad los SQL utilizados para crear el SCHEMA de la base de datos, para crear la tabla, para
realizar la carga de los datos y una captura de pantalla con el resultado de una SELECT que
retorne el nmero total de registros cargados.
NOTA:
- Cread un SCHEMA llamado p15 y definid la tabla en l.
- Al crear la tabla no se debe aadir ninguna restriccin a ningn campo, ni crear clave
primaria.
Creacin del SCHEMA:
CREATE SCHEMA p15;
SET search_path TO p15, "$user", public;

Para realizar la carga seguiremos los siguientes pasos:

- Se debe eliminar la primera lnea (cabecera)
- Analizado el contenido de las columnas se puede pasar a crear la tabla:
CREATE TABLE loadIbexIntraDay (
ticket CHAR(15),
name CHAR(20),
last DECIMAL(6,2),
difference DECIMAL(6,2),
maxCot DECIMAL(6,2),
minCot DECIMAL(6,2),
volume DECIMAL(9,0),
quantity DECIMAL(9,2),
dateCot CHAR(10),
hourCot CHAR(6)
);

- Se realizar la carga de los datos:

COPY loadIbexIntraDay FROM 'c:/ibexIntradia.txt' WITH DELIMITER E'\t';

Master Internacional en Software

Libre - Bases de Datos

- Se comprueba el nmero de registros cargados:

NOTA: se tiene que descontar la lnea de cabecera que se ha eliminado.

b. Con los datos cargados en 'loadIbexIntraDay' realizad la siguiente consulta SQL:

-

Obtened los valores del IBEX que han tenido la mxima y mnima variacin (en Euros) a
lo largo del periodo que se estudia. Para cada valor, se desea obtener el nombre del
valor, el importe mnimo y el mximo importe, y la diferencia porcentual entre dichos
valores.

Mostrad la consulta SQL y una captura de pantalla con los resultados de la misma.
(SELECT name, MAX(last), MIN(last), TRUNC(((max(maxCot)-min(minCot))/min(minCot)*100),2) AS dif

Master Internacional en Software

Libre - Bases de Datos
FROM loadIbexIntraDay
GROUP BY ticket, name
ORDER BY dif ASC LIMIT 1)
UNION
(SELECT name, MAX(last), MIN(last), TRUNC(((max(maxCot)-min(minCot))/min(minCot)*100),2) AS dif
FROM loadIbexIntraDay
GROUP BY ticket, name
ORDER BY dif DESC LIMIT 1);

Formato de entrega
Se debern entregar dos documentos; uno en formato editable (ODT, DOC, RTF o SXW) y el
mismo convertido a formato PDF.
No comprimis los ficheros (ni ZIP, ni RAR, etc), ni entreguis ficheros adicionales al documento
(esquemas, capturas de pantalla...), todo se debe incluir en el documento principal.
El nombre del documento debe seguir el formato: BD_PEC2_Apellido1Nombre.extensin y debe
constar vuestro nombre y apellidos al principio de la PEC.
La PEC se entregar a travs del apartado 'Evaluacin' del aula correspondiente.
La fecha mxima de entrega son las 23h 59m (GMT+4) del mircoles da 08/04/2015