ygfpCJjh

1# SINF
2
3## Database creation script
4
5```SQL
6CREATE DATABASE IF NOT EXISTS warehouse; 
7
8USE warehouse; 
9
10CREATE TABLE utilizador 
11  ( 
12     id       INT NOT NULL PRIMARY KEY auto_increment, 
13     email    VARCHAR(255) NOT NULL, 
14     password VARCHAR(255) NOT NULL 
15  ); 
16
17CREATE TABLE gestor 
18  ( 
19     id_utilizador INT NOT NULL PRIMARY KEY, 
20     id_gestor     VARCHAR(5) NOT NULL, 
21     FOREIGN KEY(id_utilizador) REFERENCES utilizador(id) 
22  ); 
23
24CREATE TABLE funcionario 
25  ( 
26     id_utilizador  INT NOT NULL PRIMARY KEY, 
27     id_funcionario VARCHAR(5) NOT NULL, 
28     FOREIGN KEY(id_utilizador) REFERENCES utilizador(id) 
29  ); 
30
31CREATE TABLE picking_wave 
32  ( 
33     id           INT NOT NULL PRIMARY KEY auto_increment, 
34     data_criacao DATE NOT NULL 
35  ); 
36
37CREATE TABLE picking_order 
38  ( 
39     id              INT NOT NULL PRIMARY KEY auto_increment, 
40     id_funcionario  INT NOT NULL, 
41     id_picking_wave INT NOT NULL, 
42     reorganizacao   BIT DEFAULT 0, 
43     finished        BIT DEFAULT 0, 
44     FOREIGN KEY(id_funcionario) REFERENCES funcionario(id_utilizador), 
45     FOREIGN KEY(id_picking_wave) REFERENCES picking_wave(id) 
46  ); 
47
48CREATE TABLE picking_order_item 
49  ( 
50     id                     INT NOT NULL PRIMARY KEY auto_increment, 
51     id_picking_order       INT NOT NULL, 
52     id_localizacao_destino VARCHAR(5) NOT NULL, 
53     id_item                VARCHAR(5) NOT NULL, 
54     quantidade_pedida      INT NOT NULL, 
55     quantidade_recebida    INT DEFAULT 0, 
56     FOREIGN KEY(id_picking_order) REFERENCES picking_order(id) 
57  );
58```
59
60## Triggers
61
62### Default Date
63
64Since mySQL only supports constants as default values, we need to use triggers to set a default date. This trigger does that.
65
66```SQL
67DELIMITER $$
68CREATE TRIGGER default_date BEFORE INSERT ON picking_wave FOR EACH ROW
69IF ( ISNULL(NEW.data_criacao) ) then
70 SET NEW.data_criacao=CURDATE();
71END IF;
72$$
73DELIMITER ;
74```
75
76## Database population script
77
78To be done!
79
80## Queries
81
82To be done!