/* */
create table Progetto (
 Nome       varchar(100) not null primary key,
 Budget     numeric(11,2) default 0,
 Consegna   date not null
);
insert into Progetto values ('Dante'     , 200.0, '03/1/00');
insert into Progetto values ('B24'       , 500  , '06/1/01');
insert into Progetto values ('S3E'       , 250  , '12/1/05');
insert into Progetto values ('PostKantor', 500  , '05/1/00');
insert into Progetto values ('Mirror'    , 400  , '11/1/01');
commit;

/* */
create table Sede (
  CAP        character(5) not null primary key,
  Numero     varchar(10)  ,  
  Via        varchar(100)
);
insert into Sede values ('10015', 77 , 'Jervis'       );
insert into Sede values ('20025', 100, 'Gluk'         );
insert into Sede values ('80030', 34 , 'Campi Flegrei');
commit;

/* Il campo Sede realizza l'identificatore esterno tramite la relazione 
   Composizione. Il campo Impiegato realizza la relazione Direzione.
   L'attributo multivalore Telefono sfruttera` una tabella a parte.    */
create table Dipartimento (
  Nome        varchar(50)  not null,
  Sede        character(5) not null,
  Impiegato   varchar(6)   not null unique,
  primary key(Nome, Sede)
);
insert into Dipartimento values ('Amministr', '20025', '375');
insert into Dipartimento values ('Progetto' , '10015', '010');
insert into Dipartimento values ('Progetto' , '80030', '301');
insert into Dipartimento values ('CQ'       , '10015', '020');
insert into Dipartimento values ('CQ'       , '80030', '231');
insert into Dipartimento values ('Produzion', '80030', '210');
insert into Dipartimento values ('Ricerca'  , '10015', '030');
commit;

/* I campi DipNome, DipSede e Data realizzano la relazione Afferenza.  */
create table Impiegato (
 Matricola  varchar(6)   not null primary key,
 Nome       varchar(20)  not null,
 Cognome    varchar(20)  not null, 
 Stipendio  numeric(9,2) default 0,
 Eta        smallint     default 18,
 Data       date,
 DipNome    varchar(50)  not null,
 DipSede    character(5) not null,
 check(eta >= 18)
);
insert into Impiegato values ('101', 'Mario', 	  'Rossi', 	 34, 40, '01/15/00' , 'Produzion', '80030');
insert into Impiegato values ('103', 'Mario', 	  'Bianchi', 	 50, 35, '01/15/80' , 'Produzion', '80030');
insert into Impiegato values ('104', 'Luigi', 	  'Neri', 	 38, 61, '02/20/80' , 'Produzion', '80030');
insert into Impiegato values ('105', 'Nico', 	  'Bini', 	 44, 38, '02/20/80' , 'CQ'       , '80030');
insert into Impiegato values ('210', 'Marco', 	  'Celli', 	 49, 60, '02/20/80' , 'Progetto' , '80030');
insert into Impiegato values ('231', 'Siro', 	  'Bisi', 	 50, 60, '02/20/80' , 'Progetto' , '80030');
insert into Impiegato values ('252', 'Nico', 	  'Bini', 	 44, 70, '01/05/79' , 'Amministr', '20025');
insert into Impiegato values ('301', 'Sergio', 	  'Rossi', 	 34, 70, '05/05/79' , 'Amministr', '20025');
insert into Impiegato values ('375', 'Mario', 	  'Rossi', 	 50, 65, '01/01/66' , 'Amministr', '20025');
insert into Impiegato values ('010', 'Silvio', 	  'Zani', 	 40, 46, '10/15/97' , 'Amministr', '20025');
insert into Impiegato values ('020', 'Emma', 	  'Plafone', 	 55, 40, '11/03/89' , 'Progetto' , '10015');
insert into Impiegato values ('030', 'Antioco',   'Pegano',  55, 39, '11/03/89' , 'Progetto' , '10015');
insert into Impiegato values ('021', 'Flavia', 	  'Doretti', 	 34, 32, '09/01/97' , 'CQ'       , '10015');
insert into Impiegato values ('031', 'Lina', 'Datteri', 	 80, 31, '11/12/99' , 'Ricerca'  , '10015');
insert into Impiegato values ('022', 'Simone', 	  'Martini', 	 30, 30, '07/01/95' , 'CQ'       , '10015');
insert into Impiegato values ('032', 'Carlo', 	  'Vercelli', 79, 32, '03/01/98' , 'Ricerca'  , '10015');
insert into Impiegato values ('033', 'Martino',   'Sano', 	 25, 32, '02/15/96' , 'Ricerca'  , '10015');
commit;

alter table Dipartimento
 add constraint Composizione foreign key (Sede) references Sede(CAP);
alter table Dipartimento 
 add constraint Direzione foreign key (Impiegato) references Impiegato(Matricola);
alter table Impiegato
 add constraint Afferenza foreign key (DipNome,DipSede) references Dipartimento(Nome,Sede);
commit;

create table Partecipazione (
  Impiegato      VARCHAR(6)   NOT NULL,
  Progetto       varchar(100) not null,
  Inizio         date not null,
  primary key (Impiegato,Progetto),
  constraint lato01 foreign key (Impiegato) references Impiegato(Matricola),
  constraint lato1N foreign key (Progetto)  references Progetto(Nome)
);
insert into Partecipazione values ('231', 'PostKantor','01/01/95');
insert into Partecipazione values ('231', 'Mirror', 	'03/01/96');
insert into Partecipazione values ('105', 'PostKantor','02/01/95');
insert into Partecipazione values ('105', 'Mirror', 	'03/20/96');
insert into Partecipazione values ('210', 'PostKantor','01/01/94');
insert into Partecipazione values ('101', 'PostKantor','01/10/94');
insert into Partecipazione values ('103', 'PostKantor','01/10/94');
insert into Partecipazione values ('104', 'PostKantor','01/11/94');
insert into Partecipazione values ('030', 'Dante', 	'01/01/97');
insert into Partecipazione values ('030', 'S3E', 	'02/01/98');
insert into Partecipazione values ('030', 'B24', 	'03/01/97');
insert into Partecipazione values ('032', 'Dante', 	'01/15/97');
insert into Partecipazione values ('032', 'S3E', 	'02/04/98');
insert into Partecipazione values ('032', 'B24', 	'03/28/97');
insert into Partecipazione values ('031', 'Dante', 	'01/14/97');
insert into Partecipazione values ('031', 'B24', 	'04/01/97');
insert into Partecipazione values ('020', 'Dante', 	'01/01/98');
insert into Partecipazione values ('020', 'B24', 	'07/01/97');
insert into Partecipazione values ('021', 'Dante', 	'01/10/98');
insert into Partecipazione values ('022', 'B24', 	'07/05/97');
commit;

create table DipartimentoTelefono (
  DipNome        varchar(50)  not null,
  DipSede        character(5) not null,
  Telefono	 varchar(20)  not null,
  primary key (DipNome,DipSede,Telefono),
  foreign key (DipNome,DipSede) references Dipartimento(Nome,Sede)
);

insert into DipartimentoTelefono values ('Amministr', '20025', '0212345');
insert into DipartimentoTelefono values ('Amministr', '20025', '0212346');
insert into DipartimentoTelefono values ('Amministr', '20025', '0212347');
insert into DipartimentoTelefono values ('Progetto' , '10015', '0125528');
insert into DipartimentoTelefono values ('Progetto' , '10015', '0125529');
insert into DipartimentoTelefono values ('Progetto' , '80030', '0818533');
insert into DipartimentoTelefono values ('CQ'       , '10015', '0125521');
insert into DipartimentoTelefono values ('CQ'       , '80030', '0818533');
insert into DipartimentoTelefono values ('CQ'       , '80030', '0818534');
insert into DipartimentoTelefono values ('Produzion', '80030', '0818533');
insert into DipartimentoTelefono values ('Ricerca'  , '10015', '0125525');
insert into DipartimentoTelefono values ('Ricerca'  , '10015', '0125526');
commit;