· 9 years ago · Dec 07, 2016, 03:16 PM
1-- MySQL Script generated by MySQL Workbench
2-- 12/01/16 17:27:43
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema ejercicio3b
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema ejercicio3b
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `ejercicio3b` DEFAULT CHARACTER SET utf8 ;
18USE `ejercicio3b` ;
19
20-- -----------------------------------------------------
21-- Table `ejercicio3b`.`Oficina`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `ejercicio3b`.`Oficina` (
24 `codiofi` SMALLINT(2) NOT NULL,
25 `ciutat` VARCHAR(15) NOT NULL,
26 `regio` VARCHAR(8) NOT NULL,
27 `codirep` CHAR(3) NOT NULL,
28 `objectiu` FLOAT(8,2) NULL,
29 `vendes` FLOAT(8,2) NULL,
30 PRIMARY KEY (`codiofi`))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `ejercicio3b`.`Representant`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `ejercicio3b`.`Representant` (
38 `codirep` CHAR(3) NOT NULL,
39 `nom` VARCHAR(20) NOT NULL,
40 `edat` SMALLINT(2) NULL,
41 `codiofi` SMALLINT(2) NULL,
42 `categoria` VARCHAR(12) NOT NULL,
43 `contracte` DATE NOT NULL,
44 `codicap` CHAR(3) NULL,
45 `quota` FLOAT(8,2) NULL,
46 `vendes` FLOAT(8,2) NULL,
47 PRIMARY KEY (`codirep`))
48ENGINE = InnoDB;
49
50
51-- -----------------------------------------------------
52-- Table `ejercicio3b`.`comanda`
53-- -----------------------------------------------------
54CREATE TABLE IF NOT EXISTS `ejercicio3b`.`comanda` (
55 `codicom` INT(6) NOT NULL,
56 `data` DATE NOT NULL,
57 `codicli` CHAR(4) NOT NULL,
58 `codirep` CHAR(3) NOT NULL,
59 `codifab` CHAR(3) NOT NULL,
60 `codiprod` CHAR(5) NOT NULL,
61 `quantitat` SMALLINT(3) NOT NULL,
62 `import` FLOAT(7,2) NOT NULL,
63 PRIMARY KEY (`codicom`))
64ENGINE = InnoDB;
65
66
67-- -----------------------------------------------------
68-- Table `ejercicio3b`.`Client`
69-- -----------------------------------------------------
70CREATE TABLE IF NOT EXISTS `ejercicio3b`.`Client` (
71 `codicli` CHAR(4) NOT NULL,
72 `nom` VARCHAR(20) NOT NULL,
73 `codirep` CHAR(3) NULL,
74 `limit_credit` FLOAT(7,2) NULL,
75 PRIMARY KEY (`codicli`))
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `ejercicio3b`.`Producte`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `ejercicio3b`.`Producte` (
83 `codifab` CHAR(3) NOT NULL,
84 `codiprod` CHAR(5) NOT NULL,
85 `descripcio` VARCHAR(25) NOT NULL,
86 `preu` FLOAT(6,2) NOT NULL,
87 `existencies` SMALLINT(4) NULL,
88 PRIMARY KEY (`codifab`,`codiprod`))
89ENGINE = InnoDB;
90
91
92
93
94
95-- -----------------------------------------------------
96-- Datos `ejercicio3b`
97-- -----------------------------------------------------
98
99-- -----------------------------------------------------
100-- Datos `ejercicio3b`.`Oficina`
101-- -----------------------------------------------------
102
103insert into oficina (codiofi,ciutat,regio,codirep,objectiu,vendes) values
104( '22' , 'Denver' , 'Oest' , '108' , 3000.00 , 1860.42 ),
105( '11' , 'Nova York' , 'Est' , '106' , 5750.00 , 6926.37 ),
106( '12' , 'Xicago' , 'Est' , '104' , 7750.00 , 7350.42 ),
107( '13' , 'Atlanta' , 'Est' , '105' , 3500.00 , 3679.11 ),
108( '21' , 'Los Angeles' , 'Oest' , '108' , 7000.00 , 8359.15 );
109
110
111-- -----------------------------------------------------
112-- Datos `ejercicio3b`.`Representant`
113-- -----------------------------------------------------
114
115insert into representant ( codirep , nom , edat , codiofi , categoria , contracte , codicap , quota , vendes ) values
116
117( '106' , 'Sam Clark' , 52 , 11 , 'VP Vendes' , '1998-06-14' , '' , 2750.00 , 2999.12 ),
118( '104' , 'Bob Smith' , 33 , 12 , 'Dir Vendes' , '1997-05-19' , '106' , 2000.00 , 1425.94 ),
119( '108' , 'Larry Fitch' , 62 , 21 , 'Dir Vendes' , '1999-10-12' , '106' , 3500.00 , 3618.65 ),
120( '105' , 'Bill Adams' , 37 , 13 , 'Rep Vendes' , '1998-02-12' , '104' , 3500.00 , 3679.11 ),
121( '109' , 'Mary Jones' , 31 , 11 , 'Rep Vendes' , '1999-10-12' , '106' , 3000.00 , 3927.25 ),
122( '102' , 'Sue Smith' , 48 , 21 , 'Rep Vendes' , '1996-12-10' , '108' , 3500.00 , 4740.50 ),
123( '101' , 'Dan Roberts' , 45 , 12 , 'Rep Vendes' , '1996-10-20' , '104' , 3000.00 , 3056.73 ),
124( '110' , 'Tom Snyder' , 41 , 12 , 'Rep Vendes' , '2000-01-13' , '101' , NULL , 759.85 ),
125( '103' , 'Paul Cruz' , 29 , 12 , 'Rep Vendes' , '1997-03-01' , '104' , 2750.00 , 2867.75 ),
126( '107' , 'Nancy Angelli' , 49 , 22 , 'Rep Vendes' , '1998-11-14' , '108' , 3000.00 , 1860.42 );
127
128-- -----------------------------------------------------
129-- Datos `ejercicio3b`.`comanda`
130-- -----------------------------------------------------
131
132
133insert into comanda (codicom, data, codicli, codirep, codifab, codiprod, quantitat, import) values
134
135
136(112961 , '1999-12-17' , '2117' , '106' , 'REI' , '2A44L' , 7 , 315.00),
137(113012 , '2000-01-11' , '2111' , '105' , 'ACI' , '41003' , 35 , 37.45),
138(112989 , '2000-01-03' , '2101' , '106' , 'FEA' , '114' , 6 , 14.58),
139(113051 , '2000-02-10' , '2118' , '108' , 'QSA' , 'XK47' , 4 , 14.20),
140(112968 , '1999-10-12' , '2102' , '101' , 'ACI' , '41004' , 34 , 39.78),
141(110036 , '2000-01-30' , '2107' , '110' , 'ACI' , '4100Z' , 9 , 225.00),
142(113045 , '2000-02-02' , '2112' , '108' , 'REI' , '2A44R' , 10 , 450.00),
143(112963 , '1999-12-17' , '2103' , '105' , 'ACI' , '41004' , 28 , 32.76),
144(113013 , '2000-01-14' , '2118' , '108' , 'BIC' , '41003' , 1 , 6.52),
145(113058 , '2000-02-23' , '2108' , '109' , 'FEA' , '112' , 10 , 14.80),
146(112997 , '2000-01-08' , '2124' , '107' , 'BIC' , '41003' , 1 , 6.52),
147(112983 , '1999-12-27' , '2103' , '105' , 'ACI' , '41004' , 6 , 7.02),
148(113024 , '2000-01-20' , '2114' , '108' , 'QSA' , 'XK47' , 20 , 71.00),
149(113062 , '2000-02-24' , '2124' , '107' , 'FEA' , '114' , 10 , 24.30),
150(112979 , '1999-10-12' , '2114' , '102' , 'ACI' , '4100Z' , 6 , 150.00),
151(113027 , '2000-01-22' , '2103' , '105' , 'ACI' , '41002' , 54 , 41.04),
152(113007 , '2000-01-08' , '2112' , '108' , 'IMM' , '773C' , 3 , 29.25),
153(113069 , '2000-03-02' , '2109' , '107' , 'IMM' , '775C' , 22 , 313.50),
154(113034 , '2000-01-29' , '2107' , '110' , 'REI' , '2A45C' , 8 , 6.32),
155(112992 , '1999-11-04' , '2118' , '108' , 'ACI' , '41002' , 10 , 7.60),
156(112975 , '1999-10-12' , '2111' , '103' , 'REI' , '2A44G' , 6 , 21.00),
157(113055 , '2000-02-15' , '2108' , '101' , 'ACI' , '4100X' , 6 , 1.50),
158(113048 , '2000-02-10' , '2120' , '102' , 'IMM' , '779C' , 2 , 37.50),
159(112993 , '1999-01-04' , '2106' , '102' , 'REI' , '2A45C' , 24 , 18.96),
160(113065 , '2000-02-27' , '2106' , '102' , 'QSA' , 'XK47' , 6 , 21.30),
161(113003 , '2000-01-25' , '2108' , '109' , 'IMM' , '779C' , 3 , 56.25),
162(113049 , '2000-02-10' , '2118' , '108' , 'QSA' , 'XK47' , 2 , 7.10),
163(112987 , '1999-12-31' , '2103' , '105' , 'ACI' , '4100Z' , 11 , 275.00),
164(113057 , '2000-02-18' , '2111' , '103' , 'ACI' , '4100X' , 24 , 6.00),
165(113042 , '2000-02-02' , '2113' , '101' , 'REI' , '2A44R' , 5 , 225.00);
166
167
168-- -----------------------------------------------------
169-- Datos `ejercicio3b`.`Client`
170-- -----------------------------------------------------
171
172insert into client (codicli, nom, codirep, limit_credit) values
173
174
175('2111' , 'JCP Inc.' , '103' , 500.00),
176('2102' , 'Firsts Corp.' , '101' , 650.00),
177('2103' , 'Acme Mfg.' , '105' , 500.00),
178('2123' , 'Carter & Sons' , '102' , 400.00),
179('2107' , 'Ace International' , '110' , 350.00),
180('2115' , 'Smithson Corp' , '101' , 200.00),
181('2101' , 'Jones Mfg.' , '106' , 650.00),
182('2112' , 'Zetacorp' , '108' , 500.00),
183('2121' , 'QMA Assoc.' , '103' , 450.00),
184('2114' , 'Orion Corp.' , '102' , 200.00),
185('2124' , 'Peter Brothers' , '107' , 400.00),
186('2108' , 'Holm & Landis' , '109' , 550.00),
187('2117' , 'J.P. Sinclair' , '106' , 350.00),
188('2122' , 'Three-Way Lines' , '105' , 300.00),
189('2120' , 'Rico Enterprises' , '102' , 500.00),
190('2106' , 'Fred Lewis Corp.' , '102' , 650.00),
191('2119' , 'Solomon Inc.' , '109' , 250.00),
192('2118' , 'Midwest Systems' , '108' , 600.00),
193('2113' , 'Ian & Schimdt' , '104' , 200.00),
194('2109' , 'Chen Associates' , '103' , 250.00),
195('2105' , 'AAA Investments' , '101' , 450.00);
196
197-- -----------------------------------------------------
198-- Table `ejercicio3b`.`Producte`
199-- -----------------------------------------------------
200
201
202insert into producte ( codifab , codiprod , descripcio , preu , existencies) values
203
204( 'REI' , '2A45C' , 'V Stago Cadell' , 0.79 , 210 ),
205( 'ACI' , '4100I' , 'Extractor' , 27.50 , 25 ),
206( 'QSA' , 'XK47' , 'Reductor' , 3.55 , 38 ),
207( 'BIC' , '41672' , 'LÃ mina' , 1.80 , NULL ),
208( 'IMM' , '779C' , 'Tirant 2-Tm' , 18.75 , 9 ),
209( 'ACI' , '41003' , 'Article Tipus 3' , 1.07 , 207 ),
210( 'ACI' , '41004' , 'Article Tipus 4' , 1.17 , 139 ),
211( 'BIC' , '41003' , 'Manovella' , 6.52 , 3 ),
212( 'IMM' , '887P' , 'Pern Tirant' , 2.50 , 24 ),
213( 'QSA' , 'XK48' , 'Reductor' , 1.34 , 203 ),
214( 'REI' , '2A44L' , 'Frontissa Esq.' , 45.00 , 12 ),
215( 'FEA' , '112' , 'Coberta' , 1.48 , 115 ),
216( 'IMM' , '887H' , 'Suport Tirant' , 0.54 , 223 ),
217( 'BIC' , '41089' , 'Retn' , 2.25 , 78 ),
218( 'ACI' , '41001' , 'Article Tipus 1' , 0.55 , 277 ),
219( 'IMM' , '775C' , 'Tirant 1-Tm' , 14.25 , 5 ),
220( 'ACI' , '4100Z' , 'Muntador' , 25.00 , 28 ),
221( 'QSA' , 'XK48A' , 'Reductor' , 1.17 , 37 ),
222( 'ACI' , '41002' , 'Article Tipus 2' , 0.76 , 167 ),
223( 'REI' , '2A44R' , 'Frontissa Dreta' , 45.00 , 12 ),
224( 'IMM' , '773C' , 'Tirant 1/2-Tm' , 9.75 , 28 ),
225( 'ACI' , '4100X' , 'Ajustador' , 0.25 , 37 ),
226( 'FEA' , '114' , 'Bancada Motor' , 2.43 , 15 ),
227( 'IMM' , '887X' , 'Retenidor Tirant' , 4.75 , 32 ),
228( 'REI' , '2A44G' , 'Passador Frontissa' , 3.50 , 14 );
229
230-- -----------------------------------------------------
231-- Claves foraneas
232-- -----------------------------------------------------
233
234alter table client add(constraint foreign key(codirep) references ejercicio3b.representant (codirep));
235alter table oficina add (constraint foreign key(codirep) references ejercicio3b.representant(codirep));
236alter table representant add(constraint foreign key(codiofi) references ejercicio3b.oficina (codiofi),constraint foreign key(codicap) references ejercicio3b.representant (codirep));
237alter table comanda add(constraint foreign key(codirep) references ejercicio3b.representant (codirep));
238alter table comanda add (constraint foreign key (codicli) references ejercicio3b.client (codicli));
239alter table comanda add (constraint foreign key(codifab,codiprod) references ejercicio3b.producte (codifab,codiprod));
240
241SET SQL_MODE=@OLD_SQL_MODE;
242SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
243SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;