FINALSCHEMA.sql

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
DROP SCHEMA `BOOK_STORE`;
CREATE SCHEMA `BOOK_STORE` DEFAULT CHARACTER SET latin1 ;
USE `BOOK_STORE` ;

-- -----------------------------------------------------
-- Table `BOOK_STORE`.`PUBLISHER`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `BOOK_STORE`.`PUBLISHER` (
  `NAME` VARCHAR(45) NOT NULL,
  `ADDRESS` VARCHAR(45) NOT NULL,
  `TELEPHONE` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`NAME`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `BOOK_STORE`.`BOOK`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `BOOK_STORE`.`BOOK` (
  `ISBN` VARCHAR(45) NOT NULL,
  `TITLE` VARCHAR(45) NOT NULL,
  `SELLING_PRICE` FLOAT NOT NULL DEFAULT '0',
  `CATEGORY` VARCHAR(45) NOT NULL,
  `THRESHOLD` INT(11) NOT NULL DEFAULT '1',
  `COPIES` INT(11) NOT NULL DEFAULT '0',
  `PUBLISHER` VARCHAR(45) NOT NULL,
  `PUBLICATION_YEAR` DATE NULL DEFAULT NULL,
  `AUTHOR` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ISBN`),
  UNIQUE INDEX `title_UNIQUE` (`TITLE` ASC),
  INDEX `fk_BOOK_1_idx` (`PUBLISHER` ASC),
  INDEX `index4` (`CATEGORY` ASC),
  CONSTRAINT `fk_BOOK_1`
    FOREIGN KEY (`PUBLISHER`)
    REFERENCES `BOOK_STORE`.`PUBLISHER` (`NAME`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;






-- -----------------------------------------------------
-- Table `BOOK_STORE`.`ORDER`
-- -----------------------------------------------ISBNFULL_NAME------
CREATE TABLE IF NOT EXISTS `BOOK_STORE`.`ORDER` (
  `ORDER_ID` INT(11) NOT NULL AUTO_INCREMENT,
  `ISBN` VARCHAR(45) NOT NULL,
  `QUANTITY` INT(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ORDER_ID`, `ISBN`),
  INDEX `fk_ORDER_1_idx` (`ISBN` ASC),
  CONSTRAINT `fk_ORDER_1`
    FOREIGN KEY (`ISBN`)
    REFERENCES `BOOK_STORE`.`BOOK` (`ISBN`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `BOOK_STORE`.`USER`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `BOOK_STORE`.`USER` (
  `USER_NAME` VARCHAR(45) NOT NULL,
  `F_NAME` VARCHAR(45) NOT NULL,
  `L_NAME` VARCHAR(45) NOT NULL,
  `PASSWORD` VARCHAR(45) NOT NULL,
  `EMAIL` VARCHAR(45) NOT NULL,
  `TELEPHONE` VARCHAR(45) NOT NULL,
  `ADDRESS` VARCHAR(45) NOT NULL,
  `PRIVILAGE` TINYINT(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`USER_NAME`),
  UNIQUE INDEX `EMAIL_UNIQUE` (`EMAIL` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `BOOK_STORE`.`SALES`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `BOOK_STORE`.`SALES` (
  `SALES_ID` INT NOT NULL AUTO_INCREMENT,
  `ISBN` VARCHAR(45) NOT NULL,
  `USER_NAME` VARCHAR(45) NOT NULL,
  `DATE` DATE NOT NULL,
  `QUANTITY` INT NOT NULL,
  PRIMARY KEY (`SALES_ID`, `ISBN`, `USER_NAME`),
  INDEX `fk_SALES_1_idx` (`ISBN` ASC),
  INDEX `fk_SALES_2_idx` (`USER_NAME` ASC),
  CONSTRAINT `fk_SALES_1`
    FOREIGN KEY (`ISBN`)
    REFERENCES `BOOK_STORE`.`BOOK` (`ISBN`)
    ON DELETE  CASCADE
    ON UPDATE  CASCADE,
  CONSTRAINT `fk_SALES_2`
    FOREIGN KEY (`USER_NAME`)
    REFERENCES `BOOK_STORE`.`USER` (`USER_NAME`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
USE `BOOK_STORE`;

DELIMITER $$
USE `BOOK_STORE`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `BOOK_STORE`.`before_publisher_insert`
BEFORE INSERT ON `BOOK_STORE`.`PUBLISHER`
FOR EACH ROW
BEGIN 
	IF (NEW.NAME NOT REGEXP '^[a-zA-Z]{1,}') THEN 
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID NAME';
	END IF;
	IF (NEW.ADDRESS NOT REGEXP '[a-zA-Z0-9]{1,}') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID ADDRESS';
	END IF;
 	IF (NEW.TELEPHONE NOT REGEXP '[0-9]{11}') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID PHONE NUMBER';
	END IF;
END$$

USE `BOOK_STORE`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `BOOK_STORE`.`before_publisher_update`
BEFORE UPDATE ON `BOOK_STORE`.`PUBLISHER`
FOR EACH ROW
BEGIN 
	IF (NEW.NAME NOT REGEXP '^[a-zA-Z]{1,}') THEN 
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID NAME';
	END IF;
	IF (NEW.ADDRESS NOT REGEXP '[a-zA-Z0-9]{1,}') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID ADDRESS';
	END IF;
 	IF (NEW.TELEPHONE NOT REGEXP '[0-9]{11}') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID PHONE NUMBER';
	END IF;
END$$

USE `BOOK_STORE`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `BOOK_STORE`.`after_book_update`
AFTER UPDATE ON `BOOK_STORE`.`BOOK`
FOR EACH ROW
BEGIN
  IF NEW.COPIES < new.threshold AND OLD.COPIES > NEW.COPIES THEN
    IF OLD.THRESHOLD < OLD.COPIES THEN
		INSERT INTO `ORDER`
    	(ISBN , QUANTITY) VALUES ( NEW.ISBN , OLD.THRESHOLD - NEW.COPIES ) ;
  	ELSE
  		INSERT INTO `ORDER`
    	(ISBN , QUANTITY) VALUES ( NEW.ISBN , OLD.COPIES - NEW.COPIES ) ;
  	END IF;		
  END IF;
END$$

USE `BOOK_STORE`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `BOOK_STORE`.`before_book_insert`
BEFORE INSERT ON `BOOK_STORE`.`BOOK`
FOR EACH ROW
BEGIN
  IF NEW.ISBN NOT REGEXP '^[0-9]{1,}' THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'INVALID ISBN';
  END IF;
  IF NEW.TITLE NOT REGEXP '^[a-zA-Z0-9]{1,}' THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'INVALID TITLE';
  END IF;
  IF (NEW.CATEGORY NOT LIKE 'science' 
		AND NEW.CATEGORY NOT LIKE 'art'
		AND NEW.CATEGORY NOT LIKE 'religion'
		AND NEW.CATEGORY NOT LIKE 'history'
		AND NEW.CATEGORY NOT LIKE 'geography') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID CATEGORY';
  END IF;
  IF NEW.THRESHOLD < 0 THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'NEGATIVE THRESHOLD';
  END IF;
  IF NEW.COPIES < NEW.THRESHOLD THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'NUMBER OF COPIES LESS THAN THRESHOLD';
  END IF;
  IF NEW.SELLING_PRICE < 0 THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'NEGATIVE SELLING PRICE';
  END IF;
END$$

USE `BOOK_STORE`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `BOOK_STORE`.`before_book_update`
BEFORE UPDATE ON `BOOK_STORE`.`BOOK`
FOR EACH ROW
BEGIN
  IF NEW.ISBN NOT REGEXP '^[0-9]{1,}' THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'INVALID ISBN';
  END IF;
  IF NEW.TITLE NOT REGEXP '^[a-zA-Z0-9]{1,}' THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'INVALID TITLE';
  END IF;
  IF (NEW.CATEGORY NOT LIKE 'science' 
		AND NEW.CATEGORY NOT LIKE 'art'
		AND NEW.CATEGORY NOT LIKE 'religion'
		AND NEW.CATEGORY NOT LIKE 'history'
		AND NEW.CATEGORY NOT LIKE 'geography') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID CATEGORY';
  END IF;
  IF NEW.COPIES < 0 THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'NEGATIVE NUMBER OF COPIES';
  END IF;
  IF NEW.THRESHOLD < 0 THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'NEGATIVE THRESHOLD';
  END IF;
  IF NEW.SELLING_PRICE < 0 THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'NEGATIVE SELLING PRICE';
  END IF;
END$$



USE `BOOK_STORE`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `BOOK_STORE`.`before_order_delete`
BEFORE DELETE ON `BOOK_STORE`.`ORDER`
FOR EACH ROW
BEGIN
  UPDATE `BOOK` SET COPIES = COPIES + OLD.QUANTITY WHERE BOOK.ISBN = OLD.ISBN;
END$$

USE `BOOK_STORE`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `BOOK_STORE`.`before_order_insert`
BEFORE INSERT ON `BOOK_STORE`.`ORDER`
FOR EACH ROW
BEGIN
  IF NEW.QUANTITY < 0 THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'NEGATIVE NUMBER OF QUANTITY';
  END IF;
END$$

USE `BOOK_STORE`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `BOOK_STORE`.`before_order_update`
BEFORE UPDATE ON `BOOK_STORE`.`ORDER`
FOR EACH ROW
BEGIN
  IF NEW.QUANTITY < 0 THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'NEGATIVE NUMBER OF QUANTITY';
  END IF;
END$$

USE `BOOK_STORE`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `BOOK_STORE`.`before_user_insert`
BEFORE INSERT ON `BOOK_STORE`.`USER`
FOR EACH ROW
BEGIN 
 	IF (NEW.USER_NAME NOT REGEXP '^[a-zA-Z]+') THEN 
 		SIGNAL SQLSTATE '45000' 
 		SET MESSAGE_TEXT = 'INVALID USER_NAME';
 	END IF;
 	IF (NEW.F_NAME NOT REGEXP '^[a-zA-Z]{1,}') THEN 
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID FIRST_NAME';
	END IF;
 	IF (NEW.L_NAME NOT REGEXP '^[a-zA-Z]{1,}') THEN 
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID LAST_NAME';
	END IF;
 	IF (NEW.EMAIL NOT REGEXP '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,6}$') THEN
 		SIGNAL SQLSTATE '45000' 
 		SET MESSAGE_TEXT = 'INVALID EMAIL';
 	END IF;
	IF (NEW.PASSWORD NOT REGEXP '^.*[0-9]') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'PASSWORD MUST INCLUDE AT LEAST ONE DIGIT';
	END IF;
	IF (NEW.PASSWORD NOT REGEXP '^[a-zA-Z0-9]{7,}$') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'PASSWORD HAS NO SPECIAL CHARS AND AT LEAST 8 CHARS';
	END IF;
	IF (NEW.TELEPHONE NOT REGEXP '[0-9]{11}') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID PHONE NUMBER';
	END IF;
	IF (NEW.ADDRESS NOT REGEXP '[a-zA-Z0-9]{1,}') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID ADDRESS';
	END IF;
END$$

USE `BOOK_STORE`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `BOOK_STORE`.`before_user_update`
BEFORE UPDATE ON `BOOK_STORE`.`USER`
FOR EACH ROW
BEGIN 
 	IF (NEW.USER_NAME NOT REGEXP '^[a-zA-Z]+') THEN 
 		SIGNAL SQLSTATE '45000' 
 		SET MESSAGE_TEXT = 'INVALID USER_NAME';
 	END IF;
	IF (NEW.F_NAME NOT REGEXP '^[a-zA-Z]{1,}') THEN 
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID FIRST_NAME';
	END IF;
 	IF (NEW.L_NAME NOT REGEXP '^[a-zA-Z]{1,}') THEN 
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID LAST_NAME';
	END IF;
 	IF (NEW.EMAIL NOT REGEXP '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,6}$') THEN
 		SIGNAL SQLSTATE '45000' 
 		SET MESSAGE_TEXT = 'INVALID EMAIL';
 	END IF;
	IF (NEW.PASSWORD NOT REGEXP '^.*[0-9]') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'PASSWORD MUST INCLUDE AT LEAST ONE DIGIT';
	END IF;
	IF (NEW.PASSWORD NOT REGEXP '^[a-zA-Z0-9]{7,}$') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'PASSWORD HAS NO SPECIAL CHARS AND AT LEAST 8 CHARS';
	END IF;
	IF (NEW.TELEPHONE NOT REGEXP '[0-9]{11}') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID PHONE NUMBER';
	END IF;
	IF (NEW.ADDRESS NOT REGEXP '[a-zA-Z0-9]{1,}') THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'INVALID ADDRESS';
	END IF;
END$$


DELIMITER ;