Inicio‎ > ‎Recursos‎ > ‎Esquemas BD ToL‎ > ‎

toltablas

DROP TABLE IF EXISTS provincia;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE provincia (
  codp varchar(2) NOT NULL,
  nombre varchar(25) NOT NULL,
  PRIMARY KEY  (codp)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS localidad;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE localidad (
  codm varchar(4) NOT NULL,
  pueblo varchar(50) NOT NULL,
  provincia varchar(2) NOT NULL,
  PRIMARY KEY  (codm,provincia),
  KEY fk_LOCALIDAD_PROVINCIA (provincia),
  CONSTRAINT fk_LOCALIDAD_PROVINCIA FOREIGN KEY (provincia) REFERENCES provincia (codp) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS usuario;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE usuario (
  email varchar(50) NOT NULL,
  nombre varchar(35) NOT NULL,
  apellidos varchar(55) NOT NULL,
  dni varchar(12) NOT NULL,
  telefono varchar(15) default NULL,
  calle varchar(45) default NULL,
  calle2 varchar(45) default NULL,
  codpos varchar(5) default NULL,
  pueblo varchar(4) NOT NULL,
  provincia varchar(2) NOT NULL,
  nacido date,
  PRIMARY KEY  (email),
  UNIQUE KEY dni_UNIQUE (dni),
  KEY fk_USUARIO_LOCALIDAD (pueblo,provincia),
  CONSTRAINT fk_USUARIO_LOCALIDAD FOREIGN KEY (pueblo, provincia) REFERENCES localidad (codm, provincia) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS direnvio;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE direnvio (
  email varchar(50) NOT NULL,
  calle varchar(45) default NULL,
  calle2 varchar(45) default NULL,
  codpos varchar(5) default NULL,
  pueblo varchar(4) NOT NULL,
  provincia varchar(2) NOT NULL,
  PRIMARY KEY  (email),
  KEY fk_DIRENVIO_LOCALIDAD (pueblo,provincia),
  KEY fk_DIRENVIO_USUARIO (email),
  CONSTRAINT fk_DIRENVIO_LOCALIDAD FOREIGN KEY (pueblo, provincia) REFERENCES localidad (codm, provincia) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_DIRENVIO_USUARIO FOREIGN KEY (email) REFERENCES usuario (email) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS marca;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE marca (
  marca varchar(15) NOT NULL,
  empresa varchar(60) default NULL,
  logo blob,
  PRIMARY KEY  (marca)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS articulo;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE articulo (
  cod varchar(7) NOT NULL,
  nombre varchar(45) default NULL,
  pvp varchar(45) default NULL,
  marca varchar(15) default NULL,
  imagen blob,
  urlimagen varchar(100) default NULL,
  especificaciones text,
  PRIMARY KEY  (cod),
  KEY fk_ARTICULO_MARCA (marca),
  CONSTRAINT fk_ARTICULO_MARCA FOREIGN KEY (marca) REFERENCES marca (marca) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS camara;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE camara (
  cod varchar(7) NOT NULL,
  resolucion varchar(15) default NULL,
  sensor varchar(45) default NULL,
  tipo varchar(45) default NULL,
  factor varchar(10) default NULL,
  objetivo varchar(15) default NULL,
  pantalla varchar(20) default NULL,
  zoom varchar(40) default NULL,
  PRIMARY KEY  (cod),
  KEY fkcamara2articulo (cod),
  CONSTRAINT fkcamara2articulo FOREIGN KEY (cod) REFERENCES articulo (cod) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS memoria;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE memoria (
  cod varchar(7) NOT NULL,
  PRIMARY KEY  (cod),
  KEY fkcamara2articulo (cod),
  CONSTRAINT fkmemoria2articulo FOREIGN KEY (cod) REFERENCES articulo (cod) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS objetivo;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE objetivo (
  cod varchar(7) NOT NULL,
  tipo varchar(15) default NULL,
  montura varchar(15) default NULL,
  focal varchar(10) default NULL,
  apertura varchar(10) default NULL,
  especiales varchar(35) default NULL,
  PRIMARY KEY  (cod),
  CONSTRAINT fk_objetivo2articulo FOREIGN KEY (cod) REFERENCES articulo (cod) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS tv;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE tv (
  cod varchar(7) NOT NULL,
  panel varchar(45) default NULL,
  pantalla smallint(6) default NULL,
  resolucion varchar(15) default NULL,
  hdreadyfullhd varchar(6) default NULL,
  tdt tinyint(1) default NULL,
  PRIMARY KEY  (cod),
  KEY fkcamara2articulo (cod),
  CONSTRAINT fktv2articulo FOREIGN KEY (cod) REFERENCES articulo (cod) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS pack;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE pack (
  cod varchar(7) NOT NULL,
  PRIMARY KEY  (cod),
  KEY fkpack (cod),
  CONSTRAINT fkpack2articulo FOREIGN KEY (cod) REFERENCES articulo (cod) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS ptienea;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE ptienea (
  pack varchar(7) NOT NULL,
  articulo varchar(7) NOT NULL,
  PRIMARY KEY  (pack,articulo),
  KEY fkaarticulo (articulo),
  CONSTRAINT fkaarticulo FOREIGN KEY (articulo) REFERENCES articulo (cod) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fkapack FOREIGN KEY (pack) REFERENCES pack (cod) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS stock;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE stock (
  articulo varchar(7) NOT NULL,
  disponible int(11) default NULL,
  entrega set('Descatalogado','Próximamente','24 horas','3/4 días','1/2 semanas') default NULL,
  PRIMARY KEY  (articulo),
  KEY fk_ARTICULO_ARTICULO1 (articulo),
  CONSTRAINT fk_stock2articulo FOREIGN KEY (articulo) REFERENCES articulo (cod) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS cesta;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE cesta (
  articulo varchar(7) NOT NULL,
  usuario varchar(50) NOT NULL,
  fecha datetime default NULL,
  PRIMARY KEY  (articulo,usuario),
  KEY fk_CESTA_ARTICULO (articulo),
  KEY fk_CESTA_USUARIO (usuario),
  CONSTRAINT fk_CESTA_ARTICULO FOREIGN KEY (articulo) REFERENCES articulo (cod) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_CESTA_USUARIO FOREIGN KEY (usuario) REFERENCES usuario (email) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS pedido;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE pedido (
  numPedido int(11) NOT NULL,
  usuario varchar(50) NOT NULL,
  fecha datetime NOT NULL,
  PRIMARY KEY  (numPedido),
  KEY fk_PEDIDO_USUARIO (usuario),
  CONSTRAINT fk_PEDIDO_USUARIO FOREIGN KEY (usuario) REFERENCES usuario (email) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS linped;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE linped (
  numPedido int(11) NOT NULL,
  linea int(11) NOT NULL,
  articulo varchar(7) NOT NULL,
  importe decimal(9,2) NOT NULL,
  cantidad int(11) default NULL,
  PRIMARY KEY  (linea,numPedido),
  KEY fk_LINPED_PEDIDO (numPedido),
  KEY fk_LINPED_ARTICULO (articulo),
  CONSTRAINT fk_LINPED_ARTICULO FOREIGN KEY (articulo) REFERENCES articulo (cod) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_LINPED_PEDIDO FOREIGN KEY (numPedido) REFERENCES pedido (numPedido) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;




Comments