BD PEC4 Enunciado

Master Internacional en Software Libre Bases de Datos

1

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

Presentacin y Objetivos La Prueba de Evaluacin Continuada IV (PEC4) es la ltima de las cuatro PECs de las que consta la asignatura. Esta PEC tiene un peso del 35% en la nota final y los conceptos que se trabajan en ella son los correspondientes a los mdulos "Bases de datos en MySQL", "Bases de datos en PostgreSQL", "Desarrollo de aplicaciones con conexin a Bases de Datos" y "Casos de estudio".

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

1. Funciones PostgreSQL (45%)

La operativa del sistema informtico preparado para las empresas que desean operar en el mercado burstil parece ser todo un xito, por lo que se desea codificar una de las funciones que se cree ser critica para su funcionamiento: un disparador que permitir realizar la compra de las acciones.

En el momento en que se realice una insercin en la tabla 'BuySell' (se asumir que siempre es una 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 es igual o superior al que consta en IntraDay. En caso que no se cumpla alguna de las dos condiciones, 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 deseado por el precio de IntraDay (podemos comprar acciones siempre que tengan un precio menor al mximo que deseamos pagar) y descontaremos de IntraDay.quantityStock el nmero de acciones que se compran.

NOTA: es imprescindible para realizar el ejercicio tener las tablas creadas y los datos cargados exactamente segn la propuesta de solucin oficial de la PEC3. La existencia de la CONSTRAINT 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 se efecta al realizar las inserciones adjuntas, en la tabla BuySell (se debern ejecutar tambin los SQL adjuntos para preparar las tablas con unos datos bsicos):

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


2

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' AND intraDayHour = '09:15:00';

Mostrad el cdigo de la funcin y del disparador.

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

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?

Indicad los mensajes que retorna el SGBD e indicad si la insercin es correcta/incorrecta en los siguientes 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);



Mostrad (captura de pantalla) el contenido final de la tabla BuySell y del registro IntraDay con ticket ES0167050915 y fecha/hora '11/03/2009', '09:15:00', despus de haber realizado las inserciones del inicio del ejercicio, teniendo el disparador y la funcin creados, y habiendo ejecutado los insert de ste apartado.


3

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 que se insertan en BuySell consta dicha 'B'.

2. Sistema de consultas Web (45%)

Las expectativas del nuevo sistema informtico para la intermediacin de acciones son cada vez mayores, 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 de ser 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 y hora 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 en BuySell.

Una vez finalizado el proceso se deber mostrar un listado con los datos de todas las operaciones 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 las acciones 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 de adquisicin.


4

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 es necesario asegurar 'la seguridad' del sistema de autentificacin).

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

- En caso de indicarse un precio de compra inferior al que consta en IntraDay o no quedar acciones suficientes al intentar realizarse el insert, se producir un error por una CONSTRAINT. Aunque no hace falta realizar el tratamiento del error (puede quedar el mensaje en pantalla), se deber mostrar listado de las acciones que consta que tiene el cliente 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 en las capturas de pantalla que demuestren el funcionamiento de la aplicacin):

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 los campos.

UPDATE IntraDay SET quantityStock = 500 WHERE ticket = 'ES0167050915' AND intraDayDay = '11/03/2009' AND intraDayHour = '09:15:00'; UPDATE IntraDay SET quantityStock = 500 WHERE ticket = 'ES0167050915' AND intraDayDay = '03/09/2008' AND intraDayHour = '15:20:00'; UPDATE IntraDay SET quantityStock = 500 WHERE ticket = 'ES0167050915' AND intraDayDay = '16/06/2008' AND intraDayHour = '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 el funcionamiento y el resultado de las operaciones y consultas.


5

Programas PHP:

Capturas de pantalla de la aplicacin:

3. Consulta datos cliente/servidor (10%)

Para incrementar la interoperatibilidad del sistema de compra/venta burstil, se cree conveniente probar una consulta sobre el SGBD MySQL/MariaDB. Se desea:

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

2. Cargad los datos de las tablas recin creadas, de los ficheros .txt del mismo nombre adjuntos al enunciado de la PEC.

3. Realizad un programa en Java (segn ejemplo adjunto al enunciado de la PEC), que accediendo a la base de datos MySQL/MariaDB por JDBC, retorne el listado de acciones que tiene el cliente con id 3 (para simplificar el ejercicio constar que dicho valor siempre ser fijo en la consulta). En cada lnea se deber mostrar el nombre corto del valor, la fecha (tercera columna), 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 desarrollado y una captura de pantalla que muestre su funcionamiento.


6

Formato de entrega

Se valorar que las sentencias de creacin de tablas, los SQLs, el cdigo java y PHP sean muy legibles y estn adecuadamente indentados (tabulados).

Se debern entregar tres ficheros: un fichero ofimtico editable (ODT, DOC, RTF), una versin del mismo en formato PDF (a efectos de evitar problemas de insercin de imgenes y otros derivados de la ofimtica) y un tercer fichero de tipo zip o rar. Este ltimo deber contener todo el cdigo 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 los documentos 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 de la 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.

BD PEC4 Enunciado

Documents

Transcript of BD PEC4 Enunciado