f8y1VYT3

1drop schema if exists BOOK_STORE;
2CREATE SCHEMA BOOK_STORE;
3USE `BOOK_STORE` ;
4
5
6-- -----------------------------------------------------
7-- Table `Book`
8-- -----------------------------------------------------
9CREATE TABLE IF NOT EXISTS `Book` ( /*done*/
10  `ISBN` VARCHAR(255) NOT NULL,
11  `Title` VARCHAR(255) NOT NULL,
12  `Publication_Year` INT NOT NULL,
13  `Price` INT NOT NULL,
14  `No_of_copies` INT NOT NULL,
15  `Threshold` INT NOT NULL,
16  UNIQUE INDEX `UNIQUE_ISBN` (`ISBN` ASC),
17  PRIMARY KEY (`ISBN`));
18
19-- -----------------------------------------------------
20-- Table `Publisher`
21-- -----------------------------------------------------
22CREATE TABLE IF NOT EXISTS `Publisher` (
23  `Publisher_Name` VARCHAR(255) NOT NULL,
24  `Address` VARCHAR(255) NOT NULL,
25  `phone` VARCHAR(255) NOT NULL,
26  `ISBN` VARCHAR(255) NOT NULL,
27  INDEX `fk_book_isbn` (`ISBN` ASC),
28  PRIMARY KEY (`Publisher_Name`,`ISBN`),
29  CONSTRAINT `FK_Publisher_BOOK`
30    FOREIGN KEY (`ISBN`)
31    REFERENCES `BOOK` (`ISBN`)
32    ON DELETE NO ACTION
33    ON UPDATE NO ACTION );
34 
35  
36-- -----------------------------------------------------
37-- Table `User`
38-- -----------------------------------------------------
39CREATE TABLE IF NOT EXISTS `User` ( /*done*/
40  `Fname` VARCHAR(255) NOT NULL,
41  `Lname` VARCHAR(255) NOT NULL,
42  `Username` VARCHAR(15) NOT NULL,
43  `Password` VARCHAR(255) NOT NULL,
44  `Email` VARCHAR(255) NOT NULL,
45  `Phone` VARCHAR(255) NOT NULL, 
46  `Shippingaddress` VARCHAR(255) NOT NULL,
47  `creditnum` VARCHAR(20) NOT NULL,
48  UNIQUE INDEX `UNIQUE_EMAIL` (`Email` ASC),
49  PRIMARY KEY (`Email`));
50  
51-- -----------------------------------------------------
52-- Table `manexger`
53-- -----------------------------------------------------
54CREATE TABLE IF NOT EXISTS `maneger` ( /*done*/
55  `Fname` VARCHAR(255) NOT NULL,
56  `Lname` VARCHAR(255) NOT NULL,
57  `Password` VARCHAR(255) NOT NULL,
58  `Email` VARCHAR(255) NOT NULL,
59  `Username` VARCHAR(15) NOT NULL,
60  `Phone` VARCHAR(255) NOT NULL, 
61  UNIQUE INDEX `UNIQUE_EMAIL` (`Email` ASC),
62  PRIMARY KEY (`Email`));
63  
64
65-- -----------------------------------------------------
66-- Table `AUTHORS`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `Category` (
69  `Category` VARCHAR(255) NOT NULL,
70  `ISBN` VARCHAR(255) NOT NULL,
71  PRIMARY KEY (`ISBN`, `Category`),
72  INDEX `fk_Categorys_BOOK` (`ISBN` ASC),
73  CONSTRAINT `fk_Book_has_Categorys_Book1`
74    FOREIGN KEY (`ISBN`)
75    REFERENCES `Book` (`ISBN`)
76    ON DELETE NO ACTION
77    ON UPDATE NO ACTION);
78
79-- -----------------------------------------------------
80-- Table `order_books`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `order_books` (
83  `no_of_copies` int NOT NULL,
84  `isbn` VARCHAR(255) NOT NULL,
85  `index` int NOT NULL,
86  PRIMARY KEY (`index`),
87  UNIQUE INDEX `UNIQUE_index` (`index` ASC),
88  CONSTRAINT `FK_order_books_BOOK`
89    FOREIGN KEY (`ISBN`)
90    REFERENCES `BOOK` (`ISBN`)
91    ON DELETE NO ACTION
92    ON UPDATE NO ACTION );
93    
94 -- -----------------------------------------------------
95-- Table `sold_productions`
96-- -----------------------------------------------------
97CREATE TABLE IF NOT EXISTS `sold_productions` (
98  `date` DATE NOT NULL,
99  `no_of_copies` int not null,
100  `ISBN` VARCHAR(255) NOT NULL,
101  `EMAIL` VARCHAR(255) NOT NULL,
102  `ID` INT NOT NULL,
103  PRIMARY KEY (`ID`),
104  UNIQUE INDEX `UNIQUE_id` (`ID` ASC),
105  INDEX `fk_ORDER_User_INDEX` (`EMAIL` ASC),
106  CONSTRAINT `FK_ORDER_User`
107  FOREIGN KEY (`EMAIL`)
108  REFERENCES `User` (`Email`)
109  ON DELETE NO ACTION
110  ON UPDATE NO ACTION,
111  CONSTRAINT `FK_ORDER_book`
112  FOREIGN KEY (`ISBN`)
113  REFERENCES `BOOK` (`ISBN`)
114  ON DELETE NO ACTION
115  ON UPDATE NO ACTION);
116    
117-- -----------------------------------------------------
118-- Table `shopping_card`
119-- -----------------------------------------------------
120CREATE TABLE IF NOT EXISTS `shopping_card` (/*done*/
121  `price` INT NOT NULL,
122  `num_of_copies` INT NOT NULL,
123  `ISBN` VARCHAR(255) NOT NULL,
124  `Email` VARCHAR(255) NOT NULL,
125  INDEX `fk_BASKET_Email` (`Email` ASC),
126  INDEX `fk_BASKET_BOOK` (`ISBN` ASC),
127  UNIQUE INDEX `UNIQUE_ID` (`ISBN` ASC),     /*to be changed*/
128  PRIMARY KEY (`Email`,`ISBN`),
129  CONSTRAINT `FK_BASKET_user`
130    FOREIGN KEY (`Email`)
131    REFERENCES `User` (`Email`)
132    ON DELETE NO ACTION
133    ON UPDATE NO ACTION,
134    CONSTRAINT `FK_BASKET_BOOK`
135    FOREIGN KEY (`ISBN`)
136    REFERENCES `BOOK` (`ISBN`)
137    ON DELETE NO ACTION
138    ON UPDATE NO ACTION  );    
139
140-- -----------------------------------------------------
141-- Table `book_authors`
142-- -----------------------------------------------------
143CREATE TABLE IF NOT EXISTS `book_authors` (
144  `Author_Name` VARCHAR(255) NOT NULL,
145  `ISBN` VARCHAR(255) NOT NULL,
146  PRIMARY KEY (`ISBN`, `Author_Name`),
147  INDEX `fk_AUTHORS_BOOK` (`ISBN` ASC),
148  CONSTRAINT `fk_Book_has_Author_Book1`
149    FOREIGN KEY (`ISBN`)
150    REFERENCES `Book` (`ISBN`)
151    ON DELETE NO ACTION
152    ON UPDATE NO ACTION);
153    
154Set foreign_key_checks = 0;
155    
156    Delimiter //
157-- NO of emplyee does not exceed 8, if more do not insert it
158create trigger book_update 
159before UPDATE  ON BOOK
160for each row 
161Begin 
162
163 IF (NEW.No_of_copies < 0 ) THEN
164         call error ;
165      END IF;
166End
167//
168select * from user;//
169-- insert into book values ('kimo','hima2','2012-01-01',50,15,20,'Science','hamada');//
170select * from book;//
171
172  select No_of_copies
173  from BOOK;//
174  
175  SET SQL_SAFE_UPDATES = 0 ;//
176
177 update  book SET No_of_copies =  No_of_copies - 10 ;//
178select * from maneger;//
179insert into BOOK_STORE.maneger values ('admin', 'admin','92668751', 'admin@admin.com','admin', '012');//
180
181select * from book;