create table areas (
    aid              integer, 
    aname            char(30),
    descr            char(100),
    journals         char(100),
    primary key (aid));

create table researchers (
    sin              char(15),
    name             char(30),
    hiringdate       char(6),
    aid              integer not null,
    primary key      (sin),
    foreign key  (sin) references areas);

create table professors (
    sin              char(15), 
    primary key  (sin),
    foreign key  (sin) references researchers);

create table gradstudents (
    sin              char(15), 
    supsin           char(15) not null,
    primary key  (sin),
    foreign key  (sin) references researchers
                                on delete cascade
                                on update cascade,
    foreign key  (supsin) references professors(sin) 
 				on delete cascade
 				on update cascade);

create table tenuredprofessors (
    sin              char(15), 
    primary key  (sin),
    foreign key  (sin) references professors);

create table untenuredprofessors (
    sin              char(15), 
    rating 	     real,
    primary key  (sin),
    foreign key  (sin) references professors);

create table mentors (
    untenuredsin     char(15), 
    tenuredsin       char(15),
    primary key  (untenuredsin),
    foreign key  (tenuredsin) references tenuredprofessors(sin)
						on delete cascade
                                		on update cascade,
    foreign key  (untenuredsin) references untenuredprofessors(sin)
						on delete cascade
                                		on update cascade);

create table volumes (
    isbn             integer, 
    title            char(100),
    classification   char(30),
    publisher        char(20),
    date             char(6),
    primary key  (isbn));

create table papers (
    pid              integer, 
    title            char(100),
    abstr            char(300),
    bibentry         char(250),
    isbn             integer not null,
    primary key  (pid),
    foreign key  (isbn) references volumes
					on delete cascade
                                		on update cascade);
 
create table writes (
    sin              char(15), 
    pid              integer,
    primary key  (sin,pid),
    foreign key  (sin) references researchers
					on delete cascade
                                		on update cascade,
    foreign key  (pid) references papers
				        on delete cascade
                                		on update cascade);

create table Editors (
    sin              char(15), 
    isbn             integer,
    primary key  (sin,isbn),
    foreign key  (sin) references researchers
					on delete cascade
                                		on update cascade,
    foreign key  (isbn) references volumes
					on delete cascade
                                		on update cascade);

create table locations (
    address          char(30),
    primary key (address));

create table residencephones (
    phonenr          char(10), 
    address          char(50),
    primary key  (phonenr),
    foreign key  (address) references locations
					on delete cascade
                                		on update cascade); 

create table lives (
    sin              char(15), 
    phonenr          char(10),
    address          char(50),
    primary key  (sin,address),
    foreign key  (phonenr) references residencephones
						on delete cascade
                                	  	   on update cascade,
    foreign key  (address) references residencephones
						on delete cascade
                                		   on update cascade,
    foreign key  (sin) references researchers
					on delete cascade
                                		on update cascade);