PEC2_Solución (1)

Master Internacional en Software Libre - Bases de Datos

1

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),

PEC 2 Fecha de entrega: 08/04/2015 PROPUESTA SOLUCIN


2

CONSTRAINT nn_phone CHECK (phone IS NOT NULL), CONSTRAINT nn_incorporationDate CHECK (incorporationDate IS NOT NULL), CONSTRAINT ch_incorporationDate CHECK (incorporationDate > '01/01/2000' AND

incorporationDate < '31/12/2020'), CONSTRAINT ak_NSS UNIQUE (NSS)

);

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

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


3

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.


4

-- 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


5

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;


6

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);


7

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;


8

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';


9

- 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


1

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

PEC2_Solución (1)

Documents

Transcript of PEC2_Solución (1)