-- phpMyAdmin SQL Dump
-- version 2.6.1
-- http://www.phpmyadmin.net

-- Structure de la table `client`
CREATE TABLE `client` (
`numcli` varchar(5) NOT NULL default '',
`nomcli` varchar(15) NOT NULL default '',
`prenomcli` varchar(15) NOT NULL default '',
`adrscli` varchar(25) NOT NULL default '',
`cpcli` int(11) NOT NULL default '0',
`villecli` varchar(20) NOT NULL default '',
PRIMARY KEY  (`numcli`)) 
ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Contenu de la table `client`
INSERT INTO `client` VALUES ('C0044', 'PITT', 'Brad', '8 rue de l''epoir', 59800, 'LILLE');
INSERT INTO `client` VALUES ('C0045', 'FORD', 'Harisson', '2 rue James Bond', 59540, 'CAUDRY');
INSERT INTO `client` VALUES ('C0046', 'ANDERSON', 'Pamela', '100C rue de la naivete', 59000, 'LILLE');
INSERT INTO `client` VALUES ('C0047', 'POULAIN', 'Amelie', '7 rue de Silicon', 59000, 'LILLE');
INSERT INTO `client` VALUES ('C0048', 'GARNACHE', 'Isabelle', 'rue de la madonne', 59000, 'LILLE');

-- Structure de la table `contenir`
CREATE TABLE `contenir` 
(`numpret` varchar(5) NOT NULL default '',
`numfilm` varchar(4) NOT NULL default '',
PRIMARY KEY  (`numpret`,`numfilm`))
ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Contenu de la table `contenir`
INSERT INTO `contenir` VALUES ('P1125', 'F007');
INSERT INTO `contenir` VALUES ('P1125', 'F010');
INSERT INTO `contenir` VALUES ('P1126', 'F008');
INSERT INTO `contenir` VALUES ('P1127', 'F005');
INSERT INTO `contenir` VALUES ('P1127', 'F006');
INSERT INTO `contenir` VALUES ('P1128', 'F005');
INSERT INTO `contenir` VALUES ('P1128', 'F009');
INSERT INTO `contenir` VALUES ('P1129', 'F006');
INSERT INTO `contenir` VALUES ('P1130', 'F005');
INSERT INTO `contenir` VALUES ('P1131', 'F005');
INSERT INTO `contenir` VALUES ('P1131', 'F010');

-- Structure de la table `film`

CREATE TABLE `film`
(`numfilm` varchar(4) NOT NULL default '',
`titrefilm` varchar(30) NOT NULL default '',
`genrefilm` varchar(20) default NULL,
`couleurtarif` varchar(10) NOT NULL default '',
PRIMARY KEY  (`numfilm`))
ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Contenu de la table `film`
INSERT INTO `film` VALUES ('F004', 'Les 7 mercenaires', 'Western', 'Jaune');
INSERT INTO `film` VALUES ('F005', 'La planète des singes', 'Science fiction', 'Vert');
INSERT INTO `film` VALUES ('F006', 'Les caprices d''un fleuve', 'Aventure', 'Rouge');
INSERT INTO `film` VALUES ('F007', 'La vie est belle', 'Comédie dramatique', 'Rouge');
INSERT INTO `film` VALUES ('F008', 'Indiana Jones', 'Aventure', 'Bleu');
INSERT INTO `film` VALUES ('F009', 'Mourir d''aimer', 'Drame psycho', 'Jaune');
INSERT INTO `film` VALUES ('F010', 'Le train sifflera 3 fois', 'Western', 'Rouge');
INSERT INTO `film` VALUES ('F011', 'Le soldat RYAN', NULL, 'Vert');

-- 
-- 
-- Structure de la table `pret`
-- 

CREATE TABLE `pret` (
`numpret` varchar(5) NOT NULL default '',
`datepret` date NOT NULL default '0000-00-00',
`mtpret` int(11) NOT NULL default '0',
`numcli` varchar(5) NOT NULL default '',
PRIMARY KEY  (`numpret`)) 
ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 
--  Contenu de la table `pret`
-- 
INSERT INTO `pret` VALUES ('P1125', '2014-07-17', 10, 'C0046');
INSERT INTO `pret` VALUES ('P1126', '2014-07-15', 6, 'C0046');
INSERT INTO `pret` VALUES ('P1127', '2014-07-18', 12, 'C0048');
INSERT INTO `pret` VALUES ('P1128', '2014-08-17', 11, 'C0044');
INSERT INTO `pret` VALUES ('P1129', '2014-08-06', 5, 'C0047');
INSERT INTO `pret` VALUES ('P1130', '2014-07-31', 7, 'C0045');
INSERT INTO `pret` VALUES ('P1131', '2014-09-03', 12, 'C0047');

--
-- Structure de la table `tarif`
CREATE TABLE `tarif` (
`couleurtarif` varchar(10) NOT NULL default '',
`mttarif` int(11) NOT NULL default '0',
PRIMARY KEY  (`couleurtarif`))
ENGINE=InnoDB DEFAULT CHARSET=latin1;

--  Contenu de la table `tarif`
INSERT INTO `tarif` VALUES ('Bleu', 6);
INSERT INTO `tarif` VALUES ('Jaune', 4);
INSERT INTO `tarif` VALUES ('Rouge', 5);
INSERT INTO `tarif` VALUES ('Vert', 7);

-- gestion des contraintes d'intégrité référentielle
-- lien entre un pret et le cliet correspondant
alter table pret
add foreign key(numcli) references client(numcli);

-- lien entre le pret et les films correspondant
ALTER TABLE `contenir` 
ADD FOREIGN KEY(numpret) references pret(numpret),
ADD FOREIGN KEY(numfilm) references film(numfilm);

-- lien entre un film et le tarif correspondant
ALTER TABLE `film` 
ADD FOREIGN KEY(`couleurtarif`) references tarif(`couleurtarif`);