Examen B
Arquitectura, LIKE i JOIN, modelatge i normalització (T1–T4)
Part A · Conceptes (2,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?
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ó.
Indica on guardaries cada dada (base de dades operativa, data warehouse o data lake) i si és estructurada o no:
- Les comandes obertes d'avui.
- Les vendes per mes i botiga dels últims sis anys, netes, per fer informes.
- Els vídeos que pugen els clients amb les seves valoracions.
- Base de dades operativa · estructurada.
- Data warehouse · estructurada i neta, per analitzar.
- Data lake (o NoSQL) · no estructurada, en brut.
Criteri: 0,25 per cas.
Una botiga té la base de dades copiada en dos servidors (rèplica). Un treballador executa per error DELETE FROM Pedidos;.
- Què passa amb la rèplica?
- Com es poden recuperar les comandes?
- Quin error de SQL ha comès?
- La rèplica copia també l'error: les comandes desapareixen als dos servidors. La rèplica serveix per a la disponibilitat, no per recuperar.
- Amb una còpia de seguretat (backup) feta abans de l'error.
- Un DELETE sense WHERE: esborra totes les files. No dona cap error.
Criteri: 0,25 per apartat.
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.
- 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)
Taula Clientes (ClienteID, Nombre, Ciudad, Email). Escriu el WHERE per trobar:
- Els noms que comencen per «A».
- Els correus de l'empresa «empresa.cat».
- Les ciutats que contenen «Sant».
- Els noms que tenen una «o» com a segona lletra.
- Els clients amb correu que no és de Gmail.
Després explica què fa malament aquesta consulta: SELECT * FROM Clientes WHERE Nombre = 'A%';
WHERE Nombre LIKE 'A%'WHERE Email LIKE '%@empresa.cat'WHERE Ciudad LIKE '%Sant%'WHERE Nombre LIKE '_o%'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.
Taules:
Pedidos (PedidoID, Fecha, ClienteID)
Detalle_pedido (PedidoID, ProductoID, Cantidad)
Productos (ProductoID, Nombre, Precio)- Escriu una consulta que mostri, per a cada comanda, el nom de cada producte i la quantitat.
- 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ó? - Una comanda sense cap línia de detall, surt al resultat? Per què?
- 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;- 5 files (una per línia). COUNT amb asterisc = 5. COUNT(DISTINCT p.PedidoID) = 2 comandes.
- 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)
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.
- Entitats i identificadors.
- Relacions, cardinalitats i on va la dosi.
- Model lògic.
- Tipus de dades per a: telèfon, data, hora, dosi i número de col·legiat.
- PACIENT (NumHistoria) · METGE (NumColegiat) · VISITA (VisitaID) · MEDICAMENT (Codi).
- 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.
- Model lògic:
- Telèfon
VARCHAR(15)(pot tenir + i zeros) · dataDATE· horaTIME· dosiVARCHAR(50)(p. ex. «500 mg cada 8 h») · col·legiatVARCHAR(12)oCHAR(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)
| PedidoID | Fecha | ClienteID | NomClient | ProductoID | NomProducte | Preu | Quantitat |
|---|---|---|---|---|---|---|---|
| 501 | 03/03 | 7 | Anna | 10 | Ratolí | 20 | 2 |
| 501 | 03/03 | 7 | Anna | 11 | Teclat | 35 | 1 |
| 502 | 05/03 | 8 | Joan | 10 | Ratolí | 20 | 1 |
Un producte surt un sol cop per comanda.
- Clau primària i dependències funcionals.
- Resultat en 3FN, indicant quina dependència resol cada separació.
- Si el preu del ratolí puja a 25 €, què passa amb les comandes antigues? Com ho resols?
- Clau: (PedidoID, ProductoID). PedidoID → Fecha, ClienteID · ClienteID → NomClient · ProductoID → NomProducte, Preu · (PedidoID, ProductoID) → Quantitat.
- 2FN (parcials): separa PEDIDOS (PedidoID → Fecha, ClienteID, NomClient) i PRODUCTOS (ProductoID → NomProducte, Preu). 3FN (transitiva PedidoID → ClienteID → NomClient): separa CLIENTES.
- 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.