Part A · Conceptes (2,5 punts)

De l'Excel a la base de dades0,5 punts

Una empresa guarda els clients en tres fulls d'Excel, un per departament. Quins tres problemes dels fitxers plans patirà? Com els resol una base de dades relacional?

Solució

Problemes (DIM):

  • Duplicació: el mateix client és a tres fulls.
  • Inconsistència: si un departament canvia l'adreça i els altres no, hi ha dues adreces.
  • Manteniment difícil: cada canvi s'ha de repetir a mà.

Base de dades: guarda cada dada un sol cop, controla la integritat i hi pot treballar tothom alhora.

Criteri: 0,1 per problema · 0,2 la solució.

On ho guardaries?0,75 punts

Indica on guardaries cada dada (base de dades operativa, data warehouse o data lake) i si és estructurada o no:

  1. Les comandes obertes d'avui.
  2. Les vendes per mes i botiga dels últims sis anys, netes, per fer informes.
  3. Els vídeos que pugen els clients amb les seves valoracions.
Solució
  1. Base de dades operativa · estructurada.
  2. Data warehouse · estructurada i neta, per analitzar.
  3. Data lake (o NoSQL) · no estructurada, en brut.

Criteri: 0,25 per cas.

Rèplica o còpia de seguretat0,75 punts

Una botiga té la base de dades copiada en dos servidors (rèplica). Un treballador executa per error DELETE FROM Pedidos;.

  1. Què passa amb la rèplica?
  2. Com es poden recuperar les comandes?
  3. Quin error de SQL ha comès?
Solució
  1. La rèplica copia també l'error: les comandes desapareixen als dos servidors. La rèplica serveix per a la disponibilitat, no per recuperar.
  2. Amb una còpia de seguretat (backup) feta abans de l'error.
  3. Un DELETE sense WHERE: esborra totes les files. No dona cap error.

Criteri: 0,25 per apartat.

DDL o DML0,5 punts

Classifica com a DDL o DML: CREATE TABLE, INSERT, ALTER TABLE, SELECT, DROP TABLE, UPDATE. Després explica la diferència entre DELETE FROM Clientes i DROP TABLE Clientes.

Solució
  • DDL (estructura): CREATE TABLE, ALTER TABLE, DROP TABLE.
  • DML (dades): INSERT, SELECT, UPDATE.

DELETE FROM Clientes esborra les files: la taula queda buida. DROP TABLE Clientes esborra la taula sencera, també l'estructura.

Criteri: 0,05 per ordre · 0,2 la diferència.

Part B · Consultes (3 punts)

Cerques amb LIKE1,5 punts

Taula Clientes (ClienteID, Nombre, Ciudad, Email). Escriu el WHERE per trobar:

  1. Els noms que comencen per «A».
  2. Els correus de l'empresa «empresa.cat».
  3. Les ciutats que contenen «Sant».
  4. Els noms que tenen una «o» com a segona lletra.
  5. Els clients amb correu que no és de Gmail.

Després explica què fa malament aquesta consulta: SELECT * FROM Clientes WHERE Nombre = 'A%';

Solució
  1. WHERE Nombre LIKE 'A%'
  2. WHERE Email LIKE '%@empresa.cat'
  3. WHERE Ciudad LIKE '%Sant%'
  4. WHERE Nombre LIKE '_o%'
  5. WHERE Email NOT LIKE '%@gmail.com'

Error: amb = el % no és un comodí. Busca exactament el text «A%» i no troba res. Cal LIKE 'A%'.

Criteri: 0,24 per patró · 0,3 per l'explicació de l'error.

JOIN i recomptes1,5 punts

Taules:

Pedidos        (PedidoID, Fecha, ClienteID)
Detalle_pedido (PedidoID, ProductoID, Cantidad)
Productos      (ProductoID, Nombre, Precio)
  1. Escriu una consulta que mostri, per a cada comanda, el nom de cada producte i la quantitat.
  2. La comanda 1001 té 3 productes i la 1002 en té 2. Quantes files dona la consulta anterior? Què retornen COUNT amb asterisc i COUNT(DISTINCT p.PedidoID) sobre aquesta unió?
  3. Una comanda sense cap línia de detall, surt al resultat? Per què?
Solució
  1. Consulta:
SELECT p.PedidoID, pr.Nombre, d.Cantidad
FROM Pedidos p
INNER JOIN Detalle_pedido d ON d.PedidoID = p.PedidoID
INNER JOIN Productos pr ON pr.ProductoID = d.ProductoID;
  1. 5 files (una per línia). COUNT amb asterisc = 5. COUNT(DISTINCT p.PedidoID) = 2 comandes.
  2. No. L'INNER JOIN només retorna les files que coincideixen a les dues taules.

Criteri: 0,8 consulta (0,4 per JOIN) · 0,4 recomptes · 0,3 INNER JOIN.

Part C · Modelatge (2,5 punts)

Modela una clínica2,5 punts

Una clínica guarda els pacients (número d'història, nom, telèfon), els metges (número de col·legiat, nom, especialitat) i les visites (identificador, data, hora i diagnòstic). Cada visita és d'un sol pacient i amb un sol metge. També guarda els medicaments (codi, nom) que es receten a cada visita, amb la dosi. Una visita pot tenir diversos medicaments i un medicament es recepta a moltes visites.

  1. Entitats i identificadors.
  2. Relacions, cardinalitats i on va la dosi.
  3. Model lògic.
  4. Tipus de dades per a: telèfon, data, hora, dosi i número de col·legiat.
Solució
  1. PACIENT (NumHistoria) · METGE (NumColegiat) · VISITA (VisitaID) · MEDICAMENT (Codi).
  2. PACIENT 1:N VISITA · METGE 1:N VISITA · VISITA N:M MEDICAMENT (recepta). La dosi depèn de la parella visita–medicament: va a la taula intermèdia.
  3. Model lògic:
PACIENTSNumHistoriaPKNomTelefon
METGESNumColegiatPKNomEspecialitat
VISITESVisitaIDPKDataHoraDiagnosticNumHistoriaFK→ PACIENTSNumColegiatFK→ METGES
MEDICAMENTSCodiPKNom
RECEPTESVisitaIDPK, FK→ VISITESCodiPK, FK→ MEDICAMENTSDosi
  1. Telèfon VARCHAR(15) (pot tenir + i zeros) · data DATE · hora TIME · dosi VARCHAR(50) (p. ex. «500 mg cada 8 h») · col·legiat VARCHAR(12) o CHAR(n) (és un codi, no es fan càlculs).

Criteri: 0,4 entitats · 0,6 relacions · 1 model lògic (dues FK a VISITES 0,4 · taula intermèdia 0,6) · 0,5 tipus.

Part D · Normalització (2 punts)

Normalitza una taula de comandes2 punts
PedidoIDFechaClienteIDNomClientProductoIDNomProductePreuQuantitat
50103/037Anna10Ratolí202
50103/037Anna11Teclat351
50205/038Joan10Ratolí201

Un producte surt un sol cop per comanda.

  1. Clau primària i dependències funcionals.
  2. Resultat en 3FN, indicant quina dependència resol cada separació.
  3. Si el preu del ratolí puja a 25 €, què passa amb les comandes antigues? Com ho resols?
Solució
  1. Clau: (PedidoID, ProductoID). PedidoID → Fecha, ClienteID · ClienteID → NomClient · ProductoID → NomProducte, Preu · (PedidoID, ProductoID) → Quantitat.
  2. 2FN (parcials): separa PEDIDOS (PedidoID → Fecha, ClienteID, NomClient) i PRODUCTOS (ProductoID → NomProducte, Preu). 3FN (transitiva PedidoID → ClienteID → NomClient): separa CLIENTES.
CLIENTESClienteIDPKNomClient
PEDIDOSPedidoIDPKFechaClienteIDFK→ CLIENTES
PRODUCTOSProductoIDPKNomProductePreu
DETALLEPedidoIDPK, FK→ PEDIDOSProductoIDPK, FK→ PRODUCTOSQuantitat
  1. Si només hi ha el preu a PRODUCTOS, les comandes antigues semblarien de 25 €. Solució: afegir PreuVenda a DETALLE. El preu de catàleg i el preu de venda són dos fets diferents.

Criteri: 0,6 clau i dependències · 1 descomposició · 0,4 preu històric.