create.sql

CREATE DATABASE  IF NOT EXISTS `term_project`;
USE `term_project`;

CREATE TABLE `author` (
  `author_name` varchar(45) NOT NULL,
  `author_address` varchar(255) NOT NULL,
  `author_url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`author_name`)
) ENGINE=InnoDB;

CREATE TABLE `award` (
  `award_name` varchar(45) NOT NULL,
  `award_year` int NOT NULL,
  PRIMARY KEY (`award_name`,`award_year`)
) ENGINE=InnoDB;

CREATE TABLE `award_author` (
  `award_name` varchar(45) DEFAULT NULL,
  `award_year` int DEFAULT NULL,
  `author_name` varchar(45) DEFAULT NULL,
  KEY `award_idx` (`award_name`,`award_year`),
  KEY `author_name_idx` (`author_name`),
  CONSTRAINT `fk_author` FOREIGN KEY (`author_name`) REFERENCES `author` (`author_name`),
  CONSTRAINT `fk_award` FOREIGN KEY (`award_name`, `award_year`) REFERENCES `award` (`award_name`, `award_year`)
) ENGINE=InnoDB;

CREATE TABLE `award_book` (
  `award_name` varchar(45) DEFAULT NULL,
  `award_year` int DEFAULT NULL,
  `book_ISBN` varchar(255) DEFAULT NULL,
  KEY `award_idx` (`award_name`,`award_year`),
  KEY `book_ISBN_idx` (`book_ISBN`),
  CONSTRAINT `fk_award_book` FOREIGN KEY (`award_name`, `award_year`) REFERENCES `award` (`award_name`, `award_year`),
  CONSTRAINT `fk_book` FOREIGN KEY (`book_ISBN`) REFERENCES `book` (`book_ISBN`)
) ENGINE=InnoDB;

CREATE TABLE `book` (
  `book_ISBN` varchar(255) NOT NULL,
  `book_title` varchar(255) NOT NULL,
  `book_year` int NOT NULL,
  `book_price` decimal(10,0) NOT NULL,
  `book_category` varchar(255) NOT NULL,
  `author_name` varchar(45) NOT NULL,
  PRIMARY KEY (`book_ISBN`),
  KEY `author_name_idx` (`author_name`),
  CONSTRAINT `author_name` FOREIGN KEY (`author_name`) REFERENCES `author` (`author_name`)
) ENGINE=InnoDB;

CREATE TABLE `book_warehouse` (
  `book_ISBN` varchar(255) NOT NULL,
  `warehouse_code` varchar(45) NOT NULL,
  `number` int NOT NULL,
  PRIMARY KEY (`book_ISBN`,`warehouse_code`),
  KEY `warehouse_code` (`warehouse_code`),
  CONSTRAINT `book_warehouse_ibfk_1` FOREIGN KEY (`book_ISBN`) REFERENCES `book` (`book_ISBN`),
  CONSTRAINT `book_warehouse_ibfk_2` FOREIGN KEY (`warehouse_code`) REFERENCES `warehouse` (`warehouse_code`)
) ENGINE=InnoDB;

CREATE TABLE `customer` (
  `customer_email` varchar(255) NOT NULL,
  `customer_name` varchar(45) NOT NULL,
  `customer_address` varchar(255) NOT NULL,
  `customer_phone` varchar(45) NOT NULL,
  PRIMARY KEY (`customer_email`)
) ENGINE=InnoDB;

CREATE TABLE `reservation` (
  `Reservation_id` int NOT NULL,
  `Reservation_pickup_time` varchar(45) NOT NULL,
  `Reservation_date` varchar(45) NOT NULL,
  `customer_email` varchar(255) NOT NULL,
  `book_ISBN` varchar(255) NOT NULL,
  PRIMARY KEY (`Reservation_id`),
  KEY `customer_email_idx` (`customer_email`),
  KEY `book_ISBN_idx` (`book_ISBN`),
  CONSTRAINT `reservation_book_ISBN` FOREIGN KEY (`book_ISBN`) REFERENCES `book` (`book_ISBN`),
  CONSTRAINT `reservation_customer_email` FOREIGN KEY (`customer_email`) REFERENCES `customer` (`customer_email`)
) ENGINE=InnoDB;

CREATE TABLE `shopping_basket` (
  `basket_id` varchar(45) NOT NULL,
  `order_date` date NOT NULL,
  `customer_email` varchar(255) NOT NULL,
  PRIMARY KEY (`basket_id`),
  KEY `customer_email_idx` (`customer_email`),
  CONSTRAINT `customer_email` FOREIGN KEY (`customer_email`) REFERENCES `customer` (`customer_email`)
) ENGINE=InnoDB;

CREATE TABLE `shopping_basket_book` (
  `basket_id` varchar(45) NOT NULL,
  `book_ISBN` varchar(255) NOT NULL,
  `number` int NOT NULL,
  PRIMARY KEY (`book_ISBN`,`basket_id`),
  KEY `basket_id` (`basket_id`),
  CONSTRAINT `shopping_basket_book_ibfk_1` FOREIGN KEY (`basket_id`) REFERENCES `shopping_basket` (`basket_id`),
  CONSTRAINT `shopping_basket_book_ibfk_2` FOREIGN KEY (`book_ISBN`) REFERENCES `book` (`book_ISBN`)
) ENGINE=InnoDB;

CREATE TABLE `warehouse` (
  `warehouse_code` varchar(45) NOT NULL,
  `warehouse_address` varchar(255) NOT NULL,
  `warehouse_phone` varchar(45) NOT NULL,
  PRIMARY KEY (`warehouse_code`)
) ENGINE=InnoDB;