create table employee
	( fname		varchar(15)	not null,
	  minit		char,
	  lname		varchar(15)	not null,
	  ssn		int		primary key not null,
	  bdate		char(8),
	  address	varchar(30),
	  sex		char,
	  salary	int,
	  superssn	int		references employee(ssn),
	  dno		int		references department(dnumber) not null );


create index emp_dno_idx on employee(dno);
create index emp_name_idx on employee(lname);



create table department
	( dname		varchar(15)	unique not null,
	  dnumber	int		primary key not null,
	  mgrssn	int		references employee(ssn),
	  mgrstartdate	char(8)		);


create index dept_mgr_idx on department(mgrssn);



create table dept_locations
	( dnumber	int		references department(dnumber) not null,
	  dlocation	varchar(15)	not null,
	primary key (dnumber,dlocation) );


create index dept_loc_dno_idx on dept_locations(dnumber);



create table project
	( pname		varchar(15)	unique not null,
	  pnumber	int		primary key not null,
	  plocation	varchar(15),
	  dnum		int		references department(dnumber) );


create index project_dnum_idx on project(dnum);



create table works_on
	( essn		int		references employee(ssn) not null,
	  pno		int		references project(pnumber) not null,
	  hours		int,
	primary key (essn,pno)		);


create index works_ssn_idx on works_on(essn);
create index works_pno_idx on works_on(pno);



create table dependent
	( essn		int		references employee(ssn) not null,
	  dependent_name varchar(15)	not null,
	  sex		char,
	  bdate		char(8),
	  relationship	varchar(8),
	primary key (essn,dependent_name) );


create index dependent_ssn_idx on dependent(essn);



insert into department
	values ( 'Research', 5, 333445555, '22-MAY-78' );

insert into department
	values ( 'Administration', 4, 987654321, '01-JAN-85' );

insert into department
	values ( 'Headquarters', 1, 888665555, '19-JUN-71' );


insert into employee
	values ( 'James', 'E', 'Borg', 888665555, '10-NOV-27', '450 Stone, Houston, TX', 'M', 55000, null, 1 );

insert into employee
	values	( 'Franklin', 'T', 'Wong', 333445555, '18-DEC-45', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5);

insert into employee
	values	( 'John', 'B', 'Smith', 123456789, '09-JAN-55', '731 Fondren, Houston, TX', 'M', 30000, 333445555, 5 );

insert into employee
	values	( 'Jennifer', 'S', 'Wallace', 987654321, '20-JUN-31', '291 Berry, Bellair, TX', 'F', 43000, 888665555, 4 );

insert into employee
	values	( 'Alicia', 'J', 'Zelaya', 999887777, '19-JUL-58', '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4 );

insert into employee
	values	( 'Ramesh', 'K', 'Narayan', 666884444, '15-SEP-52', '975 Fire Oak, Humble, TX', 'M', 38000, 333445555, 5 );

insert into employee
	values	( 'Joyce', 'A', 'English', 453453453, '31-JUL-62', '5631 Rice, Houston, TX', 'F', 25000, 333445555, 5 );

insert into employee
	values	( 'Ahmad', 'V', 'Jabbar', 987987987, '29-MAR-59', '980 Dallas, Houston, TX', 'M', 25000, 987654321, 1 );

insert into dept_locations
	values ( 1, 'Houston' );

insert into dept_locations
	values ( 4, 'Stafford' );

insert into dept_locations
	values ( 5, 'Bellaire' );

insert into dept_locations
	values ( 5, 'Sugarland' );

insert into dept_locations
	values ( 5, 'Houston' );


insert into project
	values ( 'ProductX', 1, 'Bellaire', 5 );

insert into project
	values ( 'ProductY', 2, 'Sugarland', 5 );

insert into project
	values ( 'ProductZ', 3, 'Houston', 5 );

insert into project
	values ( 'Computerization', 10, 'Stafford', 4 );

insert into project
	values ( 'Reorganization', 20, 'Houston', 1 );

insert into project
	values ( 'Newbenefits', 30, 'Stafford', 4 );


insert into works_on
	values ( 123456789, 1, 32 );

insert into works_on
	values ( 123456789, 2, 7 );

insert into works_on
	values ( 666884444, 3, 40 );

insert into works_on
	values ( 453453453, 1, 20 );

insert into works_on
	values ( 453453453, 2, 20 );

insert into works_on
	values ( 333445555, 2, 10 );

insert into works_on
	values ( 333445555, 3, 10 );

insert into works_on
	values ( 333445555, 10, 10 );

insert into works_on
	values ( 333445555, 20, 10 );

insert into works_on
	values ( 999887777, 30, 30 );

insert into works_on
	values ( 999887777, 10, 10 );

insert into works_on
	values ( 987987987, 10, 35 );

insert into works_on
	values ( 987987987, 30, 5 );

insert into works_on
	values ( 987654321, 30, 20 );

insert into works_on
	values ( 987654321, 20, 15 );

insert into works_on
	values ( 888665555, 20, null );


insert into dependent
	values ( 333445555, 'Alice', 'F', '05-APR-76', 'DAUGHTER' );

insert into dependent
	values ( 333445555, 'Theodore', 'M', '25-OCT-73', 'SON' );

insert into dependent
	values ( 333445555, 'Joy', 'F', '03-MAR-48', 'SPOUSE' );

insert into dependent
	values ( 987654321, 'Abner', 'M', '29-FEB-32', 'SPOUSE' );

insert into dependent
	values ( 123456789, 'Michael', 'M', '01-JAN-78', 'SON' );

insert into dependent
	values ( 123456789, 'Alice', 'F', '31-DEC-78', 'DAUGHTER' );

insert into dependent
	values ( 123456789, 'Elizabeth', 'F', '05-MAY-57', 'SPOUSE' );
