· 8 years ago · May 10, 2018, 02:18 AM
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='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14Drop schema if exists mydb ;
15
16CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
17USE `mydb` ;
18
19-- -----------------------------------------------------
20-- Table `mydb`.`Publisher`
21-- -----------------------------------------------------
22CREATE TABLE IF NOT EXISTS `mydb`.`Publisher` (
23 `Name` VARCHAR(45) NOT NULL,
24 `Address` VARCHAR(45) NOT NULL,
25 `Telephone` VARCHAR(20) NOT NULL,
26 PRIMARY KEY (`Name`))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `mydb`.`BOOK`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `mydb`.`BOOK` (
34 `ISBN` INT NOT NULL,
35 `Title` VARCHAR(45) NOT NULL,
36 `Publisher_Name` VARCHAR(45) NOT NULL,
37 `Publication_Year` year NOT NULL,
38 `Price` INT NOT NULL,
39 `Category` VARCHAR(45) NOT NULL,
40 `Min_Quantity` INT NULL DEFAULT 1,
41 `Quantity` INT NOT NULL,
42 PRIMARY KEY (`ISBN`),
43 INDEX `fk_BOOK_Publisher1_idx` (`Publisher_Name` ASC),
44 CONSTRAINT `fk_BOOK_Publisher1`
45 FOREIGN KEY (`Publisher_Name`)
46 REFERENCES `mydb`.`Publisher` (`Name`)
47 ON DELETE NO ACTION
48 ON UPDATE NO ACTION)
49ENGINE = InnoDB;
50
51
52-- -----------------------------------------------------
53-- Table `mydb`.`ORDERS`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `mydb`.`ORDERS` (
56 `ISBN` INT NOT NULL,
57 `Quantity` INT NOT NULL,
58 PRIMARY KEY (`ISBN`),
59 CONSTRAINT `fk_ORDERS_BOOK1`
60 FOREIGN KEY (`ISBN`)
61 REFERENCES `mydb`.`BOOK` (`ISBN`)
62 ON DELETE NO ACTION
63 ON UPDATE NO ACTION)
64ENGINE = InnoDB;
65
66
67-- -----------------------------------------------------
68-- Table `mydb`.`USERS`
69-- -----------------------------------------------------
70CREATE TABLE IF NOT EXISTS `mydb`.`USERS` (
71 `User_Name` VARCHAR(45) NOT NULL,
72 `Password` VARCHAR(45) NOT NULL,
73 `First_Name` VARCHAR(15) NOT NULL,
74 `Last_Name` VARCHAR(15) NOT NULL,
75 `Email_Address` VARCHAR(45) NOT NULL,
76 `Phone_Number` VARCHAR(20) NOT NULL,
77 `Shipping_Address` VARCHAR(45) NOT NULL,
78 `User_Type` INT NOT NULL,
79 PRIMARY KEY (`Email_Address`))
80ENGINE = InnoDB;
81
82
83-- -----------------------------------------------------
84-- Table `mydb`.`Shopping_Cart`
85-- -----------------------------------------------------
86CREATE TABLE IF NOT EXISTS `mydb`.`Shopping_Cart` (
87 `User_Email` VARCHAR(45) NOT NULL,
88 `ISBN` INT NOT NULL,
89 `Quantity` INT NOT NULL,
90 PRIMARY KEY (`User_Email`, `ISBN`),
91 INDEX `fk_Shopping_Cart_BOOK1_idx` (`ISBN` ASC),
92 CONSTRAINT `fk_Shopping_Cart_BOOK1`
93 FOREIGN KEY (`ISBN`)
94 REFERENCES `mydb`.`BOOK` (`ISBN`)
95 ON DELETE NO ACTION
96 ON UPDATE NO ACTION,
97 CONSTRAINT `fk_Shopping_Cart_USERS1`
98 FOREIGN KEY (`User_Email`)
99 REFERENCES `mydb`.`USERS` (`Email_Address`)
100 ON DELETE NO ACTION
101 ON UPDATE NO ACTION)
102ENGINE = InnoDB;
103
104
105-- -----------------------------------------------------
106-- Table `mydb`.`Sales`
107-- -----------------------------------------------------
108CREATE TABLE IF NOT EXISTS `mydb`.`Sales` (
109 `User_Email` VARCHAR(45) NOT NULL,
110 `ISBN` INT NOT NULL,
111 `Quantity` INT NOT NULL,
112 `Date` DATE NOT NULL,
113 PRIMARY KEY (`User_Email`, `ISBN`),
114 INDEX `fk_Sales_BOOK1_idx` (`ISBN` ASC),
115 CONSTRAINT `fk_Sales_USERS1`
116 FOREIGN KEY (`User_Email`)
117 REFERENCES `mydb`.`USERS` (`Email_Address`)
118 ON DELETE NO ACTION
119 ON UPDATE NO ACTION,
120 CONSTRAINT `fk_Sales_BOOK1`
121 FOREIGN KEY (`ISBN`)
122 REFERENCES `mydb`.`BOOK` (`ISBN`)
123 ON DELETE NO ACTION
124 ON UPDATE NO ACTION)
125ENGINE = InnoDB;
126
127
128-- -----------------------------------------------------
129-- Table `mydb`.`Author`
130-- -----------------------------------------------------
131CREATE TABLE IF NOT EXISTS `mydb`.`Author` (
132 `ISBN` INT NOT NULL,
133 `Author_Name` VARCHAR(45) NOT NULL,
134 PRIMARY KEY (`ISBN`, `Author_Name`),
135 CONSTRAINT `fk_Author_BOOK`
136 FOREIGN KEY (`ISBN`)
137 REFERENCES `mydb`.`BOOK` (`ISBN`)
138 ON DELETE NO ACTION
139 ON UPDATE NO ACTION)
140ENGINE = InnoDB;
141
142DELIMITER $$
143
144CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`Negativity_Check` BEFORE UPDATE ON `BOOK` FOR EACH ROW
145BEGIN
146declare msg varchar(128);
147
148 if(new.Quantity < 0) then
149 set msg = concat('Error: Can\'t have negative quantity', cast(new.Quantity as char));
150 signal sqlstate '45000' set message_text = msg;
151 end if;
152END
153$$
154DELIMITER ;
155
156DELIMITER $$
157
158CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`MIN_Check` AFTER UPDATE ON `BOOK` FOR EACH ROW
159BEGIN
160 if(new.Quantity < old.Min_Quantity) then
161 insert into mydb.ORDERS values (old.ISBN, 30);
162 end If;
163END
164$$
165DELIMITER ;
166
167DELIMITER $$
168CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`Confirm_Order` BEFORE DELETE ON `ORDERS` FOR EACH ROW
169BEGIN
170 update mydb.BOOK set Quantity = old.Quantity + Quantity where ISBN = old.ISBN;
171END
172
173$$
174DELIMITER ;
175
176SET SQL_MODE=@OLD_SQL_MODE;
177SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
178SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
179
180
181INSERT INTO PUBLISHER VALUES('McGrawHill','USA','+1-503-5050501');
182INSERT INTO PUBLISHER VALUES('Pearson','USA','+1-503-5050502');
183INSERT INTO PUBLISHER VALUES('Wiley','USA','+1-503-5050503');
184INSERT INTO PUBLISHER VALUES('Springer','USA','+1-503-5050504');
185
186INSERT INTO BOOK VALUES('1','The Lost Tribe','McGrawHill','1996',96,'COMEDY',5,60);
187INSERT INTO BOOK VALUES('2','Atlantis','McGrawHill','1996',96,'COMEDY',5,60);
188INSERT INTO BOOK VALUES('3','Cast Away','Pearson','1996',96,'COMEDY',5,60);
189INSERT INTO BOOK VALUES('4','Inferno','Wiley','1996',96,'COMEDY',5,60);
190INSERT INTO BOOK VALUES('5','Perfume','Springer','1996',96,'COMEDY',5,60);
191INSERT INTO BOOK VALUES('6','Tintin','Springer','1996',96,'COMEDY',5,60);