cuMnUv1j

1-- MySQL Script generated by MySQL Workbench
2-- 28 أبر, 2017 EET 06:25:29 م
3-- Model: New Model    Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17drop SCHEMA mydb;
18CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
19USE `mydb` ;
20
21-- -----------------------------------------------------
22-- Table `mydb`.`Publisher`
23-- -----------------------------------------------------
24CREATE TABLE IF NOT EXISTS `mydb`.`Publisher` (
25  `Name` VARCHAR(15) NOT NULL,
26  `Address` VARCHAR(45) NULL,
27  `Telephone` VARCHAR(15) NULL,
28  PRIMARY KEY (`Name`))
29ENGINE = InnoDB;
30
31
32-- -----------------------------------------------------
33-- Table `mydb`.`Book`
34-- -----------------------------------------------------
35CREATE TABLE IF NOT EXISTS `mydb`.`Book` (
36  `ISBN` VARCHAR(15) NOT NULL,
37  `Title` VARCHAR(15) NOT NULL,
38  `Price` INT NOT NULL,
39  `Category` VARCHAR(15) NOT NULL check(Category IN('Science','Art','Religion','History','Geography')), 
40  `Publication_Year` DATE NOT NULL,
41  `Threshold` INT NOT NULL,
42  `Quantity` INT NOT NULL,
43  `Publisher_Name` VARCHAR(15) NOT NULL,
44  PRIMARY KEY (`ISBN`),
45  UNIQUE INDEX `ISBN_UNIQUE` (`ISBN` ASC),
46  INDEX `fk_Book_Publisher1_idx` (`Publisher_Name` ASC),
47  CONSTRAINT `fk_Book_Publisher1`
48    FOREIGN KEY (`Publisher_Name`)
49    REFERENCES `mydb`.`Publisher` (`Name`)
50    ON DELETE NO ACTION
51    ON UPDATE NO ACTION)
52ENGINE = InnoDB;
53
54
55-- -----------------------------------------------------
56-- Table `mydb`.`Author`
57-- -----------------------------------------------------
58CREATE TABLE IF NOT EXISTS `mydb`.`Author` (
59  `Name` VARCHAR(15) NOT NULL,
60  PRIMARY KEY (`Name`))
61ENGINE = InnoDB;
62
63
64-- -----------------------------------------------------
65-- Table `mydb`.`Book_has_Author`
66-- -----------------------------------------------------
67CREATE TABLE IF NOT EXISTS `mydb`.`Book_has_Author` (
68  `Book_ISBN` VARCHAR(15) NOT NULL,
69  `Author_Name` VARCHAR(15) NOT NULL,
70  PRIMARY KEY (`Book_ISBN`, `Author_Name`),
71  INDEX `fk_Book_has_Author_Author1_idx` (`Author_Name` ASC),
72  INDEX `fk_Book_has_Author_Book1_idx` (`Book_ISBN` ASC),
73  CONSTRAINT `fk_Book_has_Author_Book1`
74    FOREIGN KEY (`Book_ISBN`)
75    REFERENCES `mydb`.`Book` (`ISBN`)
76    ON DELETE NO ACTION
77    ON UPDATE NO ACTION,
78  CONSTRAINT `fk_Book_has_Author_Author1`
79    FOREIGN KEY (`Author_Name`)
80    REFERENCES `mydb`.`Author` (`Name`)
81    ON DELETE NO ACTION
82    ON UPDATE NO ACTION)
83ENGINE = InnoDB;
84
85
86-- -----------------------------------------------------
87-- Table `mydb`.`Order`
88-- -----------------------------------------------------
89CREATE TABLE IF NOT EXISTS `mydb`.`Manager_Order` (
90  `no_of_copies` INT NOT NULL,
91  `OrderID` INT NOT NULL AUTO_INCREMENT,
92  `Book_ISBN` VARCHAR(15) NOT NULL,
93   `confirm` boolean not null,
94  PRIMARY KEY (`OrderID`, `Book_ISBN`),
95  INDEX `fk_Order_Book1_idx` (`Book_ISBN` ASC),
96  CONSTRAINT `fk_Order_Book1`
97    FOREIGN KEY (`Book_ISBN`)
98    REFERENCES `mydb`.`Book` (`ISBN`)
99    ON DELETE NO ACTION
100    ON UPDATE NO ACTION)
101ENGINE = InnoDB;
102alter table Manager_Order 
103	 ALTER COLUMN confirm SET DEFAULT false;
104
105
106-- -----------------------------------------------------
107-- Table `mydb`.`User`
108-- -----------------------------------------------------
109CREATE TABLE IF NOT EXISTS `mydb`.`User` (
110  `User_name` VARCHAR(15) NOT NULL,
111  `Fname` VARCHAR(15) NOT NULL,
112  `Lname` VARCHAR(15) NOT NULL,
113  `Email` VARCHAR(15) NOT NULL,
114  `Password` VARCHAR(15) NOT NULL,
115  `Phone_number` VARCHAR(15) NULL,
116  `Shipping_address` VARCHAR(45) NOT NULL,
117  `access` VARCHAR(15) NOT NULL,
118  
119  PRIMARY KEY (`Email`))
120ENGINE = InnoDB;
121
122
123-- -----------------------------------------------------
124-- Table `mydb`.`User_has_inCart`
125-- -----------------------------------------------------
126CREATE TABLE IF NOT EXISTS `mydb`.`User_has_inCart` (
127  `User_Email` VARCHAR(15) NOT NULL,
128  `Book_ISBN` VARCHAR(15) NOT NULL,
129  `no_of_copies` INT NOT NULL,
130  PRIMARY KEY (`User_Email`, `Book_ISBN`),
131  INDEX `fk_User_has_Book_Book1_idx` (`Book_ISBN` ASC),
132  INDEX `fk_User_has_Book_User1_idx` (`User_Email` ASC),
133  CONSTRAINT `fk_User_has_Book_User1`
134    FOREIGN KEY (`User_Email`)
135    REFERENCES `mydb`.`User` (`Email`)
136    ON DELETE NO ACTION
137    ON UPDATE NO ACTION,
138  CONSTRAINT `fk_User_has_Book_Book1`
139    FOREIGN KEY (`Book_ISBN`)
140    REFERENCES `mydb`.`Book` (`ISBN`)
141    ON DELETE NO ACTION
142    ON UPDATE NO ACTION)
143ENGINE = InnoDB;
144
145
146-- -----------------------------------------------------
147-- Table `mydb`.`Purchases`
148-- -----------------------------------------------------
149CREATE TABLE IF NOT EXISTS `mydb`.`Purchases` (
150  `User_Email` VARCHAR(15) NOT NULL,
151  `Book_ISBN` VARCHAR(15) NOT NULL,
152  `no_of_copies` VARCHAR(45) NOT NULL,
153  `date` DATE NOT NULL,
154  PRIMARY KEY (`User_Email`, `Book_ISBN`),
155  INDEX `fk_User_has_Book1_Book1_idx` (`Book_ISBN` ASC),
156  INDEX `fk_User_has_Book1_User1_idx` (`User_Email` ASC),
157  CONSTRAINT `fk_User_has_Book1_User1`
158    FOREIGN KEY (`User_Email`)
159    REFERENCES `mydb`.`User` (`Email`)
160    ON DELETE NO ACTION
161    ON UPDATE NO ACTION,
162  CONSTRAINT `fk_User_has_Book1_Book1`
163    FOREIGN KEY (`Book_ISBN`)
164    REFERENCES `mydb`.`Book` (`ISBN`)
165    ON DELETE NO ACTION
166    ON UPDATE NO ACTION)
167ENGINE = InnoDB;
168
169
170-- -----------------------------------------------------
171-- Table `mydb`.`Credit_card`
172-- -----------------------------------------------------
173CREATE TABLE IF NOT EXISTS `mydb`.`Credit_card` (
174  `ccn` INT NOT NULL,
175  `Expire_Date` DATE NOT NULL,
176  `User_Email` VARCHAR(15) NOT NULL,
177  PRIMARY KEY (`ccn`, `User_Email`),
178  INDEX `fk_Credit_card_User1_idx` (`User_Email` ASC),
179  CONSTRAINT `fk_Credit_card_User1`
180    FOREIGN KEY (`User_Email`)
181    REFERENCES `mydb`.`User` (`Email`)
182    ON DELETE NO ACTION
183    ON UPDATE NO ACTION)
184ENGINE = InnoDB;
185
186-- check email
187alter table User add constraint check_email check (Email like '%_@__%.__%');
188
189-- check max number of tuple in book
190DELIMITER $$
191
192CREATE TRIGGER Max_books
193BEFORE INSERT
194ON Book
195FOR EACH ROW
196BEGIN
197  SELECT COUNT(*) INTO @cnt FROM Book;
198  IF @cnt >= 999999 THEN
199    CALL raise_error;-- raise an error
200  END IF;
201END
202$$
203DELIMITER ;
204
205-- check max number of authors
206
207DELIMITER $$
208
209CREATE TRIGGER Avg_authors
210BEFORE INSERT
211ON Book_has_Author
212FOR EACH ROW
213BEGIN
214  SELECT COUNT(*) INTO @cnt FROM Book_has_Author;
215  IF @cnt >= 999999 THEN
216    CALL raise_error;-- raise an error
217  END IF;
218END
219$$
220DELIMITER ;
221
222
223-- check negativity of book count
224
225DELIMITER $$
226
227CREATE TRIGGER negative_constrain BEFORE update ON Book
228FOR EACH ROW BEGIN
229   IF (NEW.Quantity < 0) THEN
230         CALL raise_error;-- raise an error
231   END IF;
232END$$
233DELIMITER ;
234
235
236DELIMITER $$
237
238CREATE TRIGGER threshold AFTER update ON Book
239
240FOR EACH ROW BEGIN
241
242   IF (NEW.Quantity < OLD.Threshold) THEN
243         insert into mydb.Manager_Order (no_of_copies , Book_ISBN)
244         values(20 , OLD.ISBN);
245   END IF;
246END$$
247DELIMITER ;
248
249
250DELIMITER $$
251
252-- CREATE TRIGGER confirm_order BEFORE DELETE ON Manager_Order
253
254-- FOR EACH ROW BEGIN
255--    update Book set Quantity = OLD.no_of_copies + Quantity
256--    where OLD.Book_ISBN = ISBN;
257-- END$$
258-- DELIMITER ;
259-- 
260
261
262
263
264
265
266
267
268  
269  
270 
271   -- update Book set Quantity = 3 where ISBN = '2';
272-- insert into Manager_Order (no_of_copies ,Book_ISBN ,confirm) values (5,'1' , false) ;
273
274-- 	select Book_ISBN from mydb.Manager_Order where OrderID = 1 ;
275 -- select * from Book;
276 -- update Book  set Quantity =  Quantity + 1000 where ISBN = '1';
277
278
279SET SQL_MODE=@OLD_SQL_MODE;
280SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
281SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;