Script Biblioteca base de datos.
Enviado por Jairo Artist • 23 de Marzo de 2016 • Informes • 1.018 Palabras (5 Páginas) • 176 Visitas
Create database bibliotecaaa;
Use bibliotecaaa;
CREATE TABLE IF NOT EXISTS `genero` (
`id_genero` int(3) unsigned zerofill NOT NULL AUTO_INCREMENT,
`denominacion` varchar(50) NOT NULL,
PRIMARY KEY(id_genero)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `imagenes` (
`id_imagen` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
`nombre` varchar(50),
`imagen` longblob,
PRIMARY KEY(id_imagen)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `imagenpais` (
`id_imagenPais` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
`nombre` varchar(50),
`imagen` longblob,
PRIMARY KEY(id_imagenPais)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `tipodocumento` (
`id_tipoDoc` int(3) unsigned zerofill NOT NULL AUTO_INCREMENT,
`denominacion` varchar(50),
`Descripcion` varchar(50),
PRIMARY KEY(id_tipoDoc)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `tipoprestamo` (
`id_tipoPrestamo` int(2) unsigned zerofill NOT NULL AUTO_INCREMENT,
`denominacion` varchar(50),
PRIMARY KEY(id_tipoPrestamo)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `tipousuario` (
`id_tipoUsuario` int(2) unsigned zerofill NOT NULL AUTO_INCREMENT,
`denominacion` varchar(50),
PRIMARY KEY(id_tipoUsuario)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `ubicacion` (
`id_ubicacion` int(3) unsigned zerofill NOT NULL AUTO_INCREMENT,
`ubicacion` varchar(50) NOT NULL,
PRIMARY KEY(id_ubicacion)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `imageneslibros` (
`idImagen` int(3) unsigned zerofill NOT NULL AUTO_INCREMENT,
`imagen` longblob,
`nombre` varchar(30),
PRIMARY KEY(idImagen)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `paises` (
`id_pais` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
`nombrePais` varchar(50) NOT NULL,
`id_imagenPais` int(4) unsigned zerofill NULL,
PRIMARY KEY(id_pais),
INDEX(id_imagenPais),
FOREIGN KEY(id_imagenPais)REFERENCES imagenpais(id_imagenPais)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `autor` (
`id_autor` int(3) unsigned zerofill NOT NULL AUTO_INCREMENT,
`nombres` varchar(50),
`apellido1` varchar(20),
`apellido2` varchar(20)NULL,
`id_pais` int(4) unsigned zerofill NOT NULL,
PRIMARY KEY(id_autor),
INDEX(id_pais),
FOREIGN KEY(id_pais)REFERENCES paises(id_pais)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `editorial` (
`id_editorial` int(3) unsigned zerofill NOT NULL AUTO_INCREMENT,
`nombre` varchar(50),
`paisEditorial` int(4) unsigned zerofill NOT NULL,
PRIMARY KEY(id_editorial),
INDEX(paisEditorial),
FOREIGN KEY(paisEditorial)REFERENCES paises(id_pais)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `libros` (
`id_libro` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
`titulo` varchar(50),
`tomo` int(2),
`id_editorial` int(3) unsigned zerofill NOT NULL,
`numEdicion` int(3),
`añoEdicion` int(4),
`id_genero` int(3) unsigned zerofill NOT NULL,
`isbn` bigint(13),
`numPaginas` int(4),
`fechaRegistro` date,
`formato` varchar(100),
`idImagen` int(3) unsigned zerofill NULL,
PRIMARY KEY(id_libro),
INDEX(id_editorial, id_genero),
FOREIGN KEY(id_editorial)REFERENCES editorial(id_editorial),
FOREIGN KEY(id_genero)REFERENCES genero(id_genero),
FOREIGN KEY(idImagen)REFERENCES imageneslibros(idImagen)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `autoresporlibro` (
`id_autor` int(3) unsigned zerofill NOT NULL,
`id_libro` int(4) unsigned zerofill NOT NULL,
PRIMARY KEY(id_autor, id_libro),
INDEX(id_autor, id_libro),
FOREIGN KEY(id_autor)REFERENCES autor(id_autor),
FOREIGN KEY(id_libro)REFERENCES libros(id_libro)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `copiasporlibro` (
`SecuenciaCopXLibro` int(4) NOT NULL AUTO_INCREMENT,
`id_libro` int(4) unsigned zerofill NOT NULL,
`Copia` int(3),
`prestado` varchar(2),
`id_ubicacion` int(3) unsigned zerofill NOT NULL,
PRIMARY KEY(SecuenciaCopXLibro),
INDEX(id_libro, id_ubicacion),
FOREIGN KEY(id_libro)REFERENCES libros(id_libro),
FOREIGN KEY(id_ubicacion)REFERENCES ubicacion(id_ubicacion)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `usuario` (
`id_usuario` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
`id_tipoUsuario` int(3) unsigned zerofill NOT NULL,
`nombres` varchar(50),
`apellido1` varchar(50),
`apellido2` varchar(50)NULL,
`id_tipoDoc` int(3) unsigned zerofill NOT NULL,
...