/* Chapter 16 - Examples */


CREATE DATABASE BooksDB;
use BooksDB;

CREATE TABLE Books
(
   BookID SMALLINT NOT NULL PRIMARY KEY,
   BookTitle VARCHAR(60) NOT NULL,
   Copyright YEAR NOT NULL
)
ENGINE=INNODB;

INSERT INTO Books
VALUES (12786, 'Letters to a Young Poet', 1934),
(13331, 'Winesburg, Ohio', 1919),
(14356, 'Hell\’s Angels', 1966),
(15729, 'Black Elk Speaks', 1932),
(16284, 'Noncomformity', 1996),
(17695, 'A Confederacy of Dunces', 1980),
(19264, 'Postcards', 1992),
(19354, 'The Shipping News', 1993);

CREATE TABLE Authors
(
   AuthID SMALLINT NOT NULL PRIMARY KEY,
   AuthFN VARCHAR(20),
   AuthMN VARCHAR(20),
   AuthLN VARCHAR(20)
)
ENGINE=INNODB;

INSERT INTO Authors
VALUES (1006, 'Hunter', 'S.', 'Thompson'),
(1007, 'Joyce', 'Carol', 'Oates'),
(1008, 'Black', NULL, 'Elk'),
(1009, 'Rainer', 'Maria', 'Rilke'),
(1010, 'John', 'Kennedy', 'Toole'),
(1011, 'John', 'G.', 'Neihardt'),
(1012, 'Annie', NULL, 'Proulx'),
(1013, 'Alan', NULL, 'Watts'),
(1014, 'Nelson', NULL, 'Algren');

CREATE TABLE AuthorBook
(
   AuthID SMALLINT NOT NULL,
   BookID SMALLINT NOT NULL,
   PRIMARY KEY (AuthID, BookID),
   FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
   FOREIGN KEY (BookID) REFERENCES Books (BookID)
)
ENGINE=INNODB;

INSERT INTO AuthorBook
VALUES (1006, 14356), (1008, 15729), (1009, 12786), (1010, 17695),
(1011, 15729), (1012, 19264), (1012, 19354), (1014, 16284);



CHANGE MASTER TO
   MASTER_HOST='server1',
   MASTER_USER='slave_acct',
   MASTER_PASSWORD='slave_pw',
   MASTER_LOG_FILE='server1-bin.001234',
   MASTER_LOG_POS=79;