create table EMPLOYEES
(SSN char(9) not null,
 LAST_NAME varchar(30) not null,
 FIRST_NAME varchar(30) not null,
 ANNUAL_SALARY money not null,
 BIRTH_DATE datetime not null,

 primary key (SSN)
)
go

insert EMPLOYEES values ('111223333','Smith','Bob',64000,'1932-04-07')
insert EMPLOYEES values ('444332222','Brucks','Eric',32768,'1969-01-06')
go

create table JOB_TITLES
(  JOB_TITLE_CODE char(6) not null,
   JOB_TITLE varchar(80) not null,

   primary key (JOB_TITLE_CODE)
)
go

insert JOB_TITLES values ("141955", "ASSOCIATE DIRECTOR, Computer Center")
insert JOB_TITLES values ("130891", "Associate Professor of Computer Science")
insert JOB_TITLES values ("110689", "Head, Department of Computer Science")
insert JOB_TITLES values ("132955", "Instructor, Computer Science")
insert JOB_TITLES values ("020730", "Hazardous Waste Specialist")
insert JOB_TITLES values ("172734", "Director, Computer Center")
go

create table POSITIONS 
( PCN char(6) not null,
  JOB_TITLE_CODE1 char(6) not null,

  primary key (PCN),
  constraint positions_constr
       foreign key (JOB_TITLE_CODE1) references JOB_TITLES (JOB_TITLE_CODE)
)
go

insert POSITIONS values ('137112', '130891')
insert POSITIONS values ('341288', '141955')
insert POSITIONS values ('120033', '132955')
insert POSITIONS values ('723401', '020730')
insert POSITIONS values ('908654', '172734')
go

create table INCUMBENTS
( SSN char(9) not null,
  PCN char(6) not null,
 
  primary key (SSN, PCN),
  constraint incumbents_constr1
       foreign key (SSN) references EMPLOYEES (SSN),
  constraint incumbents_constr2
         foreign key (PCN) references POSITIONS (PCN)
)
go

insert INCUMBENTS values ("111223333", "723401")
insert INCUMBENTS values ("444332222", "120033")
go
