myapp01.mysql

--
-- Create a very simple database to hold book and author information
--
CREATE TABLE IF NOT EXISTS `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text CHARACTER SET utf8,
  `rating` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
-- 'book_authors' is a many-to-many join table between books & authors
CREATE TABLE IF NOT EXISTS `book_author` (
  `book_id` int(11) NOT NULL DEFAULT '0',
  `author_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`book_id`,`author_id`),
  KEY `author_id` (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` text CHARACTER SET utf8,
  `last_name` text CHARACTER SET utf8,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Load some sample data
--
INSERT INTO `book` (`id`, `title`, `rating`) VALUES
(1, 'CCSP SNRS Exam Certification Guide', 5),
(2, 'TCP/IP Illustrated, Volume 1', 5),
(3, 'Internetworking with TCP/IP Vol.1', 4),
(4, 'Perl Cookbook', 5),
(5, 'Designing with Web Standards', 5);

INSERT INTO `book_author` (`book_id`, `author_id`) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 4),
(3, 5),
(4, 6),
(4, 7),
(5, 8);

INSERT INTO `author` (`id`, `first_name`, `last_name`) VALUES
(1, 'Greg', 'Bastien'),
(2, 'Sara', 'Nasseh'),
(3, 'Christian', 'Degu'),
(4, 'Richard', 'Stevens'),
(5, 'Douglas', 'Comer'),
(6, 'Tom', 'Christiansen'),
(7, 'Nathan', 'Torkington'),
(8, 'Jeffrey', 'Zeldman');

ALTER TABLE `book_author`
ADD CONSTRAINT `book_author_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;