CREATE TABLE `Author` (
`AuthorId` int NOT NULL,
`AuthorName` varchar(127) NOT NULL
);
CREATE TABLE `Book` (
`BookId` int NOT NULL,
`Name` varchar(100),
`Publisher` varchar(127) NOT NULL,
`Rating` float NOT NULL DEFAULT '5',
`Reviews` int NOT NULL
);
CREATE TABLE `BookAddition` (
`Id` int NOT NULL,
`BranchId` int NOT NULL,
`BookId` int NOT NULL,
`Copies` int NOT NULL
);
CREATE TABLE `BookAuthor` (
`Id` int NOT NULL,
`AuthorId` int NOT NULL,
`BookId` int NOT NULL
);
CREATE TABLE `BookCategory` (
`Id` int NOT NULL,
`BookId` int NOT NULL,
`Category` varchar(127),
`AuthorPercentage` int NOT NULL,
`Reviews` int NOT NULL DEFAULT '0',
`UserRating` float NOT NULL DEFAULT '5'
);
CREATE TABLE `BookCategoryRating` (
`Id` int NOT NULL,
`BorrowerId` int NOT NULL,
`BookId` int NOT NULL,
`BookCategory` int DEFAULT NULL,
`Rating` int NOT NULL,
`Comments` varchar(1023) NOT NULL
);
CREATE TABLE `BookLoan` (
`Id` int NOT NULL,
`BookId` int DEFAULT NULL,
`BranchId` int NOT NULL,
`CardNo` int NOT NULL,
`DateOut` date NOT NULL,
`DueDate` date NOT NULL,
`ReturnDate` date DEFAULT NULL
);
CREATE TABLE `BookRating` (
`Id` int NOT NULL,
`BorrowerId` int NOT NULL,
`BookId` int NOT NULL,
`Rating` int NOT NULL,
`Comments` varchar(1023) NOT NULL
);
CREATE TABLE `BookStock` (
`Id` int NOT NULL,
`BookId` int NOT NULL,
`BranchId` int NOT NULL,
`Copies` int NOT NULL
);
CREATE TABLE `Borrower` (
`CardNo` int NOT NULL,
`Name` varchar(127) NOT NULL,
`Address` varchar(255) NOT NULL,
`Phone` varchar(63) NOT NULL
);
CREATE TABLE `Category` (
`Name` varchar(127) NOT NULL
);
CREATE TABLE `LibraryBranch` (
`BranchId` int NOT NULL,
`BranchName` varchar(127) NOT NULL,
`Address` varchar(255) NOT NULL
);
CREATE TABLE `Publisher` (
`Name` varchar(127) NOT NULL,
`Address` varchar(255) NOT NULL,
`Phone` varchar(63) NOT NULL
);
DELIMITER $$
drop TRIGGER if exists `AddToBookStock`;
CREATE TRIGGER `AddToBookStock` AFTER INSERT ON `BookAddition`
FOR EACH ROW
BEGIN
Update BookStock set Copies = Copies+new.copies
WHERE BranchId=new.BranchId and BookId=new.BookId;
END$$
;
drop TRIGGER if exists `DeleteFromBookStock`;
CREATE TRIGGER `DeleteFromBookStock` AFTER DELETE ON `BookAddition`
FOR EACH ROW
BEGIN
UPDATE BookStock set Copies = Copies-old.copies
WHERE BranchId=old.BranchId and BookId=old.BookId;
END$$
;
drop TRIGGER if exists `OnBooksMinused`;
CREATE TRIGGER `OnBooksMinused` AFTER DELETE ON `BookAddition`
FOR EACH ROW
BEGIN
UPDATE BookStock set Copies = Copies - old.copies
WHERE BranchId=old.BranchId and BookId=old.BookId;
END$$
;
drop TRIGGER if exists `OnBookLoanUpdated`;
CREATE TRIGGER `OnBookLoanUpdated` AFTER UPDATE ON `BookLoan`
FOR EACH ROW
BEGIN
UPDATE BookStock set Copies = Copies + 1
WHERE BranchId=old.BranchId and BookId=old.BookId;
UPDATE BookStock set Copies = Copies - 1
WHERE BranchId=new.BranchId and BookId=new.BookId;
END$$
;
drop TRIGGER if exists `ReturnToBookStock`;
CREATE TRIGGER `ReturnToBookStock` AFTER DELETE ON `BookLoan`
FOR EACH ROW
BEGIN
UPDATE BookStock set Copies = Copies+1
WHERE BranchId=old.BranchId and BookId=old.BookId;
END$$
;
drop TRIGGER if exists `TakeBookFromStock`;
CREATE TRIGGER `TakeBookFromStock` AFTER INSERT ON `BookLoan`
FOR EACH ROW
BEGIN
Update BookStock set Copies = Copies-1
WHERE BranchId=new.BranchId and BookId=new.BookId;
END
$$
;
drop function if exists `get_book_category_rating`;
CREATE FUNCTION `get_book_category_rating`(`bid` INT, `category` VARCHAR(127)) RETURNS int
NO SQL
BEGIN
DECLARE res int;
declare sum_rate int;
DECLARE cnt_rate int;
select sum(rating) into sum_rate from BookCategoryRating
where BookId=bid and BookCategory=category;
select count(rating) into cnt_rate from BookCategoryRating
where BookId=bid and BookCategory=category;
set res=sum_rate/cnt_rate;
RETURN res;
END$$
;
drop function if exists `get_book_rating`;
CREATE FUNCTION `get_book_rating`(`bid` INT) RETURNS int
NO SQL
BEGIN
DECLARE res int;
declare sum_rate int;
DECLARE cnt_rate int;
select sum(rating) into sum_rate from BookRating where BookId=bid;
select count(rating) into cnt_rate from BookRating where BookId=bid;
set res=sum_rate/cnt_rate;
RETURN res;
END$$
DELIMITER ;
Telegram: DB developers channel