PEC3_Solución

Master Internacional en Software Libre - Bases de Datos

1

Presentacin y objetivos

La Prueba de Evaluacin Continuada III (PEC3) es la tercera de les cuatro PECs de las que consta la asignatura. Dicha PEC tendr un peso del 30% de la nota final y los conceptos que se trabajan en ella son los correspondientes a los seis primeros mdulos de la asignatura.

La PEC consta de tres preguntas, la primera sobre diseo e implementacin, la segunda sobre manipulacin de datos (incluye consultas SQL) y la tercera sobre la instalacin del Apache y el PHP. Para la correcta resolucin de la ltima pregunta se deber seguir y ejecutar el caso prctico adjunto al enunciado. Algunas preguntas disponen adicionalmente de subapartados voluntarios, para aquellos que deseen profundizar los puntos tratados.

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.

1. Diseo e implementacin (60%)

A efectos de realizar inversiones de bolsa lo ms ptimas posibles, una empresa desea realizar un estudio de las variaciones en los precios de las acciones de la bolsa de valores durante un periodo de tiempo para el que se posee informacin detallada. Es por ello que se desea disear una base de datos que permita almacenar los datos segn el modelo que se describe seguidamente.

Los clientes son personas de las que deseamos mantener sus datos habituales (gnero, ao de nacimiento, etc), junto a su nmero de telfono que es obligatorio, puesto se puede utilizar para realizar transacciones.

Las operaciones de compra/venta de valores se pueden realizar sobre acciones de diversos mercados de burstiles (StockMarket) y son supervisadas siempre por un empleado (broker) de la empresa intermediaria, ya sea realizada la orden telefnicamente o telemticamente. En caso de que la orden se curse telefnicamente, debemos dejar constancia del nmero de telfono desde el que se ha cursado la operacin; dicho nmero debe coincidir con el registrado del cliente. La orden, que se realiza en una fecha y hora concreta, puede ser de compra o de venta, para un nmero determinado de ttulos (siempre igual o superior a uno e inferior a los ofertados) a un precio concreto en euros. No se considera la posibilidad de colocar rdenes de compra/venta 'a ejecutar' al llegar una accin a un precio determinado u otras operaciones ms complejas. Constar para cada mercado burstil, de forma textual, el tipo de moneda que se utiliza.

Los brokers de las empresas gestoras no tienen contrato de exclusividad, por lo que al realizar sus intermediaciones deber quedar reflejado por cuenta de que empresa gestora estn actuando. Los clientes realizan siempre (directamente) las transacciones con el mismo broker, siendo estos los que realizan las operaciones a travs de una empresa u otra. Un brker tendr siempre otro brker de referencia o sustituto, para el caso de que ste no puede atender a los clientes.

Cada valor del mercado puede repartir dividendos de forma peridica (fichero dividends.txt), por lo que nos interesa poder facilitar dicha informacin a los clientes. De los dividendos se desea

PEC 3 Fecha mxima de entrega: 28/04/2015 PROPUESTA DE SOLUCIN


2

almacenar la fecha y el importe (euros) de las ltimas cantidades repartidas (no se consideran otras posibilidades, tales como pago en valores o especias).

El valor de las acciones se actualiza automticamente en 'el sistema' con la insercin de registros en una tabla llamada 'IntraDay'. El identificador nico de dichos registros constar de ticket+fecha+hora. El precio que conste en el registro insertado ms recientemente para una accin, ser considerado el 'valor actual' para los ttulos ofertados, y por tanto el de compra o venta. Adicionalmente al campo 'volume' de dicha tabla (qu indica el total de acciones que se han comprado/vendido hasta el momento), se aadir un campo que indicar el total de acciones que se ofrecen al precio que consta en el mismo registro.

a. Realizad el diagrama entidad/relacin para la base de datos que dar soporte al sistema informtico que almacenar todos los datos, segn el modelo descrito (se deben incluir exclusivamente las entidades y relaciones bsicas que se pueden extraer del enunciado).

NOTA: Los ficheros adjuntos a la PEC y el enunciado del resto de ejercicios pueden ser de ayuda para establecer algunas de las entidades y relaciones. Por coherencia, se pide conservar el nombre de las tablas que existen en el segundo ejercicio.

b. Realizad la transformacin del modelo ER al modelo relacional. Cread claves identificadoras sintticas siempre que lo creis necesario.

BrokerCompany (id, name, startDate, endDate)

Person (id, NIF, name, phoneNumber, birthDate, deadDate, gender)

Donde (id, phoneNumber) es clave alternativa Donde (phoneNumber) no puede ser nul


3

Employee (id, startDate, endDate, idSubstitute) Donde (id) hace referencia a Person Donde (idSubstitute) referencia Employee

Client (id, idBroker, startDate, endDate) Donde (id) hace referencia a Person Donde (idBroker) hace referencia a Employee

Work (idEmployee, idBrokerCompany, startDate, endDate) Donde (idEmployee) hace referencia a Employee Donde (idBrokerCompany) hace referencia a BrokerCompany

StockMarket (id, name)

ShareStock (ticket, idStockMarket, shortName, longName, startDate, endDate) Donde (id_mercat) hace referencia a StockMarket

Dividend (ticket, dividendDate, withTax, withOutTax) Donde (ticket) hace referencia a ShareStock

IntraDay (ticket, dividendDate, dividendHour, price, volume, quantityStock) Donde (ticket) hace referencia a ShareStock

BuySell (id, ticket, intraDayDay, IntraDayHour, idEmployee, idBrokerCompany, idClient, phoneNumber, buySell, quantityStock, price, DateHour) Donde (ticket, intraDayDay, intraDayHour) hace referencia a Intraday Donde (idClient) hace referencia a Client Donde (idEmployee, BrokerCompany) hace referencia a Work

NOTA: Donde (idClient, phoneNumber) hace referencia a clave alternativa de Person

c. Cread las tablas segn atributos, claves (primarias y forneas) y restricciones que se entrevean en el SCHEMA llamado p15 (creado en la PEC2). Adjuntad el cdigo SQL (de PostgreSQL) utilizado para llevar a trmino todas las operaciones.

NOTA: 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. Indentad/tabulad el cdigo SQL para que sea muy legible (mostrad un nico campo por lnea).

RESTRICCIONES A TENER EN CUENTA OBLIGATORIAMENTE AL CREAR LAS TABLAS:

- El sustituto de un broker, no puede ser l mismo. - Las fechas de inicio que consten han de ser siempre anteriores a las fechas de finalizacin. - El gnero de una persona debe ser siempre M o F. - El nombre de una empresa gestora no puede ser NUL.

CDIGO SQL PARA LA CREACIN DE LAS TABLAS:

SET search_path TO p15, "$user", public;

CREATE TABLE BrokerCompany ( id DECIMAL(2,0), name VARCHAR(25), startDate DATE,


4

endDate DATE, CONSTRAINT PK_BrokerCompany PRIMARY KEY (id), CONSTRAINT NN_name CHECK (name IS NOT NULL));

CREATE TABLE Person ( id DECIMAL(6,0), NIF VARCHAR(12), name VARCHAR(60), phoneNumber VARCHAR(12), birthDate DATE, deadDate DATE, gender CHAR(1), CONSTRAINT PK_Person PRIMARY KEY (id), CONSTRAINT AK_Person UNIQUE (id, phoneNumber), CONSTRAINT CH_deadDate CHECK(deadDate IS NULL OR deadDate > birthDate), --CONSTRAINT NN_phoneNumber CHECK (phoneNumber IS NOT NULL) CONSTRAINT CH_gender CHECK(gender IN ('M', 'F')));

CREATE TABLE Employee ( id DECIMAL(6,0), startDate DATE, endDate DATE, substitute DECIMAL(6,0), CONSTRAINT PK_Employee PRIMARY KEY (id), CONSTRAINT FK_idEmployee FOREIGN KEY (id) REFERENCES Person(id), CONSTRAINT FK_idSubstitute FOREIGN KEY (substitute) REFERENCES Employee(id), CONSTRAINT CH_idSubstitute CHECK (substitute id), CONSTRAINT CH_empEndDate CHECK(endDate IS NULL OR endDate > startDate));

CREATE TABLE Client ( id DECIMAL(6,0), idBroker DECIMAL(7,0), startDate DATE, endDate DATE, CONSTRAINT PK_Client PRIMARY KEY (id), CONSTRAINT FK_idClient FOREIGN KEY (id) REFERENCES Person(id),

CONSTRAINT FK_idBroker FOREIGN KEY (idBroker) REFERENCES Employee(id), CONSTRAINT CH_cliEndDate CHECK(endDate IS NULL OR endDate > startDate));

CREATE TABLE Work ( idEmployee DECIMAL(6,0), idBrokerCompany DECIMAL(2,0), startDate DATE, endDate DATE, CONSTRAINT PK_Work PRIMARY KEY (idEmployee, idBrokerCompany), CONSTRAINT FK_Employee FOREIGN KEY (idEmployee) REFERENCES Employee(id), CONSTRAINT FK_BrokerCompany FOREIGN KEY (idBrokerCompany) REFERENCES BrokerCompany(id),

CONSTRAINT CH_dividendDate CHECK(endDate IS NULL OR endDate > startDate));

CREATE TABLE StockMarket ( id DECIMAL(3,0), name VARCHAR(20), currency VARCHAR(10), CONSTRAINT PK_StockMarket PRIMARY KEY(id), --CONSTRAINT CH_currency CHECK(currency = 'Euro'), CONSTRAINT CH_name CHECK (name IS NOT NULL));

CREATE TABLE ShareStock ( ticket CHAR(12), idStockMarket DECIMAL(3,0), shortName VARCHAR(6),


5

longName VARCHAR(50), startDate_price DATE, endDate_price DATE, CONSTRAINT PK_ShareStock PRIMARY KEY (ticket), CONSTRAINT FK_ShareStock FOREIGN KEY (idStockMarket) REFERENCES StockMarket(id), CONSTRAINT AK_idStockMarket UNIQUE (ticket, idStockMarket), CONSTRAINT NN_longName CHECK (longName IS NOT NULL));

CREATE TABLE Dividend ( ticket CHAR(12),

dividendDate DATE, importWithOutTAX DECIMAL(6,2),

importWithTAX DECIMAL(6,2), CONSTRAINT pk_Dividend PRIMARY KEY (ticket, dividendDate), CONSTRAINT fk_Dividend_ticket FOREIGN KEY (ticket) REFERENCES ShareStock(ticket));

CREATE TABLE IntraDay ( ticket CHAR(12), intraDayDay DATE, intraDayHour TIME, price DECIMAL(7,2), volume DECIMAL(9,0), quantityStock DECIMAL(8,0), CONSTRAINT PK_IntraDay PRIMARY KEY(ticket, intraDayDay, intraDayHour), CONSTRAINT FK_hareStock FOREIGN KEY (ticket) REFERENCES ShareStock(ticket), CONSTRAINT CH_volume CHECK (volume > 0));

CREATE TABLE BuySell ( id DECIMAL(6,0),

ticket CHAR(12), intraDayDay DATE, intraDayHour TIME, idEmployee DECIMAL(6,0), idBrokerCompany DECIMAL(2,0), idClient DECIMAL(6,0), phoneNumber VARCHAR(12), buySell CHAR(1), quantityStock DECIMAL(7,0), price DECIMAL(6,2), dateHour TIMESTAMPTZ DEFAULT NOW(), CONSTRAINT PK_BuySell PRIMARY KEY (id), CONSTRAINT FK_ticket FOREIGN KEY (ticket, intraDayDay, intraDayHour) REFERENCES

IntraDay(ticket, intraDayDay, intraDayHour), CONSTRAINT FK_idClient FOREIGN KEY (idClient) REFERENCES Client(id), CONSTRAINT FK_idEmployee FOREIGN KEY (idEmployee) REFERENCES Employee(id), CONSTRAINT FK_idClientPhoneNumber FOREIGN KEY (idClient, phoneNumber) REFERENCES

Person(id, phoneNumber), CONSTRAINT CH_BuySell CHECK (BuySell IN ('B', 'S')), CONSTRAINT CH_quantityStock CHECK (quantityStock > 0));

Nota: La clave primaria ID debera ser SERIAL, pero a efectos de facilitar las inserciones en la PEC4 se utiliza el tipo DECIMAL.

Ejercicio voluntario: Repetid las sentencias de creacin de las tablas con la sintaxis SQL de MySQL/MariaDB, utilizando InnoDB.

2. Manipulacin y consultas (25%)

a. Teniendo en cuenta la estructura de las tablas del primer ejercicio, el contenido de los ficheros adjuntos al enunciado de la PEC y el contenido de la tabla LoadIbexIntraDay, poblad de datos las


6

tablas de deben tener por nombre; StockMarket, ShareStock, IntraDay y Dividend. Mostrad las sentencias SQL utilizadas.

Indicaciones para cargar los datos en StockMarket: Cargad directamente los datos desde el fichero de texto suministrado con el enunciado de la PEC.

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

Indicaciones para cargar ShareStock: Cread una tabla temporal con nombre tempDividends y cargad todos los datos del fichero Dividends.txt en dicha tabla.

CREATE TABLE tempDividends ( ticket CHAR (12), idStockMarket DECIMAL(3,0), marketName VARCHAR(15), shortName CHAR(4), longName VARCHAR(50), dividendDate DATE, importWithTAX DECIMAL(6,2), importWithOutTAX DECIMAL(6,2));

COPY tempDividends FROM 'c:/dividends.txt' WITH DELIMITER E'\t';

Cargad los campos ticket, idStockMarket, shortName y longName en ShareStock desde la tabla temporal tempDividends. NOTA: existen valores del IBEX35 y del Mercado Continuo. INSERT INTO ShareStock (ticket, idStockMarket, shortName, longName) SELECT ticket, MAX(idStockMarket), MAX(shortName), MAX(longName) FROM tempDividends GROUP BY ticket;

Indicaciones para realizar la carga de Dividend:

Pobladla a partir de la tabla temporal tempDividends.

INSERT INTO dividend (ticket, dividendDate, importWithOutTAX, importWithTAX) SELECT ticket, dividendDate, importWithOutTAX, importWithTAX FROM tempDividends;

Indicaciones para la carga de IntraDay: Sustituir el literal 'cierre' de la hora de loadIbexIntraDay por las 18:00. Poblad la tabla IntraDay desde la tabla LoadIbexIntraDay. La clave primaria ha de ser el identificador del ticket+fecha+hora. El campo 'quantity se deber llenar con el valor '0' (cero).

UPDATE loadIbexIntraDay SET hourCot = '18:00' WHERE hourCot = 'Cierre';

INSERT INTO IntraDay (ticket, intraDayDay, intraDayHour, price, volume, quantityStock) SELECT ticket, TO_DATE(dateCot, 'DD/MM/YYYY'), TO_TIMESTAMP(hourCot, 'HH24:MI'), MAX(last), MAX(volume), 0 FROM loadIbexIntraDay GROUP BY ticket, dateCot, hourCot;


7

Llenad la siguiente tabla con el nmero de registros que han quedado en las tablas indicadas.

Nombre Tabla Num. total registros StockMarket 6 IntraDay 795055 tempDividends 351 Dividend 351 ShareStock 120

b. Realizad la siguiente consulta SQL:

Calcular el inters equivalente anual que dara segn la suma de los pagos de los dividendos de 2008 (importe bruto), el valor del IBEX35 que ha tenido ms 'volumen' total durante el periodo que estamos estudiando, segn el importe mnimo que ha tenido la accin durante el mencionado periodo.

(1/MnimaCotitzacinAccinDeMsVolumen)*(SumaDividendos_2008*100)

Indicaciones y consideraciones: Para llevar a cabo la consulta, considerad los siguientes pasos:

- Obtened en primer lugar cual ha sido el valor con ms volumen. Para ello consideraremos que la columna volume se incrementa a lo largo de una sesin, quedando en la misma el nmero total de transacciones realizadas. Por tal motivo, el volumen final de das ser la cantidad mayor, y ltimo dato del da. El valor de bolsa con ms volumen ser por lo tanto, el que d mayor cantidad al sumarse dichos volmenes de final de da del periodo estudiado.

- Obtenido el valor de ms volumen, ser necesario recuperar el precio mnimo que ha tenido durante dicho periodo.

- Del mismo valor, ser necesario recuperar la suma total de los dividendos para el ao anotado.

Obtenidas con una consulta todos dichos datos, se podr proceder a realizar el clculo y a mostrar el resultado.

Mostrad el cdigo SQL y una captura de pantalla con el resultado.

SELECT (SELECT MIN(longName) FROM IntraDay, ShareStock WHERE IntraDay.ticket = ShareStock.ticket GROUP BY IntraDay.ticket ORDER BY SUM(volume) DESC LIMIT 1) AS Name, (SELECT MIN(price) FROM IntraDay GROUP BY ticket ORDER BY SUM(volume) DESC LIMIT 1) AS MinPrice, (SELECT SUM(importWithTAX) FROM Dividend WHERE ticket = (SELECT ticket FROM IntraDay GROUP BY ticket ORDER BY SUM(volume) DESC LIMIT 1) AND TO_CHAR(dividendDate, 'YYYY') = '2008') AS Dividend_2008, (1/( SELECT MIN(price) FROM IntraDay GROUP BY ticket


8

ORDER BY SUM(volume) DESC LIMIT 1)*SUM(importWithTAX))*100 AS EqPercentAnually FROM Dividend WHERE ticket = (SELECT ticket FROM IntraDay GROUP BY ticket ORDER BY SUM(volume) DESC LIMIT 1) AND TO_CHAR(dividendDate, 'YYYY') = '2008';

El resultado indica que los dividendos anuales de 2008 del Banco de Santander, representaran un 17,25% de intereses anuales, de haberse comprado las acciones en el momento de menor precio del periodo analizado (a 3,94 Euros).

3. Instalacin (15%)

Instalad el conjunto de aplicaciones Apache y PHP desde el repositorio de la distribucin Linux utilizada, a partir del instalable de EnterpriseDB (Windows), o utilizando de referencia las indicaciones que podis encontrar en el rea de materiales del aula. Anotad las posibles incidencias, describiendo de ser necesario el entorno utilizado (Debian, Ubuntu, Windows, etc).

Seguid las indicaciones del documento Ejemplo PHP+PostgreSQL, hasta ejecutar el programa 'citascitables.php. Adjuntad un par de capturas de pantalla para demostrar su funcionamiento y describid brevemente las incidencias/dificultades de todo el proceso.

Pegad aqu las capturas de pantalla:


9

Ejercicio voluntario: Anotad las modificaciones y el cdigo SQL que sera necesario, para implementar la mejora que se indica en el documento Ejemplo PHP+PostgreSQL, a efectos de obtener las citas secuencialmente, a cada carga de la pgina web.

Formato de entrega

Se valorar que las sentencias de creacin de tablas y SQLs estn adecuadamente indentados.

Se debern entregar dos documentos; un fichero ofimtico editable (DOC, ODT, RTF...) y una versin del mismo en formato PDF (a efectos de evitar problemas de insercin de imgenes y otros derivados de la ofimtica...).

No comprimis el fichero (ni ZIP, ni RAR, etc), ni entreguis ficheros adicionales a los documentos indicados.

El nombre de los documentos a entregar debe seguir el patrn: BD_PEC3_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 59min del martes 28 de abril de 2015.

PEC3_Solución

Documents

Transcript of PEC3_Solución