PEC 4 Fecha mxima de entrega: 03/06/2015

PROPUESTA DE SOLUCIN Presentacin y ObjetivosLa Prueba de Evaluacin Continuada IV (PEC4) es la ltima de las cuatro PECs de las queconsta la asignatura. Esta PEC tiene un peso del 35% en la nota final y los conceptos que setrabajan en ella son los correspondientes a los mdulos "Bases de datos en MySQL", "Bases dedatos en PostgreSQL", "Desarrollo de aplicaciones con conexin a Bases de Datos" y "Casos deestudio".

La PEC consta de tres ejercicios prcticos. La valoracin de cada ejercicio en el global de laPEC se incluye en la cabecera del enunciado. Puesto que la asignatura no tiene examen, esnecesario realizar todas las PECs para poder superar la asignatura. Los ejercicios parten de lasolucin de las PECs anteriores, por lo que es importante tener las tablas creadas y losdatos cargados segn constan en las mismas.

1. Funciones PostgreSQL (45%)La operativa del sistema informtico preparado para las empresas que desean operar en elmercado burstil parece ser todo un xito, por lo que se desea codificar una de las funcionesque se cree ser crtica para su funcionamiento: un disparador que permitir realizar la comprade las acciones.

En el momento en que se realice una insercin en la tabla 'BuySell' (se asumir que siempre esuna compra) se deber ejecutar un disparador que ejecute una funcin que realice lo siguiente:

- Sustituir el valor del campo dateHour por la hora actual del servidor (con now()).- Asignar una 'B' al campo buySell.- Comprobar en IntraDay que para el valor identificado por ticket, fecha y hora, existen

suficientes acciones disponibles y que el precio al que se desea adquirir las acciones esigual o superior al que consta en IntraDay. En caso que no se cumpla alguna de las doscondiciones, se deber sustituir el valor del campo de BuySell.quantityStock por un cero.En el caso que se cumplan las dos condiciones, se sustituir el valor de compra deseadopor el precio de IntraDay (podemos comprar acciones siempre que tengan un preciomenor al mximo que deseamos pagar) y descontaremos de IntraDay.quantityStock elnmero de acciones que se compran.

NOTA: es imprescindible para realizar el ejercicio tener las tablas creadas y los datos cargadosexactamente segn la propuesta de solucin oficial de la PEC3. La existencia de laCONSTRAINT que impide insertar en BuySell rdenes de cero acciones es el mecanismo(sencillo y bsico) elegido como control de errores.A efectos de facilitar el desarrollo del disparador / funcin, se considerar que la compra seefecta al realizar las inserciones adjuntas, en la tabla BuySell (se debern ejecutar tambin losSQL adjuntos para preparar las tablas con unos datos bsicos):DELETE FROM BuySell;

1

DELETE FROM Client CASCADE;DELETE FROM Employee CASCADE;DELETE FROM BrokerCompany CASCADE;DELETE FROM Person CASCADE;

INSERT INTO Person VALUES (1, '12345678T', 'Pirno Gamez, Nazaret', '911231234', '01/01/1970', NULL, 'M');INSERT INTO Person VALUES (2, '3213213S', 'Romero Parta, Chavier', '939879876', '01/01/1964', NULL, 'M');INSERT INTO BrokerCompany VALUES (1, 'Gestora Ganancia', '01/01/2000', NULL);INSERT INTO Employee VALUES (2, '06/04/2000', NULL);INSERT INTO Client (id, idBroker, startDate) VALUES (1, 2, '09/10/2001');

UPDATE intraDay SET quantityStock = 500 WHERE ticket = 'ES0167050915' AND intraDayDay = '11/03/2009' ANDintraDayHour = '09:15:00';

Mostrad el cdigo de la funcin y del disparador.

CREATE FUNCTION FU_BuySell() RETURNS TRIGGER AS ' BEGIN NEW.dateHour = now(); NEW.buySell = ''B'';

IF (SELECT IntraDay.quantityStock FROM IntraDay WHERE intraDay.ticket = NEW.ticket AND IntraDay.intraDayDay = NEW.intraDayDay AND IntraDay.intraDayHour = NEW.intraDayHour) < NEW.quantityStock OR (SELECT intraDay.price FROM IntraDay

WHERE intraDay.ticket = NEW.ticket AND IntraDay.intraDayDay = NEW.intraDayDay AND IntraDay.intraDayHour = NEW.intraDayHour) > NEW.price THEN NEW.quantityStock = 0; ELSE NEW.price = (SELECT intraDay.price FROM IntraDay WHERE IntraDay.ticket = NEW.ticket AND IntraDay.intraDayDay = NEW.intraDayDay AND IntraDay.intraDayHour = NEW.intraDayHour); UPDATE IntraDay SET quantityStock = (quantityStock - NEW.quantityStock) WHERE ticket = NEW.ticket AND

IntraDayDay = NEW.intraDayDay AND IntraDayHour = NEW.intraDayHour;

END IF; RETURN NEW;END;' LANGUAGE 'plpgsql';

CREATE TRIGGER TR_BuySell BEFORE INSERT ON BuySell FOR EACH ROW EXECUTE PROCEDURE FU_BuySell();

Al restar el nmero de acciones compradas del campo quantityStock de IntraDay, qu pasarcuando se alcancen las cero acciones?

Se pueden alcanzar las cero acciones, pero no se podr continuar decrementando el valor deeste campo (no se puede comprar una cosa que no existe), puesto en la funcin sustituir, en

2

dicho caso, el valor del campo quantityStock de BuySell por cero con el fin qye entre en accin larestriccin existente para dicho campo.

Qu pasar si el nmero de telfono desde donde llama el comprador (que se intenta insertar)no es el que se tiene anotado en Person?

Puesto hay integridad referencial entre el nmero de telfono de BuySell y el nmero de telfonode Person, la insercin no se llegar a realizar.

Indicad los mensajes que retorna el SGBD e indicad si la insercin es correcta/incorrecta en lossiguientes casos:

INSERT INTO BuySell (id, ticket, intraDayDay, intraDayHour, idEmployee, idBrokerCompany, idClient, phoneNumber,quantityStock, price) VALUES (1, 'ES0167050915', '11/03/2009', '09:15:00', 2, 1, 1, '911231234', 100, 30.00);

INSERT INTO BuySell (id, ticket, intraDayDay, intraDayHour, idEmployee, idBrokerCompany, idClient, phoneNumber,quantityStock, price) VALUES (2, 'ES0167050915', '11/03/2009', '09:15:00', 2, 1, 1, '911231234', 100, 28.50);

INSERT INTO BuySell (id, ticket, intraDayDay, intraDayHour, idEmployee, idBrokerCompany, idClient, phoneNumber,quantityStock, price) VALUES (3, 'ES0167050915', '11/03/2009', '09:15:00', 2, 1, 1, '911231234', 600, 29.75);

nicamente funcionar la primera insercin, dado que en la segunda el precio de compramximo indicado es inferior al precio de IntraDay, y en la tercera no hay suficientes accionesdisponibles para realizar la compra (no se ha contemplado la posibilidad de realizar comprasparciales).Mostrad (captura de pantalla) el contenido final de la tabla BuySell y del registro IntraDay conticket ES0167050915 y fecha/hora '11/03/2009', '09:15:00', despus de haber realizado lasinserciones del inicio del ejercicio, teniendo el disparador y la funcin creados, y habiendoejecutado los insert de ste apartado.

NOTAS de desarrollo:- Cread la funcin incrementalmente, probando cada update y condicin individualmente.- En caso de borrar la tabla BuySell se borrarn automticamente los triggers asociados.- Se considera (a efectos de simplificacin) que toda insercin de registro en la tabla

BuySell corresponde a una Compra (no tenindose que tratar el posible caso de venta).- Al modificar / borrar la funcin que se desarrolla, asegurad cada vez que el trigger sigue

'disparndose'. Es muy til poner como primera lnea de la funcin la asignacin de la 'B'de 'Compra' en el campo correspondiente, y verificar a cada prueba que en los datos quese insertan en BuySell consta dicha 'B'.

3

2. Sistema de consultas Web (45%)Las expectativas del nuevo sistema informtico para la intermediacin de acciones son cada vezmayores, por lo que se decide empezar formalmente su desarrollo. Se pide realizar en PHP una aplicacin bsica de tres pantallas;

Pantalla autentificacin:Todo cliente que desee utilizar el sistema deber introducir su nick y su contrasea. En caso deser vlidos, se podr pasar a la pantalla de compra de acciones.

Pantalla compra acciones:Se debern poder introducir los siguientes datos (en sus respectivos campos);

- Nombre corto de la accin que deseamos adquirir (por ejemplo SAB de Banco Sabadell). - Nmero acciones que se desean adquirir.- Importe mximo que deseamos pagar por las acciones.- Identificador del cliente (campo id). NOTA: Se aade para simplificar el cdigo PHP

(idealmente se debera arrastrar dicho valor desde la pantalla de autentificacin).- Identificador el empleado (id del broker) con el que realizaremos la operacin (se asumir

que ste operar siempre con el gestor con ID = 2).- Nmero de telfono desde el que se realiza la operacin.

Pantalla resultados:Una vez se pulse sobre 'Aceptar' en la pantalla de compra, el programa deber:

- Recuperar el identificador 'ticket' segn el nombre corto introducido. Recuperar la fecha yhora del ltimo registro que consta en IntraDay para dicho valor.

- Con 'ticket', la fecha y hora, y el resto de datos introducidos, realizar la insercin enBuySell.

Una vez finalizado el proceso se deber mostrar un listado con los datos de todas lasoperaciones de compra que ha realizado el cliente para dicho valor, mostrndose en cada lnea:la fecha y hora de la compra, el nombre corto del valor, el nmero de acciones, el precio de lasacciones y el importe total (num. acciones*precio compra)Al final del listado deber aparecer la suma del total de acciones, junto con el precio total deadquisicin.

NOTA: - El cdigo PHP de la pantalla que pide el nombre de usuario y contrasea para la

autentificacin se puede reaprovechar de la PAC4 de semestres anteriores (no esnecesario asegurar 'la seguridad' del sistema de autentificacin).

- Se deber ampliar la tabla Client con los campos 'nick' y 'password', a efecto de poderrealizar la verificacin inicial.

- En caso de indicarse un precio de compra inferior al que consta en IntraDay o no quedaracciones suficientes al intentar realizarse el insert, se producir un error por unaCONSTRAINT. Aunque no hace falta realizar el tratamiento del error (puede quedar elmensaje en pantalla), se deber mostrar listado de las acciones que consta que tiene elcliente para el valor concreto.

Para probar la aplicacin, borrad el contenido de las tablas Person, BrokerCompany, Employee,Client y BuySell e insertad los siguientes registros (dichos datos son les que deben aparecer enlas capturas de pantalla que demuestren el funcionamiento de la aplicacin):

4

DELETE FROM BuySell;DELETE FROM Client CASCADE;DELETE FROM Employee CASCADE;DELETE FROM BrokerCompany CASCADE;DELETE FROM Person CASCADE;

ALTER TABLE Client ADD nick VARCHAR(20);ALTER TABLE Client ADD password VARCHAR(20);

INSERT INTO Person VALUES (1, '12345678T', 'Pirno Gamez, Nazaret', '911231234', '01/01/1970', NULL, 'M');INSERT INTO Person VALUES (3, '98798765Y', 'Jimeno Vilchez, Iban', '977213708', '01/01/1980', NULL, 'M');INSERT INTO Person VALUES (2, '3213213S', 'Romero Parta, Chavier', '939879876', '01/01/1964', NULL, 'M');INSERT INTO BrokerCompany VALUES (1, 'Gestora Ganancia', '01/01/2000', NULL);INSERT INTO BrokerCompany VALUES (2, 'Ilusiones', '31/12/2004', NULL);INSERT INTO Employee VALUES (2, '06/04/2000', NULL);INSERT INTO Client (id, idBroker, startDate, nick, password) VALUES (1, 2, '09/10/2001', 'pgamez', 'abc');INSERT INTO Client (id, idBroker, startDate, nick, password) VALUES (3, 2, '12/11/2003', 'jivi', '123');

Nota: al realizar el ALTER TABLE de la tabla CLIENTE, ya no podemos asegurar el orden de loscampos.

UPDATE IntraDay SET quantityStock = 500 WHERE ticket = 'ES0167050915' AND intraDayDay = '11/03/2009' ANDintraDayHour = '09:15:00';UPDATE IntraDay SET quantityStock = 500 WHERE ticket = 'ES0167050915' AND intraDayDay = '03/09/2008' ANDintraDayHour = '15:20:00';UPDATE IntraDay SET quantityStock = 500 WHERE ticket = 'ES0167050915' AND intraDayDay = '16/06/2008' ANDintraDayHour = '14:52:00';

INSERT INTO BuySell (id, ticket, intraDayDay, intraDayHour, idEmployee, idBrokerCompany, idClient, phoneNumber,quantityStock, price) VALUES (4, 'ES0167050915', '16/06/2008', '14:52:00', 2, 1, 1, '911231234', 100, 34.78);INSERT INTO BuySell (id, ticket, intraDayDay, intraDayHour, idEmployee, idBrokerCompany, idClient, phoneNumber,quantityStock, price) VALUES (5, 'ES0167050915', '03/09/2008', '15:20:00', 2, 1, 1, '911231234', 200, 31.10);INSERT INTO BuySell (id, ticket, intraDayDay, intraDayHour, idEmployee, idBrokerCompany, idClient, phoneNumber,quantityStock, price) VALUES (6, 'ES0167050915', '11/03/2009', '09:15:00', 2, 1, 1, '911231234', 100, 29.90);INSERT INTO BuySell (id, ticket, intraDayDay, intraDayHour, idEmployee, idBrokerCompany, idClient, phoneNumber,quantityStock, price) VALUES (7, 'ES0167050915', '11/03/2009', '09:15:00', 2, 2, 3, '977213708', 100, 29.65);

Se pide mostrar los programas PHP y una captura de pantalla, de cada 'pantalla', que muestre elfuncionamiento y el resultado de las operaciones y consultas.autentica.php

Brokers OnLine Broker's intranet Introduce nombre de usuario USER: PASSWORD:

5

control.php

Brokers OnLine

Id. Client: Phone number:

compra_accio.php

Buy Shares

Hour Share Quantity/CENTER> Buy price Total Quantity Share: Total value: Back to home

Las pantallas que corresponden al funcionamiento son:

8

Despus de realizarse la insercin, aparece el resumen:

Se observa que el precio de compra que se ha indicado en la pantalla de introduccin de datos,ha sido sustituido por el que tena la accin en aquel momento (en IntraDay).Despus de realizar una segunda compra aparecer:

9

3. Consulta datos cliente/servidor (10%)Para incrementar la interoperatibilidad del sistema de compra/venta burstil, se creeconveniente probar una consulta sobre el SGBD MySQL/MariaDB.Se desea:

1. Cread en el MySQL/MariaDB las tablas ShareStock y BuySell, equivalentes a lasexistentes el en PostgreSQL.

2. Cargad los datos de las tablas recin creadas, de los ficheros .txt del mismo nombreadjuntos al enunciado de la PEC.3. Realizad un programa en Java (segn ejemplo adjunto al enunciado de la PEC), queaccediendo a la base de datos MySQL/MariaDB por JDBC, retorne el listado de acciones quetiene el cliente con id 3 (para simplificar el ejercicio constar que dicho valor siempre ser fijoen la consulta). En cada lnea se deber mostrar el nombre corto del valor, la fecha (terceracolumna), el total de acciones, el precio de compra y el importe de la operacin.Se pide mostrar los SQL utilizados para la carga de los datos, el programa java desarrolladoy una captura de pantalla que muestre su funcionamiento.

CREATE TABLE ShareStock (ticket CHAR(12),idStockMarket DECIMAL(3,0),shortName VARCHAR(6),longName VARCHAR(50),CONSTRAINT PK_ShareStock PRIMARY KEY (ticket),CONSTRAINT AK_ticketShareStock UNIQUE (ticket, idStockMarket),CONSTRAINT CH_longName CHECK (longName IS NOT NULL))

ENGINE=INNODB;

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),quantity DECIMAL(7,0),price DECIMAL(6,2),CONSTRAINT PK_BuySell PRIMARY KEY (id),CONSTRAINT FK_ticket FOREIGN KEY (ticket) REFERENCES ShareStock(ticket))

ENGINE=INNODB;

LOAD DATA INFILE 'c:/ShareStock.txt' INTO TABLE ShareStock FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

LOAD DATA INFILE 'c:/BuySell.txt' INTO TABLE BuySell

10

FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

//// Check the actions of a particular user (the ID should be entered for parameter)// 20150420//import java.io.*;import java.lang.*;import java.sql.*;public class consulta {public static void main(String[] args) throws Exception { String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/test"; String user = "root"; String passwd = "1234"; Class.forName(driver); Connection conexion = DriverManager.getConnection(url, user, passwd);

// SQL Statement compra = conexion.createStatement(); ResultSet rscompra = compra.executeQuery ("SELECT ShareStock.shortName, BuySell.intraDayDay, BuySell.quantity, BuySell.price, BuySell.quantity * BuySell.priceFROM BuySell, ShareStock WHERE BuySell.ticket = ShareStock.ticket AND BuySell.idClient = 3;");

// Retrieve each row

System.out.println("SHARE_NAME DATE NUMBER PRICE IMPORT"); while (rscompra.next()) { System.out.println(rscompra.getString(1) + " \t" + rscompra.getString(2) + " \t" + rscompra.getString(3) + " \t" + rscompra.getString(4) + " \t" +rscompra.getString(5)); } // Close connection conexio.close(); }}

11

Formato de entregaSe valorar que las sentencias de creacin de tablas, los SQLs, el cdigo java y PHP sean muylegibles y estn adecuadamente indentados (tabulados).Se debern entregar tres ficheros: un fichero ofimtico editable (ODT, DOC, RTF), una versindel mismo en formato PDF (a efectos de evitar problemas de insercin de imgenes y otros deri-vados de la ofimtica) y un tercer fichero de tipo zip o rar. Este ltimo deber contener todo elcdigo PHP y JAVA. Incluid adems todo el cdigo SQL, Java, y PHP en el documento editable.

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

El nombre de los documentos a entregar debe seguir el patrn:BD_PEC4_Apellido1Nombre.extensin y debe constar vuestro nombre y apellidos al principio dela PEC.

La PEC se entregar a travs del apartado correspondiente del aula.

La fecha mxima de entrega, improrrogable, son las 23h 59min del da 3 de junio de 2015.

12