· 8 years ago · Feb 19, 2018, 08:10 AM
1nuControl:09490672
2Nombre:Joaquin Flores
3
4
5SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
6SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
7SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
8
9CREATE SCHEMA IF NOT EXISTS `FBD09_672` DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci ;
10USE `FBD09_672` ;
11
12-- -----------------------------------------------------
13-- Table `FBD09_672`.`Libro`
14-- -----------------------------------------------------
15CREATE TABLE IF NOT EXISTS `FBD09_672`.`Libro` (
16 `Titulo` VARCHAR(45) NOT NULL ,
17 PRIMARY KEY (`Titulo`) )
18ENGINE = InnoDB
19DEFAULT CHARACTER SET = latin1
20COLLATE = latin1_spanish_ci;
21
22
23-- -----------------------------------------------------
24-- Table `FBD09_672`.`Autor`
25-- -----------------------------------------------------
26CREATE 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)
36ENGINE = InnoDB
37DEFAULT CHARACTER SET = latin1
38COLLATE = latin1_spanish_ci;
39
40
41-- -----------------------------------------------------
42-- Table `FBD09_672`.`Edicion`
43-- -----------------------------------------------------
44CREATE 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)
57ENGINE = InnoDB
58DEFAULT CHARACTER SET = latin1
59COLLATE = latin1_spanish_ci;
60
61
62-- -----------------------------------------------------
63-- Table `FBD09_672`.`Copia`
64-- -----------------------------------------------------
65CREATE 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)
75ENGINE = InnoDB
76DEFAULT CHARACTER SET = latin1
77COLLATE = latin1_spanish_ci;
78
79
80-- -----------------------------------------------------
81-- Table `FBD09_672`.`Usuario`
82-- -----------------------------------------------------
83CREATE 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`) )
88ENGINE = InnoDB
89DEFAULT CHARACTER SET = latin1
90COLLATE = latin1_spanish_ci;
91
92
93-- -----------------------------------------------------
94-- Table `FBD09_672`.`Prestamo`
95-- -----------------------------------------------------
96CREATE 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)
115ENGINE = InnoDB
116DEFAULT CHARACTER SET = latin1
117COLLATE = latin1_spanish_ci;
118
119
120
121SET SQL_MODE=@OLD_SQL_MODE;
122SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
123SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
124
125******************************************************************************************************************
126
127mysql> insert into Libro values ('Fundamentos de Sistemas de Bases de Datos');
128Query OK, 1 row affected (0.00 sec)
129
130mysql> insert into Libro values ('Fundamentos de Bases de Datos');
131Query OK, 1 row affected (0.00 sec)
132
133mysql> insert into Autor values ('Fundamentos de Sistemas de Bases de Datos','Ramez Elmasri');
134Query OK, 1 row affected (0.00 sec)
135
136mysql> insert into Autor values ('Fundamentos de Sistemas de Bases de Datos','Shamkant Navathe');
137Query OK, 1 row affected (0.00 sec)
138
139mysql> insert into Autor values ('Fundamentos de Bases de Datos','Abraham Silberchatz');
140Query OK, 1 row affected (0.00 sec)
141
142mysql> insert into Edicion values (8448146441,5,2006,'Espanol','Fundamentos de Bases de Datos');
143Query OK, 1 row affected, 1 warning (0.00 sec)
144
145mysql> insert into Edicion values(9780136086208,6,2010,'Ingles','Fundamentos de Sistemas de Bases de Datos');
146Query OK, 1 row affected, 1 warning (0.00 sec)
147
148mysql> insert into Edicion values (9788478290857,5,2007,'Espanol','Fundamentos de Sistemas de Bases de Datos');
149Query OK, 1 row affected, 1 warning (0.00 sec)
150
151mysql> insert into Copia values (8448146441,1);
152Query OK, 1 row affected (0.00 sec)
153
154mysql> insert into Copia values (8448146441,2);
155Query OK, 1 row affected (0.00 sec)
156
157mysql> insert into Copia values (9780136086208,1);
158Query OK, 1 row affected (0.00 sec)
159
160mysql> insert into Copia values(9788478290857,1);
161Query OK, 1 row affected (0.00 sec)
162
163mysql> insert into Copia values(9788478290857,1);
164ERROR 1062 (23000): Duplicate entry '1-9788478290857' for key 'PRIMARY'
165mysql> insert into Copia values(9788478290857,2);
166Query OK, 1 row affected (0.00 sec)
167
168mysql> insert into Usuario values (1,'Joaquin',2011-10-24);
169Query OK, 1 row affected, 1 warning (0.00 sec)
170
171mysql> insert into Usuario values (2,'Ruben Batista',2011-10-24);
172Query OK, 1 row affected, 1 warning (0.01 sec)
173
174mysql> insert into Usuario values(3,'Juan Perez',2011-10-24);
175Query OK, 1 row affected, 1 warning (0.00 sec)
176
177
178mysql> select * from Libro;
179+-------------------------------------------+
180| Titulo |
181+-------------------------------------------+
182| Fundamentos de Bases de Datos |
183| Fundamentos de Sistemas de Bases de Datos |
184+-------------------------------------------+
1852 rows in set (0.00 sec)
186
187mysql> 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+---------------+----------+------------+---------+-------------------------------------------+
1953 rows in set (0.00 sec)
196
197mysql> select * from Copia;
198+---------------+----------+
199| ISBN | NumCopia |
200+---------------+----------+
201| 8448146441 | 1 |
202| 8448146441 | 2 |
203| 9780136086208 | 1 |
204| 9788478290857 | 1 |
205| 9788478290857 | 2 |
206+---------------+----------+
2075 rows in set (0.00 sec)
208
209mysql> insert into Prestamo values (1,9788478290857,1,2011-10-18,null);
210Query OK, 1 row affected, 1 warning (0.00 sec)
211
212mysql> insert into Prestamo values (3,9788478290857,2,2011-10-19,null);
213Query OK, 1 row affected, 1 warning (0.00 sec)
214
215mysql> insert into Prestamo values (2,8448146441,1,2011-10-19,null);
216Query OK, 1 row affected, 1 warning (0.00 sec)
217
218mysql> insert into Prestamo values (2,9788478290857,1,2011-10-19,null);
219Query OK, 1 row affected, 1 warning (0.00 sec)
220
221mysql> update Prestamo set Numero=1,ISBN=9788478290857, NumCopia=1, FechaPrestamo=2011-10-18,FechaDevolucion=2011-10-21;
222ERROR 1062 (23000): Duplicate entry '1-9788478290857-1' for key 'PRIMARY'