API tools faq
paste
Guest User

Untitled

a guest
Feb 19th, 2018
297
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 7.98 KB | None | 0 0
raw download clone embed print report
  1. nuControl:09490672
  2. Nombre:Joaquin Flores
  3.  
  4.  
  5. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  6. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  7. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
  8.  
  9. CREATE SCHEMA IF NOT EXISTS `FBD09_672` DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci ;
  10. USE `FBD09_672` ;
  11.  
  12. -- -----------------------------------------------------
  13. -- Table `FBD09_672`.`Libro`
  14. -- -----------------------------------------------------
  15. CREATE  TABLE IF NOT EXISTS `FBD09_672`.`Libro` (
  16.   `Titulo` VARCHAR(45) NOT NULL ,
  17.   PRIMARY KEY (`Titulo`) )
  18. ENGINE = InnoDB
  19. DEFAULT CHARACTER SET = latin1
  20. COLLATE = latin1_spanish_ci;
  21.  
  22.  
  23. -- -----------------------------------------------------
  24. -- Table `FBD09_672`.`Autor`
  25. -- -----------------------------------------------------
  26. CREATE  TABLE IF NOT EXISTS `FBD09_672`.`Autor` (
  27.   `Titulo` VARCHAR(45) NOT NULL ,
  28.   `NombreAutor` VARCHAR(45) NOT NULL ,
  29.   PRIMARY KEY (`Titulo`, `NombreAutor`) ,
  30.   INDEX `fk_autor_titulonum` (`Titulo` ASC) ,
  31.   CONSTRAINT `fk_autor_titulonum`
  32.     FOREIGN KEY (`Titulo` )
  33.     REFERENCES `FBD09_672`.`Libro` (`Titulo` )
  34.     ON DELETE NO ACTION
  35.     ON UPDATE NO ACTION)
  36. ENGINE = InnoDB
  37. DEFAULT CHARACTER SET = latin1
  38. COLLATE = latin1_spanish_ci;
  39.  
  40.  
  41. -- -----------------------------------------------------
  42. -- Table `FBD09_672`.`Edicion`
  43. -- -----------------------------------------------------
  44. CREATE  TABLE IF NOT EXISTS `FBD09_672`.`Edicion` (
  45.   `ISBN` BIGINT NOT NULL ,
  46.   `NumeroEd` INT NULL ,
  47.   `Anio` DATE NULL ,
  48.   `Idioma` VARCHAR(45) NULL ,
  49.   `Titulo` VARCHAR(45) NOT NULL ,
  50.   PRIMARY KEY (`ISBN`) ,
  51.   INDEX `fk_edicion_titulo` (`Titulo` ASC) ,
  52.   CONSTRAINT `fk_edicion_titulo`
  53.     FOREIGN KEY (`Titulo` )
  54.     REFERENCES `FBD09_672`.`Libro` (`Titulo` )
  55.     ON DELETE NO ACTION
  56.     ON UPDATE NO ACTION)
  57. ENGINE = InnoDB
  58. DEFAULT CHARACTER SET = latin1
  59. COLLATE = latin1_spanish_ci;
  60.  
  61.  
  62. -- -----------------------------------------------------
  63. -- Table `FBD09_672`.`Copia`
  64. -- -----------------------------------------------------
  65. CREATE  TABLE IF NOT EXISTS `FBD09_672`.`Copia` (
  66.   `ISBN` BIGINT NOT NULL ,
  67.   `NumCopia` INT NOT NULL ,
  68.   PRIMARY KEY (`NumCopia`, `ISBN`) ,
  69.   INDEX `fk_copia_isbn` (`ISBN` ASC) ,
  70.   CONSTRAINT `fk_copia_isbn`
  71.     FOREIGN KEY (`ISBN` )
  72.     REFERENCES `FBD09_672`.`Edicion` (`ISBN` )
  73.     ON DELETE NO ACTION
  74.     ON UPDATE NO ACTION)
  75. ENGINE = InnoDB
  76. DEFAULT CHARACTER SET = latin1
  77. COLLATE = latin1_spanish_ci;
  78.  
  79.  
  80. -- -----------------------------------------------------
  81. -- Table `FBD09_672`.`Usuario`
  82. -- -----------------------------------------------------
  83. CREATE  TABLE IF NOT EXISTS `FBD09_672`.`Usuario` (
  84.   `Numero` INT NOT NULL ,
  85.   `Nombre` VARCHAR(45) NULL ,
  86.   `FechaRegistro` DATE NULL ,
  87.   PRIMARY KEY (`Numero`) )
  88. ENGINE = InnoDB
  89. DEFAULT CHARACTER SET = latin1
  90. COLLATE = latin1_spanish_ci;
  91.  
  92.  
  93. -- -----------------------------------------------------
  94. -- Table `FBD09_672`.`Prestamo`
  95. -- -----------------------------------------------------
  96. CREATE  TABLE IF NOT EXISTS `FBD09_672`.`Prestamo` (
  97.   `Numero` INT NOT NULL ,
  98.   `ISBN` BIGINT NOT NULL ,
  99.   `NumCopia` INT NOT NULL ,
  100.   `FechaPrestamo` DATE NULL ,
  101.   `FechaDevolucion` DATE NULL ,
  102.   PRIMARY KEY (`Numero`, `ISBN`, `NumCopia`) ,
  103.   INDEX `fk_prestamo_numero` (`Numero` ASC) ,
  104.   INDEX `fk_prestamo_isbn` (`ISBN` ASC) ,
  105.   CONSTRAINT `fk_prestamo_numero`
  106.     FOREIGN KEY (`Numero` )
  107.     REFERENCES `FBD09_672`.`Usuario` (`Numero` )
  108.     ON DELETE NO ACTION
  109.     ON UPDATE NO ACTION,
  110.   CONSTRAINT `fk_prestamo_isbn`
  111.     FOREIGN KEY (`ISBN` )
  112.     REFERENCES `FBD09_672`.`Copia` (`ISBN` )
  113.     ON DELETE NO ACTION
  114.     ON UPDATE NO ACTION)
  115. ENGINE = InnoDB
  116. DEFAULT CHARACTER SET = latin1
  117. COLLATE = latin1_spanish_ci;
  118.  
  119.  
  120.  
  121. SET SQL_MODE=@OLD_SQL_MODE;
  122. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  123. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  124.  
  125. ******************************************************************************************************************
  126.  
  127. mysql> INSERT INTO Libro VALUES ('Fundamentos de Sistemas de Bases de Datos');
  128. Query OK, 1 ROW affected (0.00 sec)
  129.  
  130. mysql> INSERT INTO Libro VALUES ('Fundamentos de Bases de Datos');
  131. Query OK, 1 ROW affected (0.00 sec)
  132.  
  133. mysql> INSERT INTO Autor VALUES ('Fundamentos de Sistemas de Bases de Datos','Ramez Elmasri');  
  134. Query OK, 1 ROW affected (0.00 sec)
  135.  
  136. mysql> INSERT INTO Autor VALUES ('Fundamentos de Sistemas de Bases de Datos','Shamkant Navathe');
  137. Query OK, 1 ROW affected (0.00 sec)
  138.  
  139. mysql> INSERT INTO Autor VALUES ('Fundamentos de Bases de Datos','Abraham Silberchatz');
  140. Query OK, 1 ROW affected (0.00 sec)
  141.  
  142. mysql> INSERT INTO Edicion VALUES (8448146441,5,2006,'Espanol','Fundamentos de Bases de Datos');
  143. Query OK, 1 ROW affected, 1 warning (0.00 sec)
  144.  
  145. mysql> INSERT INTO Edicion VALUES(9780136086208,6,2010,'Ingles','Fundamentos de Sistemas de Bases de Datos');
  146. Query OK, 1 ROW affected, 1 warning (0.00 sec)
  147.  
  148. mysql> INSERT INTO Edicion VALUES (9788478290857,5,2007,'Espanol','Fundamentos de Sistemas de Bases de Datos');
  149. Query OK, 1 ROW affected, 1 warning (0.00 sec)
  150.  
  151. mysql> INSERT INTO Copia VALUES (8448146441,1);
  152. Query OK, 1 ROW affected (0.00 sec)
  153.  
  154. mysql> INSERT INTO Copia VALUES (8448146441,2);
  155. Query OK, 1 ROW affected (0.00 sec)
  156.  
  157. mysql> INSERT INTO Copia VALUES (9780136086208,1);
  158. Query OK, 1 ROW affected (0.00 sec)
  159.  
  160. mysql> INSERT INTO Copia VALUES(9788478290857,1);
  161. Query OK, 1 ROW affected (0.00 sec)
  162.  
  163. mysql> INSERT INTO Copia VALUES(9788478290857,1);
  164. ERROR 1062 (23000): Duplicate entry '1-9788478290857' FOR KEY 'PRIMARY'
  165. mysql> INSERT INTO Copia VALUES(9788478290857,2);
  166. Query OK, 1 ROW affected (0.00 sec)
  167.  
  168. mysql> INSERT INTO Usuario VALUES (1,'Joaquin',2011-10-24);
  169. Query OK, 1 ROW affected, 1 warning (0.00 sec)
  170.  
  171. mysql> INSERT INTO Usuario VALUES (2,'Ruben Batista',2011-10-24);
  172. Query OK, 1 ROW affected, 1 warning (0.01 sec)
  173.  
  174. mysql> INSERT INTO Usuario VALUES(3,'Juan Perez',2011-10-24);
  175. Query OK, 1 ROW affected, 1 warning (0.00 sec)
  176.  
  177.  
  178. mysql> SELECT * FROM Libro;
  179. +-------------------------------------------+
  180. | Titulo                                    |
  181. +-------------------------------------------+
  182. | Fundamentos de Bases de Datos             |
  183. | Fundamentos de Sistemas de Bases de Datos |
  184. +-------------------------------------------+
  185. 2 ROWS IN SET (0.00 sec)
  186.  
  187. mysql> SELECT * FROM Edicion;
  188. +---------------+----------+------------+---------+-------------------------------------------+
  189. | ISBN          | NumeroEd | Anio       | Idioma  | Titulo                                    |
  190. +---------------+----------+------------+---------+-------------------------------------------+
  191. |    8448146441 |        5 | 0000-00-00 | Espanol | Fundamentos de Bases de Datos             |
  192. | 9780136086208 |        6 | 0000-00-00 | Ingles  | Fundamentos de Sistemas de Bases de Datos |
  193. | 9788478290857 |        5 | 0000-00-00 | Espanol | Fundamentos de Sistemas de Bases de Datos |
  194. +---------------+----------+------------+---------+-------------------------------------------+
  195. 3 ROWS IN SET (0.00 sec)
  196.  
  197. mysql> SELECT * FROM Copia;
  198. +---------------+----------+
  199. | ISBN          | NumCopia |
  200. +---------------+----------+
  201. |    8448146441 |        1 |
  202. |    8448146441 |        2 |
  203. | 9780136086208 |        1 |
  204. | 9788478290857 |        1 |
  205. | 9788478290857 |        2 |
  206. +---------------+----------+
  207. 5 ROWS IN SET (0.00 sec)
  208.  
  209. mysql> INSERT INTO Prestamo VALUES (1,9788478290857,1,2011-10-18,NULL);
  210. Query OK, 1 ROW affected, 1 warning (0.00 sec)
  211.  
  212. mysql> INSERT INTO Prestamo VALUES (3,9788478290857,2,2011-10-19,NULL);
  213. Query OK, 1 ROW affected, 1 warning (0.00 sec)
  214.  
  215. mysql> INSERT INTO Prestamo VALUES (2,8448146441,1,2011-10-19,NULL);
  216. Query OK, 1 ROW affected, 1 warning (0.00 sec)
  217.  
  218. mysql> INSERT INTO Prestamo VALUES (2,9788478290857,1,2011-10-19,NULL);
  219. Query OK, 1 ROW affected, 1 warning (0.00 sec)
  220.  
  221. mysql> UPDATE Prestamo SET Numero=1,ISBN=9788478290857, NumCopia=1, FechaPrestamo=2011-10-18,FechaDevolucion=2011-10-21;
  222. ERROR 1062 (23000): Duplicate entry '1-9788478290857-1' FOR KEY 'PRIMARY'
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!