-- DDL Script for generating the database RecommendationSystem

  -- Deletion of previous version of the tables 
  SET FOREIGN_KEY_CHECKS=0;
  DROP TABLE IF EXISTS Book_rt,Customer_rt,Recommends_rt;
  SET FOREIGN_KEY_CHECKS=1;

  -- Table for storing the data of  Book
  CREATE TABLE Book_rt(
    id INTEGER(5) NOT NULL AUTO_INCREMENT,
    title VARCHAR(40) NOT NULL ,
    published DATE NOT NULL ,
    numberPages INTEGER(5) NOT NULL ,
    CONSTRAINT pk_Book PRIMARY KEY (id)
  );

  -- Table for storing the data of  Customer
  CREATE TABLE Customer_rt(
    id INTEGER(5) NOT NULL AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL ,
    CONSTRAINT pk_Customer PRIMARY KEY (id)
  );

  -- Table for storing the data of  Recommends
  CREATE TABLE Recommends_rt(
    id INTEGER(5) NOT NULL AUTO_INCREMENT,
    -- Attribute for storing the data of customerRecommends
    customer INTEGER(5) NOT NULL ,
    -- Attribute for storing the data of bookRecommends
    book INTEGER(5) NOT NULL ,
    CONSTRAINT pk_Recommends PRIMARY KEY (id),
    CONSTRAINT u_Recommends1 UNIQUE (customer,book)
  );

  ALTER TABLE Recommends_rt
    ADD CONSTRAINT fk_RecommendsToCustomer_customer FOREIGN KEY(customer)
      REFERENCES Customer_rt(id)
      ON DELETE restrict
      ON UPDATE cascade;

  ALTER TABLE Recommends_rt
    ADD CONSTRAINT fk_RecommendsToBook_book FOREIGN KEY(book)
      REFERENCES Book_rt(id)
      ON DELETE restrict
      ON UPDATE cascade;

  CREATE INDEX idxRecommends_customer ON Recommends_rt(customer);
  CREATE INDEX idxRecommends_book ON Recommends_rt(book);