Notes

--<ScriptOptions statementTerminator=";"/>

ALTER TABLE Actor DROP CONSTRAINT Actor_Person_FK;

ALTER TABLE Actor_in_movie DROP CONSTRAINT Actor_in_m_Acto_FK;

ALTER TABLE Actor_in_movie DROP CONSTRAINT Actor_in_m_Movi_FK;

ALTER TABLE Director DROP CONSTRAINT Director_Person_FK;

ALTER TABLE Movie DROP CONSTRAINT Movie_Director_FK;

ALTER TABLE Movie_genre DROP CONSTRAINT Movie_gen_Gen_FK;

ALTER TABLE Movie_genre DROP CONSTRAINT Movie_gen_Movie_FK;

ALTER TABLE Actor DROP CONSTRAINT Actor_PK;

ALTER TABLE Actor_in_movie DROP CONSTRAINT Actor_in_movie_PK;

ALTER TABLE Director DROP CONSTRAINT Director_PK;

ALTER TABLE Genre DROP CONSTRAINT Genre_PK;

ALTER TABLE Movie DROP CONSTRAINT Movie_PK;

ALTER TABLE Movie_genre DROP CONSTRAINT Table1_PK;

ALTER TABLE Person DROP CONSTRAINT Person_PK;

DROP TABLE Actor;

DROP TABLE Actor_in_movie;

DROP TABLE Director;

DROP TABLE Genre;

DROP TABLE Movie;

DROP TABLE Movie_genre;

DROP TABLE Person;

CREATE TABLE Actor (
Actor_ID CHAR(5) NOT NULL,
Pers_ID CHAR(5)
)
DATA CAPTURE NONE;

CREATE TABLE Actor_in_movie (
AM_ID CHAR(5) NOT NULL,
movie_id CHAR(5) NOT NULL,
Actor_ID CHAR(5) NOT NULL,
Role VARCHAR(30)
)
DATA CAPTURE NONE;

CREATE TABLE Director (
Dir_ID CHAR(5) NOT NULL,
Pers_ID CHAR(5) NOT NULL
)
DATA CAPTURE NONE;

CREATE TABLE Genre (
Gen_ID CHAR(5) NOT NULL,
Genre VARCHAR(20) NOT NULL
)
DATA CAPTURE NONE;

CREATE TABLE Movie (
movie_id CHAR(5) NOT NULL,
Title VARCHAR(45) NOT NULL,
Description VARCHAR(250),
Review VARCHAR(100),
Dir_ID CHAR(5) NOT NULL
)
DATA CAPTURE NONE;

CREATE TABLE Movie_genre (
Genre_movie_ID CHAR(5) NOT NULL,
movie_id CHAR(5) NOT NULL,
Gen_ID CHAR(5) NOT NULL
)
DATA CAPTURE NONE;

CREATE TABLE Person (
Pers_ID CHAR(5) NOT NULL,
Name VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Birthday DATE,
Gender CHAR(5) NOT NULL
)
DATA CAPTURE NONE;

ALTER TABLE Actor ADD CONSTRAINT Actor_PK PRIMARY KEY
(Actor_ID);

ALTER TABLE Actor_in_movie ADD CONSTRAINT Actor_in_movie_PK PRIMARY KEY
(AM_ID);

ALTER TABLE Director ADD CONSTRAINT Director_PK PRIMARY KEY
(Dir_ID);

ALTER TABLE Genre ADD CONSTRAINT Genre_PK PRIMARY KEY
(Gen_ID);

ALTER TABLE Movie ADD CONSTRAINT Movie_PK PRIMARY KEY
(movie_id);

ALTER TABLE Movie_genre ADD CONSTRAINT Table1_PK PRIMARY KEY
(Genre_movie_ID);

ALTER TABLE Person ADD CONSTRAINT Person_PK PRIMARY KEY
(Pers_ID);

ALTER TABLE Actor ADD CONSTRAINT Actor_Person_FK FOREIGN KEY
(Pers_ID)
REFERENCES Person
(Pers_ID)
ON DELETE CASCADE;

ALTER TABLE Actor_in_movie ADD CONSTRAINT Actor_in_m_Acto_FK FOREIGN KEY
(Actor_ID)
REFERENCES Actor
(Actor_ID)
ON DELETE CASCADE;

ALTER TABLE Actor_in_movie ADD CONSTRAINT Actor_in_m_Movi_FK FOREIGN KEY
(movie_id)
REFERENCES Movie
(movie_id)
ON DELETE CASCADE;

ALTER TABLE Director ADD CONSTRAINT Director_Person_FK FOREIGN KEY
(Pers_ID)
REFERENCES Person
(Pers_ID)
ON DELETE CASCADE;

ALTER TABLE Movie ADD CONSTRAINT Movie_Director_FK FOREIGN KEY
(Dir_ID)
REFERENCES Director
(Dir_ID)
ON DELETE CASCADE;

ALTER TABLE Movie_genre ADD CONSTRAINT Movie_gen_Gen_FK FOREIGN KEY
(Gen_ID)
REFERENCES Genre
(Gen_ID)
ON DELETE CASCADE;

ALTER TABLE Movie_genre ADD CONSTRAINT Movie_gen_Movie_FK FOREIGN KEY
(movie_id)
REFERENCES Movie
(movie_id)
ON DELETE CASCADE;



insert into person values ('P100', 'Brando', 'Marlon', '1924-04-03', 'M');
insert into person values ('P101', 'Ford Coppola', 'Francis', '1939-04-07', 'M');
insert into person values ('P102', 'Al', 'Pacino', '1940-04-25', 'M'),
('P103', 'James', 'Caan', '1940-03-26', 'M'), ('P104', 'Brian', 'De Palma', '1940-09-11', 'M'),
('P105', 'Michel', 'Pfeiffer', '1958-04-29', 'F'), ('P106', 'Steven', 'Bauer', '1956-12-02', 'M'),
('P107', 'Andrew', 'Bergman', '1945-02-20', 'M'), ('P108', 'Mattthew', 'Broederick', '1962-03-21', 'M'),
('P109', 'Bruno', 'Kirby', '1949-04-28', 'M'), ('P110', 'Paolo', 'Sorrentino', '1970-05-31', 'M'),
('P111', 'Jude', 'Law', '1972-12-29', 'M'), ('P112', 'Diane', 'Keaton', '1946-01-05', 'F'),
('P113', 'Silvio', 'Orlando', '1957-06-30', 'M'), ('P114', 'John N.', 'Smith', '1943-07-31', 'M'),
('P115', 'George', 'Dzundza', '1945-07-19', 'M'), ('P116', 'Courtney B.', 'Vance', '1960-03-12', 'M'),
('P117', 'Gary', 'Oldman', '1958-03-21', 'M'), ('P118', 'Winona', 'Ryder', '1971-10-29', 'F'),
('P119', 'Anthony', 'Hopkins', '1937-12-31', 'M'), ('P120', 'Ridley', 'Scott', '1937-11-30', 'M'),
('P121', 'Julianne', 'Moore', '1960-12-03', 'M'), ('P122', 'Robert', 'De Niro', '1943-08-17', 'M'),
('P123', 'Robert', 'Duval', '1931-01-05', 'M'), ('P124', 'Joe', 'Wright', null , 'M'),
('P125', 'Lily', 'James', '1989-04-05', 'F'), ('P126', 'Ben', 'Mendelsohn', '1969-04-03', 'M'),
('P127', 'Eran', 'Creevy', null, 'M'), ('P128', 'Nicholas', 'Hoult', '1989-12-07', 'M'),
('P129', 'Felicity', 'Jones', '1983-10-17', 'F'), ('P130', 'George', 'Miller', '1945-03-03', 'M'),
('P131', 'Tom', 'Hardy', '1977-09-15', 'M'), ('P132', 'Charlize', 'Theron', '1975-08-07', 'F'),
('P133', 'Peter', 'Berg', '1964-03-11', 'M'), ('P134', 'Will', 'Smith', '1968-09-25', 'M'),
('P135', 'Jason', 'Bateman', '1969-01-14', 'M');

Insert into actor values ('A001', 'P100'), ('A002', 'P102'), ('A003', 'P103'), ('A004', 'P105'), ('A005', 'P106'), ('A006', 'P108'),
('A007', 'P109'), ('A008', 'P111'), ('A009', 'P112'), ('A010', 'P113'), ('A011', 'P115'), ('A012', 'P116'), ('A013', 'P117'),
('A014', 'P118'), ('A015', 'P119'), ('A016', 'P121'), ('A017', 'P122'), ('A018', 'P123'), ('A019', 'P125'),
('A020', 'P126'), ('A021', 'P128'), ('A022', 'P129'), ('A023', 'P131'), ('A024', 'P132'), ('A025', 'P134'), ('A026', 'P135');

insert into director Values ('D001', 'P101'), ('D002', 'P104'), ('D003', 'P107'), ('D004', 'P110'), ('D005', 'P114'), ('D006', 'P120'),
('D007', 'P127'), ('D008', 'P130'), ('D009', 'P133'), ('D010', 'P124');

insert into movie values
('M001', 'The godfather (1972)', 'The aging patriarch of an organized crime dynasty transfers control of his clandestine empire to his
reluctant son.', null, 'D001'),
('M002', 'Scarface (1983)', 'In Miami in 1980, a determined Cuban immigrant takes over a drug cartel and succumbs to greed.', null, 'D002'),
('M003', 'The Freshman (1990)', 'A NYC film school student accepts a job with a local mobster who resembles a famous cinema godfather and
who takes the young man under his wing, after demanding total loyalty.', null, 'D003'),
('M004', 'The Young Pope (2016 -)', 'The beginning of the pontificate of Lenny Belardo, alias Pius XIII,
the first American Pope in history.', null, 'D004'),
('M005', 'Dangerous Minds (1995)','An ex-Marine turned teacher struggles to connect with her students in an inner city school.', null, 'D005'),
('M006', 'Bram Stoker''s Dracula (1992)', 'The centuries old vampire Count Dracula comes to England to seduce
his barrister Jonathan Harker''s fiancée Mina Murray and inflict havoc in the foreign land.', null, 'D001'),
('M007', 'Hannibal (2001)', 'Living in exile, Hannibal Lecter tries to reconnect with now disgraced F.B.I. Agent Clarice Starling, and finds
himself a target for revenge from a powerful victim.', null, 'D006'),
('M008', 'The Godfather: Part II (1974)', 'The early life and career of Vito Corleone in 1920s New York is portrayed while his son, Michael,
expands and tightens his grip on the family crime syndicate.', null, 'D001'),
('M009', 'Darkest Hour (2017)', 'During the early days of World War II, the fate of Western Europe hangs on the newly-appointed British
Prime Minister Winston Churchill, who must decide whether to negotiate with Hitler, or fight on against incredible odds.', null, 'D010'),
('M010', 'Collide (2016)', 'An American backpacker gets involved with a ring of drug smugglers as their driver, though he winds up on the
run from his employers across Cologne high-speed Autobahn.', null, 'D007'),
('M011', 'Mad Max: Fury Road (2015)', 'A woman rebels against a tyrannical ruler in postapocalyptic Australia in search for her home-land
with the help of a group of female prisoners, a psychotic worshipper, and a drifter named Max.', null, 'D008'),
('M012', 'Hancock (2008)', 'Hancock is a superhero whose ill considered behavior regularly causes damage in the millions. He changes when
the person he saves helps him improve his public image.', null, 'D009');

insert into genre values
('001','Drama'),
('002','Crime'),
('003','Comedy'),
('004','Horror'),
('005','Biography'),
('006','Thriller'),
('007','History'),
('008','Action'),
('009','Adventure'),
('010','Sci-Fi'),
('011','Documentary'),
('012','Family');

insert into movie_genre values
('MG001','M001','001'),
('MG002','M001','002'),
('MG003','M002','001'),
('MG004','M002','002'),
('MG005','M003','002'),
('MG006','M003','003'),
('MG007','M004','001'),
('MG008','M005','005'),
('MG009','M005','001'),
('MG010','M006','004'),
('MG012','M007','001'),
('MG013','M007','002'),
('MG014','M007','006'),
('MG015','M008','001'),
('MG016','M008','002'),
('MG017', 'M009', '001'),
('MG018', 'M009', '005'),
('MG019', 'M009', '007'),
('MG020', 'M010', '008'),
('MG021', 'M010', '002'),
('MG022', 'M010', '006'),
('MG023', 'M011', '008'),
('MG024', 'M011', '009'),
('MG025', 'M011', '010'),
('MG026', 'M012', '008'),
('MG027', 'M012', '002'),
('MG028', 'M012', '001');

insert into actor_in_movie values
('AM001', 'M001', 'A001', 'Don Vito Corleone'),
('AM002', 'M001', 'A002', 'Michael Corleone'),
('AM003', 'M001', 'A003', 'Sony Corleone'),
('AM004', 'M002', 'A002', 'Tony Montana'),
('AM005', 'M002', 'A005', 'Many Ribera'),
('AM006', 'M002', 'A004', 'Elvira Hancock'),
('AM007', 'M003', 'A001', 'Carmine Sabatini'),
('AM008', 'M003', 'A006', 'Clark Kellogg'),
('AM009', 'M003', 'A007', 'Victor Ray'),
('AM010', 'M004', 'A008', 'Lenny Belardo'),
('AM011', 'M004', 'A009', 'Sister Mary'),
('AM012', 'M004', 'A010', 'Cardinal Voiello'),
('AM013', 'M005', 'A004', 'Louanne Johnson'),
('AM014', 'M005', 'A011', 'Hal Griffith'),
('AM015', 'M005', 'A012', 'Mr. George Grandey'),
('AM016', 'M006', 'A013', 'Dracula'),
('AM017', 'M006', 'A014', 'Mina Murray/Elisabeta'),
('AM018', 'M006', 'A015', 'Professor Abraham Van Helsing'),
('AM019', 'M007', 'A015', 'Hannibal Lecter'),
('AM020', 'M007', 'A016', 'Clarice Starling'),
('AM021', 'M007', 'A013', 'Mason Verger'),
('AM022', 'M008', 'A002', 'Michael'),
('AM023', 'M008', 'A018', 'Tom Hagen'),
('AM024', 'M008', 'A017', 'Vito Corleone'),
('AM025', 'M009', 'A013', 'Winston Churchill'),
('AM026', 'M009', 'A019', 'Elizabeth Layton'),
('AM027', 'M009', 'A020', 'King George VI'),
('AM028', 'M010', 'A021', 'Casey Stein'),
('AM029', 'M010', 'A022', 'Julliette Marne'),
('AM030', 'M010', 'A015', 'Hagen Kahl'),
('AM031', 'M011', 'A023', 'Max Rockatansky'),
('AM032', 'M011', 'A024', 'Imperator Furiosa'),
('AM033', 'M011', 'A021', 'Nux'),
('AM034', 'M012', 'A025', 'John Hancock'),
('AM035', 'M012', 'A024', 'Mary'),
('AM036', 'M012', 'A026', 'Ray');