Empresa bàsica: compra de clients i compra a proveïdors

Introducció

Simularem una botiga bàsica de bicicletes, simplificant-la al màxim. Què fa una botiga de bicicletes?

• Compra bicicletes als seus proveïdors (els diferents fabricants de bicicletes)
• Ven bicicletes als seus clients
• Porta un control del stock/magatzem

Gestionarem tot aquest flux d'informació amb una petita base de dades. Primer, les sentències SQL que gestionen tot aquest funcionament; segon, ho implmentarem mitjançant procediments.

NOTA: quan haguem vist els triggers/disparadors, ho farem amb triggers (de moment encara no podem fer-ho).

Base de dades

Estructura de la base de dades:

*
PROVEIDOR (id_proveidor, proveidor)
CLIENT (id_client, client)
PRODUCTE (id_producte, producte, preu, stock, id_proveidor)
COMPRA_CLIENT (id_client, id_producte, quantitat, data)
COMPRA_PROVEIDOR (id_proveidor, id_producte, quantitat, data)
*/

CREATE DATABASE empresa2 CHARACTER SET utf8 COLLATE utf8_general_ci;
use empresa2;

DROP TABLE IF EXISTS COMPRA_PROVEIDOR;
DROP TABLE IF EXISTS COMPRA_CLIENT;
DROP TABLE IF EXISTS PRODUCTE;
DROP TABLE IF EXISTS CLIENT;
DROP TABLE IF EXISTS PROVEIDOR;

CREATE TABLE PROVEIDOR (
id_proveidor smallint PRIMARY KEY,
proveidor  VARCHAR(50) NOT NULL
);

CREATE TABLE CLIENT (
id_client smallint AUTO_INCREMENT PRIMARY KEY,
client  VARCHAR(50) NOT NULL
);

CREATE TABLE PRODUCTE (
id_producte smallint PRIMARY KEY,
producte VARCHAR(50) NOT NULL,
preu DECIMAL(6,2) NOT NULL,
stock SMALLINT NULL,
id_proveidor smallint NOT NULL,
FOREIGN KEY (id_proveidor) REFERENCES PROVEIDOR(id_proveidor)
);

CREATE TABLE COMPRA_CLIENT (
id_client smallint,
id_producte smallint,
quantitat smallint,
data datetime,
FOREIGN KEY (id_client) REFERENCES CLIENT(id_client),
FOREIGN KEY (id_producte) REFERENCES PRODUCTE(id_producte)
);

CREATE TABLE COMPRA_PROVEIDOR (
id_proveidor smallint,
id_producte smallint,
quantitat smallint,
data datetime,
FOREIGN KEY (id_proveidor) REFERENCES PROVEIDOR(id_proveidor),
FOREIGN KEY (id_producte) REFERENCES PRODUCTE(id_producte)
);

Creem les dades estàtiques (els productes i els proveïdors):

INSERT INTO PROVEIDOR VALUES (1,'Conor');
INSERT INTO PROVEIDOR VALUES (2,'Merida');
INSERT INTO PROVEIDOR VALUES (3,'BH');
INSERT INTO PROVEIDOR VALUES (4,'Deporvillage');

# inicialment tenim un stock de 3 unitats de tots els productes
INSERT INTO PRODUCTE VALUES (1,'Bicicleta MTB Deporvillage GR900',659.00,3,4);
INSERT INTO PRODUCTE VALUES (2,'Bicicleta MTB Deporvillage PR500',559.0,3,4);
INSERT INTO PRODUCTE VALUES (3,'Bicicleta MTB Conor - WRC 6700',433.45,3,1);
INSERT INTO PRODUCTE VALUES (4,'Bicicleta MTB Merida Big Nine 60',688.95,3,2);
INSERT INTO PRODUCTE VALUES (5,'Bicicleta MTB Deporvillage SL100 Lady',479.00,3,4);
INSERT INTO PRODUCTE VALUES (6,'Bicicleta MTB Focus Jam 6.9',2896.40,3,2);
INSERT INTO PRODUCTE VALUES (7,'Bicicleta MTB Focus Thron 6.8',2160.65,3,2);
INSERT INTO PRODUCTE VALUES (8,'Bicicleta MTB Haibike Seet 10',799.00,3,1);
INSERT INTO PRODUCTE VALUES (9,'Bicicleta MTB Haibike Seet 9',674.00,3,1);
INSERT INTO PRODUCTE VALUES (10,'Bicicleta MTB Deporvillage GR900',659.00,3,4);
INSERT INTO PRODUCTE VALUES (11,'Bicicleta MTB Focus Whistler 3.6',649.00,3,1);
INSERT INTO PRODUCTE VALUES (12,'Bicicleta MTB Kross Hexagon 6.0',499.95,3,3);
INSERT INTO PRODUCTE VALUES (13,'Bicicleta MTB Kross Hexagon 5.0',449.95,3,3);
INSERT INTO PRODUCTE VALUES (14,'Bicicleta MTB Kross Hexagon 3.0',453.00,3,3);
INSERT INTO PRODUCTE VALUES (15,'Bicicleta MTB BH Spike 2.0',569.90,3,3);

Creem les dades dinàmiques, les dades que es van generant amb el temps: els clients, les compres dels clients, i les compres als proveïdors:

# compra del client 1
INSERT INTO CLIENT(client) VALUES ('Amy Woodcock');
INSERT INTO COMPRA_CLIENT VALUES (1,1,1,NOW());
UPDATE PRODUCTE set stock=stock-1 WHERE id_producte=1; # ara en queden dos


# compra del client 2
INSERT INTO CLIENT(client) VALUES ('Paolo Randall');
INSERT INTO COMPRA_CLIENT VALUES (2,1,1,NOW());
UPDATE PRODUCTE set stock=stock-1 WHERE id_producte=1; # ara en queden un

# compra del client 3
INSERT INTO CLIENT(client) VALUES ('Israel Mcmillan');
INSERT INTO COMPRA_CLIENT VALUES (3,2,2,NOW());
UPDATE PRODUCTE set stock=stock-2 WHERE id_producte=2; # ara en queden un (n'ha comprat dos)

# compra del client 4, i compra al proveïdor 4
INSERT INTO CLIENT(client) VALUES ('Carlie Webster');
INSERT INTO COMPRA_CLIENT VALUES (4,1,1,NOW());
UPDATE PRODUCTE set stock=stock-1 WHERE id_producte=1; # ara no en queda cap. Hem de comprar aquest model de bici al proveïdor
SELECT * FROM PRODUCTE WHERE id_producte=1; # no en queda cap
INSERT INTO COMPRA_PROVEIDOR VALUES (4,1,3,NOW()); # fem la compra al proveïdor
UPDATE PRODUCTE set stock=stock+3 WHERE id_producte=1;
SELECT * FROM PRODUCTE WHERE id_producte=1; # n'hi torna a haver tres unitats

Programació

Tot aquest procés de compra de bicicletes per part dels clients, i compra de bicicletes de la botiga als proveïdors, que hem fet manualment, és el que s'ha d'implementar amb programació, mitjançant PROCEDURES. Concretament, hem d'implementar les següent regles, que anomenem regles del negoci ([1]):

• S'ha de comprovar que el id_producte existeix.
• Quan quedin 0 o 1 productes en el stock s'ha de fer la compra de 3 unitats al proveïdor.
• S'ha de comprovar que la quantitat > 0, i no es pot fer la compra de x unitats si el número d'unitats que en queden és inferior.
• hauríem de comprovar que el client ja existeix a la base de dades. Si no existeix, s'ha de fer un insert a la taula CLIENT.

Programarem el proceciment compraBicileta amb diferents versions incrementals, separant el problema en problemes petits.

Versió 3

---

creat per Joan Quintana Compte, abril 2022