DROP TABLE Casting ;

DROP TABLE Acteur ;

DROP TABLE Seance ;

DROP TABLE Film ;

DROP TABLE Cinema ;



CREATE TABLE Cinema(
  
idcine INT CONSTRAINT cinema_idcine_pk PRIMARY KEY,
  
nom VARCHAR(15),
  
adresse VARCHAR(50));



CREATE TABLE Film (
  
idfilm INT CONSTRAINT film_idfilm_pk PRIMARY KEY,
  
titre VARCHAR(20),
  
realisateur VARCHAR(20),
  
datesortie DATE);



CREATE TABLE Seance (
  
idseance INT CONSTRAINT seance_idseance_pk PRIMARY KEY,
  
heuredbt TIMESTAMP,
  
heurefin TIMESTAMP,
  
numsalle INT,
  
film INT CONSTRAINT seance_film_fk REFERENCES Film,
  
cine INT CONSTRAINT seance_cine_fk REFERENCES Cinema,
  
prix INT);



CREATE TABLE Acteur(
  
idacteur INT CONSTRAINT seance_idacteur_pk PRIMARY KEY,
  
nom VARCHAR(20),
  
prenom VARCHAR(20));



CREATE TABLE Casting(
  
idacteur INT CONSTRAINT casting_idacteur_fk REFERENCES Acteur,
  
idfilm INT CONSTRAINT casting_idfilm_fk REFERENCES Film,
  
personnage VARCHAR(30),
  
CONSTRAINT casting_idacteur_idfilm_pk PRIMARY KEY (idacteur,idfilm));



INSERT INTO Cinema VALUES (1,'UGC Ciné Cité','25 Route du Rhin, 67000, STRASBOURG');

INSERT INTO Cinema VALUES (2,'Pathé Brumath','ZI 67170 BRUMATH');

INSERT INTO Cinema VALUES (3,'Ciné Vox','17 rue des Francs-Bourgeois, 67000, STRASBOURG');

INSERT INTO Cinema VALUES (4,'Star St Exupéry','18 rue du 22 Novembre, 67000, STRASBOURG');

INSERT INTO Cinema VALUES (5,'Le Star','27 rue du Jeu des enfants, 67000, STRASBOURG');



INSERT INTO Film VALUES (1,'Chinatown','Roman Polanski',TO_DATE('1974','YYYY'));

INSERT INTO Film VALUES (2,'Blade','Stephen Norrington',TO_DATE('1998','YYYY'));

INSERT INTO Film VALUES (3,'Demolition man','Marco Brambilla',TO_DATE('1993','YYYY'));

INSERT INTO Film VALUES (4,'Rising Sun','Philip Kaufman',TO_DATE('1993','YYYY'));

INSERT INTO Film VALUES (5,'Impitoyable','Clint Eastwood',TO_DATE('1992','YYYY'));

INSERT INTO Film VALUES (6,'French Connection','William Friedkin',TO_DATE('1971','YYYY'));

INSERT INTO Film VALUES (7,'The Shining','Stanley Kubrick',TO_DATE('1980','YYYY'));



INSERT INTO Seance VALUES (1,TO_TIMESTAMP('14-00','HH24-MI'),TO_TIMESTAMP('15-50','HH24-MI'),1,1,1,8.2);

INSERT INTO Seance VALUES (2,TO_TIMESTAMP('16-00','HH24-MI'),TO_TIMESTAMP('17-50','HH24-MI'),1,1,1,8.2);

INSERT INTO Seance VALUES (3,TO_TIMESTAMP('18-00','HH24-MI'),TO_TIMESTAMP('19-50','HH24-MI'),1,1,1,8.2);

INSERT INTO Seance VALUES (4,TO_TIMESTAMP('20-00','HH24-MI'),TO_TIMESTAMP('21-50','HH24-MI'),1,1,1,8.2);

INSERT INTO Seance VALUES (5,TO_TIMESTAMP('22-00','HH24-MI'),TO_TIMESTAMP('23-50','HH24-MI'),1,1,1,8.2);

INSERT INTO Seance VALUES (6,TO_TIMESTAMP('14-00','HH24-MI'),TO_TIMESTAMP('15-50','HH24-MI'),3,1,2,8.3);

INSERT INTO Seance VALUES (7,TO_TIMESTAMP('16-00','HH24-MI'),TO_TIMESTAMP('17-50','HH24-MI'),3,1,2,8.3);

INSERT INTO Seance VALUES (8,TO_TIMESTAMP('18-00','HH24-MI'),TO_TIMESTAMP('19-50','HH24-MI'),3,1,2,8.3);

INSERT INTO Seance VALUES (9,TO_TIMESTAMP('20-00','HH24-MI'),TO_TIMESTAMP('21-50','HH24-MI'),3,1,2,8.3);

INSERT INTO Seance VALUES (10,TO_TIMESTAMP('22-00','HH24-MI'),TO_TIMESTAMP('23-50','HH24-MI'),3,1,2,8.3);

INSERT INTO Seance VALUES (11,TO_TIMESTAMP('14-00','HH24-MI'),TO_TIMESTAMP('15-50','HH24-MI'),2,5,1,8.2);

INSERT INTO Seance VALUES (12,TO_TIMESTAMP('16-00','HH24-MI'),TO_TIMESTAMP('17-50','HH24-MI'),2,5,1,8.2);

INSERT INTO Seance VALUES (13,TO_TIMESTAMP('18-00','HH24-MI'),TO_TIMESTAMP('19-50','HH24-MI'),2,5,1,8.2);

INSERT INTO Seance VALUES (14,TO_TIMESTAMP('20-00','HH24-MI'),TO_TIMESTAMP('21-50','HH24-MI'),2,5,1,8.2);

INSERT INTO Seance VALUES (15,TO_TIMESTAMP('22-00','HH24-MI'),TO_TIMESTAMP('23-50','HH24-MI'),2,5,1,8.2);

INSERT INTO Seance VALUES (16,TO_TIMESTAMP('14-00','HH24-MI'),TO_TIMESTAMP('15-50','HH24-MI'),2,5,2,8.3);

INSERT INTO Seance VALUES (17,TO_TIMESTAMP('16-00','HH24-MI'),TO_TIMESTAMP('17-50','HH24-MI'),2,5,2,8.3);

INSERT INTO Seance VALUES (18,TO_TIMESTAMP('18-00','HH24-MI'),TO_TIMESTAMP('19-50','HH24-MI'),2,5,2,8.3);

INSERT INTO Seance VALUES (19,TO_TIMESTAMP('20-00','HH24-MI'),TO_TIMESTAMP('21-50','HH24-MI'),2,5,2,8.3);

INSERT INTO Seance VALUES (20,TO_TIMESTAMP('22-00','HH24-MI'),TO_TIMESTAMP('23-50','HH24-MI'),2,5,2,8.3);

INSERT INTO Seance VALUES (21,TO_TIMESTAMP('22-00','HH24-MI'),TO_TIMESTAMP('23-50','HH24-MI'),1,6,5,5.0);





INSERT INTO Acteur VALUES (1,'Nicholson','Jack');

INSERT INTO Acteur VALUES (2,'Dunaway','Faye');

INSERT INTO Acteur VALUES (3,'Snipes','Wesley');

INSERT INTO Acteur VALUES (4,'Stallone','Silvester');

INSERT INTO Acteur VALUES (5,'Bullock','Sandra');

INSERT INTO Acteur VALUES (6,'Connery','Sean');

INSERT INTO Acteur VALUES (7,'Keitel','Harvey');

INSERT INTO Acteur VALUES (8,'Eastwood','Clint');

INSERT INTO Acteur VALUES (9,'Hackman','Gene');

INSERT INTO Acteur VALUES (10,'Freeman','Morgan');

INSERT INTO Acteur VALUES (11,'Scheider','Roy');



INSERT INTO Casting VALUES (1,1,'Jake J.J. Gittes');

INSERT INTO Casting VALUES (1,7,'Jack Torrance');

INSERT INTO Casting VALUES (2,1,'Evelyn Cross Mulwray');

INSERT INTO Casting VALUES (3,2,'Blade/Eric Brooks');

INSERT INTO Casting VALUES (3,3,'Simon Phoenix');

INSERT INTO Casting VALUES (4,3,'John Spartan');

INSERT INTO Casting VALUES (5,3,'Lt. Lenina Huxley');

INSERT INTO Casting VALUES (3,4,'Lt. Web Smith');

INSERT INTO Casting VALUES (6,4,'Capt. John Connor');

INSERT INTO Casting VALUES (7,4,'Lt. Web Smith');

INSERT INTO Casting VALUES (8,5,'William Bill Munny');

INSERT INTO Casting VALUES (9,5,'Little Bill Daggett');

INSERT INTO Casting VALUES (10,5,'Ned Logan');

INSERT INTO Casting VALUES (9,6,'Det. Jimmy Popeye Doyle');

INSERT INTO Casting VALUES (11,6,'Det. Buddy Cloudy Russo');
