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

De wikijoan
Salta a la navegació Salta a la cerca

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

Bicicleta conor.png

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:

  • Quan quedin 0 productes en el stock s'ha de fer la compra de 3 unitats al proveïdor.
  • 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ó 1

Versió 2

Versió 3

creat per Joan Quintana Compte, abril 2022

Obtingut de «http://wiki.joanillo.org/index.php?title=Empresa_bàsica:_compra_de_clients_i_compra_a_proveïdors&oldid=298923»