queries-3-3-ch30

CREATE DATABASE library;
USE library;

CREATE TABLE categories (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL
);

CREATE TABLE clients (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL
);

CREATE TABLE authors (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 author_status ENUM('alive', 'deceased') NOT NULL
);

CREATE TABLE books (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 title VARCHAR(100) NOT NULL,
 author_id INT UNSIGNED NOT NULL,
 category_id INT UNSIGNED NOT NULL,
 FOREIGN KEY (author_id) REFERENCES authors (id),
 FOREIGN KEY (category_id) REFERENCES categories (id)
);

CREATE TABLE clients_books (
  client_id INT UNSIGNED,
  book_id INT UNSIGNED,
  FOREIGN KEY  (client_id) REFERENCES clients (id),
  FOREIGN KEY (book_id) REFERENCES books (id),
  PRIMARY KEY(client_id, book_id)
);