-- Select very uncertain movies: wide range of possible years
-- Assign confidence values uniformly to alternatives

create table U_Movies as
 select *, uniform as conf
 from Movies
 where [max(year) - min(year)] > 30;

-- Join in recent high-confidence ratings for these movies

create table M_Ratings as
 select title, year, date, rating
 from Ratings R, U_Movies M
 where R.movie_id = M.movie_id
  and R.date like '2005%'
  and conf(R) >= 0.4;

-- Get directors back using lineage

create table Dir_Ratings as
 select R.title, R.year, R.rating, M.director
 from M_Ratings R, U_Movies M
 where R ==> M;

-- Find directors of what are probably series: more than
-- one alternative with same director, different year

create table Series as
 select movie_id, director, uniform as conf
 from Movies M1
 where exists
  [select * from Movies M2 
   where M2.director = M1.director 
   and M2.year <> M1.year];

-- Find controversial series: high variance in ratings

select S.movie_id, S.director, 
 R1.rating as rating1, 
 R2.rating as rating2
 from Series S, Ratings R1, Ratings R2
 where S.movie_id = R1.movie_id
 and S.movie_id = R2.movie_id
 and R1.rating - R2.rating >=3;