--
-- Host: localhost    Database: user104
-- ------------------------------------------------------


--
-- Table structure for table `authors`
--

CREATE TABLE `authors` (
  `author_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `full_name` varchar(100) NOT NULL,
  `address` varchar(255) NOT NULL,
  PRIMARY KEY (`author_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `authors`
--

INSERT INTO `authors` VALUES (1,'Лев Толстой','Россия, Ясная Поляна'),(2,'Фёдор Достоевский','Россия, Санкт-Петербург'),(3,'Александр Пушкин','Россия, Москва');

--
-- Table structure for table `books`
--

CREATE TABLE `books` (
  `book_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `publish_year` year(4) NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  `publisher_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`book_id`),
  KEY `author_id` (`author_id`),
  KEY `publisher_id` (`publisher_id`),
  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`),
  CONSTRAINT `books_ibfk_2` FOREIGN KEY (`publisher_id`) REFERENCES `publishers` (`publisher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `books`
--

INSERT INTO `books` VALUES (1,'Война и мир',2020,1,1),(2,'Преступление и наказание',2019,2,2),(3,'Евгений Онегин',2021,3,3);

--
-- Table structure for table `booksellers`
--

CREATE TABLE `booksellers` (
  `bookseller_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  PRIMARY KEY (`bookseller_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `booksellers`
--

INSERT INTO `booksellers` VALUES (1,'Книготорг'),(2,'Библио-Мир'),(3,'Дом книги');

--
-- Table structure for table `publishers`
--

CREATE TABLE `publishers` (
  `publisher_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `address` varchar(255) NOT NULL,
  PRIMARY KEY (`publisher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `publishers`
--

INSERT INTO `publishers` VALUES (1,'Эксмо','Москва, ул. Примерная, 10'),(2,'АСТ','Москва, ул. Книжная, 15'),(3,'Питер','Санкт-Петербург, Невский пр., 25');

--
-- Table structure for table `purchases`
--

CREATE TABLE `purchases` (
  `purchase_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `book_id` int(10) unsigned NOT NULL,
  `bookseller_id` int(10) unsigned NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`purchase_id`),
  KEY `book_id` (`book_id`),
  KEY `bookseller_id` (`bookseller_id`),
  CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`),
  CONSTRAINT `purchases_ibfk_2` FOREIGN KEY (`bookseller_id`) REFERENCES `booksellers` (`bookseller_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `purchases`
--

INSERT INTO `purchases` VALUES (1,1,1,950.00),(2,2,2,720.50),(3,3,3,680.00);


