· 4 years ago · Sep 09, 2021, 06:10 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema CONVOCATORIA
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema CONVOCATORIA
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `CONVOCATORIA` DEFAULT CHARACTER SET latin1 ;
15USE `CONVOCATORIA` ;
16
17-- -----------------------------------------------------
18-- Table `CONVOCATORIA`.`CUSTOMERS`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `CONVOCATORIA`.`CUSTOMERS` (
21 `customerId` INT NOT NULL,
22 `identification` VARCHAR(15) NOT NULL,
23 `nameC` VARCHAR(70) NOT NULL,
24 `surname` VARCHAR(12) NOT NULL,
25 `mail` VARCHAR(256) NOT NULL,
26 PRIMARY KEY (`customerId`),
27 UNIQUE INDEX `mail_UNIQUE` (`mail` ASC) VISIBLE,
28 UNIQUE INDEX `identification_UNIQUE` (`identification` ASC) VISIBLE)
29ENGINE = InnoDB;
30
31
32-- -----------------------------------------------------
33-- Table `CONVOCATORIA`.`RECEIPTS`
34-- -----------------------------------------------------
35CREATE TABLE IF NOT EXISTS `CONVOCATORIA`.`RECEIPTS` (
36 `receiptId` INT NOT NULL,
37 `customer_id` INT NOT NULL,
38 `dateC` DATE NOT NULL,
39 PRIMARY KEY (`receiptId`),
40 INDEX `FK_CUSTOMER_idx` (`customer_id` ASC) VISIBLE,
41 CONSTRAINT `FK_CUSTOMER`
42 FOREIGN KEY (`customer_id`)
43 REFERENCES `CONVOCATORIA`.`CUSTOMERS` (`customerId`)
44 ON DELETE NO ACTION
45 ON UPDATE NO ACTION)
46ENGINE = InnoDB;
47
48
49-- -----------------------------------------------------
50-- Table `CONVOCATORIA`.`PRODUCTS`
51-- -----------------------------------------------------
52CREATE TABLE IF NOT EXISTS `CONVOCATORIA`.`PRODUCTS` (
53 `productId` INT NOT NULL,
54 `nameProduct` VARCHAR(80) NOT NULL,
55 `descriptionProduct` VARCHAR(150) NOT NULL,
56 `brand` VARCHAR(45) NOT NULL,
57 `dateCreation` DATETIME NOT NULL,
58 PRIMARY KEY (`productId`))
59ENGINE = InnoDB;
60
61
62-- -----------------------------------------------------
63-- Table `CONVOCATORIA`.`DETAILS`
64-- -----------------------------------------------------
65CREATE TABLE IF NOT EXISTS `CONVOCATORIA`.`DETAILS` (
66 `detailId` INT NOT NULL,
67 `receipt_id` INT NOT NULL,
68 `product_id` INT NOT NULL,
69 `quantity` VARCHAR(4) NOT NULL,
70 PRIMARY KEY (`detailId`),
71 INDEX `FK_DETAILS_idx` (`receipt_id` ASC) VISIBLE,
72 INDEX `FK_PRODUCTS_idx` (`product_id` ASC) VISIBLE,
73 CONSTRAINT `FK_DETAILS`
74 FOREIGN KEY (`receipt_id`)
75 REFERENCES `CONVOCATORIA`.`RECEIPTS` (`receiptId`)
76 ON DELETE NO ACTION
77 ON UPDATE NO ACTION,
78 CONSTRAINT `FK_PRODUCTS`
79 FOREIGN KEY (`product_id`)
80 REFERENCES `CONVOCATORIA`.`PRODUCTS` (`productId`)
81 ON DELETE NO ACTION
82 ON UPDATE NO ACTION)
83ENGINE = InnoDB;
84
85
86SET SQL_MODE=@OLD_SQL_MODE;
87SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
88SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
89
90
91
92
93
94 insert into customers (customerId,identification,nameC,surname,mail) values (1,'93587587','Carlos','Valderrama','asdsvxgaqw@gmail.com');
95 insert into customers (customerId,identification,nameC,surname,mail) values (2,'93566587','Carlos','Castro','asdsaqw@gmail.com');
96 insert into customers (customerId,identification,nameC,surname,mail) values (3,'93786985','Juan','Perez','sdsa@gmail.com');
97 insert into customers (customerId,identification,nameC,surname,mail) values (4,'93126599','Laura','Ruiz','gdgfhh@gmail.com');
98 insert into customers (customerId,identification,nameC,surname,mail) values (5,'93326547','Jose','Smith','fdhfhcvn@gmail.com');
99 insert into customers (customerId,identification,nameC,surname,mail) values (6,'93522598','Karina','Tellez','fdgret@gmail.com');
100 insert into customers (customerId,identification,nameC,surname,mail) values (7,'93544537','Karen','Larzo','hsqrwe@gmail.com');
101 insert into customers (customerId,identification,nameC,surname,mail) values (8,'93178588','Thomas','Castro','htutrh@gmail.com');
102 insert into customers (customerId,identification,nameC,surname,mail) values (9,'93266587','Hellert','Stol','vcfdsfh@gmail.com');
103 insert into customers (customerId,identification,nameC,surname,mail) values (10,'93510587','Hall','Stip','dfgyre@gmail.com');
104 insert into customers (customerId,identification,nameC,surname,mail) values (11,'93365587','Jeremy','Parker','hretjr@gmail.com');
105 insert into customers (customerId,identification,nameC,surname,mail) values (12,'93166487','Pertol','Sands','cvcnsa@gmail.com');
106 insert into customers (customerId,identification,nameC,surname,mail) values (13,'93562587','Ana','Aragon','addgsawqsag@gmail.com');
107 insert into customers (customerId,identification,nameC,surname,mail) values (14,'93546587','Lucia','Torres','fxbbnxfdfg@gmail.com');
108 insert into customers (customerId,identification,nameC,surname,mail) values (15,'93566087','Stephen','Saenz','ewtysf@gmail.com');
109 insert into customers (customerId,identification,nameC,surname,mail) values (16,'93554587','Kerly','Philips','shryreery@gmail.com');
110 insert into customers (customerId,identification,nameC,surname,mail) values (17,'93526587','Steward','Smith','dgdfhh@gmail.com');
111 insert into customers (customerId,identification,nameC,surname,mail) values (18,'93532587','Jaminton','Steal','adfa@gmail.com');
112 insert into customers (customerId,identification,nameC,surname,mail) values (19,'93565987','Kerla','krons','cvvcnxc@gmail.com');
113 insert into customers (customerId,identification,nameC,surname,mail) values (20,'93512987','Diana','Gonzales','cvbxvcbre@gmail.com');
114
115
116 insert into products (productId,nameProduct,descriptionProduct,brand,dateCreation) values (1,'Iphone 8','64GB Original','Apple','2018-05-21');
117 insert into products (productId,nameProduct,descriptionProduct,brand,dateCreation) values (2,'Iphone X','64GB Original','Apple','2018-07-21');
118 INSERT INTO products (productId,nameProduct,descriptionProduct,brand,dateCreation) values (3,'Xbox One','Capacidad de 500GB','Microsoft','2016-04-22');
119 INSERT INTO products (productId,nameProduct,descriptionProduct,brand,dateCreation) values (4,'Iphone 6','Capacidad de 280GB','APPLE','2018-06-10');
120 INSERT INTO products
121 (productId,nameProduct,descriptionProduct,brand,dateCreation) values (5,'Huawei P8 Lite ','Capacidad de 32 GB','HUAWEI ','2016-04-22');
122INSERT INTO products
123(productId,nameProduct,descriptionProduct,brand,dateCreation) values (6,'SAMSUMG GALAXY J8 ','Capacidad de 64 GB', 'SAMSUNG',' 2020-09-17');
124INSERT INTO products
125(productId,nameProduct,descriptionProduct,brand,dateCreation) values (7,'Iphone X ','Capacidad de 125GB','APPLE','2016-04-22');
126INSERT INTO products
127(productId,nameProduct,descriptionProduct,brand,dateCreation) values (8,'Play 5','Capacidad de 500GB','Sony Interactive Entertainment.','2021-04-05');
128INSERT INTO products
129(productId,nameProduct,descriptionProduct,brand,dateCreation) values (9,'Nokia 1103','Capacidad de 130GB','NOKIA','2010-01-30');
130INSERT INTO products (productId,nameProduct,descriptionProduct,brand,dateCreation) values (10,' Portatil HP ','Capacidad de 500GB','HP','2018-12-31');
131 INSERT INTO products
132(productId,nameProduct,descriptionProduct,brand,dateCreation) values (11,'Xbox One','Capacidad de 500GB','Microsoft','2016-04-22');
133INSERT INTO products
134(productId,nameProduct,descriptionProduct,brand,dateCreation) values (12,'Iphone 6','Capacidad de 280GB','APPLE','2018-06-10');
135INSERT INTO products
136(productId,nameProduct,descriptionProduct,brand,dateCreation) values (13,'Huawei P8 Lite ','Capacidad de 32 GB','HUAWEI ','2016-04-22');
137INSERT INTO products
138(productId,nameProduct,descriptionProduct,brand,dateCreation) values (14,'SAMSUMG GALAXY J8 ','Capacidad de 64 GB', 'SAMSUNG',' 2020-09-17');
139INSERT INTO products
140(productId,nameProduct,descriptionProduct,brand,dateCreation) values (15,'Iphone X ','Capacidad de 125GB','APPLE','2016-04-22');
141INSERT INTO products
142(productId,nameProduct,descriptionProduct,brand,dateCreation) values (16,'Play 5','Capacidad de 500GB','Sony Interactive Entertainment.','2021-04-05');
143INSERT INTO products
144(productId,nameProduct,descriptionProduct,brand,dateCreation) values (17,'Nokia 1103','Capacidad de 130GB','NOKIA','2010-01-30');
145INSERT INTO products
146(productId,nameProduct,descriptionProduct,brand,dateCreation) values (18,' Portatil HP ','Capacidad de 500GB','HP','2018-12-31');
147INSERT INTO products
148(productId,nameProduct,descriptionProduct,brand,dateCreation) values (19,'Procesador Intel I8 ','8 Nucleos decima generacion','accer',' 2020-02-15');
149INSERT INTO products
150(productId,nameProduct,descriptionProduct,brand,dateCreation) values (20,'procesador intel i9 ','2 Nucleos 8va generacion','Microsoft','2016-04-22');
151INSERT INTO products
152(productId,nameProduct,descriptionProduct,brand,dateCreation) values (21,'SAMSUMG A80','Capacidad de 120GB','SAMSUMG ','2010-10-08');
153INSERT INTO products
154(productId,nameProduct,descriptionProduct,brand,dateCreation) values (22,'Mouse Logitech M18','Inalambrico USB','LOGITECH' ,' 2018-08-27');
155insert into products (productId,nameProduct,descriptionProduct,brand,dateCreation) values (23,'Televisor LG Smart Tv','42 pulgadas' ,'LG',' 2020-05-10');
156
157
158
159 insert into receipts (receiptId,customer_id,dateC) values (1,1,'2021-04-22');
160insert into receipts (receiptId,customer_id,dateC) values (2,2,'2021-05-12');
161insert into receipts (receiptId,customer_id,dateC) values (3,3,'2021-06-07');
162insert into receipts (receiptId,customer_id,dateC) values (4,4,'2021-09-15');
163insert into receipts (receiptId,customer_id,dateC) values (5,5,'2021-02-21');
164insert into receipts (receiptId,customer_id,dateC) values (6,6,'2021-01-18');
165insert into receipts (receiptId,customer_id,dateC) values (7,7,'2021-02-02');
166insert into receipts (receiptId,customer_id,dateC) values (8,8,'2021-06-03');
167insert into receipts (receiptId,customer_id,dateC) values (9,9,'2021-07-24');
168insert into receipts (receiptId,customer_id,dateC) values (10,10,'2021-03-25');
169insert into receipts (receiptId,customer_id,dateC) values (11,11,'2021-06-26');
170insert into receipts (receiptId,customer_id,dateC) values (12,12,'2021-10-27');
171insert into receipts (receiptId,customer_id,dateC) values (13,13,'2021-12-08');
172insert into receipts (receiptId,customer_id,dateC) values (14,14,'2021-11-19');
173insert into receipts (receiptId,customer_id,dateC) values (15,15,'2021-11-30');
174insert into receipts (receiptId,customer_id,dateC) values (16,16,'2021-08-27');
175insert into receipts (receiptId,customer_id,dateC) values (17,17,'2021-06-28');
176insert into receipts (receiptId,customer_id,dateC) values (18,18,'2021-12-31');
177insert into receipts (receiptId,customer_id,dateC) values (19,19,'2021-02-11');
178insert into receipts (receiptId,customer_id,dateC) values (20,20,'2021-04-09');
179
180
181
182
183insert into details values (1,1,1,2);
184insert into details values (2,2,2,2);
185insert into details values (3,3,3,3);
186insert into details values (4,4,4,4);
187insert into details values (5,5,5,5);
188insert into details values (6,6,6,6);
189insert into details values (7,7,7,7);
190insert into details values (8,8,8,8);
191insert into details values (9,9,9,9);
192insert into details values (10,10,10,10);
193insert into details values (11,11,11,11);
194insert into details values (12,12,12,12);
195insert into details values (13,13,13,13);
196insert into details values (14,14,14,14);
197insert into details values (15,15,15,15);
198insert into details values (16,16,16,16);
199insert into details values (17,17,17,17);
200insert into details values (18,18,18,18);
201insert into details values (19,19,19,19);
202insert into details values (20,20,20,20);