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

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

-- 
-- Table structure for table `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 ;

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

-- 
-- Table structure for table `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 ;

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

-- 
-- Table structure for table `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;

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

-- 
-- Table structure for table `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 ;

-- 
-- 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;