### Exercice 1

### 2)
W(c( p ),d,e)
assoc_1(a(p,e),b(p,e),c(p,e),f)
V(a( p ),b( p ),g(e))
X(g ( p ),h)
Y(k( p ))
Z(l( p ))
assoc_3 (k( p,e ),g(p,e),i)
assoc_4 (k (p,e), l(p,e),j)

### Exercice 2

INSERT INTO bateau (num_bateau,nom_bateau) VALUES
(1,'Titanic'),
(2,'Concordia'),
(3,'Black_Pearl'),
(4,'France')
(5,'La_Licorne')
(6,'Le_faucon_millenium')
(7,'L_astrolabe');

INSERT INTO escale (date_arrive, date_depart, num_bateau) VALUES
('09/04/1912', '15/04/1912',1)
('13/11/2014', '15/11/2014',2)
('02/08/2014', '03/10/2014',3)
('17/08/2014', '15/10/2014',4)
('12/07/2014', '25/07/2014',5)
('17/08/3016', '15/10/3016',6)
('01/08/1785', '05/10/1786',7);

INSERT INTO proprietaire (num_propriétaire,nom, prenom) VALUES
(1,'Costa', 'Croisiere'),
(2,'Sparrow', 'Jack'),
(3,'Macron', 'Manu'),
(4,'Haddock','Archibald'),
(5,'Solo', 'Hanne'),
(6,'De la Pérouse', 'Jean-François');

INSERT INTO conteneur (num_proprietaire,num_conteneur) VALUES
(1,1),
(1,2),
(2,3),
(3,4),
(4,5),
(5,6),
(6,7),
(7,7);

INSERT INTO mouvement (num_escale, num_conteneur, poids, pays, est_chargement, date_mouvement) VALUES
(1, 1, 12325, 'Etats-Unis', TRUE, '12/04/1912'),
(2, 1, 1325, 'Italie', FALSE, '14/11/2014'),
(3, 2, 12325, 'Bresil', TRUE, '01/09/2014'),
(4, 2, 20325, 'Poudlard', TRUE, '03/09/2014'),
(5, 1, 12325, 'Liechenstein', TRUE, '15/07/2014'),
(6, 1, 5678, 'Zimbabwe', FALSE, '20/08/3016');

-- Autres insertions pour les tests :

INSERT INTO bateau (nom_bateau) VALUES
('Porte-Conteneurs AG111'),
('Petit Porte-Conteneurs P12');

INSERT INTO proprietaire (nom, prenom) VALUES
('Baya', ''),
('Totruna', ''),
('Tex wix', ''),
('ART 24', '');

INSERT INTO conteneur (num_proprietaire) VALUES
(1),
(2),
(3),
(4),
(2),
(2),
(5),
(5);

INSERT INTO escale (date_arrive, date_depart, num_bateau) VALUES
('10/11/2014', '18/11/2014', 3),
('30/11/2014', '15/12/2014', 2);

INSERT INTO mouvement (num_escale, num_conteneur, poids, pays, est_chargement, date_mouvement) VALUES
(1, 2, 15248, 'Portugal', TRUE, '13/12/2014'),
(1, 3, 7854, 'Bresil', TRUE, '12/12/2014'),
(2, 4, 11589, 'Bresil', TRUE, '17/11/2014'),
(2, 5, 20325, 'Portugal', FALSE, '11/11/2014'),
(3, 5, 19874, 'Portugal', TRUE, '16/11/2014'),

JPP
INSERT INTO bateau (nom_bateau) VALUES
('Titanic'),
('Concordia'),
('Black_Pearl'),
('France'),
('La_Licorne'),
('Le_faucon_millenium'),
('Lastrolabe');

INSERT INTO escale (date_arrive, date_depart, num_bateau) VALUES
('09/04/1912', '15/04/1912', 1),
('13/11/2014', '15/11/2014',2),
('02/08/2014', '03/10/2014',3),
('17/08/2014', '15/10/2014',4),
('12/07/2014', '25/07/2014',5),
('17/08/3016', '15/10/3016',6),
('01/08/1785', '05/10/1786',7);

INSERT INTO proprietaire (nom, prenom) VALUES
('Costa', 'Croisiere'),
('Sparrow', 'Jack'),
('Macron', 'Manu'),
('Haddock','Archibald'),
('Solo', 'Hanne'),
('De la Pérouse', 'Jean-François');

INSERT INTO conteneur (num_proprietaire) VALUES
(1),
(1),
(2),
(3),
(4),
(5),
(6),
(7);

INSERT INTO mouvement (num_escale, num_conteneur, poids, pays, est_chargement, date_mouvement) VALUES
(1, 1, 12325, 'Etats-Unis', TRUE, '12/04/1912'),
(2, 1, 1325, 'Italie', FALSE, '14/11/2014'),
(3, 2, 12325, 'Bresil', TRUE, '01/09/2014'),
(4, 2, 20325, 'Poudlard', TRUE, '03/09/2014'),
(5, 1, 12325, 'Liechenstein', TRUE, '15/07/2014'),
(6, 1, 12325, 'Zimbabwe', FALSE, '11/12/2014');

JPP2
INSERT INTO bateau (nom_bateau, num_bateau) VALUES
('Titanic',1),
('Concordia',2),
('Black Pearl',3),
('France',4),
('La Licorne',5),
('Le faucon millenium',6),
('Lastrolabe',7);

INSERT INTO escale (date_arrive, date_depart, num_bateau, num_escale) VALUES
('09/04/1912', '15/04/1912', 1, 1),
('13/11/2014', '15/11/2014',2, 2),
('02/08/2014', '03/10/2014',3, 3),
('17/08/2014', '15/10/2014',4, 4),
('12/07/2014', '25/07/2014',5, 5),
('17/08/3016', '15/10/3016',6, 6),
('01/08/1785', '05/10/1786',6, 7);

INSERT INTO proprietaire (nom, prenom, num_proprietaire) VALUES
('Costa', 'Croisiere', 1),
('Sparrow', 'Jack', 2),
('Macron', 'Manu', 3),
('Haddock','Archibald', 4),
('Solo', 'Hanne', 5),
('De la Pérouse', 'Jean-François', 6);

INSERT INTO conteneur (num_proprietaire, num_conteneur) VALUES
(1, 1),
(1, 2),
(2, 3),
(3, 4),
(4, 5),
(5, 6),
(6, 7);

INSERT INTO mouvement (num_escale, num_conteneur, poids, pays, est_chargement, date_mouvement) VALUES
(1, 1, 12325, 'Etats-Unis', TRUE, '12/04/1912'),
(2, 1, 1325, 'Italie', FALSE, '14/11/2014'),
(3, 2, 12325, 'Bresil', TRUE, '01/09/2014'),
(4, 2, 20325, 'Poudlard', TRUE, '03/09/2014'),
(5, 1, 12325, 'Liechenstein', TRUE, '15/07/2014'),
(6, 1, 12325, 'Zimbabwe', FALSE, '11/12/2014');

Select poids from mouvement
where date_mouvement = '*/2014';

2) Select sum(poids) from mouvement
where extract (YEAR FROM date_mouvement)=2014;

3) SELECT COUNT(num_conteneur) from conteneur
JOIN mouvement using(num_conteneur)
JOIN escale using(num_escale)
where extract (YEAR FROM date_depart)=2014 and est_chargement= TRUE;

6) SELECT nom_bateau FROM bateau
JOIN escale using(num_bateau)
JOIN mouvement using(num_escale)
WHERE extract (YEAR FROM date_depart)=2014 and est_chargement= TRUE