//2
//A
CREATE TABLE Client_C (
    login VARCHAR2(20) PRIMARY KEY,
    nomClient VARCHAR2(30),
    prenomClient VARCHAR2(30)
);

CREATE TABLE Film (
    numFilm number PRIMARY KEY,
    nomFilm VARCHAR2(30)
);

CREATE TABLE Location_L (
    login VARCHAR2(20) REFERENCES Client_C,
    numFilm number REFERENCES Film,
    dateLocation DATE,
    PRIMARY KEY(login, numFilm)
);

//B
INSERT INTO Client_C VALUES('RABAN_PROP1', 'AURIEL', 'Samuel');
INSERT INTO Client_C VALUES('RABAN_PROP2', 'BEL', 'Commode');
INSERT INTO Client_C VALUES('BANANA', 'O', 'Beng');

INSERT INTO Film VALUES(01, 'La petite sirene d alarme');
INSERT INTO Film VALUES(02, 'Retour vers le présent');
INSERT INTO Film VALUES(03, 'starsheep trooper');
INSERT INTO Film VALUES(04, 'Splatoon');
INSERT INTO Film VALUES(05, 'BESOIN DE VIOLENCE 4');
INSERT INTO Film VALUES(06, 'Il étais une fois chez moi');

INSERT INTO Location_L VALUES('RABAN_PROP1', 01, '02-JAN-2002');
INSERT INTO Location_L VALUES('RABAN_PROP2', 02, '05-MAR-2004');
INSERT INTO Location_L VALUES('BANANA', 03, '07-DEC-2012');
INSERT INTO Location_L VALUES('RABAN_PROP1', 06, '02-JAN-2002');
INSERT INTO Location_L VALUES('RABAN_PROP2', 04, '05-MAR-2004');
INSERT INTO Location_L VALUES('BANANA', 05, '07-DEC-2012');

//C
CREATE VIEW MES_LOCATIONS (nomClient, numFilm, dateLocation)
AS SELECT nomClient, numFilm, dateLocation FROM Location_L NATURAL JOIN Client_C WHERE login = USER;

commit;

//3

//B
GRANT SELECT ON Client_C TO RABAN_PROP2;

//4

//A

GRANT SELECT ON MES_LOCATIONS TO RABAN_PROP2;
GRANT SELECT,INSERT,DELETE ON Location_L TO RABAN_PROP2;
GRANT INSERT,UPDATE(nomClient, prenomClient) ON Client_C TO RABAN_PROP2;
GRANT INSERT ON Film TO RABAN_PROP2;

//D

SELECT * FROM Client_C;
SELECT * FROM Film;
SELECT * FROM location_L;

COMMIT;