association class
-------------------------Drop tables in case you have created them------------------------
drop view all_movie_data;
drop table actor_nomination;
drop table award;
drop table actor_in_role;
drop table actor;
drop table role;
drop table character;
drop table movie;
------------------------set the default date format-------------------------
alter session set nls_date_format = 'YYYY-MM-DD';
-------------------------movie table------------------------
create table movie (
movie_id int generated always as identity start with 100 primary key,
title varchar(100),
release_date date
);
insert into movie(title, release_date) values('Sleuth', '1972-12-10');
insert into movie(title, release_date) values('Sleuth', '2007-10-12');
select * from movie;
-------------------------character table------------------------
create table character (
character_id int generated always as identity start with 200 primary key,
first_name varchar(20),
last_name varchar(30),
gender varchar(10)
);
insert into character (first_name, last_name, gender) values('Andrew','Wyke','Male');
insert into character (first_name, last_name, gender) values('Milo','Tindale','Male');
select * from character;
-------------------------role table------------------------
create table role (
movie_id int references movie,
character_id int references character,
primary key (movie_id, character_id)
);
insert into role (movie_id, character_id) values(100, 200);
insert into role (movie_id, character_id) values(100, 201);
insert into role (movie_id, character_id) values(101, 200);
insert into role (movie_id, character_id) values(101, 201);
select * from role;
-------------------------actor table------------------------
create table actor (
actor_id int generated always as identity start with 300 primary key,
first_name varchar(20),
last_name varchar(30),
middle_name varchar(20),
sag_name varchar(50),
birthdate date,
gender varchar(10)
);
insert into actor(first_name, last_name, middle_name, sag_name, birthdate, gender)
values('Lawrence','Olivier','Kerr','Laurence Olivier','1907-05-22','Male');
insert into actor(first_name, last_name, middle_name, sag_name, birthdate, gender)
values('Maurice','Micklewhite','Joseph','Michael Caine','1933-03-14','Male');
insert into actor(first_name, last_name, middle_name, sag_name, birthdate, gender)
values('David','Law','Jude Heyworth','Jude Law','1972-12-29','Male');
select * from actor;
-------------------------actor_in_role table------------------------
create table actor_in_role (
actor_id int references actor,
movie_id int,
character_id int,
primary key(actor_id, movie_id, character_id),
foreign key(movie_id, character_id) references role
);
insert into actor_in_role(actor_id, movie_id, character_id) values(300,100,200);
insert into actor_in_role(actor_id, movie_id, character_id) values(301,100,201);
insert into actor_in_role(actor_id, movie_id, character_id) values(301,101,200);
insert into actor_in_role(actor_id, movie_id, character_id) values(302,101,201);
select * from actor_in_role;
-------------------------award table------------------------
create table award (
award_id int generated always as identity start with 400 primary key,
award_name varchar(50),
award_category varchar(50)
);
insert into award(award_name, award_category)
values('Academy Awards','Best Actor in a leading Role');
insert into award(award_name, award_category)
values('Golden Globe Awards','Best Actor in a leading Role: Drama');
select * from award;
-------------------------actor_nomination table------------------------
create table actor_nomination (
award_id int references award,
actor_id int,
movie_id int,
character_id int,
primary key(award_id, actor_id, movie_id, character_id),
foreign key (actor_id, movie_id, character_id) references actor_in_role
);
insert into actor_nomination(actor_id, movie_id, character_id, award_id) values(300, 100, 200, 400);
insert into actor_nomination(actor_id, movie_id, character_id, award_id) values(301, 100, 201, 400);
insert into actor_nomination(actor_id, movie_id, character_id, award_id) values(300, 100, 200, 401);
insert into actor_nomination(actor_id, movie_id, character_id, award_id) values(301, 100, 201, 401);
select * from actor_nomination;
-------------------- Query showing all data --------------------------
create view all_movie_data as
select title, extract(year from release_date) as movie_year,
character.first_name || ' ' || character.last_name as character_name,
sag_name,
award_name as Nomination_Award,
award_category as Nomination_Category
from movie
join role
on movie.movie_id = role.movie_id
join character
on character.character_id = role.character_id
join actor_in_role
on actor_in_role.movie_id = role.movie_id and
actor_in_role.character_id = role.character_id
join actor
on actor.actor_id = actor_in_role.actor_id
left
outer
join actor_nomination
on actor_nomination.actor_id = actor_in_role.actor_id and
actor_nomination.movie_id = actor_in_role.movie_id and
actor_nomination.character_id = actor_in_role.character_id
left
outer
join award
on award.award_id = actor_nomination.award_id;
select * from all_movie_data;