· 8 years ago · Jun 09, 2018, 07:58 PM
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 (`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 `expiry` DATE NOT NULL,
49 UNIQUE INDEX `UNIQUE_EMAIL` (`Email` ASC),
50 PRIMARY KEY (`Email`));
51
52-- -----------------------------------------------------
53-- Table `manexger`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `maneger` ( /*done*/
56 `Fname` VARCHAR(255) NOT NULL,
57 `Lname` VARCHAR(255) NOT NULL,
58 `Password` VARCHAR(255) NOT NULL,
59 `Email` VARCHAR(255) NOT NULL,
60 `Username` VARCHAR(15) NOT NULL,
61 `Phone` VARCHAR(255) NOT NULL,
62 UNIQUE INDEX `UNIQUE_EMAIL` (`Email` ASC),
63 PRIMARY KEY (`Email`));
64
65
66-- -----------------------------------------------------
67-- Table `AUTHORS`
68-- -----------------------------------------------------
69CREATE TABLE IF NOT EXISTS `Category` (
70 `Category` VARCHAR(255) NOT NULL,
71 `ISBN` VARCHAR(255) NOT NULL,
72 PRIMARY KEY (`ISBN`, `Category`),
73 INDEX `fk_Categorys_BOOK` (`ISBN` ASC),
74 CONSTRAINT `fk_Book_has_Categorys_Book1`
75 FOREIGN KEY (`ISBN`)
76 REFERENCES `Book` (`ISBN`)
77 ON DELETE NO ACTION
78 ON UPDATE NO ACTION);
79
80-- -----------------------------------------------------
81-- Table `order_books`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `order_books` (
84 `no_of_copies` int NOT NULL,
85 `isbn` VARCHAR(255) NOT NULL,
86 `index` int NOT NULL,
87 PRIMARY KEY (`index`),
88 UNIQUE INDEX `UNIQUE_index` (`index` ASC),
89 CONSTRAINT `FK_order_books_BOOK`
90 FOREIGN KEY (`ISBN`)
91 REFERENCES `BOOK` (`ISBN`)
92 ON DELETE NO ACTION
93 ON UPDATE NO ACTION );
94
95 -- -----------------------------------------------------
96-- Table `sold_productions`
97-- -----------------------------------------------------
98CREATE TABLE IF NOT EXISTS `sold_productions` (
99 `date` DATE NOT NULL,
100 `no_of_copies` int not null,
101 `ISBN` VARCHAR(255) NOT NULL,
102 `EMAIL` VARCHAR(255) NOT NULL,
103 `ID` INT NOT NULL,
104 PRIMARY KEY (`ID`),
105 UNIQUE INDEX `UNIQUE_id` (`ID` ASC),
106 INDEX `fk_ORDER_User_INDEX` (`EMAIL` ASC),
107 CONSTRAINT `FK_ORDER_User`
108 FOREIGN KEY (`EMAIL`)
109 REFERENCES `User` (`Email`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION,
112 CONSTRAINT `FK_ORDER_book`
113 FOREIGN KEY (`ISBN`)
114 REFERENCES `BOOK` (`ISBN`)
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION);
117
118-- -----------------------------------------------------
119-- Table `shopping_card`
120-- -----------------------------------------------------
121CREATE TABLE IF NOT EXISTS `shopping_card` (/*done*/
122 `price` INT NOT NULL,
123 `no_of_copies` INT NOT NULL,
124 `ISBN` VARCHAR(255) NOT NULL,
125 `Email` VARCHAR(255) NOT NULL,
126 INDEX `fk_BASKET_Email` (`Email` ASC),
127 INDEX `fk_BASKET_BOOK` (`ISBN` ASC),
128 UNIQUE INDEX `UNIQUE_ID` (`ISBN` ASC), /*to be changed*/
129 PRIMARY KEY (`Email`,`ISBN`),
130 CONSTRAINT `FK_BASKET_user`
131 FOREIGN KEY (`Email`)
132 REFERENCES `User` (`Email`)
133 ON DELETE NO ACTION
134 ON UPDATE NO ACTION,
135 CONSTRAINT `FK_BASKET_BOOK`
136 FOREIGN KEY (`ISBN`)
137 REFERENCES `BOOK` (`ISBN`)
138 ON DELETE NO ACTION
139 ON UPDATE NO ACTION );
140
141-- -----------------------------------------------------
142-- Table `book_authors`
143-- -----------------------------------------------------
144CREATE TABLE IF NOT EXISTS `book_authors` (
145 `Author_Name` VARCHAR(255) NOT NULL,
146 `ISBN` VARCHAR(255) NOT NULL,
147 PRIMARY KEY (`ISBN`, `Author_Name`),
148 INDEX `fk_AUTHORS_BOOK` (`ISBN` ASC),
149 CONSTRAINT `fk_Book_has_Author_Book1`
150 FOREIGN KEY (`ISBN`)
151 REFERENCES `Book` (`ISBN`)
152 ON DELETE NO ACTION
153 ON UPDATE NO ACTION);
154
155Set foreign_key_checks = 0;
156
157 Delimiter //
158-- NO of emplyee does not exceed 8, if more do not insert it
159create trigger book_update
160before UPDATE ON BOOK
161for each row
162Begin
163
164 IF (NEW.No_of_copies < 0 ) THEN
165 call error ;
166 END IF;
167End
168//
169select * from user;//
170-- insert into book values ('kimo','hima2','2012-01-01',50,15,20,'Science','hamada');//
171select * from book;//
172
173 select No_of_copies
174 from BOOK;//
175
176 SET SQL_SAFE_UPDATES = 0 ;//
177
178update book SET No_of_copies = No_of_copies + 10 ;//
179select * from maneger;//
180insert into BOOK_STORE.maneger values ('admin', 'admin','92668751', 'admin','admin', '012');//
181insert into BOOK_STORE.User values ('admin', 'admin','admin', '92668751', 'admin', '012','admin street', 1234567890123, '2018-12-12');//
182
183select * from book;//
184select * from book_authors;//
185select * from publisher;//
186select * from category;//
187select * from order_books;//
188select * from shopping_card;//
189select * from User;//
190select BOOK.ISBN, Title, Publication_year, Price, No_of_copies, publisher_name, author_name, Category from BOOK_STORE.BOOK join BOOK_STORE.PUBLISHER on BOOK.ISBN = PUBLISHER.ISBN join BOOK_STORE.BOOK_AUTHORS on PUBLISHER.ISBN = BOOK_AUTHORS.ISBN join BOOK_STORE.CATEGORY on BOOK_AUTHORS.ISBN = CATEGORY.ISBN where BOOK.ISBN = '1996';//
191select * from sold_productions;//
192select BOOK.ISBN, Title, Publication_year, Price, No_of_copies, publisher_name, author_name, Category from BOOK_STORE.BOOK join BOOK_STORE.PUBLISHER on BOOK.ISBN = PUBLISHER.ISBN join BOOK_STORE.BOOK_AUTHORS on PUBLISHER.ISBN = BOOK_AUTHORS.ISBN join BOOK_STORE.CATEGORY on BOOK_AUTHORS.ISBN = CATEGORY.ISBN where BOOK.ISBN = '1996';