drop table movies;
drop table persons;
drop type movie_t;
drop type person_list;
drop type person_t;

create type person_t as object
(lname varchar(40), fnames varchar(40));
/
create type person_list as table of ref person_t;
/
create or replace type movie_t as object
(title varchar(40), year_made int,
 director ref person_t,
 actors person_list);
/
create table movies of movie_t
  nested table actors store as movie_actors;

create table persons of person_t;

insert into persons (lname, fnames)
  select lname, fnames from max.persons;
  
insert into movies (title, year_made, director, actors)
  select title, year_made,
    (select ref(p) from persons p, max.persons mp
      where mp.id = m.director
      and mp.fnames = p.fnames and mp.lname = p.lname),
    cast(multiset
      (select ref(p) from persons p, max.persons mp, max.acts_in a
        where mp.id = a.actor and a.movie = m.id
        and mp.fnames = p.fnames and mp.lname = p.lname)
      as person_list)
    from max.movies m;
