-- Base exemple pour le cours SQL de début. --
-- version 0.0.0 du 09/11/2015 --
-- auteur : fk
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-- Génération d'un script SQL à partir d'une base Access V 1.0 - Pierre Loisel - CERTA
-- Script généré le 12/11/2015 - SGBD cible : MySql version 4
-- modifié par fk
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET NAMES utf8;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

-- Base de données: `gescom`
CREATE DATABASE IF NOT EXISTS `veto` ;
USE `veto` ;

-- --------------------------------------------------------
-- suppression des tables avant création et insertions
-- /!\ ne pas changer l'ordre des suppressions !!
-- --------------------------------------------------------
drop table if exists animal;
drop table if exists espece;
drop table if exists proprietaire;
-- --------------------------------------------------------
-- table animal
create table animal(
	id INT AUTO_INCREMENT not null,
	nom VARCHAR(50),
	date_naiss DATE,
	sexe enum("M", "F") not NULL,
	taille int not null,
	idEspece INT not null,
	idProprio INT not null,
	primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into animal(id,nom,date_naiss,sexe,taille,idEspece,idProprio) values
	(1,'Hastuce','2012-1-1',"F",45,1,1), 	(2,'Toto','2000-1-1',"M",15,6,2),
	(3,'Mozart','2012-3-1',20,1,12,1),		(4,'Rex','2008-1-1',"M",65, 2,5),
	(5,'Mirza','2008-1-1',"F",39,2,3), 		(6,'Roxi','2008-1-1',"M",48,2,4),
	(7,'Naga','2012-3-1',"F",120,5,4), 		(8,'Apricot','2011-3-1',"M",25,3,6), 
	(9,'Baya','2015-1-1',"F",23,3,9), 		(10,'Cook','2015-1-1',"M",7,11,8);

-- --------------------------------------------------------
-- table espece
create table espece(
	id INT AUTO_INCREMENT not null,
	nom VARCHAR(50),
	primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into espece(id,nom) values
	(1,'cheval'), (2,'chien'), (3,'chat'), (4,'poisson'), 
	(5,'serpent'), (6,'tortue'), (7,'reptile (autre)'), 
	(8,'rhinocéroce'), (9,'bovin'), (10,'ovin'), 
	(11,'oiseau'), (12,'lapin'), (13,'cobaye'), (14,'rongeur (autre)');

-- --------------------------------------------------------
-- table propriétaire
create table proprietaire(
	id INT AUTO_INCREMENT not null,
	nom VARCHAR(50),
	ville VARCHAR(50),
	primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into proprietaire(id,nom,ville) values
	(1,'Thomas','Strasbourg'), 	(2,'Amandine','Saverne'),
	(3,'Sarah','Molsheim'), 	(4,'Cüneyt','Strasbourg'), 
	(5,'Bilal','Strasbourg'), 	(6,'Magomed','Petrograd'), 
	(7,'Alik','Rhor'), 			(8,'Anatole','France'), 
	(9,'Djoumayatou','Strasbourg'), (10,'Kheda','Strasbourg');

-- --------------------------------------------------------
-- création des clés etrangères
alter table animal add foreign key (idEspece) references espece(id);
alter table animal add foreign key (idProprio) references proprietaire(id);
commit;