-- Per i dettagli sul linguaggio di definizione dei dati di PostgreSQL, vedere:
--    http://www.postgresql.org/docs/9.1/static/ddl.html

-- Creazione dello schema.
-- http://www.postgresql.org/docs/9.1/static/sql-createschema.html
create schema azienda;

-- Normalmente, per poter creare un oggetto (un dominio, una tabella, etc…)
-- all'interno di uno schema è necessario qualificare il nome dell'oggetto
-- con il nome dello schema. Ad esempio, per creare un dominio chiamato 'cf_persona'
-- all'interno dello schema 'azienda', è necessario scrivere:
--
--    create domain azienda.cf_persona as <definizione del dominio>;
--
-- Similmente, per creare una tabella 'impiegato' bisogna scrivere:
--
--    create table azienda.impiegato ( <definizione della tabella> );
--
-- Se non si indica in modo esplicito uno schema, gli oggetti sono creati
-- all'interno di uno schema predefinito chiamato 'public'.
-- Il seguente comando PostgreSQL permette di cambiare lo schema predefinito
-- da 'public' ad 'azienda'. Dopo questo comando, gli oggetti vengono
-- automaticamente aggiunti allo schema 'azienda', a meno che lo schema
-- non sia dato in modo esplicito. Ciò consente di semplificare la notazione
-- nei comandi successivi. Per ulteriori dettagli, vedere:
--
--    http://www.postgresql.org/docs/9.1/static/ddl-schemas.html#DDL-SCHEMAS-PATH
--
set search_path to azienda,public;


-- Creazione dei domini (http://www.postgresql.org/docs/9.1/static/sql-createdomain.html)
create domain    cf_persona           as char(16);
create domain    nomi_persona         as varchar(15);
create domain    iniziali_persona     as char(1)
   constraint    carattere_alfabetico_maiuscolo
        check    (value similar to '[A-Z]'); -- http://www.postgresql.org/docs/9.1/static/functions-matching.html
create domain    data                 as date;
create domain    indirizzi            as varchar(35);
create domain    sesso_persona as char(1)
        check    (value = 'M' or value = 'F');
create domain    stipendi_persona     as numeric(9,2) -- http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
   constraint    stipendio_valido
        check    (value > 0.0);
create domain    numeri_dipartimento  as integer
   constraint    num_dip_valido
        check    (value between 1 and 10);
create domain    nomi_dipartimento    as varchar(20);
create domain    numeri_progetto      as integer
        check    (value > 0); 


-- Creazione delle tabelle.
-- http://www.postgresql.org/docs/9.1/static/sql-createtable.html
create table impiegato (
  -- Ciascun campo deve contenere almeno il nome del campo e il dominio.
  -- Le definizioni dei campi sono separate tra loro da una virgola.
  nome           nomi_persona,
  iniziale       iniziali_persona,
  cognome        nomi_persona,
  cf             cf_persona,
  data_nascita   data,
  indirizzo      indirizzi,
  sesso          sesso_persona,
  stipendio      stipendi_persona,
  supervisore    cf_persona,
  dip            numeri_dipartimento,

  -- Vincoli di tabella.
  -- Anche le definizioni dei vincoli sono separate tra loro da una virgola.
  constraint     impiegato_pk         primary key (cf),
  constraint     supervisore_fk       foreign key (supervisore) references impiegato(cf)
                                                                on update cascade
                                                                on delete set null

  -- Non è possibile creare la seguente chiave esterna a questo punto,
  -- perché la tabella 'dipartimento' non è stata ancora definita.
  --constraint     dipartimento_fk      foreign key (dip) references dipartimento(dnumero)
);

create table dipartimento (
  -- Quando i vincoli d'integrità fanno riferimento a un singolo campo,
  -- è possibile specificarli come vincoli di colonna, scrivendoli
  -- di seguito al dominio (vedi anche le tabelle seguenti).
  -- Si noti che la sintassi in alcuni casi è diversa da quella per i vincoli di tabella.
  dnumero        numeri_dipartimento  constraint dipartimento_pk primary key,
  dnome          nomi_dipartimento    constraint dnome_unico     unique
                                      constraint dnome_esiste    not null,
  manager        cf_persona           constraint manager_fk      references impiegato(cf)
                                                                 on update cascade
                                                                 on delete no action
                                                                 -- La seguente clausola rende la verifica del vincolo "differibile".
                                                                 -- Sarà spiegata dopo aver studiato le transazioni.
                                                                 deferrable initially immediate
                                      constraint manager_esiste  not null,
  data_inizio    data
);

-- A questo punto possiamo completare la definizione della tabella 'impiegato':
alter table impiegato add constraint dip_fk foreign key (dip) references dipartimento(dnumero)
                                                              on update cascade
                                                              on delete no action
                                                              deferrable initially immediate;

create table sedi_dipartimento (
  dnumero        numeri_dipartimento  references dipartimento(dnumero)
                                      on update cascade
                                      on delete cascade,
  dsede          indirizzi,
  
  -- Il nome del vincolo (la parte 'constraint <nome>') può essere omesso.
  -- In tal caso, il sistema assegna un nome automaticamente.
  -- Quando una chiave primaria è composta da più di un attributo,
  -- non è possibile specificare il vincolo di chiave primaria come vincolo di colonna,
  -- ossia la seguente sintassi è illegale:
  --
  --   create table sedi_dipartimento (
  --      dnumero numeri_dipartimento primary key,
  --      dsede   indirizzi           primary key -- Errore!
  --   );
  primary key    (dnumero, dsede)
);


create table progetto (
  pnumero        numeri_progetto      primary key,
  pnome          varchar(100)         not null unique, -- chiave alternativa
  -- Gli attributi destinazione di una chiave esterna possono essere omessi (dnumero in questo caso).
  -- In tal caso, si assume che la chiave esterna faccia riferimento alla chiave primaria
  -- della tabella destinazione (dipartimento in questo caso).
  dnum           numeri_dipartimento  references dipartimento
                                      on update cascade
                                      on delete no action,
  psede          indirizzi
);

create table lavora_a (
  imp            cf_persona           references impiegato
                                      on update cascade
                                      on delete cascade,
  progetto       integer              references progetto
                                      on update cascade
                                      on delete cascade,
  ore_settimana  integer              not null constraint numero_ore_valido check (ore_settimana > 0),
  
  primary key    (imp, progetto)
);

create table persona_a_carico (
  imp           cf_persona            references impiegato
                                      on update cascade
                                      on delete cascade,
  nome          nomi_persona,
  sesso         sesso_persona         not null,
  data_nascita  data                  not null,
  legame        varchar(30)           not null,

  primary key  (imp, nome)
);
