DROP DATABASE if exists DCL2 ;
CREATE DATABASE DCL2 ;
USE DCL2 ;
CREATE TABLE PAYS (
pays_id INT NOT NULL AUTO_INCREMENT,
pays_libelle VARCHAR(50),
CONSTRAINT PK_PAYS PRIMARY KEY (pays_id)
) ENGINE=INNODB ;
INSERT INTO PAYS VALUES
(1,'FRANCE'),
(2,'USA'),
(3,'ALGERIE') ;
CREATE TABLE REALISATEURS (
real_id INT NOT NULL AUTO_INCREMENT,
real_nom VARCHAR(50),
real_prenom VARCHAR(50),
real_date_nais DATE,
real_nb_films_ecrits INT,
real_nb_films_real INT,
real_pays INT NOT NULL,
CONSTRAINT PK_REALISATEURS PRIMARY KEY (real_id),
FOREIGN KEY (real_pays) REFERENCES PAYS (pays_id)
) ENGINE=INNODB ;
INSERT INTO REALISATEURS VALUES
(1,'BESSON','Luc','1959-03-18',40,99,1),
(2,'LUCAS','Georges','1944-05-14',79,64,2),
(3,'CAMERON','James','1954-08-16',22,23,2),
(4,'BOON','Dany','1966-06-26',5,1,1) ;
CREATE TABLE FILMS (
film_id INT NOT NULL AUTO_INCREMENT,
film_titre VARCHAR(50),
film_genre1 VARCHAR(50),
film_genre2 VARCHAR(50),
film_date_sortie DATE,
film_pays_id INT NOT NULL,
film_real_id INT NOT NULL,
film_distributeur VARCHAR(50),
film_resume VARCHAR(150),
film_duree INT,
film_nb_ent_fr INT,
film_recette_usa FLOAT,
film_recette_monde FLOAT,
film_budget FLOAT ,
CONSTRAINT PK_FILMS PRIMARY KEY (film_id),
FOREIGN KEY (film_pays_id) REFERENCES PAYS (pays_id),
FOREIGN KEY (film_real_id) REFERENCES REALISATEURS (real_id)
) ENGINE=INNODB ;
INSERT INTO FILMS VALUES
(1,'SUBWAY','Policier','Drame','1985-04-10',1,1,'GAUMONT','description Subway',104,2917562,390659,1272637,2.6),
(2,'NIKITA','Drame','Romantique','1990-02-21',1,1,'GAUMONT','description Nikita',118,3787845,5017971,0,7.6),
(3,'STAR WARS - LE RETOUR DU JEDI','Action','SF','1983-10-19',2,2,'20th Century Fox','description Jedi',133,4263000,191648000,472000000,32),
(4,'AVATAR','Action','SF','2009-10-16',2,3,'20th Century Fox','description Avatar',170,12018251,760505847,2946271769,237),
(5,'BIENVENUE CHEZ LES CHTI','Comédie',NULL,'2008-02-27',1,4,'PATHE','description Les Chti',100,21000000,0,245000000,11) ;
CREATE VIEW STATISTIQUES AS SELECT film_id, film_duree, film_nb_ent_fr, film_recette_usa, film_recette_monde, film_budget
FROM FILMS ;
CREATE TABLE ACTEURS (
act_id INT NOT NULL AUTO_INCREMENT,
act_nom VARCHAR(50),
act_prenom VARCHAR(50),
act_date_nais DATE,
act_nb_films INT,
act_pays INT NOT NULL,
CONSTRAINT PK_ACTEURS PRIMARY KEY (act_id),
FOREIGN KEY (act_pays) REFERENCES PAYS (pays_id)
) ENGINE=INNODB ;
INSERT INTO ACTEURS VALUES
(1,'ADJANI','Isabelle','1955-06-27',42,1),
(2,'LAMBERT','Christophe','1957-03-29',64,1),
(3,'BOHRINGER','Richard','1942-06-16',132,1),
(4,'GALABRU','Michel','1922-10-27',277,1),
(5,'PARILLAUD','Anne','1960-05-06',35,1),
(6,'FORD','Harrison','1942-06-13',64,2),
(7,'FISHER','Carrie','1956-10-21',74,2),
(8,'SALDANA','Zoe','1978-06-19',31,2),
(9,'WEAVER','Sigourney','1949-10-08',66,2),
(10,'RENO','Jean','1948-06-30',75,1),
(11,'BOON','Dany','1966-06-28',23,1),
(12,'MERAD','Kad','1964-03-27',55,3) ;
CREATE TABLE CASTING (
cast_film_id INT NOT NULL,
cast_act_id INT NOT NULL,
cast_role VARCHAR(50),
cast_nb_jrs_tour INT,
CONSTRAINT PK_ACTEURS PRIMARY KEY (cast_film_id,cast_act_id),
FOREIGN KEY (cast_film_id) REFERENCES FILMS (film_id),
FOREIGN KEY (cast_act_id) REFERENCES ACTEURS (act_id)
) ENGINE=INNODB ;
INSERT INTO CASTING VALUES
(1,1,'HELENA',100),
(1,2,'FRED',100),
(1,3,'INSPECTEUR GESBERG',NULL),
(1,4,'LE FLEURISTE',35),
(1,10,'LE BATTEUR',20),
(2,5,'NIKITA',68),
(2,10,'VICTOR LE NETTOYEUR',9),
(3,6,'HAN SOLO',201),
(3,7,'PRINCESSE LEIA',203),
(4,8,'NEYTIRI',50),
(4,9,'Dr. Grace Augustine ',45),
(5,11,'ANTOINE BAILLEUL',125),
(5,12,'PHILIPPE ABRAMS',126);
