· 8 years ago · Jun 09, 2018, 12:10 PM
1 drop schema if exists bookStore;
2create schema bookStore;
3use bookStore;
4
5create table Book (
6 BOOK_ID INT AUTO_INCREMENT,
7 ISBN varchar(15) PRIMARY key,
8 Title varchar(40) not null,
9 Pub_Id int not null,
10
11 -- Pub_name varchar(40) not null,
12 Pub_year year,
13 Price double not null,
14 Cat_id int not null,
15 Copies int not null,
16 Threshold int not null);
17
18create table Book_Authors (
19 ISBN varchar(15) not null,
20 Author_name varchar(40) not null,
21
22 constraint pk primary key (ISBN, Author_name));
23
24create table Publisher (
25 Pub_Id int ,
26
27 Pub_name varchar(40) ,
28 Address varchar(40) not null,
29 Phone varchar(15),
30 constraint pk2 primary key (Pub_Id, Pub_name)
31
32 );
33
34create table Book_Order (
35 ISBN varchar(15) primary key,
36 Copies int not null,
37 Accepted boolean not null);
38
39create table Library_User (
40 User_Id int AUTO_INCREMENT,
41 Username varchar(40) not null,
42
43 F_Name varchar(40) not null,
44 L_Name varchar(40) not null,
45 Email varchar(40) not null unique,
46 User_Password varchar(40) not null,
47 Phone varchar(15),
48 Address varchar(40),
49 Is_Manager boolean default 0,
50 constraint pk5 primary key (User_Id));
51
52
53
54
55create table Category (
56 Cat_Name varchar(40) not null unique,
57 Cat_Id int primary key auto_increment);
58
59create table Sales (
60 User_Id int ,
61 -- Username varchar(40) not null,
62 ISBN varchar(15) not null,
63 Copies int not null,
64 Sell_Date date not null,
65 Price double not null,
66 constraint pk primary key (User_Id, ISBN, Sell_Date));
67
68alter table Book_Authors add constraint fk1 foreign key (ISBN) references Book(ISBN) ON UPDATE CASCADE;
69alter table Book add constraint fk2 foreign key (Pub_Id) references Publisher(Pub_Id) ON UPDATE CASCADE;
70alter table Book add constraint fk3 foreign key (Cat_id) references Category(Cat_Id) ON UPDATE CASCADE;
71alter table Book_Order add constraint fk4 foreign key (ISBN) references Book(ISBN) ON UPDATE CASCADE;
72alter table Sales add constraint fk5 foreign key (ISBN) references Book(ISBN) ON UPDATE CASCADE;
73alter table Sales add constraint fk6 foreign key (User_Id) references Library_User(User_Id) ON UPDATE CASCADE;
74
75
76DELIMITER $$
77
78DROP TRIGGER IF EXISTS bookStore.Threshold_Check$$
79USE `bookStore`$$
80CREATE DEFINER = CURRENT_USER TRIGGER `bookStore`.`Threshold_Check` AFTER UPDATE ON `Book` FOR EACH ROW
81BEGIN
82
83declare difference int;
84declare testISBN VARCHAR(15);
85set testISBN = '';
86if NEW.Copies < NEW.Threshold then
87 set difference = NEW.Threshold - NEW.Copies;
88 select ISBN into testISBN from Book_Order where ISBN = NEW.ISBN;
89 if (testISBN = '') then
90 insert into Book_Order values (NEW.ISBN, difference, False);
91 else
92 update Book_Order set Copies = Copies + difference where ISBN = NEW.ISBN;
93 end if;
94end if;
95
96END
97$$
98DELIMITER ;
99
100
101DELIMITER $$
102
103DROP TRIGGER IF EXISTS bookStore.Copies_Check$$
104USE `bookStore`$$
105CREATE DEFINER = CURRENT_USER TRIGGER `bookStore`.`Copies_Check` BEFORE UPDATE ON `Book` FOR EACH ROW
106BEGIN
107
108if NEW.Copies < 0 then
109 SIGNAL SQLSTATE '45000'
110 SET MESSAGE_TEXT = 'Available number of copies is not sufficient !';
111end if;
112
113END
114$$
115DELIMITER ;
116
117
118
119
120DELIMITER $$
121
122DROP TRIGGER IF EXISTS bookStore.Modefied_Copies$$
123USE `bookStore`$$
124CREATE DEFINER = CURRENT_USER TRIGGER `bookStore`.`Modefied_Copies` BEFORE DELETE ON `Book_Order` FOR EACH ROW
125BEGIN
126
127if OLD.Accepted then
128 update book as b
129 set b.Copies = b.Copies + OLD.Copies
130 where b.ISBN = OLD.ISBN;
131end if;
132
133END
134$$
135DELIMITER ;
136
137delete from Library_User where User_Id = 3 ;
138INSERT INTO Library_User (Username, F_Name, L_Name, Email, User_Password, Phone, Address, is_manager) VALUES ('admin','aya', 'lotfy', 'aialotf2014@gmail.com','1234', '01221930396', 'Alexandria, Egypt', true);
139
140INSERT INTO Library_User (Username, F_Name, L_Name, Email, User_Password, Phone, Address) VALUES ('salmayehia','salma','yehia','sdvohsophvo','1234','iegfiowe','null');
141
142select * from library_user;
143
144select * from library_user;
145
146SELECT * FROM Library_User WHERE Username='admin' AND User_Password = '1234';
147
148
149insert into category values ("comedy", 1), ("action", 2), ("fiction", 3) , ("drama", 4);
150SELECT Cat_name from Category;
151
152insert into publisher values ( 5, "basha", "address of publlisger", "31546546");
153
154 INSERT INTO Book VALUES ('isbn2','iogoigoi',(SELECT PUB_ID FROM PUBLISHER WHERE PUB_NAME = 'basha'),1996,'1234.0',(SELECT Cat_Id FROM CATEGORY WHERE CAT_NAME = 'action'),'464','25');
155
156
157select * from book;