M2 - Bases de dades / Practiques UF3 : PL-SQL
Pràctica PL/SQL Cal lliurar un fitxer (.sql) amb totes les comandes SQL emprades i tot el codi PL/SQL, degudament comentat.
Data màxima lliurament: 30/04/2021
Especificació
Per a la implementació d'una base de dades que suporti anàlisi multidimensional és desitja crear una taula que emmagatzemi les diferents característiques referides a una data; això és: dia, dia de la setmana,mes, any, trimestre... L'esquema relacional d'aquesta taula seria els següent:
TEMPS (data, dia, dia_setmana, mes, trimestre, any)
1.- Crea la taula TEMPS en la base de dades atenent a l'esquema relacional anterior; considerant l'atribut data com a clau principal. La resta dels atributs no admeten valors nuls.
2.- Crea una funció PL/SQL que donat un nombre enter obtingui en format text el corresponent mes. Per exemple:
EXECUTE convertir_mes(1) → Gener
3.- Crea un procediment emmagatzemat PL/SQL que rebi com a paràmetres un rang de dates i afegeixi en la taula TEMPS un registre per cada dia comprès entre aquestes dates. Per exemple:
EXECUTE afegir_temps('01/01/2019', '10/01/2019');
Per realitzar el procediment sol·licitat en el punt 3, has d'emprar la següent funció, que a partir d'una data et retorna el dia següent a aquesta data.
CREATE OR REPLACE FUNCTION dia_seguent (p_data_actual DATE)
RETURN DATE IS
BEGIN
RETURN TO_DATE(TO_CHAR(p_data_actual + 1));
END;Desprès de cridar al procediment «afegir_temps» de l'exemple anterior, la taula TEMPS hauria de contenir la següent informació:
| data | dia | dia_setmana | mes | trimestre | any |
|---|---|---|---|---|---|
| 01/01/2019 | 1 | Dimarts | Gener | T1 | 2019 |
| 01/01/2019 | 2 | Dimecres | Gener | T1 | 2019 |
| 01/01/2019 | 3 | Dijous | Gener | T1 | 2019 |
| 01/01/2019 | 4 | Divendres | Gener | T1 | 2019 |
| 01/01/2019 | 5 | Dissabte | Gener | T1 | 2019 |
| 01/01/2019 | 6 | Diumenge | Gener | T1 | 2019 |
| 01/01/2019 | 7 | Dilluns | Gener | T1 | 2019 |
| 01/01/2019 | 8 | Dimarts | Gener | T1 | 2019 |
| 01/01/2019 | 9 | Dimecres | Gener | T1 | 2019 |
| 01/01/2019 | 10 | Dijous | Gener | T1 | 2019 |
Ajuda: Pots ajudar-te de la funció TO_CHAR, que passant-li com a paràmetre una data i un atribut de temps com dia, mes, any... retorni el corresponent dia, mes, any de la data rebuda com a primer paràmetre.
4.- Executa el procediment creat per a que generi els registres corresponents als anys 2019 i 2020.
La taula TEMPS creada anteriorment la utilitzarem per datar les vendes realitzades per una empresa especialitzada en la venda d'impressores. Per tant l'esquema relacional de la base de dades quedaria de la següent forma:
TEMPS (data, dia, dia_setmana, mes, trimestre, any)
PRODUCTES (ref, nom_producte, unitats, preu, reposicio)
VENDES (num, data, producte, unitats, import) ON producte REFERENCIA A ref de la taula PRODUCTES
5.- Crea les taules VENDES i PRODUCTES atenent a l'esquema relacional anterior; considerant l'atribut num com a clau principal de la taula VENDES i ref la clau principal de la taula PRODUCTES. La resta dels atributs de la relació no admeten valors nuls.
6.- Crea dos procediments emmagatzemats: «registrar_producte» que rebent una referencia de producte, el nom, les unitats en magatzem del producte , el preu i las unitats de reposició afegeixi els valors corresponents en la taula de productes; i «registrar_venda» que rebent una data, la referencia d'un producte i les unitats venudes, utilitzi una seqüència per obtenir el número de la venda, calculi l'import (segons taula producte) i afegeixi els valors corresponents en la taula de vendes.
Nota: el procediment «registrar_venda» utilitzarà la funció existeix_producte, que a partir d'una referencia de producte retornara «true» si aquest existeix i «false» en cas contrari. Si el producte no existeix no s'haurà d'afegir la venda.
7.- Utilitza els procediments emmagatzemats creats en l'apartat 6 per afegir els següents productes i realitzar les següents vendes:
Productes
| ref | nom_producte | unitats | preu | reposicio |
|---|---|---|---|---|
| CA2535 | IMPRESORA CANON PIXMA IP2850 | 10 | 45,48 € | 6 |
| CA2542 | IMPRESORA CANON PIXMA IX6850 A3 | 8 | 234,38 € | 4 |
| HP8053 | IMPRESORA HP PAGEWIDE PRO 452DW | 5 | 573,00 € | 3 |
| CA2704 | IMPRESORA CANON PIXMA MX495 | 6 | 64,90 € | 5 |
| HP8143 | MULTIFUNCION TINTA HP ENVY 4524 | 4 | 95,00 € | 3 |
| CA2950 | IMPRESORA CANON PIXMA TS8050 | 3 | 169,90 € | 2 |
Vendes
| num | data | produce | unitats | import |
|---|---|---|---|---|
| 1 | 07/01/2019 | CA2535 | 4 | 181,92 € |
| 2 | 14/01/2019 | CA2542 | 2 | 468,76 € |
| 3 | 06/05/2019 | CA2704 | 10 | 640,90 € |
| 4 | 16/05/2019 | HP8053 | 1 | 573,00 € |
| 5 | 25/07/2019 | CA2950 | 4 | 679,60 € |
| 6 | 02/08/2019 | HP8143 | 8 | 760,00 € |
| 7 | 13/09/2019 | CA2704 | 2 | 129,80 € |
| 8 | 09/12/2019 | CA2542 | 3 | 703,14 € |
| 9 | 11/12/2019 | CA2950 | 10 | 1699,00 € |
| 10 | 10/02/2020 | HP8143 | 1 | 95,00 € |
| 11 | 11/05/2020 | HP8053 | 1 | 573,00 € |
| 12 | 12/06/2020 | CA2542 | 10 | 2343,80 € |
| 13 | 14/08/2020 | CA2535 | 12 | 545,76 € |
| 14 | 21/08/2020 | HP8053 | 2 | 1146,00 € |
| 15 | 05/10/2020 | CA2704 | 1 | 64,90 € |
| 16 | 26/10/2020 | HP8143 | 3 | 285,00 € |
| 17 | 23/11/2020 | CA2950 | 5 | 849,50 € |
| 18 | 07/12/2020 | CA2535 | 8 | 363,84 € |
| 19 | 21/12/2020 | CA2950 | 6 | 1019,40 € |
8.- Crea un procediment «mostrar_estadistiques» que generi la següent informació, considerant que la major venda es refereix a les vendes amb major valor econòmic, no amb major nombre d'unitats venudes:
- Valor total de les vendes.
- Valor total de les vendes en 2019.
- Valor total de les vendes en 2020.
- Llistat ordenat de les vendes per dia de la setmana.
- Llistat ordenat de les vendes per mes de l'any.
- Llistat ordenat de les vendes per trimestre.
UTILITZA UN CURSOR QUE RECOLLÍ TOTES LES DADES DE LA TAULA VENDA I LES MATRIUS ASSOCIATIVES QUE NECESSITIS PER ANAR ACUMULANT EL TOTAL DE VENDES PER DIA DE LA SETMANA, MES I TRIMESTRE.
Ajuda: A continuació es mostra un exemple de la sortida produïda per el procediment creat:
EXECUTE mostrar_estadistiques
Estadístiques
---------------------- Totals --------------------------- Vendes Totals: 13122,32 € En 2019: 5836,12 € En 2020: 7286,20 €
Llistats
Valor de les vendes per dies (els resultats no són correctes)------------
Dilluns: 5288,16 € Dimarts: 2558,04 € Dimecres: 703,14 € Dijous: 2778,98 € Divendres: 1794,00 €
Valor de les vendes per mesos-----------
Gener: 650,68 € Febrer: 95,00 € Març: 0,00 € Abril: 0,00 € Maig: 1786,90 € Juny: 2343,80 € Juliol: 679,60 € Agost: 2451,76 € Setembre: 129,80 € Octubre: 349,90 € Novembre: 849,50 € Desembre: 3785,38 €
Valor de les vendes per trimestres------
T1: 745,68 € T2: 4130,70 € T3: 3261,16 € T4. 4984,78 €
