drop database if exists company;

create database company;
use company;

create table employee ( 
FNAME varchar(10),
MINIT  char(1),
LNAME  varchar(15),
SSN   integer,
BDATE  date,
ADDRESS  varchar(20),
SEX char(1),
SALARY float,
SUPERSSN  integer,
DNO integer,
primary key(SSN));

create table department(
DNAME varchar(10),
DNUMBER integer,
MGRSSN  integer,
MGRSTARDATE date,
primary key(DNUMBER),
CONSTRAINT foreign key(MGRSSN) references employee(ssn));
 
create table dept_locations(
DNUMBER integer,
DLOCATION varchar(15),
primary key(DNUMBER,DLOCATION),
CONSTRAINT foreign key(DNUMBER) references DEPARTMENT(DNUMBER));


create table PROJECT(
PNAME varchar(10),
PNUMBER integer,
PLOCATION varchar(15),
DNUM integer,
primary key(PNUMBER),
CONSTRAINT foreign key(DNUM) references DEPARTMENT(DNUMBER));

create table WORKS_ON(
ESSN integer,
PNO integer,
hours integer,
primary key(ESSN,PNO),
CONSTRAINT foreign key(ESSN) references employee(ssn),
CONSTRAINT foreign key(PNO) references project(PNUMBER));

create table dependent(
essn integer,
dependentname varchar(25),
sex char(1),
bdate date,
relationship  varchar(10),
primary key (essn, dependentname),
CONSTRAINT foreign key(essn) references employee(ssn));


ALTER TABLE employee ADD CONSTRAINT foreign key(superssn) references employee(ssn); 

ALTER table employee ADD CONSTRAINT foreign key(DNO) references department(dnumber); 


insert into employee values ('Nikos','I','Vairis',7,'1970-06-13','Ikarou 7','M',1700.73,NULL,NULL);

insert into department values('Research',2,7,'2009-08-31');

insert into project values('nshield',19, 'Stafford',2);

insert into works_on values(7,19,20);

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

insert into dependent values(7,'Stelios Vairis','M','1996-08-08','son');

insert into employee values ('Manolis','I','Drakos',8,'1965-06-23','Faistou 8','M',1900.93,NULL,2);

update employee
set Dno=2,superssn=8
where ssn=7;

insert into department values('Develop',3,8,'2011-08-31');

insert into employee values ('Nikos','I','Kakas',27,'1980-06-13','Kalokairinou 7','M',1600.73,7,3);

insert into employee values ('Stelios','I','Perakos',26,'1975-06-13','Faistou 9','M',1670.73,7,2);

insert into employee values ('Manoliss','G','Perakakhs',22,'1979-06-13','Faistou 19','M',1170.73,8,3);

insert into project values('ithaki',29, 'Stafford',3);

insert into project values('TOWL',229, 'New York',3);

insert into works_on values(8,29,15);

insert into works_on values(26,29,11);

insert into works_on values(27,229,10);

insert into works_on values(22,229,30);