-- phpMyAdmin SQL Dump
-- version 2.9.1.1-Debian-2ubuntu1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: May 30, 2007 at 03:45 PM
-- Server version: 5.0.38
-- PHP Version: 5.2.1
-- 
-- Database: `bibliotheque`
-- 
DROP DATABASE `bibliotheque`;
CREATE DATABASE `bibliotheque` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `bibliotheque`;

-- --------------------------------------------------------

-- 
-- Table structure for table `auteur`
-- 

DROP TABLE IF EXISTS `auteur`;
CREATE TABLE `auteur` (
  `AUT_NUM` smallint(5) unsigned NOT NULL auto_increment,
  `AUT_NOM` varchar(60) NOT NULL,
  `AUT_PRENOM` varchar(60) NOT NULL,
  `AUT_DATE_NAISSANCE` date default NULL,
  PRIMARY KEY  (`AUT_NUM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `auteur`
-- 

INSERT INTO `auteur` (`AUT_NUM`, `AUT_NOM`, `AUT_PRENOM`, `AUT_DATE_NAISSANCE`) VALUES 
(1, 'Herbert', 'Brian', '1944-10-12'),
(2, 'Anderson', 'Kevin', '1962-03-27'),
(3, 'Herbert', 'Frank', '1920-10-08'),
(4, 'Stephenson', 'Neil', '1957-09-05'),
(5, 'Simmons', 'Dan', '1948-04-04'),
(6, 'Silveberg', 'Robert', '1954-05-18');

-- --------------------------------------------------------

-- 
-- Table structure for table `collection`
-- 

DROP TABLE IF EXISTS `collection`;
CREATE TABLE `collection` (
  `COL_NUM` tinyint(3) unsigned NOT NULL auto_increment,
  `COL_NOM` varchar(60) NOT NULL,
  `COL_EDITEUR` varchar(60) NOT NULL,
  PRIMARY KEY  (`COL_NUM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

-- 
-- Dumping data for table `collection`
-- 

INSERT INTO `collection` (`COL_NUM`, `COL_NOM`, `COL_EDITEUR`) VALUES 
(2, 'Ailleurs et Demain', 'Robert Laffont'),
(4, 'Ailleurs et Demain Classiques', 'Robert Laffont'),
(5, 'Antologie de la Science Fiction', 'Le Livre de Poche'),
(6, 'Special Suspens', 'Robert Laffont'),
(7, 'Science-Fiction', 'J''ai Lu'),
(8, 'SF', 'Le Livre de Poche');

-- --------------------------------------------------------

-- 
-- Table structure for table `ecrit`
-- 

DROP TABLE IF EXISTS `ecrit`;
CREATE TABLE `ecrit` (
  `ECR_NUM_LIVRE` smallint(5) unsigned NOT NULL,
  `ECR_NUM_AUTEUR` smallint(5) unsigned NOT NULL,
  `ECR_ANNEE` year(4) NOT NULL,
  PRIMARY KEY  (`ECR_NUM_LIVRE`,`ECR_NUM_AUTEUR`),
  KEY `ECR_NUM_AUTEUR` (`ECR_NUM_AUTEUR`),
  KEY `ECR_NUM_LIVRE` (`ECR_NUM_LIVRE`,`ECR_NUM_AUTEUR`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `ecrit`
-- 

INSERT INTO `ecrit` (`ECR_NUM_LIVRE`, `ECR_NUM_AUTEUR`, `ECR_ANNEE`) VALUES 
(1, 3, 1969),
(2, 3, 1972),
(3, 3, 1974),
(4, 3, 1976),
(5, 3, 1980),
(6, 3, 1983),
(7, 1, 2001),
(8, 1, 2002),
(9, 1, 1986),
(9, 3, 1986),
(10, 1, 2004),
(10, 2, 2004),
(11, 5, 1999),
(12, 5, 2000),
(13, 4, 2003),
(14, 5, 2001),
(15, 5, 2002),
(16, 5, 2003);

-- --------------------------------------------------------

-- 
-- Table structure for table `livre`
-- 

DROP TABLE IF EXISTS `livre`;
CREATE TABLE `livre` (
  `LIV_NUM` smallint(5) unsigned NOT NULL auto_increment,
  `LIV_TITRE` varchar(60) NOT NULL,
  `LIV_SS_TITRE` varchar(60) default NULL,
  `COL_NUM` tinyint(3) unsigned NOT NULL,
  `LIV_GENRE` enum('Policier','Science-Fiction','Historique') NOT NULL default 'Policier',
  PRIMARY KEY  (`LIV_NUM`),
  KEY `COL_NUM` (`COL_NUM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

-- 
-- Dumping data for table `livre`
-- 

INSERT INTO `livre` (`LIV_NUM`, `LIV_TITRE`, `LIV_SS_TITRE`, `COL_NUM`, `LIV_GENRE`) VALUES 
(1, 'Dune', ' ', 2, 'Science-Fiction'),
(2, 'Dune', 'Le Messie de Dune', 2, 'Science-Fiction'),
(3, 'Dune', 'L''empereur Dieu de Dune', 2, 'Science-Fiction'),
(4, 'Dune', 'Les hérétiques de Dune', 2, 'Policier'),
(5, 'Dune', 'Les enfants de Dune', 2, 'Science-Fiction'),
(6, 'L''Effet Lazare', '', 4, 'Science-Fiction'),
(7, 'Dune, la génèse', 'La Guerre des machines', 2, 'Science-Fiction'),
(8, 'Dune, la génèse', 'Le Jihad Butlérien', 2, 'Science-Fiction'),
(9, 'L''Homme de deux mondes', ' ', 4, 'Science-Fiction'),
(10, 'Dune, la génèse', 'La Bataille de Corrin', 2, 'Science-Fiction'),
(11, 'Hyperion', NULL, 2, 'Science-Fiction'),
(12, 'Hyperion', 'La Chute d''Hypérion', 2, 'Science-Fiction'),
(13, 'Snow Crash', 'Le samourai virtuel', 8, 'Science-Fiction'),
(14, 'Ilium', NULL, 2, 'Science-Fiction'),
(15, 'Olympus', 'Ilium II', 2, 'Science-Fiction'),
(16, 'Endymion', 'Hyperion III', 2, 'Policier');

-- 
-- Constraints for dumped tables
-- 

-- 
-- Constraints for table `ecrit`
-- 
ALTER TABLE `ecrit`
  ADD CONSTRAINT `ecrit_ibfk_1` FOREIGN KEY (`ECR_NUM_LIVRE`) REFERENCES `livre` (`LIV_NUM`) ON UPDATE CASCADE,
  ADD CONSTRAINT `ecrit_ibfk_2` FOREIGN KEY (`ECR_NUM_AUTEUR`) REFERENCES `auteur` (`AUT_NUM`) ON UPDATE CASCADE;

-- 
-- Constraints for table `livre`
-- 
ALTER TABLE `livre`
  ADD CONSTRAINT `livre_ibfk_1` FOREIGN KEY (`COL_NUM`) REFERENCES `collection` (`COL_NUM`) ON UPDATE CASCADE;