bbLCBgka

1drop schema if exists BOOK_STORE;
2CREATE SCHEMA BOOK_STORE;
3USE `BOOK_STORE` ;
4
5-- -----------------------------------------------------
6-- Table `Publisher`
7-- -----------------------------------------------------
8CREATE TABLE IF NOT EXISTS `Publisher` (
9  `Name` VARCHAR(255) NOT NULL,
10  `Address` VARCHAR(255) NOT NULL,
11  `Telephone` VARCHAR(255) NOT NULL,
12  PRIMARY KEY (`Name`));
13 
14  
15-- -----------------------------------------------------
16-- Table `CUSTOMER`
17-- -----------------------------------------------------
18CREATE TABLE IF NOT EXISTS `CUSTOMER` (
19  `Email` VARCHAR(255) NOT NULL,
20  `Fname` VARCHAR(255) NOT NULL,
21  `Lname` VARCHAR(255) NOT NULL,
22  `address` VARCHAR(255) NOT NULL,
23  `Password` VARCHAR(255) NOT NULL,
24  `Phone_number` VARCHAR(255) NOT NULL, 
25  PRIMARY KEY (`Email`));
26  
27
28-- -----------------------------------------------------
29-- Table `Book`
30-- -----------------------------------------------------
31CREATE TABLE IF NOT EXISTS `Book` (
32  `ISBN` VARCHAR(255) NOT NULL,
33  `Title` VARCHAR(255) NOT NULL,
34  `Price` INT NOT NULL,
35  `Category` VARCHAR(255) NOT NULL check(Category IN('Science','Art','Religion','History','Geography')),
36  `Publication_Year` DATE NOT NULL,
37  `Threshold` INT NOT NULL,
38  `Quantity` INT NOT NULL,
39  `Publisher_Name` VARCHAR(255) NOT NULL,
40  PRIMARY KEY (`ISBN`),
41  CONSTRAINT `fk_Book_Publisher`
42    FOREIGN KEY (`Publisher_Name`)
43    REFERENCES `Publisher` (`Name`)
44    ON DELETE NO ACTION
45    ON UPDATE NO ACTION);
46    
47-- -----------------------------------------------------
48-- Table `ORDER`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `ORDER` (
51  `ID` INT NOT NULL,
52  `CUSTOMER_EMAIL` VARCHAR(255) NOT NULL,
53  `date` DATE NOT NULL,
54  PRIMARY KEY (`ID`),
55  CONSTRAINT `FK_ORDER_CUSTOMER`
56    FOREIGN KEY (`CUSTOMER_EMAIL`)
57    REFERENCES `CUSTOMER` (`Email`)
58    ON DELETE NO ACTION
59    ON UPDATE NO ACTION);
60    
61-- -----------------------------------------------------
62-- Table `ORDER`
63-- -----------------------------------------------------
64CREATE TABLE IF NOT EXISTS `BASKET` (
65  `ORDER_ID` INT NOT NULL,
66  `BOOK_ISBN` VARCHAR(255) NOT NULL,
67  `NO_OF_COPIES` INT NOT NULL,
68  PRIMARY KEY (`ORDER_ID`,`BOOK_ISBN`),
69  CONSTRAINT `FK_BASKET_ORDER`
70    FOREIGN KEY (`ORDER_ID`)
71    REFERENCES `ORDER` (`ID`)
72    ON DELETE NO ACTION
73    ON UPDATE NO ACTION,
74    CONSTRAINT `FK_BASKET_BOOK`
75    FOREIGN KEY (`BOOK_ISBN`)
76    REFERENCES `BOOK` (`ISBN`)
77    ON DELETE NO ACTION
78    ON UPDATE NO ACTION  );    
79
80-- -----------------------------------------------------
81-- Table `AUTHORS`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `AUTHORS` (
84  `Book_ISBN` VARCHAR(255) NOT NULL,
85  `Author_Name` VARCHAR(255) NOT NULL,
86  PRIMARY KEY (`Book_ISBN`, `Author_Name`),
87  CONSTRAINT `fk_Book_has_Author_Book1`
88    FOREIGN KEY (`Book_ISBN`)
89    REFERENCES `Book` (`ISBN`)
90    ON DELETE NO ACTION
91    ON UPDATE NO ACTION);