ZFrEBVKv

1CREATE TABLE IF NOT EXISTS `clientes` (
2  `numclie` int(11) NOT NULL,
3  `nombre` varchar(20) CHARACTER SET utf8 NOT NULL,
4  `repclie` int(11) DEFAULT NULL,
5  `limitecredito` int(11) NOT NULL,
6  PRIMARY KEY (`numclie`)
7) ENGINE=InnoDB DEFAULT CHARSET=latin1;
8
9CREATE TABLE IF NOT EXISTS `oficinas` (
10  `oficina` int(11) NOT NULL,
11  `ciudad` varchar(20) CHARACTER SET utf8 NOT NULL,
12  `region` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
13  `dir` int(11) DEFAULT NULL,
14  `objetivo` decimal(19,4) DEFAULT NULL,
15  `ventas` decimal(19,4) DEFAULT '0.0000',
16  PRIMARY KEY (`oficina`)
17) ENGINE=InnoDB DEFAULT CHARSET=latin1;
18
19CREATE TABLE IF NOT EXISTS `empleados` (
20  `numemp` int(11) NOT NULL,
21  `nombre` varchar(20) CHARACTER SET utf8 NOT NULL,
22  `edad` int(11) DEFAULT NULL,
23  `oficina` int(11) DEFAULT NULL,
24  `titulo` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
25  `contrato` datetime DEFAULT NULL,
26  `jefe` int(11) DEFAULT NULL,
27  `cuota` decimal(19,4) DEFAULT NULL,
28  `ventas` decimal(19,4) NOT NULL DEFAULT '0.0000',
29  `empleadoscol` varchar(45) DEFAULT NULL,
30  PRIMARY KEY (`numemp`)
31
32) ENGINE=InnoDB DEFAULT CHARSET=latin1;
33
34CREATE TABLE IF NOT EXISTS `productos` (
35  `idfab` varchar(20) CHARACTER SET utf8 NOT NULL,
36  `idproducto` varchar(20) CHARACTER SET utf8 NOT NULL,
37  `descripcion` varchar(20) CHARACTER SET utf8 NOT NULL,
38  `precio` decimal(19,4) DEFAULT NULL,
39  `existencias` int(11) NOT NULL,
40  PRIMARY KEY `CPProd` (`idfab`,`idproducto`)
41) ENGINE=InnoDB DEFAULT CHARSET=latin1;
42
43CREATE TABLE IF NOT EXISTS `pedidos` (
44  `codigo` int(11) NOT NULL AUTO_INCREMENT,
45  `numpedido` varchar(20) NOT NULL,
46  `fechapedido` datetime NOT NULL,
47  `clie` int(11) NOT NULL,
48  `rep` int(11) NOT NULL,
49  `fab` varchar(20) NOT NULL,
50  `producto` varchar(20) NOT NULL,
51  `cant` int(11) NOT NULL,
52  `importe` decimal(19,4) DEFAULT NULL,
53  `pedidoscol` varchar(45) DEFAULT NULL,
54  PRIMARY KEY (`codigo`),
55  UNIQUE KEY `UX_numpedido` (`codigo`),
56FOREIGN KEY (`rep`) REFERENCES EMPLEADOS (`numemp`),
57 FOREIGN KEY (`clie`) REFERENCES CLIENTES(`numclie`),
58FOREIGN KEY (`fab`,`producto`) REFERENCES PRODUCTOS(`idfab`,`idproducto`)
59) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;
60
61alter table `empleados`
62add FOREIGN KEY (`jefe`) REFERENCES EMPLEADOS (`numemp`);
63
64 alter table `empleados`
65 add FOREIGN KEY (`oficina`) REFERENCES OFICINAS (`oficina`);
66alter table `clientes`
67add FOREIGN KEY (`repclie`) REFERENCES EMPLEADOS (`numemp`);
68alter table `oficinas`
69add FOREIGN KEY (`dir`) REFERENCES EMPLEADOS (`numemp`);
70
71CREATE TABLE IF NOT EXISTS `sysdiagrams` (
72  `diagram_id` int(11) NOT NULL AUTO_INCREMENT,
73  `name` varchar(128) CHARACTER SET utf8 NOT NULL,
74  `principal_id` int(11) NOT NULL,
75  `version` int(11) DEFAULT NULL,
76  `definition` longblob,
77  PRIMARY KEY (`diagram_id`),
78  UNIQUE KEY `UK_principal_name` (`principal_id`,`name`)
79) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
80	
81FOREIGN KEY (`rep`) REFERENCES EMPLEADOS (`numemp`),
82 FOREIGN KEY (`clie`) REFERENCES CLIENTES(`numclie`),
83FOREIGN KEY (`fab`,`producto`) REFERENCES PRODUCTOS(`idfab`,`idproducto`)
84	
85FOREIGN KEY (`rep`) REFERENCES empleados (`numemp`),
86 FOREIGN KEY (`clie`) REFERENCES clientes(`numclie`),
87FOREIGN KEY (`fab`,`producto`) REFERENCES productos(`idfab`,`idproducto`)