create type artiste
/

create type oeuvre as object (titre VARCHAR(30), annee DATE, 
auteur REF artiste, cote NUMBER)
/

create type refoeuvre as object (refo REF oeuvre)
/

create type listeoeuvre as table of refoeuvre
/

create type artiste as object (nom VARCHAR(30),prenom VARCHAR(30),
style VARCHAR(15), loeuvres listeoeuvre)
/

create type adresse as object (rue VARCHAR(30), ville VARCHAR(30))
/

create type musee as object (nom VARCHAR(30), adr adresse)
/

create table Lesoeuvres of oeuvre;

create table Lesartistes of artiste
nested table loeuvres store as artoeuvres;

create table lesmusees of musee;

REM Proposez une implementation de la table objet-relationnelle Lesexpositions

create type exposition as object(oeuvres listeoeuvre,
mus REF musee, 
datedeb DATE,
duree NUMBER,
MEMBER FUNCTION tarif RETURN integer,
PRAGMA RESTRICT_REFERENCES(tarif,WNDS,WNPS))
/

create table Lesesexpositions of exposition
nested table oeuvres store as expoeuvre;

create or replace type body exposition as
MEMBER FUNCTION tarif RETURN integer is
tar NUMBER:=0;
vilmus VARCHAR(30);
BEGIN
SELECT sum(e.refo.cote) INTO tar
FROM THE (SELECT x.oeuvres FROM Lesesexpositions x WHERE VALUE(x)=self) e;
SELECT m.adr.ville into vilmus
FROM Lesmusees m 
WHERE REF(m)=self.mus;
IF vilmus='Paris' then tar:=tar*2 ;
end if;
return(tar);
END tarif;
END;
.
/

REM insertions dans les tables

INSERT INTO lesartistes VALUES('Estudianti','bidule','burlesque', listeoeuvre());
INSERT INTO lesoeuvres SELECT 'ExamBD','21-FEB-2000', REF(a), 3 FROM lesartistes a where a.nom='Estudianti';

INSERT INTO THE(select a.loeuvres from lesartistes a where a.nom='Estudianti') SELECT REF(e) from lesoeuvres e where e.titre='ExamBD';

INSERT INTO lesartistes VALUES('Lucie','x','first',listeoeuvre());
INSERT INTO lesartistes VALUES('Camille','x','second',listeoeuvre());
INSERT INTO lesoeuvres SELECT 'oeuvre1','20-JAN-1998',REF(b),2 FROM lesartistes b where b.nom='Lucie';
INSERT INTO lesoeuvres SELECT 'oeuvre2','23-JUL-1999',REF(b),3 FROM lesartistes b where b.nom='Camille'; 
INSERT INTO lesoeuvres SELECT 'oeuvre3','12-DEC-2000',REF(b),1 FROM lesartistes b where b.nom='Camille'; 

INSERT INTO THE (select a.loeuvres from lesartistes a where a.nom='Lucie')SELECT REF(e) from lesoeuvres e where e.titre='oeuvre1';
INSERT INTO THE (select a.loeuvres from lesartistes a where a.nom='Camille')SELECT REF(e) from lesoeuvres e where e.titre='oeuvre2';
INSERT INTO THE (select a.loeuvres from lesartistes a where a.nom='Camille')SELECT REF(e) from lesoeuvres e where e.titre='oeuvre3';

INSERT INTO lesmusees VALUES('lespontsdece',adresse('Gallieni','Angers'));
INSERT INTO lesmusees VALUES('maison',adresse('Foch','Angers'));
INSERT INTO lesmusees VALUES('cheztruc',adresse('Princes','Toulouse'));
INSERT INTO lesmusees VALUES('chezbidule',adresse('presdubiel','Faverges'));

INSERT INTO lesesexpositions SELECT listeoeuvre(),REF(m),'18-FEB-2000',5 FROM lesmusees m where m.nom='lespontsdece';

INSERT INTO lesesexpositions SELECT listeoeuvre(),REF(m),'18-FEB-2000',5 FROM lesmusees m where m.nom='maison';


insert into the (select e.oeuvres from lesesexpositions e where e.mus.nom='maison') select REF(e) from Lesoeuvres e where e.titre='oeuvre3' or e.titre='oeuvre2';

insert into the (select e.oeuvres from lesesexpositions e where e.mus.nom='lespontsdece') select REF(e) from Lesoeuvres e where e.titre='oeuvre1' or e.titre='oeuvre2';

REM Donnez la version SQL des 3 premieres requetes de l exercice precedent.

SELECT count(*) from lesoeuvres where cote>=3;

select e.titre,e.auteur.nom from lesoeuvres e;

select m.adresse.ville, m.nom
from lesmusees m
group by m.adresse.ville;

select e.mus.nom,e.tarif() from lesesexpositions e;

update lesmusees set adr=adresse('Gallieni','Paris') where nom='lespontsdece';

select e.mus.nom,e.tarif() from lesesexpositions e;

