-- create tables
CREATE TABLE movie (
  id_movie INT NOT NULL,
  title VARCHAR(45) NOT NULL,
  year_pubblication INT NULL,
  PRIMARY KEY (id_movie)
);
CREATE TABLE actor (
  id_actor INT NOT NULL,
  name VARCHAR(45) NOT NULL,
  surname VARCHAR(45) NOT NULL,
  gender CHAR(1) NULL,
  PRIMARY KEY (id_actor)
);
CREATE TABLE category (
  id_category INT NOT NULL,
  name VARCHAR(45) NOT NULL,
  PRIMARY KEY (id_category)
);
CREATE TABLE actor_movie (
  id_actor INT NOT NULL,
  id_movie INT NOT NULL,
  movie_character VARCHAR(45) NULL,
  gender_character CHAR(1) NULL,
  CONSTRAINT fk_actor_movie_actor
    FOREIGN KEY (id_actor) REFERENCES actor (id_actor),
  CONSTRAINT fk_actor_movie_movie
    FOREIGN KEY (id_movie) REFERENCES movie (id_movie)
);
CREATE TABLE category_movie (
  id_category INT NOT NULL,
  id_movie INT NOT NULL,
  CONSTRAINT fk_category_movie_category
    FOREIGN KEY (id_category) REFERENCES category (id_category),
  CONSTRAINT fk_category_movie_movie
    FOREIGN KEY (id_movie) REFERENCES movie (id_movie)
);
-- insert value
INSERT INTO movie VALUES ('1', 'Carpe diem', '1989'),
('2', 'Mrs. Doubtfire', '1993'),('3', 'Matrix', '1999'),('4', 'Boys Don\'t Cry', '1999'),
('5', 'Ritorno al futuro', '1985'),('6', 'Matrix Reloaded', '2003');

INSERT INTO actor VALUES ('1', 'Dustin', 'Hoffman', 'M'),('3', 'Robin', 'Williams', 'M'),
('5', 'Keanu', 'Reeves', 'M'),('6', 'Laurence', 'Fishburne', 'M'),('7', 'Carrie-Anne', 'Moss', 'F'),
('8', 'Hilary', 'Swank', 'F'),('9', 'Micheal J.', 'Fox', 'M'),('10', 'Christopher', 'Lloyd', 'M');

INSERT INTO category VALUES ('1', 'COMEDY'),('2', 'DRAMA'),('3', 'SCI-FI'),
('4', 'ACTION'),('5', 'BIOGRAPHICAL'), ('6', 'ADVENTURE');

INSERT INTO category_movie VALUES (2, 1),(1, 2),(2, 2),(3, 3),(4, 3),(5, 4),(2, 4),(6, 5),(1, 5),(3, 5),(3, 6),(4, 6);

INSERT INTO actor_movie VALUES (1,1,'Prof. John Keating','M');
INSERT INTO actor_movie VALUES (3,2,'Daniel Hillard','M'),(3,2,'Mrs. Doubtfire','F');
INSERT INTO actor_movie VALUES (5,3,'Neo','M'),(6,3,'Morpeus','M'),(7,3,'Trinity','F');
INSERT INTO actor_movie VALUES (8,4,'Brandon Teena','M'),(8,4,'Teena Brandon','F');
INSERT INTO actor_movie VALUES (9,5,'Marty McFly','M'),(10,5,'Doc','M');
INSERT INTO actor_movie VALUES (5,6,'Neo','M'),(6,6,'Morpeus','M'),(7,6,'Trinity','F');
-- query