|
1 -- use pis; |
|
2 drop table compres if exists; |
|
3 drop table excepcions if exists; |
|
4 drop table gent if exists; |
|
5 drop table compradors if exists; |
|
6 drop table compradors_excepcions if exists; |
|
7 |
|
8 CREATE TABLE compres ( |
|
9 data TIMESTAMP NOT NULL, |
|
10 botiga CHAR(30) NOT NULL, |
|
11 motiu TEXT, |
|
12 preu DECIMAL(18,10) UNSIGNED NOT NULL, |
|
13 comentari TEXT, |
|
14 |
|
15 PRIMARY KEY (data) |
|
16 ) TYPE = InnoDB; |
|
17 |
|
18 CREATE TABLE excepcions ( |
|
19 id MEDIUMINT NOT NULL AUTO_INCREMENT, |
|
20 data TIMESTAMP NOT NULL, |
|
21 article TINYTEXT, |
|
22 preu DECIMAL(18,10) UNSIGNED NOT NULL, |
|
23 |
|
24 KEY data (data), |
|
25 PRIMARY KEY (id), |
|
26 FOREIGN KEY (data) REFERENCES compres (data) |
|
27 ) TYPE = InnoDB; |
|
28 |
|
29 CREATE TABLE gent ( |
|
30 id MEDIUMINT NOT NULL AUTO_INCREMENT, |
|
31 nom VARCHAR(50) NOT NULL, |
|
32 habitant BOOL, |
|
33 |
|
34 PRIMARY KEY (id) |
|
35 ) TYPE = InnoDB; |
|
36 |
|
37 CREATE TABLE compradors ( |
|
38 id MEDIUMINT NOT NULL AUTO_INCREMENT, |
|
39 data TIMESTAMP NOT NULL, |
|
40 persona MEDIUMINT NOT NULL, |
|
41 pagat DECIMAL(18,10) UNSIGNED NOT NULL, |
|
42 toca_pagar DECIMAL(18,10) UNSIGNED NOT NULL, |
|
43 |
|
44 KEY data (data), |
|
45 KEY persona (persona), |
|
46 FOREIGN KEY (data) REFERENCES compres(data), |
|
47 FOREIGN KEY (persona) REFERENCES gent(id), |
|
48 PRIMARY KEY (id) |
|
49 ) TYPE = InnoDB; |
|
50 |
|
51 CREATE TABLE compradors_excepcions ( |
|
52 id MEDIUMINT NOT NULL AUTO_INCREMENT, |
|
53 ex_id MEDIUMINT NOT NULL, |
|
54 persona MEDIUMINT NOT NULL, |
|
55 toca_pagar DECIMAL(18,10) UNSIGNED NOT NULL, |
|
56 |
|
57 KEY ex_id (ex_id), |
|
58 KEY persona (persona), |
|
59 FOREIGN KEY (ex_id) REFERENCES excepcions (id), |
|
60 FOREIGN KEY (persona) REFERENCES gent (id), |
|
61 PRIMARY KEY (id) |
|
62 ) TYPE = InnoDB; |
|
63 |
|
64 CREATE TABLE pagaments ( |
|
65 data TIMESTAMP NOT NULL, |
|
66 comentari TEXT, |
|
67 |
|
68 PRIMARY KEY (data) |
|
69 ) TYPE = InnoDB; |
|
70 |
|
71 CREATE TABLE pagadors ( |
|
72 id MEDIUMINT NOT NULL AUTO_INCREMENT, |
|
73 data TIMESTAMP NOT NULL, |
|
74 persona MEDIUMINT NOT NULL, |
|
75 pagat DECIMAL(18,10) UNSIGNED NOT NULL, |
|
76 cobrat DECIMAL(18,10) UNSIGNED NOT NULL, |
|
77 |
|
78 KEY data (data), |
|
79 KEY persona (persona), |
|
80 FOREIGN KEY (data) REFERENCES pagaments(data), |
|
81 FOREIGN KEY (persona) REFERENCES gent(id), |
|
82 PRIMARY KEY (id) |
|
83 ) TYPE = InnoDB; |
|
84 |
|
85 -- INSERT INTO gent (nom,habitant) VALUES('Lluis', 1); |
|
86 -- INSERT INTO gent (nom,habitant) VALUES('Marc', 1); |
|
87 -- INSERT INTO gent (nom,habitant) VALUES('Natxo', 1); |
|
88 -- INSERT INTO gent (nom,habitant) VALUES('Borja', 1); |
|
89 -- INSERT INTO gent (nom,habitant) VALUES('Xumi', 0); |
|
90 -- INSERT INTO gent (nom,habitant) VALUES('Boira', 0); |
|
91 |
|
92 -- SET @date := NOW(); |
|
93 -- INSERT INTO compres (data,botiga,motiu,preu) VALUES(@date,'Esclat', |
|
94 -- 'Compra setmanal',2203); |
|
95 -- INSERT INTO excepcions (data,article,preu) VALUES(@date,'Bicicleta', 200); |
|
96 -- INSERT INTO excepcions (data,article,preu) VALUES(@date,'Patates', 3); |
|
97 |
|
98 -- INSERT INTO compradors (data,persona,pagat,toca_pagar) |
|
99 -- VALUES(@date,1,1203,500); |
|
100 -- INSERT INTO compradors (data,persona,pagat,toca_pagar) |
|
101 -- VALUES(@date,2,0,500); |
|
102 -- INSERT INTO compradors (data,persona,pagat,toca_pagar) |
|
103 -- VALUES(@date,3,500,500); |
|
104 -- INSERT INTO compradors (data,persona,pagat,toca_pagar) |
|
105 -- VALUES(@date,4,500,500); |
|
106 |
|
107 -- INSERT INTO compradors_excepcions (ex_id, persona, toca_pagar) |
|
108 -- VALUES(1,1,200); |
|
109 -- INSERT INTO compradors_excepcions (ex_id, persona, toca_pagar) |
|
110 -- VALUES(2,5,3); |